Timestamp Object as part of composite pk on Oracle 9i

jolmstead2k · **Joined:** Fri Aug 27, 2004 8:50 am **Posts:** 3

Gentlemen;

I have as a requirement use of a Timestamp object as part of a composite primary key. The data for the timestamp initialization originates from form request parameters and is processed as follows:

Calendar calendar= Calendar.getInstance();
calendar.set(year , month-1, day, 0, 0, 0);
Timestamp stamp = new Timestamp(calendar.getTimeInMillis());
stamp.setNanos(0);

Oracle 9i evidently stores dates with hrs, seconds, nanoseconds with values of 0. When I define a timestamp with these characteristics, I consistently get exceptions when attempting to do deletes or updates that indicate the provided primary key does not identify the record. I am using the Oracle9i dialect. I can retrieve collections of these objects or perform inserts without any problem.

What do I need to do here to get this to work? Do I need to rewrite these DAO methods in native sql in order to invoke Oracles toDate functionon these date values? I thought this would be included in the dialect. Any help would be appreciated.

Thanks;

John Olmstead

Hibernate version: 2.1

Mapping documents:

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

<hibernate-mapping>


<class
name="com.meyertool.model.mpcs.JobItemDueDate"
table="MPCS.JOB_ITEM_DUE_DATE"
>

<composite-id name="comp_id" class="com.meyertool.model.mpcs.JobItemDueDatePK">
<key-property
name="dueDate"
column="DUE_DATE"
type="java.sql.Timestamp"
length="7"
/>
<key-property
name="jobLineItemNo"
column="JOB_LINE_ITEM_NO"
type="java.lang.Integer"
length="3"
/>
<key-property
name="jobNo"
column="JOB_NO"
type="java.lang.String"
length="12"
/>
<key-property
name="resourceNo"
column="RESOURCE_NO"
type="java.lang.String"
length="25"
/>
<key-property
name="resourceType"
column="RESOURCE_TYPE"
type="java.lang.String"
length="15"
/>
</composite-id>

<property
name="orderStatus"
type="java.lang.String"
column="ORDER_STATUS"
length="10"
/>
<property
name="quantityDue"
type="java.lang.Integer"
column="QUANTITY_DUE"
length="6"
/>
<property
name="bucket"
type="java.lang.String"
column="BUCKET"
length="1"
/>
<property
name="firmPlan"
type="java.lang.String"
column="FIRM_PLAN"
length="1"
/>



</class>
</hibernate-mapping>

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

Not Applicable, using Spring.

Full stack trace of any exception that occurs:

org.springframework.orm.hibernate.HibernateObjectRetrievalFailureException: No row with the given identifier exists: com.meyertool.model.mpcs.JobItemDueDatePK@1bb6a69[dueDate=2004-11-17 00:00:00.0,jobLineItemNo=1,jobNo=W180,resourceNo=199301,resourceType=PART], of class: com.meyertool.model.mpcs.JobItemDueDate; nested exception is net.sf.hibernate.ObjectNotFoundException: No row with the given identifier exists: com.meyertool.model.mpcs.JobItemDueDatePK@1bb6a69[dueDate=2004-11-17 00:00:00.0,jobLineItemNo=1,jobNo=W180,resourceNo=199301,resourceType=PART], of class: com.meyertool.model.mpcs.JobItemDueDate
net.sf.hibernate.ObjectNotFoundException: No row with the given identifier exists: com.meyertool.model.mpcs.JobItemDueDatePK@1bb6a69[dueDate=2004-11-17 00:00:00.0,jobLineItemNo=1,jobNo=W180,resourceNo=199301,resourceType=PART], of class: com.meyertool.model.mpcs.JobItemDueDate
at net.sf.hibernate.ObjectNotFoundException.throwIfNull(ObjectNotFoundException.java:24)
at net.sf.hibernate.impl.SessionImpl.load(SessionImpl.java:1921)
at org.springframework.orm.hibernate.HibernateTemplate$3.doInHibernate(HibernateTemplate.java:224)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:176)
at org.springframework.orm.hibernate.HibernateTemplate.load(HibernateTemplate.java:222)
at com.meyertool.dao.mpcs.JobItemDueDateDAOHibernate.deleteItemDueDate(JobItemDueDateDAOHibernate.java:47)
at test.dao.JobItemDueDateDAOTest.testInsertAndDelete(JobItemDueDateDAOTest.java:56)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit2.JUnitStarter.main(JUnitStarter.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:78)

Name and version of the database you are using:

Oracle 9i

The generated SQL (show_sql=true):

Hibernate: select jobitemdue0_.DUE_DATE as DUE_DATE0_, jobitemdue0_.JOB_LINE_ITEM_NO as JOB_LINE2_0_, jobitemdue0_.JOB_NO as JOB_NO0_, jobitemdue0_.RESOURCE_NO as RESOURCE4_0_, jobitemdue0_.RESOURCE_TYPE as RESOURCE5_0_, jobitemdue0_.ORDER_STATUS as ORDER_ST6_0_, jobitemdue0_.QUANTITY_DUE as QUANTITY7_0_, jobitemdue0_.BUCKET as BUCKET0_, jobitemdue0_.FIRM_PLAN as FIRM_PLAN0_ from MPCS.JOB_ITEM_DUE_DATE jobitemdue0_ where jobitemdue0_.DUE_DATE=? and jobitemdue0_.JOB_LINE_ITEM_NO=? and jobitemdue0_.JOB_NO=? and jobitemdue0_.RESOURCE_NO=? and jobitemdue0_.RESOURCE_TYPE=?

Debug level Hibernate log excerpt:

2004-09-21 10:54:08,330 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PlanTable -> MPCS.PLAN_TABLE>
2004-09-21 10:54:08,362 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PoAcknTemp -> MPCS.PO_ACKN_TEMP>
2004-09-21 10:54:08,393 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PoAutoSr -> MPCS.PO_AUTO_SRS>
2004-09-21 10:54:08,393 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PoBalRemSqr -> MPCS.PO_BAL_REM_SQR>
2004-09-21 10:54:08,408 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PoEngrSpec -> MPCS.PO_ENGR_SPEC>
2004-09-21 10:54:08,440 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PoGovtReq -> MPCS.PO_GOVT_REQ>
2004-09-21 10:54:08,549 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PoItemJli -> MPCS.PO_ITEM_JLI>
2004-09-21 10:54:08,565 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PoItemSchedule -> MPCS.PO_ITEM_SCHEDULE>
2004-09-21 10:54:08,580 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PoLabReqMatl -> MPCS.PO_LAB_REQ_MATL>
2004-09-21 10:54:08,596 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PoLabRequest -> MPCS.PO_LAB_REQUEST>
2004-09-21 10:54:08,612 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PrattAsn -> MPCS.PRATT_ASN>
2004-09-21 10:54:08,643 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PrattcMrbInven -> MPCS.PRATTC_MRB_INVEN>
2004-09-21 10:54:08,674 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PrattPoaiTemp -> MPCS.PRATT_POAI_TEMP>
2004-09-21 10:54:08,705 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.PrattPoaTemp -> MPCS.PRATT_POA_TEMP>
2004-09-21 10:54:08,721 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.ProcessPlan -> MPCS.PROCESS_PLAN>
2004-09-21 10:54:08,737 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.ProductionLabor -> MPCS.PRODUCTION_LABOR>
2004-09-21 10:54:08,752 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.ProfitRemSqr -> MPCS.PROFIT_REM_SQR>
2004-09-21 10:54:08,768 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.QltHrDevSqr -> MPCS.QLT_HR_DEV_SQR>
2004-09-21 10:54:08,799 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.QspPart -> MPCS.QSP_PART>
2004-09-21 10:54:08,799 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.QspPartJob -> MPCS.QSP_PART_JOBS>
2004-09-21 10:54:08,830 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.Quote -> MPCS.QUOTE>
2004-09-21 10:54:08,846 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.QuoteItemEst -> MPCS.QUOTE_ITEM_EST>
2004-09-21 10:54:08,877 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.QuoteLineItem -> MPCS.QUOTE_LINE_ITEM>
2004-09-21 10:54:08,908 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.RawProdLabor -> MPCS.RAW_PROD_LABOR>
2004-09-21 10:54:08,940 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.RawReceiver -> MPCS.RAW_RECEIVERS>
2004-09-21 10:54:09,002 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.RcvdSerialNo -> MPCS.RCVD_SERIAL_NO>
2004-09-21 10:54:09,018 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.ReceivedItem -> MPCS.RECEIVED_ITEM>
2004-09-21 10:54:09,033 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.Reference -> MPCS.REFERENCE>
2004-09-21 10:54:09,049 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.ResourceAsgn -> MPCS.RESOURCE_ASGN>
2004-09-21 10:54:09,065 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.ReturnsItem -> MPCS.RETURNS_ITEM>
2004-09-21 10:54:09,112 INFO [net.sf.hibernate.cfg.Binder] - <Mapping class: com.meyertool.model.mpcs.ReturnsSerialNo -> MPCS.RETURNS_SERIAL_NO>

jolmstead2k · **Joined:** Fri Aug 27, 2004 8:50 am **Posts:** 3

Thanks!