java - ERROR: You have an error in your SQL syntax in Hibernate

Question

I'm having a problem with a record deletion from data base. I've been trying to figure out what i'm doing wrong for couple of hours now. I searched some topics here but none of them answered what am i doing wrong. I've made these 3 tables in my data base:

CREATE TABLE IF NOT EXISTS `dat1`.`M_CUSTOMER_LIST` (
  `CUSTOMER_LIST_ID` int(4) NOT NULL primary KEY AUTO_INCREMENT,
  `C_NAME` varchar(45) NOT NULL,
  `C_LAST_NAME` varchar(45) NOT NULL,
  `C_P_CODE` varchar(12) NOT NULL,
  `C_MAIL` varchar(45) NOT NULL,
  `C_PHONE_NR` varchar(12) NOT NULL,
  `C_USER_TYPE` varchar(45) NOT NULL,
  `C_PASSWORD` varchar(45) NOT NULL
);

CREATE TABLE IF NOT EXISTS `dat1`.`M_SERVICES` (
 `M_SERVICE_ID` int(4) NOT NULL primary KEY AUTO_INCREMENT,
 `SERVICE_NAME` varchar(45) NOT NULL,
 `SERVICE_PRICE` INT(4) NOT NULL,
 `SERVICE_DESCRIPTION` int(60) NOT NULL
);

CREATE TABLE IF NOT EXISTS `dat1`.`ORDER` (
  `ORDER_ID` int(4) NOT NULL primary KEY AUTO_INCREMENT,
  `ORDER_DATE` varchar(45) NOT NULL,
  `M_CUST` int(4) NOT NULL,
  `M_SERV` int(4) NOT NULL,
  foreign key (`M_SERV`) references `M_SERVICES`(`M_SERVICE_ID`),
  foreign key (`M_CUST`) references `M_CUSTOMER_LIST`(`CUSTOMER_LIST_ID`)
);

This is my hibernate mapping file:

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 <hibernate-mapping>
   <class name="dat3.MCustomerList" table="m_customer_list">
     <id column="CUSTOMER_LIST_ID" name="customerList_id" type="int">
       <generator class="native"/>
     </id>
    <property column="C_NAME" name="c_name" type="string"/>
    <property column="C_L_NAME" name="c_l_name" type="string"/>
    <property column="C_P_CODE" name="c_p_code" type="string"/>
    <property column="C_EMAIL" name="c_email" type="string"/>
    <property column="C_PHONE_NR" name="c_phone_nr" type="string"/>
    <property column="C_USER_TYPE" name="c_user_type" type="string"/>
    <property column="C_PASSWORD" name="c_password" type="string"/>
  </class>
  <class name="dat3.M_Services" table="m_services">
    <id column="M_SERVICE_ID" name="m_service_id" type="int">
  <generator class="native"/>
    </id>
    <property column="SERVICE_NAME" name="service_name" type="string"/>
    <property column="SERVICE_PRICE" name="service_price" type="int"/>
    <property column="SERVICE_DESCRIPTION" name="service_description" type="string"/>
  </class>
  <class name="dat3.Order" table="order">
    <id column="ORDER_ID" name="order_id" type="int">
  <generator class="native"/>
    </id>
    <property column="ORDER_DATE" name="order_date" type="date"/>
    <many-to-one class="dat3.MCustomerList" column="M_CUST" name="customerList" not-null="true"/>
    <many-to-one class="dat3.M_Services" column="M_SERV" name="m_service" not-null="true"/>
   </class>
</hibernate-mapping>

So when i try to delete one record from my jTable, it gives me an error. Here's the code which i'm using to delete data from jTable and data base:

    if ((this.jTable1.getSelectedRow() != -1) && (this.jTable1.getValueAt(this.jTable1.getSelectedRow(), 0) != ""))
    {
        ServiceCheck serviceCheck = new ServiceCheck();
        serviceCheck.deleteOrder(Integer.valueOf(this.jTable1.getModel().getValueAt(this.jTable1.getSelectedRow(), 0).toString()).intValue());
    }
    else
    {
      JOptionPane.showMessageDialog(null, "Error");
    }

ServiceCheck.java

public void deleteOrder(int id){
  Session session = factory.openSession();
  Transaction tx = null;
  try{
     tx = session.beginTransaction();
     Order order = (Order)session.get(Order.class, Integer.valueOf(id)); 
     session.delete(order); 
     JOptionPane.showMessageDialog(null, "You have successfully deleted your order");
     tx.commit();
  }catch (HibernateException e) {
     if (tx!=null) tx.rollback();
     e.printStackTrace(); 
  }finally {
     session.close(); 
  }
}

Here's the error:

 ERROR: 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 order0_ where order0_.ORDER_ID=2' at line 1

Any help would be gladly appreciated!

Answer 1

ORDER is a reserved SQL keyword. Create the ORDER table with a different name