<One-to-one> Filter Required

[Sandeep Vaid]

Consider this scenatio :
I have one-to-one relation between Product and ProductBasic BUT at database level i have one-to-many between
PRODUCT and PRODUCTBASIC tables as history is mainted in the same table :

PRODUCT ----> PID (PK)
PRODUCTBASIC ---> PID (FK), USECODE forms Composite-key

PRODUCT
PID PNAME
1 Pen

PRODUCTBASIC
PID USECODE STARTTIME ENDTIME
1 100 06-MAY-2009 11:00 07-MAY-2009 10:00
1 100 07-MAY-2009 10:00 31-DEC-9999 00:00

Now while querying for ProductBasic, i will always fetch the row which satisfies the condition STARTTIME<CURRENTTIME><ENDTIME
and this will surely return me only one row.

Ideally the generated sql query should be :

select * from PRODUCT product left outer join PRODUCTBASIC productbasic on '100'=productbasic.USETYPE
and product.PID=productbasic.PID
and (
productbasic.STARTTIME><'09-MAY-2009 10:00'
and '09-MAY-2009 10:00'<productbasic.ENDTIME
)
where
product.PID='1'>

[Marco Ehrentreich]

Hi Sandeep,

was this a question or just a statement

Anyway, I think I understand the problem and I had recently the same. A simple solution to integrate this with O/R mapping based on JPA or Hibernate is to use named queries with which you can exactly define this kind of "filter" to get a specific version of an entity by defining a EJBQL query corresponding to the SQL statement you have given.

This is not exactly as nice to use as getting an entity simply by its primary key but it's surely better than using native SQL queries.

Marco

[Sandeep Vaid]

I think we are on different page.
Hibernate says, "With clause" with Fetch is not allowed, use Filters. BUT filters (<filter>)are not allowed with one-to-one associations.
May i know the solution in HQL?