”check the manual that corresponds to your MySQL server version for the right syntax to use“解决

今天使用hibernate3来反向建表，突然报了一个如下的错误：

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Hibernate: insert into user (name, password, gender) values (?, ?, ?)
Hibernate: insert into order (userid, order_name) values (?, ?)
Exception in thread "main" org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
  at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:145)
  at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
  at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
  at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
  at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
  at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
  at Test.TestHibernate.test(TestHibernate.java:45)
  at Test.Main.main(Main.java:7)
Caused by: java.sql.BatchUpdateException: 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 (userid, order_name) values (9, '123')' at line 1
  at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2028)
  at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1451)
  at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
  at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
  ... 9 more
Caused by: 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 (userid, order_name) values (9, '123')' at line 1
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
  at java.lang.reflect.Constructor.newInstance(Unknown Source)
  at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
  at com.mysql.jdbc.Util.getInstance(Util.java:386)
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
  at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1980)
  ... 12 more

我感觉自己写的东西没什么错误，因为照着书上写的。那几天在看hibernate的集合映射，下面是我的其余代码：

数据库映射配置文件，Bean.hbn.xml:

<?xml version="1.0" encoding='GBK'?>  
<!DOCTYPE hibernate-mapping PUBLIC  
                            "-//Hibernate/Hibernate Mapping DTD 3.0//EN"  
                            "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd" >  
  
<hibernate-mapping package="com.edu.nju.firstSSH.Bean">  
    <class name="User" table="user">  
        <id name="userid" column="userid">  
            <generator class="native"></generator>  
        </id>  
        <property name="name" column="name" type="java.lang.String"  
             length="16"></property>  
        <property name="password" column="password" type="java.lang.String"  
             length="16" />  
        <property name="gender" column="gender" type="java.lang.Integer" length="1" />
        
        <set name="orders" table="order">
             <key column="userid" not-null="true"></key>
             <element type="java.lang.String" column="order_name" not-null="true"></element>
        </set>
    </class> 
</hibernate-mapping>

实体类User，这个User里面有一个String的list集合，用来记录订单：

package com.edu.nju.firstSSH.Bean;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;


/**
 * @author Guest
 *
 */
public class User {

  private String name;
  private String password;
  private int gender;
  private int userid;
  private Set<String> orders = new HashSet<String>();
  
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  
  public String getPassword() {
    return password;
  }
  public void setPassword(String password) {
    this.password = password;
  }
  public int getGender() {
    return gender;
  }
  public void setGender(int gender) {
    this.gender = gender;
  }

  public int getUserid() {
    return userid;
  }

  public void setUserid(int userid) {
    this.userid = userid;
  }

  public Set<String> getOrders() {
    return orders;
  }
  public void setOrders(Set<String> orders) {
    this.orders = orders;
  } 
}

最后是测试类：

package Test;

import java.util.HashSet;
import java.util.Set;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import com.edu.nju.firstSSH.Bean.Order;
import com.edu.nju.firstSSH.Bean.User;

public class TestHibernate implements Test{

  private SessionFactory sessionFactory;
  public void test(){
    Configuration cfg = new Configuration().configure();
    sessionFactory = cfg.buildSessionFactory();
    Session session = sessionFactory.openSession();
    Transaction tr = session.beginTransaction();
    //User user = new User();
    //user.setGender(1);
    //user.setName("liyao");
    //user.setPassword("123");
    
    //Order o = new Order();
    //o.setOrderName("伙食");
    //user.getOrders().add(o);
    //Set<String> s = new HashSet<String>();
    //s.add("abc");
    //user.getOrders().add("abc");
    //user.setOrders(s);
    Set<String> set = new HashSet<String>();
        set.add("123");
        set.add("123");


       
            User user = new User();
            user.setUserid(2);
            user.setName("123");
            user.setOrders(set);
            session.save(user);
    tr.commit();
      session.close();
      sessionFactory.close();
  }
  public SessionFactory getSessionFactory() {
    return sessionFactory;
  }
  public void setSessionFactory(SessionFactory sessionFactory) {
    this.sessionFactory = sessionFactory;
  }
}

我这里的sessionFactory没有初始化，因为我用spring配的，spring就不贴了。

我感觉这些写的都没什么，可是一运行就报错，大致意思是sql的语法有问题，我很纳闷，这是使用hibernate反向建表啊，我压根没写什么sql语句，难道hibernate自建的语句有问题？通过设置hibernate.cfg.xml的属性：

<property name="show_sql">true</property>

可以看到他的sql语句为下面两条：

Hibernate: insert into user (name, password, gender) values (?, ?, ?)
Hibernate: insert into order (userid, order_name) values (?, ?)

这两条怎么会有错？

上网查阅之后发现这样的报错大都是因为编写sql语法导致的，可是我真的没写一条sql啊，很郁闷。。。。。

后来我又上网查找了别人写的映射set集合的例子，复制过来，发现可以运行，然后我仔细对比我和他的区别，最终终于找到了错误原因！

居然是我建的“order”表是数据库的保留字，也就是不能建立“order”表，把他改成“orders”或者其他非保留字，就可以运行了。

<set name="orders" table="orders">

这个问题整整困扰了我一天时间把，看来以后建表要格外小心了，注意避开数据库的保留字。

那么，如果真是非要用那个保留字怎么办呢？上网查了一下，分2种情况：

1，如果是表名，那么可以用如下形式：

<set name="orders" table= "`order`" >

在双引号的内部，表名的外部在上一对``符号，这个符号可不是单引号，网上有很多人说是单引号，真是胡扯。这个符号与波浪号同一个键，在最左上那里。

2，如果是列名，那么可以使用上面的方式，也可以用[ ]来代替``。

好了这个问题解决了。

最后再补充一下。如果写的sql语句的表名与关键字冲突的话，在mysql下面把表名用[]括起来，如果是sql server数据库用反单引号。