java mysql update

Question

I have this error while executing a java class to update a mysql db

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 'usage = 6000 where user_id = 1 and api_id = 1'
 at line 1
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct
orAccessorImpl.java:39)..

here is the code:

int u_id=1;
int a_id=1;

String url = "jdbc:mysql://localhost:3306/new_db";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "1234";
int APIusage = 0;


try {
    Class.forName(driver).newInstance();

    Connection conn = DriverManager.getConnection(url, userName, password);
    String query = "update api_consume set usage = ? where user_id = ? and api_id = ?";
    PreparedStatement preparedStmt = conn.prepareStatement(query);
    preparedStmt.setInt(1, 6000);
    preparedStmt.setInt(2, u_id);
    preparedStmt.setInt(3, a_id);

    // execute the java preparedstatement
    preparedStmt.executeUpdate();

    conn.close();
} 
catch (Exception e) {
      e.printStackTrace();
}

Can you please have a look into it to see why there is a syntax error? thanks

João Silva · Accepted Answer · 2012-09-07 09:39:44Z

4

usage is a reserved word in MySQL, thus you need to quote it:

String query = "update api_consume set `usage` = ? where user_id = ? and api_id = ?";

answered Sep 7, 2012 at 9:39

João Silva

91.8k29 gold badges156 silver badges158 bronze badges

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

Comments

Chris · Accepted Answer · 2012-09-07 09:40:14Z

0

It is because of usage key word. Change that column name to usage_msg or something.

answered Sep 7, 2012 at 9:40

Chris

5,71410 gold badges43 silver badges59 bronze badges

Collectives™ on Stack Overflow

java mysql update

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related