5 Replies - 1735 Views - Last Post: 09 December 2014 - 12:26 PM Rate Topic: -----

#1 mikenola   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 09-April 14

AddEmployee.Java and ResultSetTableModel.java connection problem

Posted 07 December 2014 - 09:19 AM

Hi

I am having a problem getting a connection to my local db from the code provided in the class. It is from Deitel how to program early objects 9th edition and is using JDBC to attach to a database called Employees.

the console errors are

Exception in thread "main" java.lang.RuntimeException: Uncompilable source code - exception java.lang.ClassNotFoundException is never thrown in body of corresponding try statement
	at addemployee.AddEmployee.<init>(AddEmployee.java:46)
	at addemployee.AddEmployee.main(AddEmployee.java:167) 


but the code for the catch block is never reached (which is what line 46 and 167 are).

The first code is
  public AddEmployee() 
    {
        super("Add Employees");

        try 
        {
            //create TableModel for results of query select * from employees
            
            tableModel = new ResultSetTableModel(JDBC_DRIVER, DATABASE_URL,
                    USERNAME, PASSWORD, DEFAULT_QUERY);
            
           
            connection = tableModel.getConnection();
        }//end try


My Mysql instance is running and the employees database is reachable through the command prompt and the MYSQL Workbench.
but I cannot get this to connect.

below are the two complete java files in the project.

AddEmployee.Java

//
package addemployee;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import javax.swing.JFrame;
import javax.swing.JTable;
import javax.swing.JButton;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JOptionPane;
import javax.swing.table.TableModel;

public class AddEmployee extends JFrame {
    //jdbc driver and database url

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DATABASE_URL = "jdbc:mysql://localhost/employees";
    static final String USERNAME = "deitel";
    static final String PASSWORD = "deitel";

//default query retreives all data from employees table
    static final String DEFAULT_QUERY = "SELECT * FROM employees";

    private Connection connection;
    private Statement statement;
    private ResultSetTableModel tableModel;
    private JTable table;
    private JButton addEmployee;
    private JButton addSalariedEmployee;
    private JButton addCommissionEmployee;
    private JButton addBasePlusCommissionEmployee;
    private JButton addHourlyEmployee;

    public AddEmployee() 
    {
        super("Add Employees");

        try 
        {
            //create TableModel for results of query select * from employees
            
            tableModel = new ResultSetTableModel(JDBC_DRIVER, DATABASE_URL,
                    USERNAME, PASSWORD, DEFAULT_QUERY);
            
           
            connection = tableModel.getConnection();
        }//end try
        catch (ClassNotFoundException cnfex) 
        {
            System.err.println("Failed to load jdbc driver. ");
            System.out.println(cnfex.getMessage());
            cnfex.printStackTrace();
            System.exit(1);
            
        }//end catch
    
    catch (SQLException sqlex)  
        {
            System.err.println("unalbe lto connect");
        sqlex.printStackTrace();
            
        System.exit(1);
    }//end catch

    //if connected to database, set up gui
    JPanel topPanel = new JPanel();
    addEmployee  = new JButton("Add Generic Employee");

    addEmployee.addActionListener (

    new ButtonHandler());
        topPanel.add (addEmployee);

    //create 4 buttons
    addSalariedEmployee  = new JButton("Add Salaried Employee");
    addSalariedEmployee.addActionListener (
    new ButtonHandler());

    addCommissionEmployee  = new JButton("Add Commissioned Employee");

    addCommissionEmployee.addActionListener (
    new ButtonHandler());

   addBasePlusCommissionEmployee  = new JButton("Add Base Plus Commissioned Employee");

    addBasePlusCommissionEmployee.addActionListener (
    new ButtonHandler());

        addHourlyEmployee  = new JButton("Add Hourly Employee");

    addHourlyEmployee.addActionListener (
    new ButtonHandler());

        //add four buttons to center panel
    JPanel centerPanel = new JPanel();

    centerPanel.add (addSalariedEmployee);

    centerPanel.add (addCommissionEmployee);

    centerPanel.add (addBasePlusCommissionEmployee);

    centerPanel.add (addHourlyEmployee);

    JPanel inputPanel = new JPanel();

    inputPanel.setLayout (

    new BorderLayout());
        inputPanel.add (topPanel, BorderLayout.NORTH);

    inputPanel.add (centerPanel, BorderLayout.CENTER);

    table  = new JTable((TableModel) tableModel);

    setLayout(

    new BorderLayout());
        add(inputPanel, BorderLayout.NORTH);

    add(

    new JScrollPane(table), BorderLayout.CENTER);

        setSize(

    800, 300);
        setVisible(true);
        setDefaultCloseOperation(DISPOSE_ON_CLOSE);

    addWindowListener(
             new WindowAdapter() {
                    
    public void windowClosed(WindowEvent event) {
        tableModel.disconnectFromDatabase();
        System.exit(0);
    }//end windowclosed
}//end windowadapter inner class
);// end addwindowListener
    }//end addemployees

    //add employee t database
    private void addEmployee(String query) 
    {
        try 
        {
            statement = connection.createStatement();
            statement.executeUpdate(query);
            tableModel.setQuery(DEFAULT_QUERY);

        }//end try
        catch (SQlException sqlex)
        {
            sqlex.printStacktrace();
        }//edn catch
    }//end addEmployee

    public static void main(String[] args) {
        new AddEmployee();
}//end main
    //inner class ButtonHandeler

    private class ButtonHandler implements ActionListener 
    {    
     public void actionPerformed(ActionEvent event) 
     {
        String socialSecurityNumber = JOptionPane.showInputDialog(
                "Enmployee Social Security Number");
        String insertQuery = "", displayQuery = "";

        //add generic employee to table of empllyees
        if (event.getSource() == addEmployee) 
        {
            String firstName = 
                    JOptionPane.showInputDialog("First Name");
            String lastName = 
                    JOptionPane.showInputDialog("Last Name");
            String birthday = 
                    JOptionPane.showInputDialog("Birthday");
            String employeeType = 
                    JOptionPane.showInputDialog("Emploee Type");
            String departmentName = 
                    JOptionPane.showInputDialog("Department Name");
            insertQuery = " INSERT INTO employees VALUES (' " +
                    socialSecurityNumber + "', '" + firstName + "', '" +
                    lastName = "', '" + birthday + "', '" + employeeType + 
                    "', '" + departmentName + "') '";
            displayQuery = "SELECT socialSecurityNumber, firstName, "
                    + "lastName, birthday, employeeType, departmentName FROM "
                    + "employees";

        }//end if
        
        //add salaried employee to the table salariedEmployee
        else if (event.getSource() == addSalariedEmployee) {
            double weeklySalary = Double.parseDouble(JOptionPane.showInputDialog("Weekly Salary"));
            insertQuery = "INSERT INTO salariedEmployee VALUES (' "
                    + socialSecurityNumber + " ','" + weeklySalary + " ','0')";
            displayQuery = "SELECT employees.socialSecurityNumber, "
                    + "employees.firstName, employees.lastName, "
                    + "employees.employeeType, salariedEmployees.weeklySalary "
                    + "FROM employees, salariedEmployees Where "
                    + "employees.socialSecurityNumber = " +
                    "salariedEmployees.socialSecuritnumber ";
        }//end salaried employee else if
        
        //add commission emplyee to tabel commissionEmployees
        else if ( event.getSource()== addCommissionEmployee ) 
        {
            int grossSales =Integer.parseInt(
            JOptionPane.showInputDialog("Gross Sales:"));
            double commissionRate = Double.parseDouble(
            JOptionPane.showInputDialog("Commission Rate: "));
            insertQuery = "INSERT INTO commissionEmployees VALUES ( ' " 
            + socialSecurityNumber + " ',' " + grossSales 
            + " ',' " + commissionRate + " ' , '0' )";  
            displayQuery = "SELECT employees.socialSecurityNumber, " +
                    "employees.firstName, employees.lastName, " +
                    "employees.employeeType, commissionEmployees.grossSales, " +
                    "commissionEmployees.commissionRate FROM employees, " +
                    "commissionEmployees WHERE employees.SocialSecurityNumber ="
                    +"commissionEmployees.socialSecurityNumber";
                              
        }//end elsif add commissonEmployees
        
        //add base pluse commission employee to table
        else if (event.getSource() == addBasePlusCommissionEmployee )
        {
            int grossSales = Integer.parseInt(
            JOptionPane.showInputDialog("Gross Sales: "));
            double commissionRate=Double.parseDouble(
            JOptionPane.showInputDialog("Commission Rate: "));
            double baseSalary = Double.parseDouble(
            JOptionPane.showInputDialog(" Base Salary : "));
            insertQuery = "SELECT employees.socialSecurityNumber " +
            "VALUES ( ' " + socialSecurityNumber + " ', '" + grossSales +
            " ', ' " +commissionRate + " ', ' " +baseSalary + " ','0')";
            displayQuery = "SELECT employees.socialSecurityNumber,  " +
            "employees.firstName, employees.lastName, employees."+
             "employeeType, basePlusCommissionEmplyees.baseSalary, "+
             "basePlusCommissionEmployees.grossSales, basePluse" +
             "commissionEmployees.commissionRate FROM employees, " +
             "basePlusCommissionEmployees WHERE " +
             "employees.socialSecurityNumber = " +
             "basePlusCommissionEmployees.socialSecurityNumber";          
                              
        }//end else if addbasepluscommission
        
        //add hourly employee to table hourly Employees
        else
        {
            int hours = Integer.parseInt(
            JOptionPane.showInputDialog("Hours : "));
            double wage = Double.parseDouble(
            JOptionPane.showInputDialog("Wages : "));
            insertQuery = "INSERT INTO hourlyEmployees VALUES ( ' "+
            socialSecurityNumber + " ', '" + hours + "' , '" + wage +
                    " ','0')";
            displayQuery = "SELECT employees.socialSecurityNumber , " +
            "employees.firstName, employees.lastName, " +
            "employees.employeeType, hourlyEmployees.hours, " +
            "hourlyEmployees.wage FROM employees, hourlyEmployees " +
            "WHERE employees.socialSecurityNumber = "+
             "hourlyEmployees.socialSecurityNumber ";
            
        }//end else hourly employee
        
        //execute insert query and display employee inof
        try
        {
            statement =connection.createStatement();
            statement.executeUpdate (insertQuery);
            //display the employee info
            tableModel.setQuery(displayQuery);
            
        }//end try
        catch (SQLException exception)
        {
          JOptionPane.showMessageDialog(null, exception.toString());
        }//end catch
     }//end method actionPerformed
    }// end innerclass buttonhandler
}//end class AddEmployees



and ResultsSetTableModel.java

package addemployee;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.swing.table.AbstractTableModel;

public final class ResultSetTableModel extends AbstractTableModel {
    
    

    private final Connection connection;
    private final Statement statement;
    private ResultSet resultSet;
    private ResultSetMetaData metaData;
    private int numberOfRows;

    // keep track of database connection status
    private boolean connectedToDatabase = false;

    // constructor initializes resultSet and obtains its meta data object;
    // determines number of rows
    public ResultSetTableModel(String driver, String url, String username,
            String password, String query) throws SQLException {
        // connect to database
        connection = DriverManager.getConnection(url, username, password);

        // create Statement to query database
        statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        // update database connection status
        connectedToDatabase = true;

        // set query and execute it
        setQuery(query);
    } // end constructor ResultSetTableModel

    
      
    // get class that represents column type
    @Override
    public Class getColumnClass(int column) throws IllegalStateException {
        // ensure database connection is available
        if (!connectedToDatabase) {
            throw new IllegalStateException("Not Connected to Database");
        }

        // determine Java class of column
        try {
            String className = metaData.getColumnClassName(column + 1);

            // return Class object that represents className
            return Class.forName(className);
        } // end try
        catch (ClassNotFoundException exception) {
            exception.printStackTrace();
        } catch (SQLException exception) {
            exception.printStackTrace();
        } // end catch
         // end catch

        return Object.class; // if problems occur above, assume type Object
    } // end method getColumnClass

    // get number of columns in ResultSet
    @Override
    public int getColumnCount() throws IllegalStateException {
        // ensure database connection is available
        if (!connectedToDatabase) {
            throw new IllegalStateException("Not Connected to Database");
        }

        // determine number of columns
        try {
            return metaData.getColumnCount();
        } // end try
        catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } // end catch

        return 0; // if problems occur above, return 0 for number of columns
    } // end method getColumnCount

    // get name of a particular column in ResultSet
    @Override
    public String getColumnName(int column) throws IllegalStateException {
        // ensure database connection is available
        if (!connectedToDatabase) {
            throw new IllegalStateException("Not Connected to Database");
        }

        // determine column name
        try {
            return metaData.getColumnName(column + 1);
        } // end try
        catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } // end catch

        return ""; // if problems, return empty string for column name
    } // end method getColumnName

    // return number of rows in ResultSet
    
    @Override
    public int getRowCount() throws IllegalStateException {
        // ensure database connection is available
        if (!connectedToDatabase) {
            throw new IllegalStateException("Not Connected to Database");
        }

        return numberOfRows;
    } // end method getRowCount

    // obtain value in particular row and column
  
    public Object getValueAt(int row, int column)
            throws IllegalStateException {
        // ensure database connection is available
        if (!connectedToDatabase) {
            throw new IllegalStateException("Not Connected to Database");
        }

        // obtain a value at specified ResultSet row and column
        try {
            resultSet.absolute(row + 1);
            return resultSet.getObject(column + 1);
        } // end try
        catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } // end catch

        return ""; // if problems, return empty string object
    } // end method getValueAt

    // set new database query string
    public void setQuery(String query)
            throws SQLException, IllegalStateException {
        // ensure database connection is available
        if (!connectedToDatabase) {
            throw new IllegalStateException("Not Connected to Database");
        }

        // specify query and execute it
        resultSet = statement.executeQuery(query);

        // obtain meta data for ResultSet
        metaData = resultSet.getMetaData();

        // determine number of rows in ResultSet
        resultSet.last();                   // move to last row
        numberOfRows = resultSet.getRow();  // get row number      

        // notify JTable that model has changed
        fireTableStructureChanged();
    } // end method setQuery

    // close Statement and Connection               
    public void disconnectFromDatabase() {
        if (connectedToDatabase) {
            // close Statement and Connection            
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } // end try                                 
            catch (SQLException sqlException) {
                sqlException.printStackTrace();
            } // end catch                               
            finally // update database connection status
            {
                connectedToDatabase = false;
            } // end finally                             
        } // end if
    } // end method disconnectFromDatabase         

   }  // end class ResultSetTableModel



The output is supposed to look like the attached png file


the attached file is the employee2.sql file that creates the database for mysql. The file extension was changed to txt so this site would let me upload it.

If anyone can please help me get past this connection issue I would appreciate it.

My jdbc connector is in the correct place, and I have even added to my project library and it does not alter things.

Thank you
Mike

Attached image(s)

  • Attached Image

Attached File(s)



Is This A Good Question/Topic? 0
  • +

Replies To: AddEmployee.Java and ResultSetTableModel.java connection problem

#2 g00se   User is online

  • D.I.C Lover
  • member icon

Reputation: 3704
  • View blog
  • Posts: 16,980
  • Joined: 20-September 08

Re: AddEmployee.Java and ResultSetTableModel.java connection problem

Posted 07 December 2014 - 11:31 AM

Quote

but the code for the catch block is never reached (which is what line 46 and 167 are).


It's never reached as it can never run - your code won't compile. Read the error message more carefully - you're trying to catch an exception which is not thrown. Kludge: for now, change ClassNotFoundException to Exception and clean it up properly as proof to yourself that you understand the (important) problem later
Was This Post Helpful? 0
  • +
  • -

#3 mikenola   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 09-April 14

Re: AddEmployee.Java and ResultSetTableModel.java connection problem

Posted 07 December 2014 - 11:59 AM

g00se,
I appreciate your response, now i have to figure out how to clean up this code. It was provided by instructor and was supposed to work. I cannot do the rest of the assignment until this works, so I am pretty hosed.

I tried renaming ClassNotFoundException to Exception, and in the first catch block in AddEmployee.java, and as you guessed It throws up and more problems appear.

It seems to be looking for my jar in the classpath, but it is there. It is on the machine classpath, I have included it in the library of the package, and the database can be found through the command prompt and mysql.

I will try to find out more information about ClassNotFoundException and see if I can figure out where I have hosed up.

This post has been edited by andrewsw: 07 December 2014 - 01:50 PM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#4 CasiOo   User is offline

  • D.I.C Lover
  • member icon

Reputation: 1578
  • View blog
  • Posts: 3,551
  • Joined: 05-April 11

Re: AddEmployee.Java and ResultSetTableModel.java connection problem

Posted 07 December 2014 - 01:27 PM

There is no getConnection() method in your ResultSetTableModel
No ClassNotFoundException is thrown inside the try-block, so no need to catch it

It should probably catch SQLException instead
Was This Post Helpful? 0
  • +
  • -

#5 mikenola   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 09-April 14

Re: AddEmployee.Java and ResultSetTableModel.java connection problem

Posted 07 December 2014 - 02:50 PM

CasiOo,
I don't mean to be obtuse, but (from the instructors lecture) the ResultsSetTableModel was supposed to be dealing with it.
Can you please give me guidance on how to fix it so I get the method to work?

Thanks
MIke

View PostCasiOo, on 07 December 2014 - 01:27 PM, said:

There is no getConnection() method in your ResultSetTableModel
No ClassNotFoundException is thrown inside the try-block, so no need to catch it

It should probably catch SQLException instead

Was This Post Helpful? 0
  • +
  • -

#6 mikenola   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 09-April 14

Re: AddEmployee.Java and ResultSetTableModel.java connection problem

Posted 09 December 2014 - 12:26 PM

CaSi0o,
found those problems, and fixed a bunch of bad typing on my part. code mostly works until i get to the last else statement trying to add the hourly employee wages.
then it errors with a JDBC4 error telling me "employees" table/alias is not unique.

I am going to start a new thread on that issue.
Thank you for your help.
Mike


View Postmikenola, on 07 December 2014 - 02:50 PM, said:

CasiOo,
I don't mean to be obtuse, but (from the instructors lecture) the ResultsSetTableModel was supposed to be dealing with it.
Can you please give me guidance on how to fix it so I get the method to work?

Thanks
MIke

View PostCasiOo, on 07 December 2014 - 01:27 PM, said:

There is no getConnection() method in your ResultSetTableModel
No ClassNotFoundException is thrown inside the try-block, so no need to catch it

It should probably catch SQLException instead

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1