Can't Update the SQL through Java-CodePudding

I'm trying to make CRUD (Create, Read, Update, Delete) to my projects. But it seems the "update" doesn't work. It keeps saying

java.sql.SQLSyntaxErrorException : You have an error in your SQL syntax; check the manual that coresponds to your MariaDB server version for the right syntax to use near "Number" = 0813874810 WHERE Name = "Gregory" at line 1)

What the solution for this?

Here is my code:

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/employeedata", "root", "");
String sql = "UPDATE employeetab SET Name = '"   txtEmployeeName.getText()                       
          "',Address = '"   txtEmployeeAddress.getText()
          "',Gender = '"   gender_type
          "',Phone Number = '"   txtEmployeePhone.getText()
          "' WHERE Name = '"   txtEmployeeName.getText()   "'";
stm = conn.prepareStatement(sql);
stm.execute(sql);
JOptionPane.showMessageDialog(this, "Update successfully");
this.setVisible(false);

CodePudding user response：

Problem comes from the space in column Phone Number. To make it work you need to escape the column name with `.

UPDATE employeetab 
SET Name = 'something',Address = 'some address',Gender = 'whatever',`Phone Number` = '000000000'
WHERE Name = 'something';

You should follow sql naming conventions, normally words in column names are separated by _. Your column name should be - phone_number.

Also, as mentioned in comments, you should not just add user input into sql queries, because you are leaving yourself wide open for sql injection.

CodePudding user response：

You need to follow the naming conventions , their is space between 'Phone Number' column you should not write like this you need to add _ in between of this two.

CodePudding user response：

try this :

           String gender_type = null;
        if (ButtonM.isSelected()){
                gender_type = "Male";
                }else if(ButtonFM.isSelected()){
                gender_type = "Female";
                }
                try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/employeedata","root","");
                String sql = "UPDATE employeetab SET Name = ? ,"  
                "  Address = ? ,"  
                " Gender = ? ,"  
                " Phone Number = ? ,"  
                " WHERE Name = ? ," ;

                PreparedStatement pStmt = conn.prepareCall(sql);
                pStmt.setString(1, txtEmployeeName.getText() "");
                pStmt.setString(2, txtEmployeeAddress.getText() "");
                pStmt.setString(3, gender_type "");
                pStmt.setString(4, txtEmployeePhone.getText() "");
                pStmt.setString(5, txtEmployeeName.getText());
                pStmt.executeUpdate();
                JOptionPane.showMessageDialog(this, "Update successfully");
                this.setVisible(false);


                }catch (Exception e){
                JOptionPane.showMessageDialog(null, e);
                }

its cleaner and should work.