Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

(Employee Database Query Application) Modify Exercise 24.4 to provide a JComboBo

ID: 3850295 • Letter: #

Question

(Employee Database Query Application) Modify Exercise 24.4 to provide a JComboBox and a JTextArea to allow the user to perform a query that is either selected from the JComboBox or defined in the JTextArea. Sample predefined queries are:

a) Select all employees working in Department SALES.

b) Select hourly employees working over 30 hours.

c) Select all commission employees in descending order of the commission rate.

This is the original program that needs to be edited

import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.awt.*;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.awt.event.*;
import java.util.*;
import javax.swing.*;
import javax.swing.JButton;

public class AddEmployees extends JFrame
{

//Declaring variables necessary for data base connection

private Connection connection;
private Statement statement;
private ResultSet resultSet;
private ResultSetMetaData rsMetaData;

//Declaring variable necessary for GUI panel
private Container container;
private JTable table;
private JTextField input;
private JButton addSalariedEmployee, addCommissionEmployee, addBasePlusCommissionEmployee, addHourlyEmployee;

public AddEmployees() {
super("Add Employees");
String url = "jdbc:mysql:employees";
try {
Class.forName("com.mysql.jdbc.DB2jDriver");
connection = DriverManager.getConnection(url);
}
catch (ClassNotFoundException ex)
{
System.err.println("Failed to load JDBC driver");
ex.printStackTrace();
System.exit(1);//terminate program
}
catch (SQLException ex)
{
System.err.println("Unable to connect");
ex.printStackTrace();
System.exit(1);
}
// if conected to database, set up GUI
JPanel topPanel = new JPanel();
topPanel.setLayout(new FlowLayout());
topPanel.add(new JLabel("Enter query to insert employees:"));
input = new JTextField(50);
topPanel.add(input);
input.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
addEmployee(input.getText());
}
}
);
JPanel centerPanel = new JPanel();
centerPanel.setLayout(new FlowLayout());
addSalariedEmployee = new JButton("Add Salaried Employee");
addSalariedEmployee.addActionListener(new ButtonHandler());
addCommissionEmployee = new JButton("Add Commission Employee");
addBasePlusCommissionEmployee = new JButton("Add Base Plus Commission Employee");
addBasePlusCommissionEmployee.addActionListener(new ButtonHandler());
addHourlyEmployee = new JButton("Add Hourly Employee");
addHourlyEmployee.addActionListener(new ButtonHandler());

// add four buttons to centerPanel
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(4, 4);
container = getContentPane();
container.setLayout(new BorderLayout());
container.add(inputPanel, BorderLayout.NORTH);
container.add(table, BorderLayout.CENTER);
getTable();
setSize(800, 300);
setVisible(true);
}

//getTable method creates a database statement and tries to execute query to retrive all elements from employee table
private void getTable ()
{
try
{
statement = connection.createStatement();
resultSet = statement.executeQuery("SELECT * FROM employees");
displayResultSet(resultSet);
}
catch (SQLException sqlex) {
sqlex.printStackTrace();
}
}

private void addEmployee(String query)
{
try
{
statement = connection.createStatement();
statement.executeUpdate(query);
getTable();
}
catch (SQLException sqlex)
{
sqlex.printStackTrace();
}
}
private void displayResultSet ( ResultSet rs )
throws SQLException
{
// position to first record
boolean moreRecords = rs.next();
if ( !moreRecords )
{
JOptionPane.showMessageDialog(this, "ResultSet contained no records");
return;
}
Vector columnHeads = new Vector();
Vector rows = new Vector();
try
{
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); ++i)
{
columnHeads.addElement (rsmd.getColumnName(i));
do
{
rows.addElement(getNextRow(rs, rsmd));
}
while (rs.next());
// display table with resultset contents
table = new JTable(rows, columnHeads);
JScrollPane scroller = new JScrollPane( table );
container.remove(1);
container.add(scroller, BorderLayout.CENTER );
container.validate();
}
}
catch ( SQLException ex )
{
ex.printStackTrace();
}
}
private Vector getNextRow( ResultSet rs, ResultSetMetaData rsmd )
throws SQLException
{
Vector currentRow = new Vector();
for ( int i = 1;i<= rsmd.getColumnCount();++i)
switch(rsmd.getColumnType(i))
{
case Types.VARCHAR:
case Types.LONGNVARCHAR:
currentRow.addElement(rs.getString(i));
break;
case Types.INTEGER:
currentRow.addElement(
new Long( rs.getLong(i)));
break;
case Types.REAL:
currentRow.addElement (new Float( rs.getDouble(i)));
break;
case Types.DATE:
currentRow.addElement (rs.getDate(i));
break;
default:
System.out.println("Type was:" + rsmd.getColumnTypeName(i));
}
return currentRow;
} // end of method
public void shutDown()
{
try
{
connection.close();
}
catch ( SQLException sqlex)
{
System.err.println("Unable to disconnect");
sqlex.printStackTrace();
}
}
public static void main( String[] args)
{
final AddEmployees application = new AddEmployees();
application.addWindowListener( new WindowAdapter()
{
public void windowClosing( WindowEvent e)
{
application.shutDown();
System.exit( 0 );
}
}
);
}
// inner class ButtonHandler handle button event
private class ButtonHandler
implements ActionListener
{
public void actionPerformed( ActionEvent event )
{
String socialSecurityNumber = JOptionPane.showInputDialog( "Employee Social Security Number" );
String insertQuery = "", displayQuery = "";
if (event.getSource()
== addSalariedEmployee )
{
double weeklySalary = Double.parseDouble(JOptionPane.showInputDialog("Weekly Salary:"));
insertQuery = "INSERT INTO salariedEmployees VALUES ( '" + socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
displayQuery = "SELECT employees.socialSecurityNumber, " + "employees.firstName, employees.lastName, " +
"employees.employeeType, salariedEmployees.weeklySalary" + "FROM employees, salariedEmployees WHERE " +
"employees.socialSecurityNumber = " +"salariedEmployees.socialSecurityNumber";
}
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 + "','0' )";
displayQuery = "SELECT employees.socialSecurityNumber," + "employees.firstName, employees.lastName,"
+" employees.employeesType,commissionEmployees.grossSales," +
" commissionEmployees.commissionRate FROM employees," + " commissionEmployees WHERE employees.socialSecurityNumber="
+" commissionEmployees.socialSecurityNumber";
}
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 = "INSERT INTO basePlusCommissionEmployees "+"VALUES ( '" + socialSecurityNumber + "','"
+ grossSales + " ', '" + commissionRate + "', '"+ baseSalary + "', '0')";
displayQuery = "SELECT employees.socialSecurityNumber,"+
"employees.firstName, employees.lastName, employees." + "employeeType, basePlusCommissionEmployees.baseSalary," +
"basePlusCommissionEmployees.grossSales, basePlus"+
"CommissionEmployees.commissionRate FROM employees, " +"basePlusCommissionEmployees WHERE "
+ "employees.socialSecurityNumber = " + "basePlusCommissionEmployees.socialSecurityNumber";
}
//add hourly employees to table
else
{
int hours = Integer.parseInt( JOptionPane.showInputDialog("Hours:" ));
double wage = Double.parseDouble(JOptionPane.showInputDialog("Wage:"));
insertQuery = "INSERT INTO hourlyEmployees VALUES ( '" + socialSecurityNumber + "', '0' )";
displayQuery = "SELECT employees.socialSecurityNumber, "+ "employees.firstName, employees.lastName,"+
"employees.employeeType, hourlyEmployees.hours,"+ "hourlyEmployees.wage FROM employees,hourlyEmployees " +
"WHERE employees.socialSecurityNumber =" + "hourlyEmployees.socialSecurityNumber";
}
// execute insert query
try
{
statement = connection.createStatement();
statement.executeUpdate(insertQuery);
statement = connection.createStatement();
resultSet = statement.executeQuery( displayQuery);
displayResultSet(resultSet);
}
catch ( SQLException exception)
{
exception.printStackTrace();
}
}
  
}
}

Explanation / Answer

The code is given below :

import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.awt.*;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.awt.event.*;
import java.util.*;
import javax.swing.*;
import javax.swing.JButton;
public class AddEmployees extends JFrame
{
//Declaring variables necessary for data base connection

private Connection connection;
private Statement statement;
private ResultSet resultSet;
private ResultSetMetaData rsMetaData;
//Declaring variable necessary for GUI panel
private Container container;
private JTable table;
private JTextField input;
private JButton addSalariedEmployee, addCommissionEmployee, addBasePlusCommissionEmployee, addHourlyEmployee;
public AddEmployees() {
super("Add Employees");
String url = "jdbc:mysql:employees";
try {
Class.forName("com.mysql.jdbc.DB2jDriver");
connection = DriverManager.getConnection(url);
}
catch (ClassNotFoundException ex)
{
System.err.println("Failed to load JDBC driver");
ex.printStackTrace();
System.exit(1);//terminate program
}
catch (SQLException ex)
{
System.err.println("Unable to connect");
ex.printStackTrace();
System.exit(1);
}
// if conected to database, set up GUI
JPanel topPanel = new JPanel();
topPanel.setLayout(new FlowLayout());
topPanel.add(new JLabel("Enter query to insert employees:"));
input = new JTextField(50);
topPanel.add(input);
input.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
addEmployee(input.getText());
}
}
);
JPanel centerPanel = new JPanel();
centerPanel.setLayout(new FlowLayout());
addSalariedEmployee = new JButton("Add Salaried Employee");
addSalariedEmployee.addActionListener(new ButtonHandler());
addCommissionEmployee = new JButton("Add Commission Employee");
addBasePlusCommissionEmployee = new JButton("Add Base Plus Commission Employee");
addBasePlusCommissionEmployee.addActionListener(new ButtonHandler());
addHourlyEmployee = new JButton("Add Hourly Employee");
addHourlyEmployee.addActionListener(new ButtonHandler());
// add four buttons to centerPanel
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(4, 4);
container = getContentPane();
container.setLayout(new BorderLayout());
container.add(inputPanel, BorderLayout.NORTH);
container.add(table, BorderLayout.CENTER);
getTable();
setSize(800, 300);
setVisible(true);
}
//getTable method creates a database statement and tries to execute query to retrive all elements from employee table
private void getTable ()
{
try
{
statement = connection.createStatement();
resultSet = statement.executeQuery("SELECT * FROM employees");
displayResultSet(resultSet);
}
catch (SQLException sqlex) {
sqlex.printStackTrace();
}
}
private void addEmployee(String query)
{
try
{
statement = connection.createStatement();
statement.executeUpdate(query);
getTable();
}
catch (SQLException sqlex)
{
sqlex.printStackTrace();
}
}
private void displayResultSet ( ResultSet rs )
throws SQLException
{
// position to first record
boolean moreRecords = rs.next();
if ( !moreRecords )
{
JOptionPane.showMessageDialog(this, "ResultSet contained no records");
return;
}
Vector columnHeads = new Vector();
Vector rows = new Vector();
try
{
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); ++i)
{
columnHeads.addElement (rsmd.getColumnName(i));
do
{
rows.addElement(getNextRow(rs, rsmd));
}
while (rs.next());
// display table with resultset contents
table = new JTable(rows, columnHeads);
JScrollPane scroller = new JScrollPane( table );
container.remove(1);
container.add(scroller, BorderLayout.CENTER );
container.validate();
}
}
catch ( SQLException ex )
{
ex.printStackTrace();
}
}
private Vector getNextRow( ResultSet rs, ResultSetMetaData rsmd )
throws SQLException
{
Vector currentRow = new Vector();
for ( int i = 1;i<= rsmd.getColumnCount();++i)
switch(rsmd.getColumnType(i))
{
case Types.VARCHAR:
case Types.LONGNVARCHAR:
currentRow.addElement(rs.getString(i));
break;
case Types.INTEGER:
currentRow.addElement(
new Long( rs.getLong(i)));
break;
case Types.REAL:
currentRow.addElement (new Float( rs.getDouble(i)));
break;
case Types.DATE:
currentRow.addElement (rs.getDate(i));
break;
default:
System.out.println("Type was:" + rsmd.getColumnTypeName(i));
}
return currentRow;
} // end of method
public void shutDown()
{
try
{
connection.close();
}
catch ( SQLException sqlex)
{
System.err.println("Unable to disconnect");
sqlex.printStackTrace();
}
}
public static void main( String[] args)
{
final AddEmployees application = new AddEmployees();
application.addWindowListener( new WindowAdapter()
{
public void windowClosing( WindowEvent e)
{
application.shutDown();
System.exit( 0 );
}
}
);
}
// inner class ButtonHandler handle button event
private class ButtonHandler
implements ActionListener
{
public void actionPerformed( ActionEvent event )
{
String socialSecurityNumber = JOptionPane.showInputDialog( "Employee Social Security Number" );
String insertQuery = "", displayQuery = "";
if (event.getSource()
== addSalariedEmployee )
{
double weeklySalary = Double.parseDouble(JOptionPane.showInputDialog("Weekly Salary:"));
insertQuery = "INSERT INTO salariedEmployees VALUES ( '" + socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
displayQuery = "SELECT employees.socialSecurityNumber, " + "employees.firstName, employees.lastName, " +
"employees.employeeType, salariedEmployees.weeklySalary" + "FROM employees, salariedEmployees WHERE " +
"employees.socialSecurityNumber = " +"salariedEmployees.socialSecurityNumber";
}
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 + "','0' )";
displayQuery = "SELECT employees.socialSecurityNumber," + "employees.firstName, employees.lastName,"
+" employees.employeesType,commissionEmployees.grossSales," +
" commissionEmployees.commissionRate FROM employees," + " commissionEmployees WHERE employees.socialSecurityNumber="
+" commissionEmployees.socialSecurityNumber";
}
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 = "INSERT INTO basePlusCommissionEmployees "+"VALUES ( '" + socialSecurityNumber + "','"
+ grossSales + " ', '" + commissionRate + "', '"+ baseSalary + "', '0')";
displayQuery = "SELECT employees.socialSecurityNumber,"+
"employees.firstName, employees.lastName, employees." + "employeeType, basePlusCommissionEmployees.baseSalary," +
"basePlusCommissionEmployees.grossSales, basePlus"+
"CommissionEmployees.commissionRate FROM employees, " +"basePlusCommissionEmployees WHERE "
+ "employees.socialSecurityNumber = " + "basePlusCommissionEmployees.socialSecurityNumber";
}
//add hourly employees to table
else
{
int hours = Integer.parseInt( JOptionPane.showInputDialog("Hours:" ));
double wage = Double.parseDouble(JOptionPane.showInputDialog("Wage:"));
insertQuery = "INSERT INTO hourlyEmployees VALUES ( '" + socialSecurityNumber + "', '0' )";
displayQuery = "SELECT employees.socialSecurityNumber, "+ "employees.firstName, employees.lastName,"+
"employees.employeeType, hourlyEmployees.hours,"+ "hourlyEmployees.wage FROM employees,hourlyEmployees " +
"WHERE employees.socialSecurityNumber =" + "hourlyEmployees.socialSecurityNumber";
}
// execute insert query
try
{
statement = connection.createStatement();
statement.executeUpdate(insertQuery);
statement = connection.createStatement();
resultSet = statement.executeQuery( displayQuery);
displayResultSet(resultSet);
}
catch ( SQLException exception)
{
exception.printStackTrace();
}
}
  
}
}

Related Questions

(Dr. Acevedo) During your second year of medical school, you are tasked with pur
Question #92780
(Dr. Beal) was in Walnut Creek the other day I don\'t play basketball and I\'ve
Question #3056663
(Due 7/24) The number of chocolate chips in an 18-ounce bag is approximately nor
Question #2949656
(Due at beginning of class October 18 or before, 10 points Possible) (Ch. 6) Ass
Question #3594872
(Due date: February ,2018) 1) Calculate the molality of each of the following aq
Question #563469
(Due date: February ,2018) 1) Calculate the molality of each of the following aq
Question #1083353
(Duplicate Elimination) Use a one-dimensional array to solve the following probl
Question #3783949
(Dynamic Programming) Im having trouble in this code. Below is the original skel
Question #3730595

Navigate

Previous
(Employee Class) Create a class called Employee that includes three instance var
Next
(Employee Database) In Section 10.5, we introduced an employee-payroll hierarchy

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.