MySQLSyntaxErrorException: Error in SQL Syntax

Question

Please have a look at the following code

 public int insert(int index, String hashWord)
    {
        String str="";
        int result=0;
        int returnResult=0;

        try
        {
            con.setAutoCommit(false);
            PreparedStatement ps = con.prepareStatement("insert into Key_Word (key,hashed_word) values(?,?)");
            ps.setInt(1, index);
            ps.setString(2, hashWord);

            result = ps.executeUpdate();
            con.commit();

            if(result>0)
            {
                returnResult = 1;
            }
            else
            {
                returnResult = -1;
                System.out.println( index+" failed to update");
            }


        }
        catch(SQLException s)
        {
            returnResult = -1;
            s.printStackTrace();
            try {
                con.rollback();
                System.out.println(index+" Exception Occured. Update Failed");

            } catch (SQLException ex) {
                ex.printStackTrace();
                str = index+" Exception Occured. Update Failed. Rollback Failed";
            }
        }

        return returnResult;
    }

When I run this code, it gives the below error.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'key,hashed_word) values(0,'001')' at line 1
0 Exception Occured. Update Failed
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
Operation Broke
Completed
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2441)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2366)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2350)
    at inglesrapido.DatabaseHandler.insert(DatabaseHandler.java:64)
    at inglesrapido.KeyWordTableCreator.readAndUpdate(KeyWordTableCreator.java:53)
    at inglesrapido.KeyWordTableCreator.<init>(KeyWordTableCreator.java:25)
    at inglesrapido.Main.main(Main.java:26)

Below is my MySQL table

enter image description here

what is wrong here?

guido · Accepted Answer · 2014-04-25 15:01:29Z

8

key is a reserved word, it needs to be escape to be used in queries, like in:

insert into Key_Word (`key`,hashed_word) values(?,?)

It would be much better however to choose another column name, which doesn't clash with a sql keyword.

edited Apr 25, 2014 at 15:01

answered Apr 25, 2014 at 14:33

guido

19.3k6 gold badges74 silver badges97 bronze badges

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

3 Comments

Luiggi Mendoza Over a year ago

I would recommend the latter more than the former.

Grijesh Chauhan Over a year ago

yes but still it is in general good practice to parenthesis by '`' for all table, column names

guido Over a year ago

@GrijeshChauhan backticks are non-standard, it is good if you decidedly target mysql. At that point I would suggest get rid of sql and use jpa for the good.

Collectives™ on Stack Overflow

MySQLSyntaxErrorException: Error in SQL Syntax

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related