Geek asks and answers

Postgresql CITEXT Datatype Issue JPA Hibernate

I find it difficult to use the CITEXT data type in PostgreSQL using JPA and Hibernate. CITEXT is supposed to provide a case-insensitive data type, but when used with JPA / Hibernate it does not behave case-insensitive. Has anyone else had this problem or did she know about it? I saw some mention (but very, very little) about the JDBC problem, but it came back at least for a year and was not very clear.

I have a column "alias" that is defined as citext in postgres 9.1. I just did a test to find out if it can find a string using a named query as such:

create table test(
    nickname citext
)

@NamedQuery(name = "Person.findByNickname", 
            query = "SELECT p 
                     FROM Person p 
                     WHERE p.nickname = :nickname")

Paste the alias into the database:

insert into test values('testNick')

Then run this code:

String nickname = "testNick";

Query q = em.createNamedQuery("Person.findByNickname");
q.setParameter("nickname", nickname);
if (q.getResultList().isEmpty()) {
    return (false);
}
return (true);

This returns 'true' (that is, the database already has "testNick").

String nickname = "testnick"; //(lower case 'N')

, "false".

CITEXT, "". .

JPA Hibernate. - ?

varchar . . , , , .

.

+5
2

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 text citext , 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 "", 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-devel PgJDBC citext, "yup, ", .

Tracing

, , JPA. log_statement = 'all' postgresql.conf. SIGHUP postmaster, pg_ctl reload Pg, .

. , psql, . , , . , Hibernate PgJDBC.

, Hibernate , PgJDBC . , 9.2-devel PgJDBC citext, "yup, ", .

. citext , Hibernate . , String.equals. Hibernate, , . , text varchar / , Hibernate "FRED", "FRED" , , DB , ISN 't - Hibernate - , . , citext -backed equals hashCode .

, JPA, , @Column , . Java , , JPA .

, Hibernate, citext citext equals hashCode.

+12

. , 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>
+4

More articles:


All Articles