Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to get the all results using HQL

 
Bharath Gowda
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

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

Thanks,
Bharath
 
Jaikiran Pai
Marshal
Pie
Posts: 10444
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
some columns are boolean data type in the database.
 
Jaikiran Pai
Marshal
Pie
Posts: 10444
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
Bharath Gowda
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Pie
Posts: 10444
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic