This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Hibernate one-to-many where clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate one-to-many where clause" Watch "Hibernate one-to-many where clause" New topic
Author

Hibernate one-to-many where clause

bujin wang
Ranch Hand

Joined: Jun 21, 2003
Posts: 48
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

Joined: Apr 14, 2004
Posts: 10336

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).


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
bujin wang
Ranch Hand

Joined: Jun 21, 2003
Posts: 48
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Aug 07, 2003
Posts: 1646
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

Joined: Apr 14, 2004
Posts: 10336


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

Joined: Aug 07, 2003
Posts: 1646
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

Joined: Apr 14, 2004
Posts: 10336


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:
 
GeeCON Prague 2014
 
subject: Hibernate one-to-many where clause