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>