I am trying to retrieve a record from a table based on date.

The insertion was successful but i cannot retrieve the record
using the date column.

I have done a lot of debugging read the documentation and
searched this forum but i could not figure out.


Hibernate version:
3.0.1

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="trails">

<class name="ClassB"
table="TABLEB"
dynamic-update="true">

<id name="propPrdtId">
<column name="PROP_PRDT_ID" length="15" not-null="true"/>
</id>

<property name="propPrdtUseStartDtme">
<column name="PROP_PRDT_USE_START_DTME" not-null="true" />
</property>
</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

public void addRecord()
{
session = openSession();
Transaction tx = session.beginTransaction();
Calendar cal = Calendar.getInstance();
cal.set (2004,11,11,10,10,10);

ClassB _instance = new ClassB();
_instance.setPropPrdtId(515);
_instance.setPropPrdtUseStartDtme(cal);

session.save(_instance);
tx.commit();
}

public void findRecord()
{
session = openSession();
Transaction tx = session.beginTransaction();
Calendar cal = Calendar.getInstance();
cal.set (2004,11,11,10,10,10);

Query sqlQuery = session.createQuery
(" from ClassB srpinv where srpinv.propPrdtUseStartDtme >= :startDate");

sqlQuery.setCalendar("startDate", cal);

//Get the List of Data.
List result = sqlQuery.list();

for(int iloop=0;iloop<result.size();iloop++)
{
ClassB _instance = (ClassB) result.get(iloop);
System.out.println("id : " + _instance.getPropPrdtId());
}

tx.commit();
}

Note : - propPrdtUseStartDtme is of Calendar type in the POJO and
the respective column is of date datatype.

Full stack trace of any exception that occurs:
Record Not Found.

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

The generated SQL (show_sql=true):
select sleepingro0_.PROP_PRDT_ID as PROP1_, sleepingro0_.PROP_PRDT_USE_START_DTME as PROP2_2_
from TableB sleepingro0_ where (sleepingro0_.PROP_PRDT_USE_START_DTME=?)

The value in the database for the column is follows
12/11/2004 10:10:10 AM.


Debug level Hibernate log excerpt:

Does Calendar.set clear the milliseconds field? Just curious, because I've run into problems like this before with Oracle with either the Java or the Oracle time being too granular. For instance, if you specify a Java property of type Date for a column in Oracle of type TIMESTAMP, then your Java object has a granularity of milliseconds while your Oracle column has a (default) granularity of nanoseconds. If your DATE column is only calculated to the seconds place and your Java calendar has milliseconds then that could be why no match is found.

Another problem might be that you are using a Calendar type (vice Date or Timestamp) in the Java side but not a TIMESTAMP WITH [LOCAL] TIMEZONE type in Oracle. You may, for some reason, have a locality mismatch.

Lastly, this is all assuming that the INSERT statement actually got generated and executed. I didn't see it in your post...did the insert occur? If not, you may have a problem with your id mapping (unsaved-value or generator) or version column (unsaved-value).

Hope that helps,

- Jesse

Hi Jesse,

Thanks for your posting.
I got it right, it is the seconds that was casuing the problem.