Win a copy of 97 Things Every Java Programmer Should Know this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

<One-to-one> Filter Required

 
Ranch Hand
Posts: 392
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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'>
 
best scout
Posts: 1294
Scala IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 392
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
He's dead Jim. Grab his tricorder. I'll get his wallet and this tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic