Java -> MySQL query issue

Question

I am trying to create a java query to insert to MySQl but i keep getting errors. please see the code below. PS the connection to the DB is fine.

here is the query that is being called

public String newEmpInsert() {
 return newEmpInsert;
}
private String newEmpInsert = "INSERT INTO empInfo"
 + "(firstName, lastName, SSN, address, salary, pin, empLevel, contactInfo) "
 + "VALUES ("+firstName+", "+lastName+", "+SSN+", "+address+", "+salary+", "+pin+","+empLevel+", "+contactInfo+")";

here is the handler that is being called from the main

 public void newEmpInsert() {
 // SQL Connection
 Connection conn = null;
 try {
 conn = MySQL_connection_test.getConnection();
 // Create a statement
 Statement statement = conn.createStatement();
 statement.executeQuery(queries.newEmpInsert());
 } 
 catch (SQLException e) {
 // TODO Auto-generated catch block
 //e.printStackTrace();
 System.out.println("--------->>Invalid query!!!!<<--------------");
 System.out.println("Your query has an error, please try again!!");
 }
 // Close the connection
 finally {
 try {
 conn.close();
 System.out.println("Database closed");
 } 
 catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }
 System.out.println("Database closed");
 }
}

every time i run a query i am getting the invalid query catch. the variables are being set properly within the class and everything.

• Jonathon Reinhart: Look at the exception. Try adding System.out.printnl(e.getMesasge()). The exceptions almost always include very relevant information.

Answer 1

Your issue here cause you build wrong sql statement. As i look on your code you missing single quote on text field. Also your approach build Statement not good, it is easy get failure with special character like ' or " and expose for sql inject attach. Try using prepare statement and bind parameters.

Answer 2

Please change third row as VALUES and try to wrap your string values with single quotes.

private String newEmpInsert = "INSERT INTO empInfo"
+ "(firstName, lastName, SSN, address, salary, pin, empLevel, contactInfo) "
+ " VALUES ("+firstName+", "+lastName+", "+SSN+", "+address+", "+salary+", "+pin+", 
+ "+empLevel+", "+contactInfo+")";

• John Woo: No, that's not the case. The string literals were not wrapped with single quotes. But that's not the preferred way as it is vulnerable with sql injection. Parameterizing the values would be the best idea.
• Ainnera: i had the string literals wrapped in single quotes as well at first just to try different things but that didnt have any effect on the result
• Anura Adhikari: Did you try by changing as VALUES
• Warren Dew: For MySQL, VALUE and VALUES are interchangeable.

Answer 3

You need to remove the + from the quoted string at the end of the third line and the beginning of the fourth line:

private String newEmpInsert = "INSERT INTO empInfo"
+ "(firstName, lastName, SSN, address, salary, pin, empLevel, contactInfo) "
+ "VALUE ("+firstName+", "+lastName+", "+SSN+", "+address+", "+salary+", "+pin+", "
 // added close quote at the end of the above line
+empLevel+", "+contactInfo+")";
 // plus sign and quote deleted at beginning of above line

Answer 4

private String newEmpInsert = "INSERT INTO empInfo"
 + "(firstName, lastName, SSN, address, salary, pin, empLevel, contactInfo) "
 + "VALUES ("+firstName+", "+lastName+", "+SSN+", "+address+", "+salary+", 
"+pin+","+empLevel+", "+contactInfo")";

• padaleiana: Your answer is the same as this one by Anura Adhikari, but with less explanation.