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

Hibernate SQLRestriction using alias of the join Table.

Problem:

We Faced an issue which using SQLRestricion in the subcriteria and we use an table aliasCriteria c1 = dao.getSession().createCriteria(Parent.class,"p")

      .createCriteria("p.child", "c", Criteria.LEFT_JOIN)
      .add(Restrictions.sqlRestriction("c.address ~* 'Address%'"));

print(dao, c1);

The SQL that gets generated is as follows and failed

select this_.id as id3_0_, this_.address as address3_0_, this_.phone as phone3_0_, this_.firstName as firstName3_0_
from
   Parent this_
   left outer join Child this_1_
on
   this_.id=this_1_.id
where
   c.address ilike 'Address%'

Expected sql that uses the alias in my where clause.

select this_.id as id3_0_, this_.address as address3_0_, this_.phone as phone3_0_, this_.firstName as firstName3_0_
from
   Parent this_
   left outer join Child c
on
   this_.id=c.id
where
   c.address ilike 'Address%'

Root Cause:

We identified that the problem was with SQLRestriction implementation in hibernate, which does not care about Alias names. So we took an approach, to not use SQLRestriction, instead extended SimpleExpression to accomodate Regex 

Solution:

Instead of using SQLRestriction, we resolved it by extending SimpleExpression, which solved the problem public class CustomSimpleExpression extends SimpleExpression{     public CustomSimpleExpression(String propertyName, Object value, String op){        super(propertyName, value, op);     } } public class CustomRestrictions{      public static SimpleExpression regex(String propertyName, Object value){          return new CustomSimpleExpression(propertyName, value, " ~* ");       } } Criteria c1 = dao.getSession().createCriteria(Parent.class,"p")       .createCriteria("p.child", "c", Criteria.LEFT_JOIN)       .add(CustomRestrictions.regex(c.address,"Address%")); print(dao, c1);

[Shared Presentation] Introduction to NoSQL by Martin Fowler

I came across this wonderful presentation by Martin Fowler on NOSQL Databases.
Anyone who wants to know the basics. Please find the time to watch this