hibernate操作Mysql出现版本错误问题

问题描述：

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 'check, uploader, format, category, bookpk) values ('testbook', '1234567890', 'te' at line 1

 

以上是使用Hibernate向mysql 5.1中存储数据时出现的错误。元数据为Book，代码如下：

package org.brucefeng.sinter.domain;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang.builder.ToStringBuilder;

public class Book {
	private String bookPK;
	private String title;
	private String isbn;
	private String author;
	private String description;
	private String press;
	private String filePath;
	private boolean checked;
	private int edition;
	private int pageCount;
	private double size;
	private BookFormat format;
	private Category category;
	private Date uploadDate;
	private User uploader;
	private List<BookTag> tags;
	private List<BookComment> comments;
	public Book() {
		// TODO Auto-generated constructor stub
	}
	public String getBookPK() {
		return bookPK;
	}
	private void setBookPK(String bookPK) {
		this.bookPK = bookPK;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getPress() {
		return press;
	}
	public void setPress(String press) {
		this.press = press;
	}
	public int getEdition() {
		return edition;
	}
	public void setEdition(int edition) {
		this.edition = edition;
	}
	public int getPageCount() {
		return pageCount;
	}
	public void setPageCount(int pageCount) {
		this.pageCount = pageCount;
	}
	public double getSize() {
		return size;
	}
	public void setSize(double size) {
		this.size = size;
	}
	public BookFormat getFormat() {
		return format;
	}
	public void setFormat(BookFormat format) {
		this.format = format;
	}
	public Date getUploadDate() {
		return uploadDate;
	}
	public void setUploadDate(Date uploadDate) {
		this.uploadDate = uploadDate;
	}
	public User getUploader() {
		return uploader;
	}
	public void setUploader(User uploader) {
		this.uploader = uploader;
	}
	public List<BookTag> getTags() {
		return tags;
	}
	public void setTags(List<BookTag> tags) {
		this.tags = tags;
	}
	public List<BookComment> getComments() {
		return comments;
	}
	public void setComments(List<BookComment> comments) {
		this.comments = comments;
	}
	public String getIsbn() {
		return isbn;
	}
	public void setIsbn(String isbn) {
		this.isbn = isbn;
	}
	public String getFilePath() {
		return filePath;
	}
	public void setFilePath(String filePath) {
		this.filePath = filePath;
	}
	
	public boolean isChecked() {
		return checked;
	}
	public void setChecked(boolean checked) {
		this.checked = checked;
	}
	public Category getCategory() {
		return category;
	}
	public void setCategory(Category category) {
		this.category = category;
	}
	public boolean isTransient(){
		return this.bookPK==null;
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((author == null) ? 0 : author.hashCode());
		result = prime * result
				+ ((category == null) ? 0 : category.hashCode());
		result = prime * result + (checked ? 1231 : 1237);
		result = prime * result
				+ ((comments == null) ? 0 : comments.hashCode());
		result = prime * result
				+ ((description == null) ? 0 : description.hashCode());
		result = prime * result + edition;
		result = prime * result
				+ ((filePath == null) ? 0 : filePath.hashCode());
		result = prime * result + ((format == null) ? 0 : format.hashCode());
		result = prime * result + ((isbn == null) ? 0 : isbn.hashCode());
		result = prime * result + pageCount;
		result = prime * result + ((press == null) ? 0 : press.hashCode());
		long temp;
		temp = Double.doubleToLongBits(size);
		result = prime * result + (int) (temp ^ (temp >>> 32));
		result = prime * result + ((tags == null) ? 0 : tags.hashCode());
		result = prime * result + ((title == null) ? 0 : title.hashCode());
		result = prime * result
				+ ((uploadDate == null) ? 0 : uploadDate.hashCode());
		result = prime * result
				+ ((uploader == null) ? 0 : uploader.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Book other = (Book) obj;
		if (author == null) {
			if (other.author != null)
				return false;
		} else if (!author.equals(other.author))
			return false;
		if (category == null) {
			if (other.category != null)
				return false;
		} else if (!category.equals(other.category))
			return false;
		if (checked != other.checked)
			return false;
		if (comments == null) {
			if (other.comments != null)
				return false;
		} else if (!comments.equals(other.comments))
			return false;
		if (description == null) {
			if (other.description != null)
				return false;
		} else if (!description.equals(other.description))
			return false;
		if (edition != other.edition)
			return false;
		if (filePath == null) {
			if (other.filePath != null)
				return false;
		} else if (!filePath.equals(other.filePath))
			return false;
		if (format == null) {
			if (other.format != null)
				return false;
		} else if (!format.equals(other.format))
			return false;
		if (isbn == null) {
			if (other.isbn != null)
				return false;
		} else if (!isbn.equals(other.isbn))
			return false;
		if (pageCount != other.pageCount)
			return false;
		if (press == null) {
			if (other.press != null)
				return false;
		} else if (!press.equals(other.press))
			return false;
		if (Double.doubleToLongBits(size) != Double
				.doubleToLongBits(other.size))
			return false;
		if (tags == null) {
			if (other.tags != null)
				return false;
		} else if (!tags.equals(other.tags))
			return false;
		if (title == null) {
			if (other.title != null)
				return false;
		} else if (!title.equals(other.title))
			return false;
		if (uploadDate == null) {
			if (other.uploadDate != null)
				return false;
		} else if (!uploadDate.equals(other.uploadDate))
			return false;
		if (uploader == null) {
			if (other.uploader != null)
				return false;
		} else if (!uploader.equals(other.uploader))
			return false;
		return true;
	}
	@Override
	public String toString() {
		return ToStringBuilder.reflectionToString(this);
	}
	

}

 其配置文件为：

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
	"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
	"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="org.brucefeng.sinter.domain">
  <class name="Book" table="book">
  	<id name="bookPK" column="bookpk" unsaved-value="null">
  		<generator class="uuid.hex"></generator>
  	</id>
  	<property name="title" column="title" type="string"></property>
  	<property name="isbn" column="ISBN" type="string"></property>
  	<property name="author" column="author" type="string"></property>
  	<property name="press" column="press" type="string"></property>
  	<property name="description" column="description" type="text"></property>
  	<property name="edition" column="edition" type="integer"></property>
  	<property name="pageCount" column="pagecount" type="integer"></property>
  	<property name="size" column="size" type="double"></property>
  	<property name="filePath" column="filepath" type="string"></property>
  	<property name="uploadDate" column="uploaddate" type="timestamp"></property>
  	<property name="check" column="check" type="boolean"></property>
  	<many-to-one name="uploader" column="uploader" class="org.brucefeng.sinter.domain.User"
  				 unique="true" fetch="join" lazy="false" cascade="save-update"></many-to-one>
  	<many-to-one name="format" column="format" class="org.brucefeng.sinter.domain.BookFormat"
  				 unique="true" lazy="false" fetch="join" cascade="save-update"></many-to-one>
  	<many-to-one name="category" column="category" class="org.brucefeng.sinter.domain.Category"
  				 unique="true" fetch="join" lazy="false" cascade="all"></many-to-one>
  	<list name="tags" table="book_tag" cascade="save-update" lazy="false">
  		<key column="bookpk"></key>
  		<index column="index"></index>
  		<many-to-many column="tagpk" class="org.brucefeng.sinter.domain.BookTag" fetch="join"></many-to-many>
  	</list>
  	<list name="comments" table="book_comment" cascade="all" lazy="false">
  		<key column="bookpk"></key>
  		<index column="index"></index>
  		<many-to-many column="commentpk" class="org.brucefeng.sinter.domain.BookComment" fetch="join"></many-to-many>
  	</list>
  </class>
</hibernate-mapping>

问题解决

在数据库关系中出现了多对一，多对多关系，相对较为复杂，但配置均正确，出现上述问题实为诡异。在查询了多方资料无果之后，想起了Mysql中存在关键字问题，如果在数据库表的列名使用关键字或保留字的话，将会出现问题。于是查了一下Mysql关键字，发现在数据库表中使用了check和index两个关键字，修改之后保存即可顺利进行。

 

Mysql关键字

ADD	ALL	ALTER
ANALYZE	AND	AS
ASC	ASENSITIVE	BEFORE
BETWEEN	BIGINT	BINARY
BLOB	BOTH	BY
CALL	CASCADE	CASE
CHANGE	CHAR	CHARACTER
CHECK	COLLATE	COLUMN
CONDITION	CONNECTION	CONSTRAINT
CONTINUE	CONVERT	CREATE
CROSS	CURRENT_DATE	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_USER	CURSOR
DATABASE	DATABASES	DAY_HOUR
DAY_MICROSECOND	DAY_MINUTE	DAY_SECOND
DEC	DECIMAL	DECLARE
DEFAULT	DELAYED	DELETE
DESC	DESCRIBE	DETERMINISTIC
DISTINCT	DISTINCTROW	DIV
DOUBLE	DROP	DUAL
EACH	ELSE	ELSEIF
ENCLOSED	ESCAPED	EXISTS
EXIT	EXPLAIN	FALSE
FETCH	FLOAT	FLOAT4
FLOAT8	FOR	FORCE
FOREIGN	FROM	FULLTEXT
GOTO	GRANT	GROUP
HAVING	HIGH_PRIORITY	HOUR_MICROSECOND
HOUR_MINUTE	HOUR_SECOND	IF
IGNORE	IN	INDEX
INFILE	INNER	INOUT
INSENSITIVE	INSERT	INT
INT1	INT2	INT3
INT4	INT8	INTEGER
INTERVAL	INTO	IS
ITERATE	JOIN	KEY
KEYS	KILL	LABEL
LEADING	LEAVE	LEFT
LIKE	LIMIT	LINEAR
LINES	LOAD	LOCALTIME
LOCALTIMESTAMP	LOCK	LONG
LONGBLOB	LONGTEXT	LOOP
LOW_PRIORITY	MATCH	MEDIUMBLOB
MEDIUMINT	MEDIUMTEXT	MIDDLEINT
MINUTE_MICROSECOND	MINUTE_SECOND	MOD
MODIFIES	NATURAL	NOT
NO_WRITE_TO_BINLOG	NULL	NUMERIC
ON	OPTIMIZE	OPTION
OPTIONALLY	OR	ORDER
OUT	OUTER	OUTFILE
PRECISION	PRIMARY	PROCEDURE
PURGE	RAID0	RANGE
READ	READS	REAL
REFERENCES	REGEXP	RELEASE
RENAME	REPEAT	REPLACE
REQUIRE	RESTRICT	RETURN
REVOKE	RIGHT	RLIKE
SCHEMA	SCHEMAS	SECOND_MICROSECOND
SELECT	SENSITIVE	SEPARATOR
SET	SHOW	SMALLINT
SPATIAL	SPECIFIC	SQL
SQLEXCEPTION	SQLSTATE	SQLWARNING
SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS	SQL_SMALL_RESULT
SSL	STARTING	STRAIGHT_JOIN
TABLE	TERMINATED	THEN
TINYBLOB	TINYINT	TINYTEXT
TO	TRAILING	TRIGGER
TRUE	UNDO	UNION
UNIQUE	UNLOCK	UNSIGNED
UPDATE	USAGE	USE
USING	UTC_DATE	UTC_TIME
UTC_TIMESTAMP	VALUES	VARBINARY
VARCHAR	VARCHARACTER	VARYING
WHEN	WHERE	WHILE
WITH	WRITE	X509
XOR	YEAR_MONTH	ZEROFILL

MySQL允许部分关键字用做未引起来的识别符，因为许多人以前曾使用过它们。下面列出了一些例子：
ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP