Help In PreparedStatement
I need some help with SQL and PreparedStatement
I have add the PreparedStatement and I'm not to sure if I'm doing it correctly.
When I do a test run on Choose 1 ,2 or 3 I now I get this error.
Error - com.mysql.jdbc.Statement
here is my code below
import java.sql.*;
import java.util.*;
import java.sql.PreparedStatement;
publicclass DBAssign{
/**
* @param args
*/
static Scanner kbd;
static Connection conn =null;
//adding records...
publicstaticvoid addRecord(String id, String fname, String lname,
String street, String city, String state, String zip,
String hphone, String ophone, String deptasg,int yrsemp,
float mtpy)throws SQLException{
// make variables
PreparedStatement statement =null;
String addstring ="Insert into emptable values('" + id +"', '"
+ fname +"', " + lname +", '" + street +"'" + city +"',"
+ state +"'," + zip +"'," + hphone +"'," + ophone +"'"
+ deptasg +"'," + yrsemp +"'," + mtpy +"',)";
try{
statement=conn.prepareStatement("INSERT into user values(?,?)");
statement = (PreparedStatement) conn.createStatement();
boolean ret = statement.execute(addstring);
if (ret){
System.out.println("Updated " + statement.getUpdateCount());
}//close if
}catch (SQLException e){
System.out.println("Error " + e.getMessage());
throw (e);
}finally{
try{
if (statement !=null){
statement.close();
}//close if
}catch (SQLException e2){
System.out
.println("Error freeing resources " + e2.getMessage());
throw (e2);
}//close e2
}//close finally
}//close exception
//add to the record
publicstaticvoid addToTable(){
// get data from keyboard
String id =null;
String fname;// =null;
String lname;
String street;
String city;
String state;
String zip;
String hphone;
String ophone;
String deptasg;
int yrsemp;
float mtpy;
boolean dupkey =true;
while (dupkey ==true){
System.out.println("Enter Employee's ID");
id = kbd.next();
kbd.nextLine();
dupkey = findRecord(id);
if (dupkey ==true){
System.out.println("Key must be unique - try again");
}
}
System.out.println("Enter first name");
fname = kbd.next();
kbd.nextLine();
System.out.println("Enter last name");
lname = kbd.next();
kbd.nextLine();
System.out.println("Enter street name");
street = kbd.next();
kbd.nextLine();
System.out.println("Enter city");
city = kbd.next();
kbd.nextLine();
System.out.println("Enter state");
state = kbd.next();
kbd.nextLine();
System.out.println("Enter zip code");
zip = kbd.next();
kbd.nextLine();
System.out.println("Enter home phone number");
hphone = kbd.next();
kbd.nextLine();
System.out.println("Enter office phone");
ophone = kbd.next();
kbd.nextLine();
System.out.println("Enter dept name");
deptasg = kbd.next();
kbd.nextLine();
System.out.println("Enter years employeed");
yrsemp = kbd.nextInt();
kbd.nextLine();
System.out.println("Enter month pay");
mtpy = kbd.nextFloat();
kbd.nextLine();
try{
addRecord(id, fname, lname, street, city, state, zip, hphone,
ophone, deptasg, yrsemp, mtpy);
}catch (SQLException e){
System.out.println("Error adding record " + e.getMessage());
}
}//close add
//List records
publicstaticvoid listRecords(){
PreparedStatement statement =null;
ResultSet rs =null;
try{
statement = (PreparedStatement) conn.createStatement();
rs = statement
.executeQuery("Select empid, firstname, lastname, street, city, state, zip, homephone, officephone, department, yearsemploy, monthpay");
if (rs !=null){
while (rs.next()){
System.out.println(rs.getString("empid") +" - "
+ rs.getString("firstname") +" - "
+ rs.getInt("lastname") +" - "
+ rs.getString("street") +" - "
+ rs.getString("city") +" - "
+ rs.getString("state") +" - "
+ rs.getString("zip") +" - "
+ rs.getString("hphone") +" - "
+ rs.getString("ophone") +" - "
+ rs.getString("department") +" - "
+ rs.getString("yearsemploy") +" - "
+ rs.getString("monthpay"));
}// close the while loop
}// close the if loop
}catch (SQLException e){
System.out.println("Error listing records: " + e.getMessage());
}finally{
try{
if (statement !=null){
statement.close();
}
if (rs !=null){
rs.close();
}
}catch (SQLException e2){
System.out.println("Error freeing resource" + e2.getMessage());
}
}//close finally
}//close list
//Find a record
publicstaticboolean findRecord(String empid){
boolean retval =true;
PreparedStatement statement =null;
ResultSet rs =null;
int countemps = 0;
try{
statement = (PreparedStatement) conn.createStatement();
rs = statement
.executeQuery("Select count(*) as num from emptable where empid = '"
+ empid +"'");
rs.next();
if (rs ==null){
retval =false;
}else{
countemps = rs.getInt(1);
}
// System.out.println("Count "+countrecs);
if (countemps <= 0){
retval =false;
}
}catch (SQLException e){
System.out.println("Error finding Employees record "
+ e.getMessage());
}finally{
try{
if (statement !=null){
statement.close();
}
if (rs !=null){
rs.close();
}
}catch (SQLException e2){
System.out.println("Error freeing resource " + e2.getMessage());
retval =true;
}
}//close finally
return retval;
}//close find
//updates records
publicstaticvoid updateRecord(){
// get record to update
String id =null;
boolean findemp =false;
while (!findemp){
System.out.println("Enter Employee ID to update");
id = kbd.next();
kbd.nextLine();
findemp = findRecord(id);
if (!findemp){
System.out.println("This Employee id record " + id
+" does not exist - try again");
}
}//close while
// display data and request update
Statement stmt =null;
ResultSet rs =null;
String id2 =null;
String fname;
String lname;
String street;
String city;
String state;
String zip;
String hphone;
String ophone;
String deptasg;
String yrsemp;
String mtpy;
try{
stmt = conn.createStatement();
rs = stmt.executeQuery("Select * from emptable where empid = '"
+ id +"'");
rs.next();
System.out.println("Item First Name is "
+ rs.getString("firstname"));
System.out.print("Enter new item name: ");
fname = kbd.next();
kbd.nextLine();
System.out.println("Item Last name is " + rs.getString("lastname"));
System.out.print("Enter new item name: ");
lname = kbd.next();
kbd.nextLine();
System.out.println("Item Street is " + rs.getString("street"));
System.out.print("Enter new item name: ");
street = kbd.next();
kbd.nextLine();
System.out.println("Item City name is " + rs.getString("city"));
System.out.print("Enter new item name: ");
city = kbd.next();
kbd.nextLine();
System.out.println("Item State name is " + rs.getString("state"));
System.out.print("Enter new item name: ");
state = kbd.next();
kbd.nextLine();
System.out.println("Item Zip Code is " + rs.getString("zip"));
System.out.print("Enter new item name: ");
zip = kbd.next();
kbd.nextLine();
System.out.println("Item Home Phone is " + rs.getString("homephone"));
System.out.print("Enter new item name: ");
hphone = kbd.next();
kbd.nextLine();
System.out.println("Item Office Phone is " + rs.getString("officephone"));
System.out.print("Enter new item name: ");
ophone = kbd.next();
kbd.nextLine();
System.out.println("Item Department is " + rs.getString("department"));
System.out.print("Enter new item name: ");
deptasg = kbd.next();
kbd.nextLine();
System.out.println("Item Years Employed is " + rs.getString("yearsemploy"));
System.out.print("Enter new item name: ");
yrsemp = kbd.next();
kbd.nextLine();
System.out.println("Item Monthly pay is " + rs.getString("monthpay"));
System.out.print("Enter new item name: ");
mtpy = kbd.next();
kbd.nextLine();
String updatestring ="Update emptable set FirstName = '" + fname
+"', Last name=" + lname +", street='" + street
+"', City=" + city +", State=" + state +", Zip Code="
+ zip +", Home Phone=" + hphone +", Office Phone="
+ ophone +", Department=" + deptasg +",Monthly=" + mtpy
+" Years Employed=" + yrsemp +" where empid = '" + id2
+"'";
stmt.execute(updatestring);
int updatenum = stmt.getUpdateCount();
if (updatenum < 1){
System.out.println("Error on update");
}else{
System.out.println("Updated " + updatenum +" records");
}
}catch (SQLException e){
System.out.println("Error - " + e.getMessage());
}finally{
try{
if (stmt !=null){
stmt.close();
}
if (rs !=null){
rs.close();
}
}catch (SQLException e2){
System.out.println("Error " + e2.getMessage());
}
}//close finally
}//close updates
//deleted the record (this section is working
publicstaticvoid deleteRecord(){
Statement stmt =null;
String delstring;
String id;
try{
stmt = conn.createStatement();
boolean findrec =false;
while (!findrec){
System.out.println("Enter Employee ID to delete");
id = kbd.next();
kbd.nextLine();
findrec = findRecord(id);
if (!findrec){
System.out.println("This Employee id record " + id
+" does not exist - try again");
}//close if
// delete record
delstring ="Delete from emptable where empid = '" + id +"'";
stmt.execute(delstring);
System.out.println("Deleted " + stmt.getUpdateCount()
+" records");
}//close while
}catch (SQLException e){
System.out.println("Error deleting record " + e.getMessage());
}finally{
try{
if (stmt !=null){
stmt.close();
}
}catch (SQLException e2){
System.out.println("Error removing employee info");
}
}//close finally
}//close public
publicstaticvoid showMenu(){
System.out.println("");
System.out.println("1. List employees records");
System.out.println("2. Add employees record");
System.out.println("3. Update employees record");
System.out.println("4. Delete employees record");
System.out.println("5. Exit");
}
publicstaticint getOption(){
int optn = 99;
while (optn > 5 || optn < 1){
System.out.println("");
System.out.println("Enter option");
System.out.print("===>");
optn = kbd.nextInt();
}//close while
return optn;
}
publicstaticvoid main(String[] args){
int optn = 99;
String host ="localhost";
String database ="empdata";
String user ="root";
String pass ="";
// make keyboard object
kbd =new Scanner(System.in);
String connstring ="jdbc:mysql://" + host +"/" + database +"?user="
+ user +"&password=" + pass;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(connstring);
while (optn != 5){
showMenu();
optn = getOption();
switch (optn){
case 1:
listRecords();
break;
case 2:
addToTable();
break;
case 3:
updateRecord();
break;
case 4:
deleteRecord();
case 5:
System.out.println("OK - later Come back soon");
default:
break;
}//close switch
}//close while
}catch (ClassNotFoundException e1){
System.out.println("ERROR - Class not found " + e1.getMessage());
}catch (SQLException e2){
System.out.println("ERROR - " + e2.getMessage());
System.out.println("ERROR - " + e2.getSQLState());
}catch (Exception e3){
System.out.println("Error - " + e3.getMessage());
}
}//close main
}//close class
Any help would be great.
Red
