Featured Post

The great debacle of healthcare.gov

This is the first time in history when the president of the United States of America, or probably for any head of state around the world,...

Monday, November 10, 2008

Hibernate: Issue with Char data type

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.



No comments: