-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
jdcyuen
 Post subject: Question on joins with an ummapped column
PostPosted: Thu Dec 27, 2007 8:51 pm 
Beginner
Beginner

Joined: Sat Jul 08, 2006 2:58 pm
Posts: 26
I am a novice at using Hibernate and have a question I hope is appropriate to ask in this forum.



I have a entity called person which contains a collection called :

Code:
public class Person {

           .
           .
           .

            private Map addresses = new HashMap();



   /**
    * @hibernate.map cascade="save-update"
    * @hibernate.collection-key column="PersonID"
    * @hibernate.collection-index column="addressTypeKey" type="string"
    * @hibernate.collection-one-to-many class="com.chisq.common.bo.Address"
    */
   public Map getAddresses() {
      return addresses;
          .
           .
           .

   }

   public void setAddresses(Map addresses) {
      this.addresses = addresses;
   }


}





Address looks like this:

Code:
public class Address  {
   
   private static final long serialVersionUID=0;
   
    private Long id;
    private Pickaddresstype addresstype;
    private String city;
    private String state;
    private String zip;
    private Integer priority;
    private List addressLines = new ArrayList(4);
    private String country;
                     .
                     .
                     .




I've noticed that there is an unmapped column called personid in the address table.


How can I query the person table to find a person a a specific address? Do I somehow use a join on the person and address table?How can I join on personid if it is not a mapped column?
Top
 Profile  
 
farzad
 Post subject: Re: Question on joins with an ummapped column
PostPosted: Fri Dec 28, 2007 11:02 am 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
jdcyuen wrote:
I've noticed that there is an unmapped column called personid in the address table.


How can I query the person table to find a person a a specific address? Do I somehow use a join on the person and address table?How can I join on personid if it is not a mapped column?


You don't need to map the person id since hibernate uses the address map definition in person entity to figure out how the join should be done. In hql you should be able to ask for a person with a specific address with a hql join:

Code:
select p from Person p inner join p.addresses address where
address.city='xyz'



Farzad-
Top
 Profile  
 
jdcyuen
 Post subject:
PostPosted: Fri Dec 28, 2007 12:51 pm 
Beginner
Beginner

Joined: Sat Jul 08, 2006 2:58 pm
Posts: 26
Thank you. But if you notice Address also contains a collection, a list:


Code:
public class Address  {
   
   private static final long serialVersionUID=0;
   
    private Long id;
    private Pickaddresstype addresstype;
    private String city;
    private String state;
    private String zip;
    private Integer priority;
    private List addressLines = new ArrayList(4);
    private String country;
                     .
                     .
                     .

    /**
     * @hibernate.list lazy="true" table="addresslines" cascade="all"
     * @hibernate.collection-key column="addressID"
     * @hibernate.collection-index column="position"
     * @hibernate.collection-element type="string" column="addressline"
     *
     * @return
     */

   public List getAddressLines() {
      return addressLines;
   }

   public void setAddressLines(List addressLines) {
      this.addressLines = addressLines;
   }

                     .
                     .
                     .


}


The the idea of the list is that first element of the list would contain the
street address like 1111 1st Ave. The second might contain an apartment number etc.

In order to include these items in the query would I have to join a third table? Would I modify your hql
statement to be

Code:
select p from Person p inner join p.addresses address inner join address.addressLines lines where
address.city='xyz'and lines='1111 1st Ave'
Top
 Profile  
 
farzad
 Post subject:
PostPosted: Fri Dec 28, 2007 12:54 pm 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
jdcyuen wrote:
Code:
select p from Person p inner join p.addresses address inner join address.addressLines lines where
address.city='xyz'and lines='1111 1st Ave'



This should work. Do you have a problem with it?


Farzad-
Top
 Profile  
 
jdcyuen
 Post subject:
PostPosted: Fri Dec 28, 2007 1:04 pm 
Beginner
Beginner

Joined: Sat Jul 08, 2006 2:58 pm
Posts: 26
No, I have not tried it yet. So if you wanted to search for other items in the lines list, would you just tack it on the end of the query? Like so?


select p from Person p inner join p.addresses address inner join address.addressLines lines where
address.city='xyz'

and lines='1111 1st Ave' and lines='Apt # 102'
and lines='third item'


Thanks for your help?
Top
 Profile  
 
farzad
 Post subject:
PostPosted: Fri Dec 28, 2007 1:10 pm 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
jdcyuen wrote:
No, I have not tried it yet. So if you wanted to search for other items in the lines list, would you just tack it on the end of the query? Like so?


select p from Person p inner join p.addresses address inner join address.addressLines lines where
address.city='xyz'

and lines='1111 1st Ave' and lines='Apt # 102'
and lines='third item'


Thanks for your help?



The only problem I could think of is that I don't think this query will return any result. Perhaps you are looking for something like:

Code:
select distinct p from Person p inner join p.addresses address inner join address.addressLines lines where
address.city='xyz'

and lines in ('1111 1st Ave', 'Apt # 102', 'third item')


by the way I added distinct after select since this will return multiple instances of a person depending on how many addresses and address lines it finds for each person.


Farzad-
Top
 Profile  
 
jdcyuen
 Post subject:
PostPosted: Fri Dec 28, 2007 1:25 pm 
Beginner
Beginner

Joined: Sat Jul 08, 2006 2:58 pm
Posts: 26
Thank you. What would be the equivalent query in SQL?
Top
 Profile  
 
farzad
 Post subject:
PostPosted: Fri Dec 28, 2007 1:28 pm 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
jdcyuen wrote:
Thank you. What would be the equivalent query in SQL?



the generated query will not look a lot different in the case of this hql. If you want to give it a try make sure you set show_sql property in hibernate so that it shows you the generated SQL. Alternatively, you can use logger jdbc driver like p6spy to print out queries generated by hibernate.



Farzad-
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.