I came across some issues when using JPA persistence in Glassfish. I have used the current Postgres as database. What I tried to achieve was a case insensitive search. There have been many posts on various forums on the internet suggesting to use the UPPER()/LOWER() string function in SQL e.g.
SELECT * From users WHERE LOWER(users.name) LIKE LOWER('%Jack%');
In EQBQL it is not possible to use the LOWER() function on the LIKE expression so converting the query pattern has to be done using Java. As Java does care about Locale/Collation the conversion in Java might be different from the conversion of SQL resulting in a non working query.
How do I achive a reliable case insensitive search?
----------
The second question also refers to collation. Recent Databases allow to specify the required collation used by the ORDER BY. MySQL has a feature to specify a collation for every query. I guess many other databases support collation in vendor specific SQL language extensions. Since there is no way to specify a Locale in EJBQL the only way to get a meaningful ordering it would be necessary to use native queries, which totally renders EQBQL useless.
Are there any plans to augment EJBQL to provide a abstraction for this feature in JPA2.0?
Regards
Heiko
[Message sent by forum member 'hewagn00' (hewagn00)]
http://forums.java.net/jive/thread.jspa?messageID=283274