When doing any query, using Criteria object specially, the Char data type doesn't work in the where clause. The query generated by the Hibernate works perfectly fine on the Oracle console and returns rows but Hibernate returns no rows. The reason might be the Char data type fills the remaining capacity with empty space and Oracle is intelligent enough to ignore those extra spaces while doing query match. But Hibernate is not that smart enough. So it doesn't get the exact match even though visually both the values look same.
The workaround is not to keep char(xx) type for the columns that might be used in the query's where clause. This is economic approach as well as it saves storage space. But if the data base can't be touched then the workaround is, create a view on top of the table and in the view, trim the char(xx) column to get rid of all trailing spaces. Or another work around could be pad the trailing spaces while doing the query but I personally don't like this approach.
References:
http://www.javalobby.org/articles/hibernate-query-101/
http://www.hibernate.org/388.html
http://forum.hibernate.org/viewtopic.php?p=2382506
No comments:
Post a Comment