Hibernate Help

[DaWickedRebel]

Hi

I am relatively new to hibernate in that I have never done anything more complex than simple object mappings. I know have a more complex situation and don't see the clear way to proceed. Any advice would be greatly appreciated.

I have an Object that is mapped to a specific table. The mapping file indicates this relationship. To determine which objects to retrieve, I need to use a table that is beyond the scope of the mapping. A mock-up of the query is:\

session.createQuery("from Object c where c.myCode in (select sub_code from another_table v where v.field1 = :myCriteria1 and v.field2 = :myCriteria2)")

How does one accomplish such a task? As a semi-newb, I know I must be missing something. Thanks again for any and all advice.

 

[jemminger]

never done it, but i think your object c should define a one-to-many or many-to-many relationship to the other table... then you would say something like

from Object c where c.myCode = OtherObject.code

you might also try the hibernate forums

http://forum.hibernate.org/

-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[dexter195]

i would suggest you have a look at

http://www.hibernate.org.

it will give you tutorials on pretty much everything you need to know on hibernate.

you wouldnt have the colons after the equals sign.

when your using hibernate you dont have to use the sql specific to the database your using. hibernate does that for you. the reason is that if you have to change the database your using, you have minimal changes to make to your source code.

but you do have to keep in mind that some free databases dont support subqueries. some versions of mysql dont support them.

 

[jemminger]

dexter,

actually you *would* use the colons - that's not sql, it's hql. the colons are placemarker for named parameters when building an hql Query.

i'm not sure however if the overall syntax is correct, as hql might have its own way of handling such a relationship

-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[dexter195]

fair enuf, i never used the hql at all. i mainly used objects to perform the queries instead of using sql. and then used iterators to go through the results, but thats not what DaWickedRebel is looking for so my work here or lack there of, is done.

 

[DaWickedRebel]

Thanks for the replies. I was able to work it out through research, trial and error. Eventually I figured out how to do effective joins with Hibernate.

 

[sedj]

DaWickedRebel : Could you post your solution for others to view ?

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[DaWickedRebel]

Sure...sorry if that is the standard.

The HQL query:

Query query = session.createQuery("select lhts from LHTShare  share join share.shareId.ShareLHTCodeId lhts where share.shareId.SSLCodeId = :ssl and share.shareId.LHTCodeId = :lht");

please forgive the lack of indents in the code...not sure how that works here...anyway..

the highlight from my Mapping XML file:

<key-many-to-one name="ShareLHTCodeId" class = "domain.objects.ContainerLHT" column="SUB_LHT_CODE"/>

To relate what I did to the pseudo-code from my initial question, domain.objects.ContainerLHT in the mapping would equate to Object c in my question, and rather than thinking in terms of table 'another_table', I created a new share object which I mapped to 'another_table', then did a join.

My example is a bit more complex than a normal join example because one of the DB tables I'm dealing with is a legacy-hybrid(I love that term!) with a composite primary key. One of the components of that composite key is actually the foreign key I needed to make my join work.

I also saw a way to accomplish which would have had me declare a List in my LHTShare class and do the mapping to my <composite-element>, in this case a ContainerLHT.

What a powerful tool this hibernate is. The more I use it, the more I like it!

 

[sedj]

Here on tek-tips, we try to focus on people helping each other - so its always good to leave a solution for other members or 'googlers' to see.

Thankyou for posting the solution

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk