Case sensitive Search in Hibernate Criteria

Question

I'm using Hibernate 3 with SQL Server 2008. I want to do case sensitive search in Hibernate Criteria. I'm not able to achieve it. My code is as below:

Criteria criteria = session.createCriteria(User.class); /*line 1*/
criteria.add(Restrictions.eq("userName", userName));    /*line 2*/

I have a class User.java which contains a String userName.

DB Entries:

id | user_name
--------------
1  | abc
2  | xyz

Now, if I pass "abc" as userName in line 2, then it should return the first record from the db. But if I pass "Abc", "ABC", "aBC" etc. as userName in line 2, no records should be fetched.

I've visited this link but it's not helpful to me as I don't want to use collation with hql or with SQL Server. I'm open to use collation with Criteria but don't know how to do it.

Answer 1

Criteria criteria = s.createCriteria(User.class);
criteria.add(
    Expression.sql("userName = ? collate Latin1_General_CS_AS_KS_WS", userName, new StringType())
);

Answer 2

You can't with hibernate. Problem is in MS SQL - by default it use case insensitive collation. There can be two workarounds:

1 Select with hibernate and do program filtering of result

2 When create table use case sensitive collation for filed you nead to search: COLLATE SQL_Latin1_General_CP850_BIN2 or other specified for your server (read from SYSTEMPROPERTY).