5

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.

RAS
  • 8,100
  • 16
  • 64
  • 86

6 Answers6

7
Criteria criteria = s.createCriteria(User.class);
criteria.add(
    Expression.sql("userName = ? collate Latin1_General_CS_AS_KS_WS", userName, new StringType())
);
ez2sarang
  • 106
  • 1
  • 4
  • 3
    Expression.sql is deprecated. Use instead: `Session session = HibernateUtil.getSession(); Criteria criteria = session.createCriteria(User.class); criteria.add(Restrictions.sqlRestriction( "user_name = ? collate Latin1_General_CS_AS", userName, new StringType())); user = (User) criteria.uniqueResult();` Please note in sqlRestriction the column name must be the original column name in the table of the database (unlike in HQL) – Thero Feb 07 '14 at 12:47
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).

alexey28
  • 5,170
  • 1
  • 20
  • 25
  • Thanks for your reply but I don't want to use collasion. Is there any other solution? – RAS Jun 14 '12 at 10:41
  • Only one - program filtering: you receive all results and than compare this results in loop with java String.equals (in case of eq) or String.contains (in case of %like%) or startWith (in case of like%) etc. – alexey28 Jun 14 '12 at 10:46
  • I want to avoid that loop also. Anyways, thanks for your fast replies. – RAS Jun 14 '12 at 11:09
  • unfortunatelly there is no other way: callation when create table or inside of query or loop with additional case sensitive checking. Well, or using other db that is case sensitive when do search with select – alexey28 Jun 14 '12 at 11:13
  • what do you mean by "inside of query"? – RAS Jun 14 '12 at 11:59
  • You can put collation in your sql query. I dont know how todo it in criteria. If you are interesting read here: http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/ – alexey28 Jun 14 '12 at 12:35
  • the link is provided by you is good. But I'm not able to use Hql. Can I use collation with Criteria? Check out my edited question. – RAS Jun 18 '12 at 12:32
0

You can try this (using ilike in Restrictions API)

Criteria criteria = s.createCriteria(User.class);   
criteria.add(Restrictions.ilike("userName", userName));

Regards

esmoreno
  • 658
  • 5
  • 12
  • There is no such method exist. – PVR Jun 18 '12 at 12:06
  • which is the version installed? See this: http://docs.jboss.org/hibernate/orm/3.3/reference/es-ES/html/querycriteria.html – esmoreno Jun 18 '12 at 14:08
  • @esmoreno, the link given by you doesn't contain any code or line that displays that **enableLike()** is an function that can be used with **Restrictions**. – RAS Jun 19 '12 at 11:15
  • @RAS, you're absolutely right.Into to document, see 15.6 section.Could you try this? – esmoreno Jun 19 '12 at 11:53
  • @esmoreno, I tried that but it doesn't work for me. The document also provides theory only. Can you provide some good example of How to use **Example**? – RAS Jun 19 '12 at 13:03
  • @Ras, forget the last tip .. Please Try This again(I edited the answer) – esmoreno Jun 19 '12 at 14:08
  • @esmoreno, thanks for your quick answer. I already tried **ilike** but it's also not working. – RAS Jun 19 '12 at 14:22
  • Can you see the query generated? – esmoreno Jun 19 '12 at 14:38
0

I add the same issue, and I changed the collation type in the database table straight in the mariadb server.

I create the table field with "COLLATE latin1_general_cs"

So every search on this field will be case sensitive.

My table field looks like this.

'case_sensitive_key' varchar(255) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL DEFAULT '',

Hope it will help some of you.

Vespucci75fr
  • 338
  • 2
  • 7
0

If you go the sql route then you have issues with referencing the alias in an otherwise HQL/Criterion query. All that is required is to subclass LikeExpression or whatever and provide your own toSqlString() method. Tested in MySql.

private static class CaseSensitiveLike extends LikeExpression {

    protected CaseSensitiveLike(final String propertyName,
            final String value, final MatchMode mode) {
        super(propertyName, value, mode);
    }

    @Override
    public String toSqlString(final Criteria criteria,
            final CriteriaQuery criteriaQuery) {
        String sql = super.toSqlString(criteria, criteriaQuery);
        return sql + " collate utf8_bin";
    }
}

and then the following calls are interchangeable:

Criterion insensitive = Restrictions.like(myProperty, myValue, MatchMode.ANYWHERE);
Criterion sensitive =  new CaseSensitiveLike(ldProperty, value, MatchMode.ANYWHERE);
visionset
  • 134
  • 7
-1

This may not be suitable for you but it may be better to use lucene/solr for these kind of queries. In addition to case insensitive queries it will also handle other common scenarios for you.

Deniz
  • 1,575
  • 1
  • 16
  • 27
  • one thing, if in mapping table if someone manually edit value from camel case to lower case or anything it will not show in list. SOlr will solve problem with search, but data inconsistency will happen if somechange in sql server – Satish Patro Nov 19 '19 at 09:30