MySql Insert Query

Question

Im trying to insert data into table using ArrayList then I got a erro message like this:

  com.mysql.jdbc.exceptions.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 'order(orderid, customername, item, qty, amount, total) VALUES(3,'lahiru','chick ' at line 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)

I try in different ways, bt din't gp right Here is my Method that used for insert values.

 public class Order {
 Connection con;
    public void passingValuesToDB(ArrayList<OrderModel> list) {
     try {
        con = new DBconnector().connect();
        String sql = "insert into order(orderid, customername, item, qty, amount, total) VALUES(?,?,?,?,?,?)";

        PreparedStatement ps = con.prepareStatement(sql);
        for(int i=0;i<list.size();i++){
            ps.setInt(1, list.get(i).getOrderid());
            ps.setString(2, list.get(i).getCustomername());
            ps.setString(3, list.get(i).getItem());
            ps.setDouble(4, list.get(i).getQty());
            ps.setDouble(5, list.get(i).getAmount());
            ps.setDouble(6, list.get(i).getTotal());
            ps.addBatch();
        }
        ps.execute();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    }

can some one please help me to solve this problem.

Answer 1

Order is a keyword, if you like to use it as a table name, you have to escape it by using backticks.

String sql = "insert into `order`(orderid, customername, item, qty, amount, total) VALUES(?,?,?,?,?,?)";

I think the better solution is to rename the table.