我在使用JPA和Hibernate的PostgreSQL中使用CITEXT数据类型时遇到困难。CITEXT应该提供一个不区分大小写的文本数据类型,但当与JPA/Hibernate一起使用时,它的行为并不区分大小写。其他人有没有遇到过这个问题,或者知道如何解决这个问题?我看到一些关于JDBC问题的提及(但非常非常少),但这至少要追溯到一年前,还不是很清楚。
我在postgres9.1中有一个"昵称"列定义为citext。我只是做了一个测试,看看它是否可以使用这样的命名查询找到一行:
create table test(
nickname citext
)
@NamedQuery(name = "Person.findByNickname",
query = "SELECT p
FROM Person p
WHERE p.nickname = :nickname")
在数据库中插入昵称:
insert into test values('testNick')
然后运行此代码:
String nickname = "testNick";
Query q = em.createNamedQuery("Person.findByNickname");
q.setParameter("nickname", nickname);
if (q.getResultList().isEmpty()) {
return (false);
}
return (true);
这将返回"true"(即数据库中已经有一个"testNick")。
如果我做这个任务
String nickname = "testnick"; //(lower case 'N')
并再次运行它,它会返回"false"。
由于该列是CITEXT,应再次返回'true'。即不区分大小写的文本。
使用JPA和Hibernate。有人有什么想法吗?
同时,我将列改回varchar,并为小写字母创建了一个函数索引。现在我必须创建一个本机查询来使用数据库函数进行搜索。我想知道是否有一种方法可以让我不必这样做来维护数据库抽象。
谨致问候。
citext
提供了不区分大小写的运算符,用于在数据库中使用以及其他citext值。
发生了什么
在创建参数化语句时,您的JPA实现会显式地将参数的类型指定为text
。citext
没有定义citext = text
运算符,因此PostgreSQL将citext
强制转换为text
,并使用区分大小写的text = text
运算符。实际上,比较citext
和text
是区分大小写的。
以下是我认为正在发生的事情。给定伪数据:
regress=# CREATE EXTENSION citext;
regress=# CREATE TABLE citest ( x citext );
regress=# INSERT INTO citest(x) VALUES ('FRED'), ('FrEd');
regress=# SELECT * FROM citest;
x
------
FRED
FrEd
(2 rows)
citext与未知字符串文字的比较将被解释为citext=citext
,并且不区分大小写:
regress=# SELECT * FROM citest WHERE x = 'FRED';
x
------
FRED
FrEd
(2 rows)
但是citext
和显式text
类型的文字之间的比较将使用citext
的隐式转换为文本将citext
参数转换为text
,然后进行区分大小写的text=text
比较:
regress=# SELECT * FROM citest WHERE x = 'FRED'::text;
x
------
FRED
(1 row)
或者更确切地说,Hibernate正在做的事情将更接近:
regress=# PREPARE blah(text) AS SELECT * FROM citest WHERE x = $1;
PREPARE
regress=# EXECUTE blah('FRED');
x
------
FRED
(1 row)
其中绑定参数时,类型被指定为text
,因为Hibernate"知道"Strings是text
。
换句话说,您需要让Hibernate通过PgJDBC显式地指定citext
数据类型作为查询的参数类型,结果如下:
regress=# PREPARE blah(citext) AS SELECT * FROM citest WHERE x = $1;
PREPARE
regress=# EXECUTE blah('FRED');
x
------
FRED
FrEd
(2 rows)
请注意已准备语句中的显式citext
类型参数。那将是。。。有趣的尤其是PgJDBC对citext
类型一无所知。您必须为Hibernate编写一个使用PgJDBC的setObject
的自定义数据类型处理程序;即便如此,Java和Pg之间也会出现运算符一致性问题(见下文)。
IMO使用传统的区分大小写类型和lower()
、ILIKE
等会更好。
Hibernate也有可能依赖于PgJDBC告诉它关于列大小写敏感性的内容。至少从9.2级开始,PgJDBC对citext
类型一无所知,所以当被问及时,它总是说"是的,这是区分大小写的"。
跟踪
如果没有看到JPA运行的实际查询,很难确定这就是正在发生的事情。尝试在postgresql.conf
中设置log_statement = 'all'
。然后SIGHUP
作为邮局主管,使用pg_ctl reload
,或重新启动Pg以使更改生效。
重新运行测试并检查日志。测试您在psql
中看到的查询以观察结果。如果你不确定发生了什么,请向他们更新你的问题。如果更新,还包括Hibernate版本和PgJDBC版本。
Hibernate也有可能依赖于PgJDBC告诉它关于列大小写敏感性的内容。至少从9.2级开始,PgJDBC对citext
类型一无所知,所以当被问及时,它总是说"是的,这是区分大小写的"。
操作员一致性困难
警告:一旦文本从数据库中出来,citext
类型就不会影响Hibernate处理文本的方式。例如,它不会对String.equals
方法产生任何影响。您需要告诉Hibernate您希望它将文本视为不区分大小写。否则,如果你有一个text
或varchar
主/外键,你可能会遇到这样的情况:Hibernate请求密钥"FRED"
,它会得到"FrEd"
,并且非常困惑,因为DB返回的密钥与它被请求的密钥不相等。如果在实体中的equals
和hashCode
实现中包含citext
支持的字符串,也会出现类似的奇怪情况。
不幸的是,JPA似乎没有在@Column
映射中指定列是否区分大小写的注释属性。Java无论如何都没有不区分大小写的字符串数据类型的概念,所以即使JPA指定了它,它也不会有太多好处
只要不使用citext
作为键,或者不在equals
和hashCode
中包含citext
值,您就可能避免混淆Hibernate。
我的回答是为了未来的读者。问题是JDBC自动将String参数强制转换为varchar,从而迫使比较区分大小写。此行为可以通过将JDBC连接参数"stringtype"设置为"未指定。
如果您正在使用JPA,请在数据源配置中放入以下内容:
<datasource jndi-name="java:jboss/datasources/testDS"
pool-name="test" enabled="true"
use-java-context="true" spy="true">
<connection-url>jdbc:postgresql://localhost:5432/postgres</connection-url>
<driver>postgresql</driver>
<connection-property name="stringtype">unspecified</connection-property>
<security>
<user-name>postgres</user-name>
<password>******</password>
</security>
</datasource>
根据Craig Ringer的回答,我理解我们应该将CITEXT与CITEXT进行比较。将输入转换为CITEXT对我有效(我在Spring Data JPA中进行了测试,其中查询在@query下使用(value='YOUR query HERE',nativeQuery=true)
所以,下面的工作应该根据我的测试。如果可用,它甚至会在昵称列上使用索引(当然,是否使用索引取决于查询优化器的决定)
@NamedQuery(name = "Person.findByNickname",
query = "SELECT p
FROM Person p
WHERE p.nickname = CAST(:nickname AS CITEXT)")