Can't Update the SQL through Java

Question

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);

Do you have a column named `Phone Number` with that space in the middle? If so you need to wrap that name in backticks. — Federico klez Culloca, May 08 '22 at 09:21
Also, you should really use prepared statements. This code is wide open to SQL injection. — Federico klez Culloca, May 08 '22 at 09:21
Any table name, column name (or any names, especially file names) should **never** have spaces in them. If indeed your column name is `Phone Number`, to "fix" your problem code `"',\`Phone Number\` = '" + ...`. To fix your problem properly, `alter table employeetab rename column \`Phone Number\` to phone_number` — Bohemian, May 08 '22 at 09:25
Seriously, read up about SQL injection attacks. This code is a loaded gun pointed at your head. I also advise against using the empty string as the password of a user called `root`. — Dawood ibn Kareem, May 08 '22 at 09:28
Also, it's been about 20 years since anyone needed to execute `Class.forName("com.mysql.jdbc.Driver");` — Bohemian, May 08 '22 at 09:37

score 1 · Answer 1 · answered May 08 '22 at 09:31

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.

score 0 · Answer 2 · answered May 08 '22 at 09:46

0

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.

answered May 08 '22 at 09:46

sushant kumbhar

11
2

Grim Ranger · Answer 3 · 2022-05-08T09:38:58.940

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.

Can't Update the SQL through Java

3 Answers3