java.sql.SQLSyntaxErrorException when execute prepare statement

Question

I tried to run preparedStatement ps to check whether the data is existed in database or not. Then it will run update if existed or insert if not existed. However, I receive syntax error

java.sql.SQLSyntaxErrorException: 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 'order WHERE date = '2019-05-22'' at line 1

I cannot spot where is the error. Anyone can help?

PreparedStatement ps,ps1;
        ps =con.prepareStatement("Select 1 from order WHERE date = ?");
        ps.setString(1, date);
        ResultSet rsOrder=ps.executeQuery();  
        if(rsOrder.next()) {  
            System.out.println("reOrder:  true");
            ps1 = con.prepareStatement("UPDATE order SET result = ?  WHERE date = ?");
            ps1.setString(1, json);
            ps1.setString(2, date);
            int rs=ps1.executeUpdate();

        }else {
            System.out.println("reOrder:  false");
            ps1 = con.prepareStatement("INSERT INTO order (date,result) VALUES (?,?)");
            ps1.setString(1, date);
            ps1.setString(2, json);
            int rs=ps1.executeUpdate();
        }

Chances are that order is a reserved keyword . You may have to escape it . — Arnaud
– Arnaud, Commented May 24, 2019 at 7:38

Arulkumar · Accepted Answer · 2019-05-24 07:40:21Z

1

order is the reserved word in MySQL. So it will not allow, with out escaping the reserved words.

You can escape it by adding ` around the keyword as:

Select 1 from `order` WHERE date = ?

UPDATE `order` SET result = ? WHERE date = ?

INSERT INTO `order` (date, result) VALUES (?, ?)

answered May 24, 2019 at 7:40

Arulkumar

13.3k15 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

phoon Over a year ago

I just simply change the table name from order to orders but I accept your answer as well

Collectives™ on Stack Overflow

java.sql.SQLSyntaxErrorException when execute prepare statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related