Hibernate操作MySQL使用reserved word引发错误: “You have an error in your SQL syntax; check the manual that co

今天利用Hibernate4.0上往MySQL 5数据库插入一个Entity时，一直出现以下的错误：

 

You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to user near `show, sid) value

 

一直觉得不应该啊，怎么会呢，不可能啊，自己就是在想这些无用的东西，也尝试了好多次，感觉都是徒劳，后来采取科学的解决步骤来逐步地一点一滴地循序渐进地找到解决方法，其实后来证明虽然一步一步来，看起来慢，但这毕竟是一条科学的方法，只要沿着这条道路往前走，离正确的答案只会越来越近的，肯定会迅速发现问题的所在的。这其中，我觉得排除法是一个很好的方法，例如我要插入这个Entity不成功，那我就把这个Entity的其他attribute先去掉，只保留一个attribute，然后看能否insert成功；然后逐步加入其它的attribute，直到一个attribute被加进去以后，引发了上述错误，这样我们就能够迅速地确定到底是哪一个attribute引发了上述错误，很快地找到问题的根源，有的放矢。

 

事实证明，开始漫无目的方法花费了一天时间都没能找到正确的解决方法，而按照后一种方法，很快就找到问题的原因(加入show这个attribute后开始出错)。下面针对此问题开始分析问题原因。

 

Hibernate 定义:

@Column(nullable = false)
private boolean show;

 

MySQL中定义是这样的：

Show bit(1) not null

 

一种可能是因为MySQL中的bit datatype和JAVA中的boolean不能正确转换，stackoverflow中有个类似的问题如下：

Causedby: org.hibernate.HibernateException:
Wrong column type in PUBLIC.PUBLIC.EVENT for column Checked. Found: bit,expected: boolean

 

I worked this out by adding columnDefinition = "BIT" to the@Column line.

@Column(name = "B", columnDefinition = "BIT", length =1)
public boolean isB() {
    return b;
}

Its defined as a'BIT(1)' in the DB as well. Also worked with TINYINT. This is the easiestsolution I've found since the change is super-minor and no need to touch theDB.

Using: MySQL Server5.5.13, Hibernate 4.1.1, JDK 1.6

 

我按照以上的思路，改造了我的show属性，可是还是不成功，由此可见，我的问题只是与上面这个问题相似，但不是由以上原因引起的。还有一些人建议should not use BIT columns in MySQL，建议使用tinyint，但也不是问题的主要原因。

 

应该是show属性引起的，继续google，还是stackoverflow中有人碰到类似的问题，解决方法如下：

Ithink the problem is that ORDER is aMySQL reserved word.

To get the INSERTstatement to execute, you'd need to have that column name enclosed inbackticks, like this:

insert into folder (folder_name, `order`) values (?,?)

 

OKI've found solution; we still can use 'order'keyword as column name like this:

@Column(name = "`order`", length = 10,precision =0)
private int order;

 

 

好吧，既然order是MySQL的reserved word,那show也很有可能，果然改成其他名称后立即得到解决：

@Column(name = "label_show", nullable = false)
private boolean show;

 

查阅MySQL 官方文档，可以得到Reserved Words in MySQL：

Table 2.1. Reserved Words in MySQL 5.0

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[a]	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[b]	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[c]
LEADING	LEAVE	LEFT
LIKE	LIMIT	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
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
SONAME	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	UPGRADE[d]
USAGE	USE	USING
UTC_DATE	UTC_TIME	UTC_TIMESTAMP
VALUES	VARBINARY	VARCHAR
VARCHARACTER	VARYING	WHEN
WHERE	WHILE	WITH
WRITE	XOR	YEAR_MONTH
ZEROFILL

 

来自 <http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html>