Hibernate Community • View topic - Using a query with lower()

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Using a query with lower()

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

mtairey

Post subject: Using a query with lower()

Posted: Wed Feb 02, 2011 5:28 pm

Newbie

Joined: Wed Feb 02, 2011 5:18 pm
Posts: 3

So, I am trying to get an HQL query that contains the lower() function to work. I am working with a PostgreSQL 9 database and Hibernate 3.3.2.

Here is the code for the query....

Code:

        String sql = "FROM BatchId b " +
              "WHERE lower(b.term) IN (:ids)";
        Query q = sessionFactory.getCurrentSession().createQuery(sql);
        q.setParameterList("ids", idSet);
        
        List qr = q.list();

It looks to me like the lower function is not working. If I run a similar query (see below) directly on the database it works fine, i.e. I get cases insensitive matches.

Code:

select * from batch_ids
where lower(term) in ('ida', 'idb', 'idc')

Does anyone have any ideas why this wouldn't work? Thanks in advance for any help with this.

Mark

Top

narayan_singh

Post subject: Re: Using a query with lower()

Posted: Thu Feb 03, 2011 3:08 pm

Newbie

Joined: Thu Jan 13, 2011 7:41 am
Posts: 1

Hi,
i tried your code against mysql
query looks fine to me and lower() is working fine
but database(atleast mysql) by default is performing case in sensitive compariosion for string values.

see below code

Code:

String[] cities={"pune","delhi","mumbai","CHENNAI"};

List<String> adressCities=ses2.createQuery("select a.city from AddressEntity a where lower(a.city) IN (:cities)").setParameterList("cities",cities).list();
         System.out.println("no of address ="+adressCities.size()+"::"+adressCities);

and values for city filed in DB are
MUMBAI
CHENNAI
DELHI
pune

this query select all the 4 values, instead it should have selected only first 3 except CHENNAI
no of address =4::[MUMBAI, CHENNAI, DELHI, pune]

and lower is working fine .as you can see from below

Code:

String[] cities={"pune","delhi","mumbai","CHENNAI"};

List<String> adressCities=ses2.createQuery("select lower
(a.city) from AddressEntity a where lower(a.city) IN (:cities)").setParameterList("cities",cities).list();
         System.out.println("no of address ="+adressCities.size()+"::"+adressCities);

this gives output as below when lower is with selected values
no of address =4::[mumbai, chennai, delhi, pune]

the issue is because of case insesitive comparision by DB
if you are tring to leave out upper case values(or other way around) from selection see below
links

http://www.mysqlfaqs.net/mysql-faqs/SQL-Statements/How-to-manage-case-sensitivity-in-string-comparisons

[url]
http://dev.mysql.com/doc/refman/5.0/en/ ... ivity.html
[/url]

HTH
narayan

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum