Hibernate supporting case-insensitive search with Postgres citext

There are issues with  Hibernate supporting case-insensitive search on citext, with Hibernate Criteria’s and Bind Parameters

Problem :

Assume this is the table structure with the data

CREATE TABLE parententity

(

  id character varying(255) NOT NULL,

  firstname citext,

  lastname character varying(255),

  CONSTRAINT parententity_pkey PRIMARY KEY (id)

)

                  id                                                           |   firstname  |  lastname

--------------------------------------+-----------+------------

AB83B745-BDCE-4033-ADF9-4C8FBC2071A5 |  vidhya         |  shanker

D753F492-F9BA-417E-ABA9-3CE50D90D65A  |  Srividhya    |  Umashanker

Here are our queries

Same Case value

Query q = dao.getSession().createSQLQuery("select * from ParentEntity where firstName=:name")
              .addEntity(ParentEntity.class)
              .setString("name", "Srividhya");

      List l = q.list();

Hibernate fetches  AB83B745-BDCE-4033-ADF9-4C8FBC2071A5 | Srividhya | Umashanker

Where as

Different value

Query q = dao.getSession().createSQLQuery("select * from ParentEntity where firstName=:name")
              .addEntity(ParentEntity.class)
              .setString("name", "SRIVIDHYA");

      List l = q.list();

Hibernate does not fetch any records

which is expected to do a case insensitive fetch,  does not seem to fetch any record. Internally it converts to varchar and does a case sensitive check.

Looks like this is a known  as per Postgres JDBC implementation

package org.postgresql.jdbc2;

    public abstract class AbstractJdbc2Statement implements BaseStatement

   {
       protected AbstractJdbc2Connection(String host, int port, String user, String database, Properties info, String url) throws SQLException
     {
          String stringType = info.getProperty("stringtype");
        if (stringType != null) {
            if (stringType.equalsIgnoreCase("unspecified"))
                bindStringAsVarchar = false;
            else if (stringType.equalsIgnoreCase("varchar"))
                bindStringAsVarchar = true;
            else
                throw new PSQLException(GT.tr("Unsupported value for stringtype parameter: {0}", stringType),
                PSQLState.INVALID_PARAMETER_VALUE);

        }

     }

    

    public void setString(int parameterIndex, String x) throws SQLException

    {

….

setString(parameterIndex, x, (connection.getStringVarcharFlag() ? Oid.VARCHAR : Oid.UNSPECIFIED));

    }

SOLUTION:

http://jdbc.postgresql.org/documentation/91/connect.html#connection-parameters

There also seems to be an option now in the postgres JDBC documentation, that allows you to add a connection option stringtype. The option is set in the connection url as
jdbc:postgresql://localhost/test?stringtype=unspecified. The postgres documentation says…

stringtype = String
Specify the type to use when binding PreparedStatement parameters set via setString(). If stringtype is set to varchar (the default), such parameters will be sent to the server as varchar parameters. If stringtype is set to
unspecified, parameters will be sent to the server as untyped values, and the server will attempt to infer an appropriate type. This is useful if you have an existing application that uses setString() to set parameters that
are actually some other type, such as integers, and you are unable to change the application to use an appropriate method such as setInt().

When tried as follows in hibernate.properties  the functionality seems to work both for citext, varchar and text

hibernate.connection.url=jdbc:postgresql://192.168.1.1:5432/test?stringtype=unspecified