Servlet,Jsp project MySQL Syntax Error

Question

I create project servlet and jsp. I am using database phpmyadmin. I met an syntax error showing product from database. Returned the following error in Eclipse EE console.I did not solve the problem. Any help will be appreciated.

 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 'order
 (id,idUser,order,total) values (default,'1','flower','83.8883')' at
 line 1

Servlet Class:

protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        // TODO Auto-generated method stub


        String idUser =  req.getParameter("idUser");
        System.out.println(idUser);
        String order =  req.getParameter("order");
        System.out.println(order);
        String total =  req.getParameter("total");
        System.out.println(total);

        try {

            Class.forName("com.mysql.jdbc.Driver");
            Connection cnx = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/shop","root","");
            PreparedStatement pr =  (PreparedStatement) cnx.prepareStatement(
                    "insert into order (id,idUser,order,total) values (default,?,?,?)"
                    );

               pr.setString(1,idUser);
               pr.setString(2,order );
               pr.setString(3, total);



               pr.executeUpdate();
               pr.close();

               resp.sendRedirect("Home");   


        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

Please post the actual query that is being fired – Niks Jun 12 '16 at 14:40 — Niks, Jun 12 '16 at 14:40
Post [SSCCE](http://sscce.org) / [MCVE] – Pshemo Jun 12 '16 at 14:41 — Pshemo, Jun 12 '16 at 14:41

score 1 · Accepted Answer · edited May 23 '17 at 12:31

From the exception I can see that your table name is order which is a reserved word in MySql. You need to modify your queries in order to use it.

Simplest MySql specific way is to escape it with " ` "

create table `order` 
#... rest of the definition
insert into `order` values 
#... rest of insert statement

There are Database agnostic ways too. For more information, refer to this answer.

Servlet,Jsp project MySQL Syntax Error

1 Answers1