how to make toUpperCase in hibernate Restrictions

Question

I wrote my code like this

criteria.add(Restrictions.eq("lastName", "ChARaN").ignoreCase()); //

This by default converting both to lower case.

SQL generated:

select
    this_.ID as ID1_0_0_,
    this_.EMAIL as EMAIL2_0_0_,
    this_.FIRST_NAME as FIRST_NA3_0_0_,
    this_.LAST_NAME as LAST_NAM4_0_0_ 
from
    Employee this_ 
where
    lower(this_.LAST_NAME)=?

    14:56:57,017 TRACE BasicBinder:81 - binding parameter [1] as [VARCHAR] - [charan]

I want Hibernate to generate query for uppercase like:

where
       upper(this_.LAST_NAME)=?

Check this out: http://stackoverflow.com/questions/11030921/case-sensitive-search-in-hibernate-criteria — abdulrafique, Feb 14 '16 at 09:57
@Charan But what difference does it make? I don't think the outcome be any different for equals operator as long as both side are using either upper or lower case. — Madhusudana Reddy Sunnapu, Feb 14 '16 at 11:20

score 0 · Answer 1 · answered Feb 14 '16 at 12:28

0

Not sure what advantage would it have if it uses UPPER instead of LOWER. Having said that you try below option to force Hibernate to use UPPER

criteria.add(Restrictions.sqlRestriction("UPPER({alias}.lastName) = UPPER(?)", "ChaRaN", StringType.INSTANCE))

answered Feb 14 '16 at 12:28

Madhusudana Reddy Sunnapu

8,064
2
18
29

if we have a index on that column, i would like to know how can i use criteria..... yes i already tried with sqlRestriction, that works,i know....,, but i am looking for any in build one if any – Charan Feb 14 '16 at 13:16
@Charan At least with criteria api I don't think if there is any such in-built function. But then, how is index related to your criteria api? – Madhusudana Reddy Sunnapu Feb 14 '16 at 13:44
index is created on that column with uppercase to fetch records fast in a huge data table... so i want to fetch with uppercase of that column,, no other go... :( .... at the same time i am not a fan of sqlRestrictions... if we are using hibernate, lets code fully in hibernate, sqlRestriction is something like native sqls – Charan Feb 15 '16 at 03:04
@Charan the other option is going wit HQL, that has an in-built upper function. – Madhusudana Reddy Sunnapu Feb 15 '16 at 03:21

how to make toUpperCase in hibernate Restrictions

1 Answers1