Hibernate Community • View topic - Query with join on composite element?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Query with join on composite element?

Page 1 of 1

[ 6 posts ]

Previous topic | Next topic

Author

Message

lasse_stromberg

Post subject: Query with join on composite element?

Posted: Mon Apr 10, 2006 8:21 am

Beginner

Joined: Wed Apr 05, 2006 5:01 am
Posts: 20

Hibernate version: 3.1.3

I have a many-to-many mapping between my domain classes Organization and Person.
Because i need an additional attribute in their association table i use a composite-element with a nested many-to-one mapping (see below for mapping files).
I want to build a query to retrieve all persons for a certain organization_id. But now i'm really lost how to form the query with hql syntax, especially in regard to the composite-element.

I would be very glad for any help from experienced hibernate developers!
Thanks in advance!

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="mypackage">
    <class name="Organization" table="organizations">
        
        <id name="organization_id" type="long">
            <column name="organization_id"/>
            <generator class="native"/>
        </id>
   
       <set name="personOrganizationComponents"
         table="person_organization_components"
         cascade="save-update">
        <key>
            <column name="organization_id" not-null="true"/>
        </key>
        <composite-element class="PersonOrganizationComponent" >            
            <many-to-one name="person"
                         column="person_id"
                         class="Person"
                         not-null="true"
                         cascade="save-update"/>
            <property   name="someAttribute"
                        column="some_attribute"
                        type="string"
                        not-null="true"/>
        </composite-element>
     </set>      
    </class>    
</hibernate-mapping>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="mypackage">

     <class name="Person" table="persons">  
        <id name="person_id" type="long">
            <column name="person_id"/>
            <generator class="native"/>
        </id>
       <property name="firstname"   column="firstname"   type="string"/>
       <property name="lastname"   column="lastname"   type="string"/>
       <property name="gender"   column="gender"   type="character"/>
    </class>
</hibernate-mapping>

Code:

public class PersonOrganizationComponent implements Serializable {

    private String attribute;
    private Organization organization;
    private Person person;

   //getter + setter here
}

Code:

public class Organization implements Serializable {

    private long organization_id;
    private Set<PersonOrganizationComponent> personOrganizationComponents = new HashSet<PersonOrganizationComponent>();

   //getter + setter here
}

Top

lasse_stromberg

Post subject:

Posted: Mon Apr 10, 2006 10:55 am

Beginner

Joined: Wed Apr 05, 2006 5:01 am
Posts: 20

Ok, i found this solution by myself.
However i get duplicate entries in my result list:

Code:

public List getAllPersonsForOrganization(long organization_id) {
        Query q = session.createQuery(
                         "select person2 "+
                         "from Person as person, Organization as organization +                             
                         "join organization.personOrganizationComponents comps" +
                         "join comps.person person2" +
                         "where organization.organization_id = "+organization_id);
        return q.list();
}

Yes, i could insert a "distinct" keyword after "select" but i want to understand why i get the duplicates.
Is it because of the two combined joins? Is it bad practice to filter them with "distinct" or should i redesign my query?

Top

pepelnm

Post subject:

Posted: Tue Apr 11, 2006 5:12 am

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

Try with:

Code:

public List getAllPersonsForOrganization(long organization_id) {
        Query q = session.createQuery(
                         "select person "+
                         "from Organization as organization +                             
                         "join organization.personOrganizationComponents comps" +
                         "join comps.person person" +
                         "where organization.organization_id = "+organization_id);
        return q.list();
}

Every time you separate table/entity names with a comma in the from clause of a SQL/HQL query you are telling the server to do a cross join (cartesian product)

Top

lasse_stromberg

Post subject:

Posted: Wed Apr 12, 2006 4:17 am

Beginner

Joined: Wed Apr 05, 2006 5:01 am
Posts: 20

thx pepelnm! now everything works as expected!

Top

pepelnm

Post subject:

Posted: Wed Apr 12, 2006 4:37 am

Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster

Better:

Code:

public List getAllPersonsForOrganization(long organizationId) {
        Query q = session.createQuery(
                         "select person"+
                         " from Organization as organization" +                             
                         " join organization.personOrganizationComponents comps" +
                         " join comps.person person" +
                         " where organization.organization_id = :organizationId");
        return q.setLong("organizationId", organizationId).list();
}

Top

rachit.gupta

Post subject: Coposite element,

Posted: Sat Apr 15, 2006 6:38 am

Newbie

Joined: Sat Apr 15, 2006 6:22 am
Posts: 1

I was also working with Composite element.
I was having three tables
1)[/b] order
[b]2) LineItems
3) product

Table line Items was having association between order and product, i.e. many-to-one and one- to-many

but
when i was saving data in order table, it is not insering data in Product table.

<class name="ocp.Order" table="ORDERS">

<id name="oid" column="id" >
<generator class="assigned"/>
</id>

<property name="opendate" />

<many-to-one name="customer" column="customer_id"/>

<set name="lineItem" table="LINE_ITEMS" >
<key column="ORDER_ID"/>
<composite-element class="ocp.LineItem" >
<many-to-one class="ocp.Product" name="product" column="product_id" not-null="true" insert="true" cascade="all" outer-join="true"/>
<property name="quantity" column="quantity"/>
<property name="lineNumber" column="line_number"/>
</composite-element>
</set>

</class>

<class name="ocp.Product" table="PRODUCTS" >
<id name="id" column="id" unsaved-value="0">
<generator class="assigned"/>
</id>
<property name="serialNumber"/>
</class>

Sql Query Generated

Hibernate: insert into ORDERS (opendate, customer_id, id) values (?, ?, ?)
Hibernate: insert into LINE_ITEMS (ORDER_ID, product_id, quantity, line_number) values (?, ?, ?, ?)

Give me d solution using composite-element

Top

Page 1 of 1

[ 6 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