MySQL Java syntax error

Question

Trying to figure out what the error is in this java code.

The SQLException reads: " 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 (item_quantity, customer_id, product_id) VALUES (5, 191, 31)'

The order table looks like

order_id int pk ai <br>
item_quantity <br>
customer_id int <br>
product_id int <br>

And the function that inserts is:

public void createOrder(int productQuantity, int customerId, int productId) throws SQLException {

    sql = "INSERT INTO order (item_quantity, customer_id, product_id) VALUES (" + productQuantity + ", " + customerId + ", " + productId + ")";
    try {
        int a = stmt.executeUpdate(sql);
        if (a == 1) {
            System.out.println("Order Added");
        } else {
            System.out.println("Order Failed");
        }
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
}

Any help would be greatly appreciated, can't seem to figure this out.

Unrelated to this specific problem, but as soon as you start getting comfortable with this way of doing SQL, you should start looking into PreparedStatements. They offer you greater security (if your int args were Strings, I would immediately be wondering about a SQL injection attack). — yshavit
– yshavit, Commented Apr 27, 2016 at 4:41

1000111 · Accepted Answer · 2016-04-27 04:38:11Z

3

Enclose the order (table name) by backtick like below:

INSERT INTO `order` (item_quantity, customer_id, product_id) VALUES...

Note:

The backticks help you from accidentally using a name that is a reserved word in SQL for example. Take a table named "where", it's a stupid name for a table I agree, but if you wrap it in backticks it will work fine

edited Apr 27, 2016 at 4:38

answered Apr 27, 2016 at 4:36

1000111

13.5k2 gold badges32 silver badges43 bronze badges

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

3 Comments

1000111 Over a year ago

could you print the sql variable and share the result?

ophie Over a year ago

sql variable:INSERT INTO 'order' (item_quantity, customer_id, product_id) VALUES (3, 211, 31)

1000111 Over a year ago

Probably you are not using backtick. Instead you used single quote.

Collectives™ on Stack Overflow

MySQL Java syntax error

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