Unable to A Simple Search

nealc · **Joined:** Wed Nov 29, 2006 2:28 pm **Posts:** 12

Hibernate version:3.2

I have a table in which the ID is a string of length 3. When I attempt to read a record based on the ID the record is not found (null is returned).

I know the record is there because:
1.) When I use Hibernate to get a list of all the records in the table I see the one I am looking for
2.) When I cut and past the Hibernate generated SQL into SQL Plus the querry works!

Any ideas?

code:

Code:

   public static ReviewStatusType findById(Session hSession, String Id)
   throws HibernateException {
      ReviewStatusType reviewStatusType = null;
      try {
         Criteria crit = hSession.createCriteria(ReviewStatusType.class);
         crit.add(Expression.eq("reviewStatusTypeId", Id));
         reviewStatusType = (ReviewStatusType) crit.uniqueResult();
      } catch (HibernateException he) {
         throw (he);
      }
      return reviewStatusType;
   }   

Mapping documents:
<id name="reviewStatusTypeId" type="string">
<column name="REVIEW_STATUS_TYPE_ID" length="3" />
<generator class="assigned" />
</id>

Name and version of the database you are using:Oracle 10g

The generated SQL (show_sql=true):
select this_.REVIEW_STATUS_TYPE_ID as REVIEW1_85_0_, this_.NAME as NAME85_0_, this_.LAST_UPDATE_USERID as LAST3_85_0_, this_.LAST_UPDATE_TS as LAST4_85_0_, this_.SORT_SEQUENCE as SORT5_85_0_
from CWNS2008.REVIEW_STATUS_TYPE this_
where this_.REVIEW_STATUS_TYPE_ID=?

persist_coder · **Posted:** Thu Nov 30, 2006 6:22 am

Hi,

Check your Id value is having any space in the database.

nealc · **Joined:** Wed Nov 29, 2006 2:28 pm **Posts:** 12

Yes, that was it.

In the DB the column was defined as char (3 Bytes). The record I was looking for had a key value of "SH ". When Hibernate executed the query it failed because I was searching for "SH" (no trailing blank) but when I cut and pasted the Hibernate generated query into SQL Plus it worked.

I believe the reason it did not work in Hibernate but did work when I cut and paste the query is that Hibernate uses paramerized queries and they must behave differently.

nealc · **Joined:** Wed Nov 29, 2006 2:28 pm **Posts:** 12

Yes, that was it.

In the DB the column was defined as char (3 Bytes). The record I was looking for had a key value of "SH ". When Hibernate executed the query it failed because I was searching for "SH" (no trailing blank) but when I cut and pasted the Hibernate generated query into SQL Plus it worked.

I believe the reason it did not work in Hibernate but did work when I cut and paste the query is that Hibernate uses paramerized queries and they must behave differently.

persist_coder · **Posted:** Thu Nov 30, 2006 10:35 am

Yes, hibernate takes the which you give as whole value, like parameterized one.

rajasaur · **Joined:** Tue Aug 23, 2005 8:52 am **Posts:** 181

I believe CHAR always right pads with whitespaces to the maximum length that its defined for, whereas VARCHAR only uses how much the actual size is. So try changing your DB to use VARCHAR and your original SQL should work.