i want to write to code to update mysql data in Java. Here is my code. and it gives error like this:

Question

 private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    // TODO add your handling code here:
       DefaultTableModel d1 = (DefaultTableModel)jTable2.getModel();
        int selectIndex = jTable2.getSelectedRow();                     
        int id = Integer.parseInt(d1.getValueAt(selectIndex, 0).toString());

          String bname = txtname.getText();
    CategoryItem citem = (CategoryItem) txtcategory.getSelectedItem();
  AuthorItem aitem = (AuthorItem) txtauthor.getSelectedItem();
   PublisherItem pitem = (PublisherItem) txtpub.getSelectedItem(); 

   String contents = txtcontent.getText();
   String pages = txtno.getText();
   String edition = txtedition.getText();

    try {
        pst = con.prepareStatement("update book set bname= ? , category= ? , author= ? , publisher= ? , contents= ? , pages= ? , edition= ? , where id= ? ");
        pst.setString(1, bname);
        pst.setInt(2, citem.id);
        pst.setInt(3, aitem.id);
        pst.setInt(4, pitem.id);
        pst.setString(5, contents);
        pst.setString(6, pages);
        pst.setString(7, edition);
        pst.setInt(8,id);
        int k=pst.executeUpdate();

        if(k==1)
        {
            JOptionPane.showMessageDialog(this,"Book Added");       
            txtname.setText("");
            txtcategory.setSelectedIndex(-1);
            txtauthor.setSelectedIndex(-1);
            txtpub.setSelectedIndex(-1);
            txtcontent.setText("");
            txtno.setText("");
            txtedition.setText("");             
        }
        else
        {
            JOptionPane.showMessageDialog(this,"Error");
        }
        // TODO add your handling code here:
    } catch (SQLException ex) {
        Logger.getLogger(Book.class.getName()).log(Level.SEVERE, null, ex);
    }
}                                        
// and it gives error like this
//com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check //the manual that corresponds to your MariaDB server version for the right syntax to use near 'where id= //1' at line 1

Answer 1

Possible reason of this error is that you wrote wrong query. Please see below:

 pst = con.prepareStatement("update book set bname= ? , category= ? , author= ? , publisher= ? , contents= ? , pages= ? , edition= ? , where id= ? ");

edition= ? , where

remove the , before where

---

Tip for future so that don't stuck in future

1. Try to debug code by you self line by line and identify line number.
2. Read exception carefully.
3. If problem is in SQL (You can determine by exception name as here MySQLSyntaxErrorException) then first execute your query manually at database console.