CODEWITHSUNDEEP
javamysqlprepared-statement
Mahdi_Nine
Mahdi_Nine

Reputation: 14761

Problem with a prepared statement

I have this code:

 Date start = new Date(Integer.parseInt(jTextField4.getText()), Integer.parseInt(jTextField16.getText()), Integer.parseInt(jTextField17.getText()));  
        Date end = new Date(Integer.parseInt(jTextField5.getText()), Integer.parseInt(jTextField18.getText()), Integer.parseInt(jTextField19.getText()));
        statement = connection.createStatement();
        preparedStatement1 = connection.prepareStatement("insert into sportmangg(customer_code,"
             + "sportman_code, start, finish, salary,amount,box salary,private salary, food salary, "
             + "other salary, bime salary, number) "
             + "values (? ,?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?");
      preparedStatement1.setString(1,jTextField15.getText());
     preparedStatement1.setString(2, jTextField1.getText());
     preparedStatement1.setDate(3, start);
     preparedStatement1.setDate(4, end);
     preparedStatement1.setInt(5, Integer.parseInt(jTextField6.getText()) );
     preparedStatement1.setInt(6,Integer.parseInt(jTextField14.getText()) );
     preparedStatement1.setInt(7, Integer.parseInt(jTextField7.getText()));
     preparedStatement1.setInt(8, Integer.parseInt(jTextField8.getText()));
     preparedStatement1.setInt(9, Integer.parseInt(jTextField9.getText()));
     preparedStatement1.setInt(10, Integer.parseInt(jTextField11.getText()));
     preparedStatement1.setInt(11, Integer.parseInt(jTextField10.getText()));
     preparedStatement1.setInt(12, Integer.parseInt(jTextField20.getText()));
     preparedStatement1.executeUpdate();

but it has this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'salary,private salary, food salary, other salary, bime salary, number) values ('' at line 1

What is the problem?

Upvotes: 0

Views: 1927

Answers (7)

stuparm
stuparm

Reputation: 553

Maybe you can try this:
https://github.com/stuparmihailo/util4j/releases/tag/v1.0
It's some simple project and has nice way for creating statements:
String query = "INSERT INTO table VALUES (?,?,?,?,?,?,?)"; PreparedStatement stmt = con.prepareStatement(query); StatementUtil.fill(stmt, 45, "text", 2, null, new Date(), false, 3.5);

Upvotes: 1

Francisco Alvarado
Francisco Alvarado

Reputation: 2813

mehdi; I think what you have to do is all of this:

  1. change names of space-named columns (private salary, food salary, other salary, bime salary) either by replacing spaces by underscores (recommended by naming conventions) or by surrounding names with grave accent char:

    `box salary`, `private salary`, `food salary`, `other salary`, `bime salary`

  2. Fix this line adding final parentheses

    + "values (? ,?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?");

    it must say:

    + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");

  3. Finally I'd recommend to take out prepareStatement argument to a String or StringBuffer variable, say "sqlString" or something, so you can manipulate it more transparently. Something like this:

String sqlString = "";
sqlString += " insert into sportmangg";
sqlString += " (customer_code, sportman_code, start, finish,";
sqlString += " salary, amount, box_salary, private_salary,";
sqlString += " food_salary, other_salary, bime_salary, number)";
sqlString += " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
preparedStatement1 = connection.prepareStatement(sqlString);

(or if you use StringBuffer use append method)

Upvotes: 0

Atul
Atul

Reputation: 2711

column or table names should not have spaces. Join them by underscore. and make them upper case... these are not rules but accepted ways of working with DB objects. If names cannot be changed in the DB and you are stuck with something like some salary, then some salary should help.

Upvotes: 0

Skarab
Skarab

Reputation: 7141

You missed ) in the last line of your SQL query so it should be:

+ "  values (? ,?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?   )";

Upvotes: 1

duffymo
duffymo

Reputation: 309008

Column names with spaces in them are a very bad idea.

If you must have them, surround them with backticks: 

`private salary`

Upvotes: 3

Gleeb
Gleeb

Reputation: 11299

You should replace private salary with private_salary and keep working with acceptable column name conventions.

Upvotes: 0

Chris Muench
Chris Muench

Reputation: 18338

You really shouldn't have spaces in the field name. Try surrounding it with ``

Upvotes: 11

Related Questions