Problem in hibernate join result

[vinoth subramaniam]

Hi all,
I am trying to fetch some information from two tables in the database. I used the inner join, and i can able to get the data from the database. The only problem is the resulted data is not in the way i expected. Please see the code here.

Table Striucture

   
[B]Person Table SQL[/B]
 
create table person (Personid int Not null, street char (20) not null, 
contactid int not null, 
constraint primary_table Primary key (personid),
constraint foreign_contact foreign key (contactid) references Contact (ID))
 
[B]Contact Table SQL[/B]
 
create table CONTACT (ID int Not null, FIRSTNAME varchar(50), LASTNAME varchar(50), EMAIL varchar(50), Constraint primay_key_contact (ID))
 
 

And the CONTACT and person class have one to many relationship according to the business logic. I.e for one contact there may be one or more streets (just for some testing purpouse i wrote like that..)

Here is the CONTACT class

  
 
package HibernateJoin;
 
import java.util.List;
import java.util.ArrayList;;
 
public class CONTACT {
private int ID;
private String FIRSTNAME;
[B]private List persons = new ArrayList();[/B]
 
//getters and setters
public int getID() {
return ID;
}
public void setID(int id) {
ID = id;
}
public String getFIRSTNAME() {
return FIRSTNAME;
}
public void setFIRSTNAME(String firstname) {
FIRSTNAME = firstname;
}
public List getPersons() {
return persons;
}
public void setPersons(List persons) {
this.persons = persons;
}
 
}
 
  

And here is the person class

   
 
ackage HibernateJoin;
 
public class Person {

private int Personid;
private String street;
[B]private CONTACT contact;[/B]

public int getPersonid() {
return Personid;
}
public void setPersonid(int personid) {
Personid = personid;
}
public String getStreet() {
return street;
}
public void setStreet(String street) {
this.street = street;
}
public CONTACT getContact() {
return contact;
}
public void setContact(CONTACT contact) {
this.contact = contact;
}

 
}
 
 

And here is the hbm.xml file for the CONTACT class

  
<?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="HibernateJoin.CONTACT">
      <id name="ID" type="int">
         <generator class="increment"/>
      </id>
 
      <property name="FIRSTNAME" type="string"/>
     [B] <bag name="persons" inverse="true" cascade="all,delete-orphan">
      <key column="contactid"/>
      <one-to-many class="HibernateJoin.Person"/>[/B]
      </bag>
 
  

And here is the hbm.xml file for person class

   
<?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="HibernateJoin.Person">
      <id name="Personid" type="int">
         <generator class="increment"/>
      </id>
 
      <property name="street" type="string"/>
[B]      <many-to-one name="contact" class="HibernateJoin.CONTACT" column="contactid"/>[/B]
 
   </class>
</hibernate-mapping>
 
 

And i am using the following classes to get the data

  
package com.tcs.Nulamda.support;
 
import java.util.Iterator;
import java.util.List;
 
import HibernateJoin.CONTACT;
import HibernateJoin.Person;
 
public class JoinData {

public  List joiningtables()

{
System.out.println("Before initialzing  session factory object...........");
[B]UsingSessionFactory dao = new UsingSessionFactory();[/B]
System.out.println("after creating session factory object...........");
List list = dao.select("[B]from CONTACT C inner join fetch C.persons as p[/B]");
System.out.println("size of list..."+list.size());

Iterator iter = list.iterator();
              
        while (iter.hasNext()) {
        CONTACT con = (CONTACT) iter.next();
            
                       
     System.out.println(con.getID());
     System.out.println(con.getFIRSTNAME());
            
         List personlist=con.getPersons();
         System.out.println(personlist);
 
 for (Iterator iterator = personlist.iterator(); iterator.hasNext() :wink:  {
            
       Person personobj= (Person) iterator.next();
       System.out.println(personobj.getStreet());
 
}
 
  

The using sessionfactory class

 
 
package com.tcs.Nulamda.support;
 
import java.util.List;
 
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
 
public class UsingSessionFactory {
public UsingSessionFactory() {
System.out
.println("Inside the Using session factory class constructor...........");
}
 
// one session factory for one application
SessionFactory sessionFactory = new Configuration().configure()
.buildSessionFactory();
 
public List select(String query) {
List list = null;
Session session = null;
try {
System.out.println("Creating the session factory object");
 
System.out.println("Creating the session instances from session object");
 
session = sessionFactory.openSession();
System.out.println("Session Created");
System.out.println("Before beginning the transaction");
Transaction tran = session.beginTransaction();
System.out.println("Transaction beguns ........");
list = session.createQuery(query).list();
System.out.println("Line is Executing ------ list = session.createQuery(ob).list();");
 
session.flush();
System.out.println("flush completed ......");
System.out.println("Before commiting ......");
tran.commit();
System.out.println("transaction commited");
 
} catch (HibernateException e) {
System.out.println("inside the hibernate exception.....");
//handle exception
} finally {
System.out.println("session closed ..........");
session.close();
}
 
System.out.println("returning the list");
return list;
}
 
}
   

The resulted list have the three CONTACT object, in which each one have the persons list object. That particular persons list have no of person object.

Resulted List == [CONTACT OBJ, ....]
|
|
[ID, FirstName, Persons]
|
|
[Person OBJ, person OBJ]
|
|
[Street, personID]

The part of the result in the console as follows

  
14:40:27,958 INFO  [STDOUT] size of list...3
14:40:27,958 INFO  [STDOUT] 9
14:40:27,958 INFO  [STDOUT] Antony
14:40:27,958 INFO  [STDOUT] North Street        
14:40:27,958 INFO  [STDOUT] 2
14:40:27,958 INFO  [STDOUT] Selvaraj
14:40:27,958 INFO  [STDOUT] North Street        
14:40:27,958 INFO  [STDOUT] Kallur Street       
14:40:27,958 INFO  [STDOUT] 2
14:40:27,958 INFO  [STDOUT] Selvaraj
14:40:27,958 INFO  [STDOUT] North Street        
14:40:27,958 INFO  [STDOUT] Kallur Street
 
  

But i need the result in the following way

Size of the list 3

9
Antony
North Street

2
selvaraj
North Street

2
Selvaraj
Kallur Street

Because i want to display this data in a table. Where i need to change, Is there any modification needed in the Mapping or in the hql query or in the classes declaration. Please help me to solve this issue.

Thanks in advance ..!

[vinoth subramaniam]

Can you please anyone respond to this question please...!

[Kuladip Yadav]

Originally posted by vinoth subramaniam:
Can you please anyone respond to this question please...!

Hey Wait ! Wait !!

Please look at the following code you can find your solution.

  List  list =  session.createQuery( "from CONTACT C")
                         .setFetchMode( "persons ",  FetchMode.SELECT )
                         .list();            
    
    Iterator iter = list.iterator();
    
    while (iter.hasNext()) {
        CONTACT con = (CONTACT) iter.next();  
         List personlist=con.getPersons();
         
    
        for (Iterator iterator = personlist.iterator(); iterator.hasNext()   {
                
           Person personobj= (Person) iterator.next();
           System.out.println(con.getID());
           System.out.println(con.getFIRSTNAME());
           System.out.println(personobj.getStreet());
        
        }
    }
   
          

I think it will solve your problem

Thanking You
- Kuldeep
[ November 11, 2008: Message edited by: Kuldeep Yadav ]

[vinoth subramaniam]

QUOTE]
Hey Wait ! Wait !!

Please look at the following code you can find your solution.


code:
--------------------------------------------------------------------------------

List list = session.createQuery( "from CONTACT C") .setFetchMode( "persons ", FetchMode.SELECT ) .list(); Iterator iter = list.iterator(); while (iter.hasNext()) { CONTACT con = (CONTACT) iter.next(); List personlist=con.getPersons(); for (Iterator iterator = personlist.iterator(); iterator.hasNext() { Person personobj= (Person) iterator.next(); System.out.println(con.getID()); System.out.println(con.getFIRSTNAME()); System.out.println(personobj.getStreet()); } }

--------------------------------------------------------------------------------



I think it will solve your problem

Thanking You
- Kuldeep


Sorry boss, can you please point out the error... sorry i cant catch that one...

[vinoth subramaniam]

Hi all,
I resolved my problem, i used criteria API instead of the HQL. The problem is with the result set. And i resloved it by the following way

list = session.createCriteria(PLAN.class).setFetchMode("suspenses",FetchMode.JOIN). setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY).list();

Thanks for your time... :