Inserting value into a single column in MySQL using Java

Question

i'm a novice programmer and following is a part of my code to insert data into the database whose name is book_details.

 try{
  //STEP 2: Register JDBC driver
  Class.forName("com.mysql.jdbc.Driver");

  //STEP 3: Open a connection
  System.out.println("Connecting to database...");
  conn = DriverManager.getConnection(DB_URL,USER,PASS);

  //STEP 4: Execute a query
  System.out.println("Creating statement...");
  stmt = conn.createStatement();
  String sql;
  sql = "INSERT INTO issue_details VALUES("+BookIDvar+",'"+StudentIDvar+"','"+StudentNamevar+"')";
  stmt.executeUpdate(sql);
  sql = "INSERT INTO book_details (book_status) VALUES ('notavailable') WHERE book_id = "+BookIDvar+"";
// there are 4 fields in the database. I wish to insert the string 'notavailable' into the 4th field which is named book_status for a particular BookID
  stmt.executeUpdate(sql);
  //STEP 6: Clean-up environment

  stmt.close();
  conn.close();
 }catch(SQLException se){
  //Handle errors for JDBC
  se.printStackTrace();
 }catch(Exception e){
  //Handle errors for Class.forName
  e.printStackTrace();
 }finally{
  //finally block used to close resources
  try{
     if(stmt!=null)
        stmt.close();
  }catch(SQLException se2){
  }// nothing we can do
  try{
     if(conn!=null)
        conn.close();
  }catch(SQLException se){
     se.printStackTrace();
  }//end finally try
 }

Here i intend to update the status of a library book available and not available. I prefer non - Boolean value for some other reasons.

After the execution the book_details database is not updated. It also gives an error

MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server .

Is there something wrong with the Sql statement or is it something else? Other operations on the same database in other locations are working fine.

Which of those two queries is failing? and given that you're wide open for [sql injection attacks](http://bobby-tables.com), no surprise that it's not working. — Marc B, Jun 30 '14 at 17:24
I guess that book_status is a string? Probably not the issue, but did you try adding a semi-colon at the end of each Statement? — Archibald, Jun 30 '14 at 17:26

score 4 · Answer 1 · edited May 23 '17 at 12:29

This SQL syntax is wrong:

INSERT INTO <table> VALUES <values> [WHERE <whatever>]

You cannot add a WHERE in an INSERT like that.

The only case to add a WHERE statement in an INSERT would be when inserting data from the result of a SELECT statement and the WHERE is part of the SELECT. For example:

INSERT INTO <table> [SELECT <columns> FROM <tables> WHERE <conditions>]

In this form, the columns obtained in the SELECT must match with the values to insert in the table.

Looks like you want to update the status of the book. You should use UPDATE statement instead of INSERT:

UPDATE <table> SET (<column> = <value>) (supports multiple columns) WHERE <conditions>

Applying this to your context, the statement should be:

UPDATE book_details SET book_status = 'notavailable' WHERE book_id = ?

Also, since this is an all or nothing transaction, you should open the transaction and commit if all the statements where executed successfully, otherwise perform a rollback. Here's how you may do it:

try {
    conn.setAutoCommit(false);
    //perform the DML statements
    //...
    //commit the transaction
    conn.commit();
} catch (Exception e) {
    //in case of error, rollback the transaction
    conn.rollback();
    //notify the error
    e.printStacktrace();
} finally {
    //ALWAYS close the connection here
    //handle the exceptions and such
    conn.close();
}

Note that when you need to pass arguments to your SQL statements, you should use PreparedStatement instead of Statement since concatenating the query could lead to SQL Injection attacks. The code should look like:

sql = "INSERT INTO issue_details VALUES(?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, BookIDvar);
pstmt.setString(2, StudentIDvar);
pstmt.setString(3, StudentNamevar);
pstmt.executeUpdate();
//similar for other SQL statements

More info:

Difference between Statement and PreparedStatement

@SoorajChandran it is unclear what you're trying to achieve. Please provide the functional requirement along to the execution of your queries to provide specific guidance. — Luiggi Mendoza, Jun 30 '14 at 17:31

score 3 · Accepted Answer · answered Jun 30 '14 at 17:28

As Luggi mentioned that is incorrect.

update book_details set book_status = 'notavailable' where book_id = bookIDvar;

assuming it already exists in the table.

If not,

insert into book_details (book_id, book_status) values (bookIDvar, 'notavailable');

Inserting value into a single column in MySQL using Java

2 Answers2