在ORACLE中,CHAR类型的字段值会自动补足空格。所以当其作为条件时,就有可能查不出想要的数据。
如果不允许改DB设计的话,那就用RTRIM来解决吧。例:
DB:
CREATE TABLE TEST ( TEST_ID VARCHAR2(64) NOT NULL, TEST_CHAR CHAR(10), CONSTRAINT TEST_ID PRIMARY KEY (TEST_ID) ); INSERT INTO TEST VALUES('1','a'); |
测试代码:
Session session = factory.getCurrentSession(); session.beginTransaction(); List list = session.createQuery("FROM Test WHERE RTRIM(TEST_CHAR) = 'a'").list(); session.close(); assertTrue(list.size() > 0); |
输出结果(ORACLE):
Hibernate: select test0_.TEST_ID as TEST1_0_, test0_.TEST_CHAR as TEST4_0_ from TEST test0_ where rtrim(TEST_CHAR)='a' 14:09:53,171DEBUG StringType:172 - returning '1' as column: TEST1_0_ 14:09:53,187DEBUG StringType:172 - returning 'a ' as column: TEST4_0_ |
而MySQL不会自动补足空格,同样的代码也是可以适用的:输出结果(MySQL):
Hibernate: select test0_.TEST_ID as TEST1_0_, test0_.TEST_CHAR as TEST4_0_ from TEST test0_ where rtrim(TEST_CHAR)='a' 14:09:01,828DEBUG StringType:172 - returning '1' as column: TEST1_0_ 14:09:01,828DEBUG StringType:172 - returning 'a' as column: TEST4_0_ |