Hibernate + Postgresql case insensitive

Question

I searched for search queries to directly solve the problem while getting our new postgresql + hibernate setting, working with insensitive search without the knowledge. We used to use mysql, which has a case-sensitive search policy, but Postgresql doesn't seem to have this.

For example, I would like Hibernate / Postgresql to return the same result for each of the following:

SELECT * FROM Foo where bar = 'Hello World'; 

and

 SELECT * FROM Foo where bar = 'Hello World'; 

The only solution I found was to somehow embed the "ilike" keyword in the resulting queries, but would you think that there would be some kind of configuration in Hibernate that could satisfy this? My experience with Hibernate and Postgresql is limited, so any input will be appreciated.

thanks

Answer 1

In SQL, I used to play a trick that, in my opinion, is compatible with SQL in most DBMSs:

 SELECT * FROM UPPER(bar) = 'HELLO WORLD' 

You can use capital letters before passing a parameter. This will probably be the same as using the Restrictions.ilike () criteria to query in Hibernate.

Answer 2

There is no way to simply tell Postgres to ignore the case .

If you change the type of all columns in which you want this comparison, select the option and then the citext data citext .

Answer 3

Spring can be useful when setting things up like sharing a sleep / postgres connection, a transaction (if necessary), and how you get the HibernateSession object. In fact, using spring, you can use HibernateTemplates (the spring object), which will proxy a lot of things for you.

However, you still have the option of programming around the queries that you run. In this case, using HibernateTemplate you can write:

 String parameter = "hello wOrlD" DetachedCriteria criteria = DetachedCriteria.forClass(objectClass) .add(Restrictions.ilike("name", parameter))); List<Object> result = template.findByCriteria(criteria); 

or you can explore using Restrictions.eq or sqlRestriction to implement its sql fragment.

Answer 4

Thank you for your responses; there seems to be no easy way around this problem at the / jdbc database level. Therefore, our solution was, although not optimal:

• Forced corpus standards in logical data.
• When we receive requests from the client, the request string is filtered through the toLowerCase () function.

When we used mysql, it's just a matter of setting up the policy in my.cfg / ini - I don't understand why this got complicated in postgresql. Such is life, though eh?