How to get the all results using HQL

[Bharath Gowda]

Hi All,

How can I get all the case sensitive results from the database(IBM DB2 v8.1) using HQL

Thanks,
Bharath

[Jaikiran Pai]

Bharath,

You will have to provide us more details about what you are trying to do Have you mapped your entities to the database tables? Are you trying to get all data from a single table using HQL? And any specific reason why you included "case sensitiveness" in your question?

[Bharath Gowda]

Hi Jaikiran,

Thanks for your replay.

I've a simple criteria to search the database based on the name entered in the text field. I'm searching in only one table.
Below code works fine when I remove the ignore case criteria. when I add this example to the Criteria, it takes all the numeric fields in the tyable in the where clause of the criteria instead of name only. Please suggest

Example example = Example.create(person);
example.ignoreCase();

Criteria criteria=
session.createCriteria(PersonBO.class);

criteria.add(Expression.like("nameFirst","%" + nameFirst + "%"));
criteria.add(Expression.like("nameLast","%" + nameLast + "%"));
criteria.add(example);

Where clause from the query:

from
T_PERSONS this_
where
this_.NAME_FIRST like ?
and this_.NAME_LAST like ?
and (
this_.LAST_UPDATED_BY=?
and this_.CREDIT_CHECK_NEEDED=?
and this_.ACTIVE=?

[Bharath Gowda]

some columns are boolean data type in the database.

[Jaikiran Pai]

I've a simple criteria to search the database based on the name entered in the text field. I'm searching in only one table.

Why not use Expression.ilike

Criteria criteria=
session.createCriteria(PersonBO.class);
criteria.add(Expression.ilike("nameFirst","%" + nameFirst + "%"));
criteria.add(Expression.ilike("nameLast","%" + nameLast + "%"));

[Bharath Gowda]

Hi Jaikiran,

Thank you so much.

It works fine for me. What I see in the query is it converts the input value into lowercase in the where clause.
can you please help me understand how does I still get the correct results from the database?

[Jaikiran Pai]

Originally posted by Bharath Gowda:
can you please help me understand how does I still get the correct results from the database?

Without seeing the actual SQL query getting fired, i don't know how it might be accomplishing this But just a guess - Case insensitivity is handled at DB server level and each server will have its own way of specifying this. So i guess the JDBC driver or may be the dialect that you are using with hibernate (the dialect is configured through the hibernate configuration file) is handling this.