• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate one-to-many where clause

 
bujin wang
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How flexible in the where clause for one-to-many?

We did some exloration and found that inside the where clause, Hibernate tries to put table name before every non-logic word. That is to say, we can't do put any function calls inside the where clause. For example "the_data > CURRENT TIMESTAMP". Hibernate will add table_name_.CURRENT " and hence stops from there.

Anyone got any experience on that?

Bujin
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How are you running your query? I think you'll have to show us, since CURRENT TIMESTAMP is not valid HQL. (In fact its not valid SQL either, though CURRENT_TIMESTAMP is valid Transact-SQL).
 
bujin wang
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For example:

Where="field1> CURRENT TIMESTAMP and field2> CURRENT DATE".

The same query can be used with ociweb's manager class without any problem:

"from theTAble where field1> CURRENT TIMESTAMP and field2> CURRENT DATE".

We are using DB2. Are there any switch to tell Hibernate not to touch the where clause we passed in, but directly run against database?

Thanks,
Burt
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What you are trying to do is run platform-specific "SQL" through Hibernate - which is not a good idea, since it removes one of the reasons for using Hibernate: namely that it is platform independant. You can run SQL directly against the DB ujsing the createSQLQuery method (see the Session class).
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm still new to Hibernate and haven't run into this, but first things first.

The obvious: have you set your SQL dialect correctly? The DB2 dialect should tell Hibernate that CURRENT and TIMESTAMP/DATE are keywords not to be interpreted by Hibernate.

The less obvious: I believe Hibernate allows you to execute direct SQL queries versus HQL queries. It sounds like you want to mix them, but perhaps searching the Hibernate documentation in this direction will lead you to nirvana (or at least a solution).
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

The DB2 dialect should tell Hibernate that CURRENT and TIMESTAMP/DATE are keywords not to be interpreted by Hibernate.

Not exactly. The dialect won't come into this. The dialect translate HQL into SQL, handling any platform-specific wierdness a DB vendor has written into their product. CURRENT TIMESTAMP has no meaning in HQL so will be interpreted as entity names (as it would be in SQL).
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Paul Sturrock:
Not exactly. The dialect won't come into this. The dialect translate HQL into SQL, handling any platform-specific wierdness a DB vendor has written into their product.
So do you specify "sysdate" in the HQL query and the dialect would translate that into SQL as "CURRENT DATE"?

I guess at this point I'd recommend posting to the Hibernate forums.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

So do you specify "sysdate" in the HQL query and the dialect would translate that into SQL as "CURRENT DATE"?

Exactly.

I guess at this point I'd recommend posting to the Hibernate forums

I dunno. The Hibernate forums are considerably less forgiving than these ones. About 90% of the answers posted equate to RTFM - Gavin et al. may be very good at writing persistance layers, but don't seem to have much patience these days :roll:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic