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:
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
