B.Bus B.Bus - 5 months ago 8
SQL Question

Servlet,Jsp project MySQL Syntax Error

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();
}
}

Answer

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.

Comments