在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_
------------------------------------------------------------
08/11/26更新
事实上用以下方法加不加RTRIM都能取出数据:
List list = session.createQuery("FROM Test WHERE TEST_CHAR = 'a'").list();
我建了一个表:create table test(name char(7));内有N条值为hello的记录
第一种:不用占位符,
java.sql.PreparedStatement ps = connection().prepareStatement("select * from test where name='hello'");
java.sql.ResultSet rs = ps.executeQuery();
记录集rs有数据;
第二种:使用占位符,
java.sql.PreparedStatement ps = connection().prepareStatement("select * from test where name=?");
ps.setString(1,"hello");
java.sql.ResultSet rs = ps.executeQuery();
记录集rs中无任何数据。
引用http://www.javaeye.com/post/152778
看来如果用PrepareStatement的话就有可能发生问题。另外,由于RTRIM可能带来索引失效,使用RPAD(expr,n[,pad])也是一种不错的选择。