?
Solved

how to write queries in parent -child relationships using hibernate

Posted on 2007-03-26
15
Medium Priority
?
12,461 Views
Last Modified: 2013-11-24
Below is the mapping files for parnet and child tables;


Parent
*****

ID NAME
1  STEVEN
2  JOHNSON


Child
*****
ID FIRSTNAME      LASTNAME                                 PID
1  XXX                             AAA                                   1
2  YYYY                          LLL                         1
3  GGGG             JJJJ            2
4  JJJJ             BJB                                  1
5  HGHH             JUIU                          2

i have 2 qustions in this..

1.Using hibernate how to retrieve child records by passing corresponding parentid(pid);i want to know how to use hibernate in a good way?

2.how to write below queries in Hibernate??
select *  from parent p,child c where p.id=c.pid and c.pid=1



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

<hibernate-mapping>
    <class name="com.dao.Parent" table="PARENT" schema="SCOTT">
        <id name="pid" type="java.lang.Long">
            <column name="PID" precision="6" scale="0" />
            <generator class="increment" />
        </id>
        <property name="pname" type="java.lang.String">
            <column name="PNAME" length="50" />
        </property>
        <set name="childs" inverse="true">
            <key>
                <column name="PID" precision="6" scale="0" />
            </key>
            <one-to-many class="com.dao.Child" />
        </set>
    </class>
</hibernate-mapping>

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

<hibernate-mapping>
    <class name="com.dao.Child" table="CHILD" schema="SCOTT">
        <id name="cid" type="java.lang.Long">
            <column name="CID" precision="6" scale="0" />
            <generator class="increment" />
        </id>
        <many-to-one name="parent" class="com.dao.Parent" fetch="select">
            <column name="PID" precision="6" scale="0" />
        </many-to-one>
        <property name="firstname" type="java.lang.String">
            <column name="FIRSTNAME" length="12" />
        </property>
        <property name="lastname" type="java.lang.String">
            <column name="LASTNAME" length="12" />
        </property>
    </class>
</hibernate-mapping>
0
Comment
Question by:chaitu chaitu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
15 Comments
 
LVL 8

Accepted Solution

by:
mnrz earned 1200 total points
ID: 18798188
you may use Criteria:

Criteria c = session.createCriteria(Parent.class);
c.createAlias("childs","childs");
c.add(Expression.eq("parent.id",pid)); //pid in your case is 1
List result = c.list();
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 18798207
c.createAlias("childs","childs");

what will it do?it will automatically join parent and child tables??

if i want outjoin on child table what should i do??
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 18798219
what is meaning of this --- ("childs","childs")
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 8

Expert Comment

by:mnrz
ID: 18798256
well
in your hbm you defined a one-to-many relationship

 <one-to-many class="com.dao.Child" />

so, hibernate will understand your child table

c.createAlias("childs","childs"); is just like "select * from Parent parent, Child childs"

and after that you can access all its fields by using the alias "childs", for example, "childs.firstName"

-----
for outer join, I am not sure but I think you should use this

c.createAlias("childs","childs",Criteria.LEFT_JOIN); or
c.createAlias("childs","childs",Criteria.FULL_JOIN);
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 18798269
Criteria c  = getSession().createCriteria(Parent.class);
                   
              System.out.println("aaaaaaaaaaa11111");
            c.createAlias("childs","childs");
            System.out.println("aaaaaaaaaaa222222");
            c.add(Expression.eq("parent.pid",1));
            List results = c.list();

WHEN I execute i am getting below exception

aaaaaaaaaaa11111
aaaaaaaaaaa222222
Exception in thread "main" org.hibernate.QueryException: could not resolve property: parent of: com.dao.Parent
      at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
      at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:37)
      at org.hibernate.persister.entity.AbstractEntityPersister.getSubclassPropertyTableNumber(AbstractEntityPersister.java:1282)
      at org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:31)
      at org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEntityPersister.java:1257)
      at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryTranslator.java:433)
      at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumnsUsingProjection(CriteriaQueryTranslator.java:393)
      at org.hibernate.criterion.SimpleExpression.toSqlString(SimpleExpression.java:45)
      at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:333)
      at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:82)
      at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:67)
      



this is my Parent.java class
*****************************

                  public class Parent  implements java.io.Serializable {


    // Fields    

     private Long pid;
     private String pname;
     private Set childs = new HashSet(0);


    // Constructors

    /** default constructor */
    public Parent() {
    }

   
    /** full constructor */
    public Parent(String pname, Set childs) {
        this.pname = pname;
        this.childs = childs;
    }

   
    // Property accessors

    public Long getPid() {
        return this.pid;
    }
   
    public void setPid(Long pid) {
        this.pid = pid;
    }

    public String getPname() {
        return this.pname;
    }
   
    public void setPname(String pname) {
        this.pname = pname;
    }

    public Set getChilds() {
        return this.childs;
    }
   
    public void setChilds(Set childs) {
        this.childs = childs;
    }
   

}
0
 
LVL 10

Assisted Solution

by:Jaax
Jaax earned 800 total points
ID: 18798355
1. Firstly you it is advisable to add  your "childs" as a SET  in your parent entity - something like this in the parent hbm file
....
<set name="children">
      <key column="PID"/>
      <one-to-many class="com.dao.Child"/>
</set>
....

Also define corresponding getters and setters in the Parent.java file

Having done so, the children can be retrieved as follows in java

Parent parent = (Parent)getSessionFactory().openSession().get(com.dao.Parent.class, parentId);
Hibernate.initialize(parent .getChildren());
if(parent .getChildren() != null){
    Iterator iter = parent .getChildren().iterator();
   while(iter.hasNext()){
       Hibernate.initialize(iter.next());
  }
}


2.You can use HQL for your queries :
String query = "select * from parent p
      left outer join p.children c
where  c.pid=1";

List list = HibernateTemplate.find(query); //Use an instance of HibernateTemplate
The list contains your results in the form of list of object arrays



0
 
LVL 8

Expert Comment

by:mnrz
ID: 18798444
sorry
please change this line

c.add(Expression.eq("parent.pid",1));

to

c.add(Expression.eq("pid",1));

please show your Child class
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 18798845
jaax,

i removed Hibernate.initialize in my code its working fine;what is the use of Hibernate.initialize ??

mnrz,

if i changed it to c.add(Expression.eq("pid",new Long(1)));  its working fine.
0
 
LVL 10

Expert Comment

by:Jaax
ID: 18799068
Do you mean to say that with Hibernate.initialize , your code fails - i.e; throws some kind of Hibernate Exception ?

Hibernate.initialize() initializes the entities on demand and not beforehand, thus improving performance.
If you had specified lazy="false" in your hbm file, hibernate would load all the objects in the object graph and that would consume memory but accords the client, the luxury to use any of the objects at its whim. On the other hand, specifying lazy="true", postpones loading of the entity until actually it is needed (by specifying Hibernate.initialize()) and thus economizes on memory. But on the flip-side, the clinet should be careful to use only those objects in the graph that have been initialized.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 18799201

i observed two things by writing in both forms.


Parent parent = (Parent)getSessionFactory().openSession().get(com.dao.Parent.class, parentId);
Hibernate.initialize(parent .getChildren());
if(parent .getChildren() != null){
    Iterator iter = parent .getChildren().iterator();
   while(iter.hasNext()){
       Hibernate.initialize(iter.next());
  }
}


from writing above code actually it execting two queries like

select parent0_.PID as PID0_0_, parent0_.PNAME as PNAME0_0_ from PARENT parent0_ where parent0_.PID=?

select childs0_.PID as PID1_, childs0_.CID as CID1_, childs0_.CID as CID1_0_, childs0_.PID as PID1_0_, childs0_.FIRSTNAME as FIRSTNAME1_0_, childs0_.LASTNAME as LASTNAME1_0_ from CHILD childs0_ where childs0_.PID=?

*******************************************************************************

but the below code executes only one query by joining two tables like....thus improving performace.


select this_.PID as PID0_1_, this_.PNAME as PNAME0_1_, childs1_.CID as CID1_0_, childs1_.PID as PID1_0_, childs1_.FIRSTNAME as FIRSTNAME1_0_, childs1_.LASTNAME as LASTNAME1_0_ from PARENT this_ inner join CHILD childs1_ on this_.PID=childs1_.PID where this_.PID=?

Criteria c  = getSession().createCriteria(Parent.class);
            c.createAlias("childs","childs");
            c.add(Expression.eq("pid",new Long(1))); //pid in your case is 1
            List results = c.list();
           
            for (Iterator iter = results.iterator(); iter.hasNext();) {
                        Child element = (Child) iter.next();
                          System.out.println("ParentDAO.findByExample()"+element.getFirstname());
                  }

Exception in thread "main" java.lang.ClassCastException: com.dao.Parent
      

but my problem is how to get only child information ;if i type cast with Parent object its working fine...but i want child objects ..


0
 
LVL 8

Expert Comment

by:mnrz
ID: 18799245
so you can create your criteria with Child, check this:

List result = session.createCriteria(Child.class).
createAlias("parent","parent").
add(Expression.eq("parent.pid",new Long(1))).
list();

this will return a list of type "Child"

0
 
LVL 10

Expert Comment

by:Jaax
ID: 18799259
After you have done this :

Parent parent = (Parent)getSessionFactory().openSession().get(com.dao.Parent.class, parentId);
Hibernate.initialize(parent .getChildren());
if(parent .getChildren() != null){
    Iterator iter = parent .getChildren().iterator();
   while(iter.hasNext()){
       Hibernate.initialize(iter.next());
  }
   
   Iterator iter = parent .getChildren().iterator();
  while(iter.hasNext()){
      Child child = (Child)iter.next();
     //Do what you like with child entity
    }

   
 

}


}

0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 18799345
final question for both of you which approach is better ;my personnel view is mnrz is said is better one because its executing only query gives us better performance what do you say Jaax..
0
 
LVL 8

Expert Comment

by:mnrz
ID: 18799395
In my opinion, the performance in both of them is alike, but with Criteria you have many options and features you may need in the future
0
 
LVL 10

Expert Comment

by:Jaax
ID: 18799630
The choice should be on the requirement. Load is useful when you are querying via PRIMARY KEY and when you are damn sure that it will result in only one record.
Criteria is mainly used for SEARCHING and as mnrz has maintained, additional search attributes can be added or removed as you like. Criteria is the Object oriented way of dealing with DB search. However, Criteria is restricted in certain aspects - like you cannot specify SQL functions and limited flexibility with joins.

To summarize:
1.If you want to load only one record based on primary key go for load or get
2.If you want to search and are expecting multiple outputs, then opt for Criteria or HQL
The choice between Criteria and HQL should be based on flexibility in terms of using advanced SQL features (HQL) and using Object oriented way of searching
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question