If I'm reading this correctly, you're putting single-quote delimiters
around the REAL value:
insertQuery = "INSERT INTO salariedWorkers VALUES ( '" +
socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
MySQL would accept that, but Derby is a lot stricter. Drop the
single-quotes if you want to stick with the concatenated string
approach to creating an SQL statement -- but better yet, use a
prepared statement with parameter markers, something like:
PreparedStatement insertQuery = con.prepareStatement("INSERT INTO
salariedWorkers VALUES ( ?, ?, ?)");
insertQuery.setString(1, socialSecurityNumber);
insertQuery.setFloat(2, salary);
insertQuery.setFloat(3, 0);
insertQuery.executeUpdate();
(might need to adjust the data types slightly here, but you get the idea)
Dan
On 13/12/06, William B. <[EMAIL PROTECTED]> wrote:
I keep receiving the error message above when I try to add a salary. Please
help
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import javax.swing.*;
public class Workers extends JFrame {
private Connection connection;
private Statement statement;
private ResultSet resultSet;
private ResultSetMetaData rsMetaData;
private Container container;
private JTable table;
private JTextField input;
private JButton addSalariedWorker, addCommissionWorker,
addBasePlusCommissionWorker, addHourlyWorker;
// constructor Workers
public Workers()
{
super( "Add Staff Members" );
// The URL specifying the workers database to which this program
// connects to using JDBC
String url = "jdbc:derby:workers";
// Load the driver to allow connection to the database
try {
Class.forName(
"org.apache.derby.jdbc.EmbeddedDriver" );
connection = DriverManager.getConnection( url );
}
catch ( ClassNotFoundException cnfex ) {
System.err.println( "Failed to load JDBC driver." );
cnfex.printStackTrace();
System.exit( 1 ); // terminate program
}
catch ( SQLException sqlex ) {
System.err.println( "Unable to connect" );
sqlex.printStackTrace();
System.exit( 1 ); // terminate program
}
// if connected to database, set up GUI
JPanel topPanel = new JPanel();
topPanel.setLayout( new FlowLayout() );
topPanel.add( new JLabel( "Enter query to insert workers:" ) );
input = new JTextField( 50 );
topPanel.add( input );
input.addActionListener(
new ActionListener() {
public void actionPerformed( ActionEvent e )
{
addWorker( input.getText() );
}
}
);
// create four buttons that allow user to add specific employee
JPanel centerPanel = new JPanel();
centerPanel.setLayout( new FlowLayout() );
addSalariedWorker = new JButton( "Add Salaried Worker" );
addSalariedWorker.addActionListener( new
ButtonHandler() );
addCommissionWorker = new JButton( "Add Commission Worker" );
addCommissionWorker.addActionListener( new
ButtonHandler() );
addBasePlusCommissionWorker =
new JButton( "Add Base Plus Commission Worker" );
addBasePlusCommissionWorker.addActionListener(
new ButtonHandler() );
addHourlyWorker = new JButton( "Add Hourly Worker" );
addHourlyWorker.addActionListener( new
ButtonHandler() );
// add four buttons to centerPanel
centerPanel.add( addSalariedWorker );
centerPanel.add( addCommissionWorker );
centerPanel.add( addBasePlusCommissionWorker );
centerPanel.add( addHourlyWorker );
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 );
} // end constructor Workers
private void getTable()
{
try {
statement = connection.createStatement();
resultSet = statement.executeQuery( "SELECT * FROM workers" );
displayResultSet( resultSet );
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
}
private void addWorker( 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 there are no records, display a message
if ( !moreRecords ) {
JOptionPane.showMessageDialog( this,
"ResultSet contained no records" );
return;
}
Vector<Object> columnHeads = new Vector<Object>();
Vector<Object> rows = new Vector<Object>();
try {
// get column heads
ResultSetMetaData rsmd = rs.getMetaData();
for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
columnHeads.addElement( rsmd.getColumnName( i ) );
// get row data
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();
} // end try
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
} // end method displayResultSet
private Vector getNextRow( ResultSet rs,
ResultSetMetaData rsmd ) throws SQLException
{
Vector<Object> currentRow = new Vector<Object>();
for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
switch( rsmd.getColumnType( i ) ) {
case Types.VARCHAR:
case Types.LONGVARCHAR:
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 method getNextRow
public void shutDown()
{
try {
connection.close();
}
catch ( SQLException sqlex ) {
System.err.println( "Unable to disconnect" );
sqlex.printStackTrace();
}
}
public static void main(String[] args)
{
final Workers application = new Workers();
application.addWindowListener(
new WindowAdapter() {
public void windowClosing( WindowEvent e )
{
application.shutDown();
System.exit( 0 );
}
}
);
}
// inner class ButtonHandler handles button event
private class ButtonHandler implements ActionListener {
public void actionPerformed( ActionEvent event )
{
String socialSecurityNumber = JOptionPane.showInputDialog(
"Employee Social Security Number" );
String insertQuery = "", displayQuery = "";
// add salaried employee to table salariedWorkers
if ( event.getSource() == addSalariedWorker ) {
double weeklySalary = Double.parseDouble(
JOptionPane.showInputDialog( "Weekly Salary:" ) );
insertQuery = "INSERT INTO salariedWorkers VALUES ( '" +
socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
displayQuery = "SELECT workers.socialSecurityNumber, " +
"workers.firstName, workers.lastName, " +
"workers.employeeType, salariedWorkers.weeklySalary" +
" FROM workers, salariedWorkers WHERE " +
"workers.socialSecurityNumber = " +
"salariedWorkers.socialSecurityNumber";
}
// add commission employee to table commissionWorker
else if ( event.getSource() == addCommissionWorker ) {
int grossSales = Integer.parseInt(
JOptionPane.showInputDialog( "Gross Sales:" ) );
double commissionRate = Double.parseDouble(
JOptionPane.showInputDialog( "Commission Rate:" ) );
insertQuery = "INSERT INTO commissionWorkers VALUES ( '" +
socialSecurityNumber + "', '" + grossSales + "', '" +
commissionRate + "', '0' )";
displayQuery = "SELECT workers.socialSecurityNumber, " +
"workers.firstName, workers.lastName, " +
"workers.employeeType, commissionWorkers.grossSales," +
" commissionWorkers.commissionRate FROM
workers, " +
"commissionWorkers WHERE workers.socialSecurityNumber="
+ "commissionWorkers.socialSecurityNumber";
}
// add base plus commission employee to table
// basePlusCommissionEmployee
else if ( event.getSource() == addBasePlusCommissionWorker ) {
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 basePlusCommissionWorkers " +
"VALUES ( '" + socialSecurityNumber + "', '" + grossSales +
"', '" + commissionRate + "', '" + baseSalary + "', '0' )";
displayQuery = "SELECT workers.socialSecurityNumber, " +
"workers.firstName, workers.lastName, workers." +
"employeeType,
basePlusCommissionWorkers.baseSalary, " +
"basePlusCommissionWorkers.grossSales,
basePlus" +
"CommissionWorkers.commissionRate FROM
workers, " +
"basePlusCommissionWorkers WHERE " +
"workers.socialSecurityNumber = " +
"basePlusCommissionWorkers.socialSecurityNumber";
}
// add hourly employee to table hourlyEmployee
else {
int hours = Integer.parseInt(
JOptionPane.showInputDialog( "Hours:" ) );
double wage = Double.parseDouble(
JOptionPane.showInputDialog( "Wage:" ) );
insertQuery = "INSERT INTO hourlyWorkers VALUES ( '" +
socialSecurityNumber + "', '" + hours + "', '" + wage +
"', '0' )";
displayQuery = "SELECT workers.socialSecurityNumber, " +
"workers.firstName, workers.lastName, " +
"workers.employeeType, hourlyWorkers.hours, " +
"hourlyWorkers.wage FROM workers, hourlyWorkers " +
"WHERE workers.socialSecurityNumber = " +
"hourlyWorkers.socialSecurityNumber";
}
// execute insert query and display employee info
try {
statement = connection.createStatement();
statement.executeUpdate( insertQuery );
// display the employee info
statement = connection.createStatement();
resultSet = statement.executeQuery( displayQuery );
displayResultSet( resultSet );
}
catch ( SQLException exception ) {
exception.printStackTrace();
}
} // end method actionPerformed
} // end inner class ButtonHandler
} // end class Workers
SQL file
connect 'jdbc:derby:workers;create=true'
;
drop table workers
;
drop table salariedWorkers
;
drop table commissionWorkers
;
drop table basePlusCommissionWorkers
;
drop table hourlyWorkers
;
create table workers (
socialSecurityNumber varchar (30) NOT NULL,
firstName varchar (30) NOT NULL,
lastName varchar (30) NOT NULL,
birthday date NOT NULL,
employeeType varchar (30) NOT NULL,
departmentName varchar (30) NOT NULL,
constraint pk_workers primary key (socialSecurityNumber)
)
;
create table salariedWorkers (
socialsecurityNumber varchar (30) NOT NULL,
weeklySalary real NOT NULL,
bonus real,
constraint fk_salariedWorkers foreign key (socialSecurityNumber)
references workers (socialSecurityNumber)
)
;
create table commissionWorkers (
socialSecurityNumber varchar (30) NOT NULL,
grossSales int NOT NULL,
commissionRate real NOT NULL,
bonus real,
constraint fk_commissionWorkers foreign key (socialSecurityNumber)
references workers (socialSecurityNumber)
)
;
create table basePlusCommissionWorkers (
socialSecurityNumber varchar (30) NOT NULL,
grossSales int NOT NULL,
commissionRate real NOT NULL,
baseSalary real NOT NULL,
bonus real,
constraint fk_basePlusCommissionWorkers foreign key (socialSecurityNumber)
references workers (socialSecurityNumber)
)
;
create table hourlyWorkers (
socialSecurityNumber varchar (30) NOT NULL,
hours int NOT NULL,
wage real NOT NULL,
bonus real,
constraint fk_hourlyWorkers foreign key (socialSecurityNumber)
references workers (socialSecurityNumber)
)
;
________________________________
Want to start your own business? Learn how on Yahoo! Small Business.
