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

本文主要是介绍”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 updateat 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 1at 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 1at 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数据库用反单引号。

这篇关于”check the manual that corresponds to your MySQL server version for the right syntax to use“解决的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---