Java/Mysql error

SoItsMe

Hi guys I have been getting the following error every time I try to run an insert on the application I am making. Any Ideas. Here is all my code:

CREATE DATABASE IF NOT EXISTS staff
;
USE staff;
DROP TABLE IF EXISTS Holiday
;
DROP TABLE IF EXISTS Availability
;
DROP TABLE IF EXISTS WorkDetails
;
DROP TABLE IF EXISTS PersonalDetails
;
CREATE TABLE PersonalDetails (
ID INT(11) unsigned NOT NULL,
FirstName varchar (30) NOT NULL auto_increment,
LastName varchar (30) NOT NULL,
Birthday date NOT NULL,
Address varchar (60) NOT NULL,
PhoneNo INT NOT NULL,
PRIMARY KEY (ID)
) TYPE=INNODB
;
CREATE TABLE WorkDetails (
ID INT NOT NULL,
Position char (30) NOT NULL,
FullTime char (3) NOT NULL,
PartTime char (3) NOT NULL,
INDEX (ID),
FOREIGN KEY (ID) REFERENCES PersonalDetails (ID)
) TYPE=INNODB
;
CREATE TABLE Availability (
ID INT NOT NULL,
SundayMorning char (30) NOT NULL,
SundayAfternoon char (30) NOT NULL,
MondayMorning char (30) NOT NULL,
MondayAfternoon char (30) NOT NULL,
TuesdayMorning char (30) NOT NULL,
TuesdayAfternoon char (30) NOT NULL,
WednesdayMorning char (30) NOT NULL,
WednesdayAfternoon char (30) NOT NULL,
ThursdayMorning char (30) NOT NULL,
ThursdayAfternoon char (30) NOT NULL,
ThursdayEvening char (30) NOT NULL,
FridayMorning char (30) NOT NULL,
FridayAfternoon char (30) NOT NULL,
SaturdayMorning char (30) NOT NULL,
SturdayAfternoon char (30) NOT NULL,
INDEX (ID),
FOREIGN KEY (ID) REFERENCES PersonalDetails (ID)
) TYPE=INNODB
;
CREATE TABLE Holiday (
ID INT NOT NULL,
Reason varchar (30) NOT NULL,
StartOfLeave date NOT NULL,
FinishOfLeave date NOT NULL,
INDEX (ID),
FOREIGN KEY (ID) REFERENCES PersonalDetails (ID)
) TYPE=INNODB
;
Insert INTO PersonalDetails VALUES(0001, 'Emma', 'Mullen', '1985-1-2', '79 Finglas Place Finglas East Dublin 11', 0860835114)
;
Insert INTO WorkDetails VALUES(0001, 'Staff', 'No', 'Yes')
;
Insert INTO Availability VALUES(0001, 'Yes', 'Yes', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'Yes', 'No', 'No', 'Yes', 'Yes')
;
Insert INTO Holiday VALUES(0001, 'Summer Holidays', '2009-2-6', '2009-9-6')
;



The above is the code I used to generate my tables.

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package emma3;
/**
*
* @author Kraxis
*/
public class EmployeePersonal {
private int ID;
private String FirstName;
private String LastName;
private String Birthday;
private String Address;
private String PhoneNo;
public EmployeePersonal()
{
}
public EmployeePersonal(int id, String firstname, String lastname, String birthday, String address, String phoneno) {
setIdentity(id);
setfn(firstname);
setln(lastname);
setDOB(birthday);
setAdd(address);
setph(phoneno);
}
public void setIdentity(int id)
{
ID = id;
}
public int getIdentity(){
return ID;
}
public void setfn(String firstname){
FirstName = firstname;
}
public String getfn(){
return FirstName;
}
public void setln(String lastname){
LastName = lastname;
}
public String getln(){
return LastName;
}
public void setDOB(String birthday){
Birthday = birthday;
}
public String getDOB(){
return Birthday;
}
public void setAdd(String address){
Address = address;
}
public String getAdd(){
return Address;
}
public void setph(String phoneno){
PhoneNo = phoneno;
}
public String getph(){
return PhoneNo;
}
}


/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package emma3;
/**
*
* @author Kraxis
*/
// Fig. 25.33: AddressBookDisplay.java
// A simple address book
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.util.List;
import javax.swing.JButton;
import javax.swing.Box;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.WindowConstants;
import javax.swing.BoxLayout;
import javax.swing.BorderFactory;
import javax.swing.JOptionPane;
public class Display2 extends JFrame
{
private EmployeePersonal currentEntry;
private DatabaseQueries databasequeries;
private List< EmployeePersonal > results;
private int numberOfEntries = 0;
private int currentEntryIndex;
private JButton browseButton;
private JLabel PhoneLabel;
private JTextField PhoneTextField;
private JLabel FirstNameLabel;
private JTextField FirstNameTextField;
private JLabel IDLabel;
private JTextField IDTextField;
private JTextField indexTextField;
private JLabel LastNameLabel;
private JTextField LastNameTextField;
private JTextField maxTextField;
private JButton nextButton;
private JLabel ofLabel;
private JLabel AddressLabel;
private JTextField AddressTextField;
private JLabel BirthdayLabel;
private JTextField BirthdayTextField;
private JButton previousButton;
private JButton queryButton;
private JLabel queryLabel;
private JPanel queryPanel;
private JPanel navigatePanel;
private JPanel displayPanel;
private JTextField queryTextField;
private JButton insertButton;
// no-argument constructor
public Display2()
{
super( "Roster System" );
// establish database connection and set up PreparedStatements
databasequeries = new DatabaseQueries();
// create GUI
navigatePanel = new JPanel();
previousButton = new JButton();
indexTextField = new JTextField( 2 );
ofLabel = new JLabel();
maxTextField = new JTextField( 2 );
nextButton = new JButton();
displayPanel = new JPanel();
IDLabel = new JLabel();
IDTextField = new JTextField( 10 );
FirstNameLabel = new JLabel();
FirstNameTextField = new JTextField( 10 );
LastNameLabel = new JLabel();
LastNameTextField = new JTextField( 10 );
PhoneLabel = new JLabel();
PhoneTextField = new JTextField( 10 );
AddressLabel = new JLabel();
AddressTextField = new JTextField( 10 );
BirthdayLabel = new JLabel();
BirthdayTextField = new JTextField( 10 );
queryPanel = new JPanel();
queryLabel = new JLabel();
queryTextField = new JTextField( 10 );
queryButton = new JButton();
browseButton = new JButton();
insertButton = new JButton();
setLayout( new FlowLayout( FlowLayout.CENTER, 10, 10 ) );
setSize( 600, 500 );
setResizable( false );
navigatePanel.setLayout(
new BoxLayout( navigatePanel, BoxLayout.X_AXIS ) );
previousButton.setText( "Previous" );
previousButton.setEnabled( false );
previousButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
{
previousButtonActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
navigatePanel.add( previousButton );
navigatePanel.add( Box.createHorizontalStrut( 10 ) );
indexTextField.setHorizontalAlignment(
JTextField.CENTER );
indexTextField.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
{
indexTextFieldActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
navigatePanel.add( indexTextField );
navigatePanel.add( Box.createHorizontalStrut( 10 ) );
ofLabel.setText( "of" );
navigatePanel.add( ofLabel );
navigatePanel.add( Box.createHorizontalStrut( 10 ) );
maxTextField.setHorizontalAlignment(
JTextField.CENTER );
maxTextField.setEditable( false );
navigatePanel.add( maxTextField );
navigatePanel.add( Box.createHorizontalStrut( 10 ) );
nextButton.setText( "Next" );
nextButton.setEnabled( false );
nextButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
{
nextButtonActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
navigatePanel.add( nextButton );
add( navigatePanel );
displayPanel.setLayout( new GridLayout( 5, 3, 4, 7 ) );
IDLabel.setText( "ID:" );
displayPanel.add( IDLabel );
IDTextField.setEditable( true );
displayPanel.add( IDTextField );
FirstNameLabel.setText( "First Name:" );
displayPanel.add( FirstNameLabel );
displayPanel.add( FirstNameTextField );
LastNameLabel.setText( "Last Name:" );
displayPanel.add( LastNameLabel );
displayPanel.add( LastNameTextField );
AddressLabel.setText( "Address" );
displayPanel.add( AddressLabel );
displayPanel.add( AddressTextField );
PhoneLabel.setText( "Phone Number:" );
displayPanel.add( PhoneLabel );
displayPanel.add( PhoneTextField );
BirthdayLabel.setText("DOB");
displayPanel.add( BirthdayLabel );
displayPanel.add( BirthdayTextField );
add( displayPanel );
queryPanel.setLayout(
new BoxLayout( queryPanel, BoxLayout.X_AXIS) );
queryPanel.setBorder( BorderFactory.createTitledBorder(
"Find an entry by last name" ) );
queryLabel.setText( "Last Name:" );
queryPanel.add( Box.createHorizontalStrut( 5 ) );
queryPanel.add( queryLabel );
queryPanel.add( Box.createHorizontalStrut( 10 ) );
queryPanel.add( queryTextField );
queryPanel.add( Box.createHorizontalStrut( 10 ) );
queryButton.setText( "Find" );
queryButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
{
queryButtonActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
queryPanel.add( queryButton );
queryPanel.add( Box.createHorizontalStrut( 5 ) );
add( queryPanel );
browseButton.setText( "Browse All Entries" );
browseButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
{
browseButtonActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
add( browseButton );
insertButton.setText( "Insert New Entry" );
insertButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
{
insertButtonActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
add( insertButton );
addWindowListener(
new WindowAdapter()
{
public void windowClosing( WindowEvent evt )
{
databasequeries.close(); // close database connection
System.exit( 0 );
} // end method windowClosing
} // end anonymous inner class
); // end call to addWindowListener
setVisible( true );
} // end no-argument constructor
// handles call when previousButton is clicked
private void previousButtonActionPerformed( ActionEvent evt )
{
currentEntryIndex--;
if ( currentEntryIndex < 0 )
currentEntryIndex = numberOfEntries - 1;
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
indexTextFieldActionPerformed( evt );
} // end method previousButtonActionPerformed
// handles call when nextButton is clicked
private void nextButtonActionPerformed( ActionEvent evt )
{
currentEntryIndex++;
if ( currentEntryIndex >= numberOfEntries )
currentEntryIndex = 0;
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
indexTextFieldActionPerformed( evt );
} // end method nextButtonActionPerformed
// handles call when queryButton is clicked
private void queryButtonActionPerformed( ActionEvent evt )
{
results =
databasequeries.getPeopleByLastName( queryTextField.getText() );
numberOfEntries = results.size();
if ( numberOfEntries != 0 )
{
currentEntryIndex = 0;
currentEntry = results.get( currentEntryIndex );
IDTextField.setText("" + currentEntry.getIdentity() );
FirstNameTextField.setText( currentEntry.getfn() );
LastNameTextField.setText( currentEntry.getln() );
PhoneTextField.setText( currentEntry.getph() );
AddressTextField.setText( currentEntry.getAdd() );
BirthdayTextField.setText( currentEntry.getDOB());
maxTextField.setText( "" + numberOfEntries );
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
nextButton.setEnabled( true );
previousButton.setEnabled( true );
} // end if
else
browseButtonActionPerformed( evt );
} // end method queryButtonActionPerformed
// handles call when a new value is entered in indextTextField
private void indexTextFieldActionPerformed( ActionEvent evt )
{
currentEntryIndex =
( Integer.parseInt( indexTextField.getText() ) - 1 );
if ( numberOfEntries != 0 && currentEntryIndex < numberOfEntries )
{
currentEntry = results.get( currentEntryIndex );
IDTextField.setText("" + currentEntry.getIdentity() );
FirstNameTextField.setText( currentEntry.getfn() );
LastNameTextField.setText( currentEntry.getln() );
PhoneTextField.setText( currentEntry.getph() );
AddressTextField.setText( currentEntry.getAdd() );
BirthdayTextField.setText( currentEntry.getDOB());
maxTextField.setText( "" + numberOfEntries );
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
} // end if
} // end method indexTextFieldActionPerformed
// handles call when browseButton is clicked
private void browseButtonActionPerformed( ActionEvent evt )
{
try
{
results = databasequeries.getAllPeople();
numberOfEntries = results.size();
if ( numberOfEntries != 0 )
{
currentEntryIndex = 0;
currentEntry = results.get( currentEntryIndex );
IDTextField.setText("" + currentEntry.getIdentity() );
FirstNameTextField.setText( currentEntry.getfn() );
LastNameTextField.setText( currentEntry.getln() );
PhoneTextField.setText( currentEntry.getph() );
AddressTextField.setText( currentEntry.getAdd() );
BirthdayTextField.setText( currentEntry.getDOB() );
maxTextField.setText( "" + numberOfEntries );
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
nextButton.setEnabled( true );
previousButton.setEnabled( true );
} // end if
} // end try
catch ( Exception e )
{
e.printStackTrace();
} // end catch
} // end method browseButtonActionPerformed
// handles call when insertButton is clicked
private void insertButtonActionPerformed( ActionEvent evt )
{
int result = databasequeries.addPerson( FirstNameTextField.getText(),
LastNameTextField.getText(), PhoneTextField.getText(),
AddressTextField.getText(), BirthdayTextField.getText() );
if ( result == 1 )
JOptionPane.showMessageDialog( this, "Person added!",
"Person added", JOptionPane.PLAIN_MESSAGE );
else
JOptionPane.showMessageDialog( this, "Person not added!",
"Error", JOptionPane.PLAIN_MESSAGE );
browseButtonActionPerformed( evt );
} // end method insertButtonActionPerformed
// main method
public static void main( String args[] )
{
new Display2();
} // end method main
} // end class AddressBookDisplay

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package emma3;
/**
*
* @author Kraxis
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
public class DatabaseQueries
{
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String DATABASE_URL = "jdbc:mysql://localhost/staff";
private static final String USERNAME = "jhtp7";
private static final String PASSWORD ="jhtp7";
private Connection connection = null;
private PreparedStatement selectAllPeople = null;
private PreparedStatement selectPeopleByLastName = null;
private PreparedStatement insertNewPerson = null;

public DatabaseQueries()
{
try
{
Class.forName(DRIVER);
connection = DriverManager.getConnection(DATABASE_URL,USERNAME, PASSWORD);
selectAllPeople = connection.prepareStatement("Select * FROM PersonalDetails");
selectPeopleByLastName = connection.prepareStatement("SELECT * FROM PersonalDetails WHERE LastName = ?");
insertNewPerson = connection.prepareStatement("INSERT INTO PersonalDetails " + "(FirstName, LastName, Birthday, Address, PhoneNo)" + "VALUES (?,?,?,?,?)");
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
System.exit(1);
}
catch(ClassNotFoundException classNotFound)
{
classNotFound.printStackTrace();
}
}
public List< EmployeePersonal >getAllPeople()
{
List< EmployeePersonal >results = null;
ResultSet resultSet = null;
try
{
resultSet = selectAllPeople.executeQuery();
results = new ArrayList< EmployeePersonal >();
while ( resultSet.next() )
{
results.add( new EmployeePersonal(resultSet.getInt("ID"),
resultSet.getString("FirstName"),
resultSet.getString("LastName"),
resultSet.getString("Birthday"),
resultSet.getString("Address"),
resultSet.getString("PhoneNo")));
}
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
finally
{
try
{
resultSet.close();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
close();
}
}
return results;
}
public List< EmployeePersonal > getPeopleByLastName( String name )
{
List<EmployeePersonal>results = null;
ResultSet resultSet = null;
try
{
selectPeopleByLastName.setString(1, name);
resultSet = selectPeopleByLastName.executeQuery();
results = new ArrayList<EmployeePersonal>();
while ( resultSet.next() )
{
results.add( new EmployeePersonal(resultSet.getInt("ID"),
resultSet.getString("FirstName"),
resultSet.getString("LastName"),
resultSet.getString("Birthday"),
resultSet.getString("Address"),
resultSet.getString("PhoneNo")));
}
}

catch (SQLException sqlException)
{
sqlException.printStackTrace();
}

finally
{
try
{
resultSet.close();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
close();
}
}
return results;
}
public int addPerson(String first, String last, String birth, String addr, String ph)
{
int result = 0;
try
{
insertNewPerson.setString(1, first);
insertNewPerson.setString(2, last);
insertNewPerson.setString(3, birth);
insertNewPerson.setString(4, addr);
insertNewPerson.setString(5, ph);
result = insertNewPerson.executeUpdate();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
close();
}
return result;
}
public void close()
{
try
{
connection.close();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
}
}

I get the following error when trying to insert anything through my GUI:

java.sql.SQLException: Field 'ID' doesn't have a default value
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)

There is obviously a problem with my auto increment. I am not sure what. Thanks.

Webmonkey

You've the auto increment beside the first name when it should be on the ID?

SoItsMe

Cheers!! Feel like a dope lol!