Joining tables

[subhashchandra medhiassam]

Hi all,
Could anybody please guide me in joining two tables using Hibernate?
Also, kindly provide me some useful examples on the same.

Thanks in advance,
Subhash

[Paul Sturrock]

The documentation covers this pretty thoroughly (with examples). Have you looked there? Is there something more specific you don't understand? Are you getting errors trying this?

[subhashchandra medhiassam]

Could anybody give me some complete example code on joining two tables using hibernate?

[subhashchandra medhiassam]

Hi,
I don't want to use HQL. without using HQL is it possible to join two tables? if yes, then could you kindly provide me with some example code?

Thanks,
Subhash

[Paul Sturrock]

We don't usually give out code examples, primarily because its not the intention of JavaRanch to be a code mill.
But, like I said, you can get examples by reading the Hibernate documentation. There are examples of joins using criteria, HQL and mapping files and annotations in there.
[ September 16, 2008: Message edited by: Paul Sturrock ]

[subhashchandra medhiassam]

Hi,
i would like to know how i would join two tables using Criteria queries. I have searched the net ample number of times but could not find anything on it.

Suppose, i have two tables a1 and b2.

a1 has columns identity(type int) and name(type varchar).
b2 has columns id(type int) and address(type varchar).

i would like to join these two tables using Criteria API and then print the result. Kindly do let me know how i should proceed.

[Paul Sturrock]

I have searched the net ample number of times but could not find anything on it.

Really? The first result in a quick google search gave me an example.

The best place to start looking for an answer to a question like this is in the Hibernate documentation which (as I said before) contains the examples you are looking for.
[ September 16, 2008: Message edited by: Paul Sturrock ]

[Cameron Wallace McKenzie]

I guess it depends on what you mean by 'join' two tables?

If you just want to create a foreign key relationship, you do that when you create the tables at the SQL level.

You can map such an association with Hibernate and JPA annotations quite easily, of course, and use a SchemaExport to create the tables that will have foreign key enforced join. Here's one I do on my website (a free tutorial!)

   
@Entity
public class Player {     
  @ManyToOne
  @JoinColumn(name="team_id")
  public Team getTeam() {return team;}     
}
 

  
public class Team {   
@OneToMany(mappedBy="team", 
             targetEntity=Player.class, 
              fetch=FetchType.EAGER,
               cascade=CascadeType.ALL)
public List<Player> getPlayers() {return players;}  
}
  

Mapping Two Tables Joined by a Foreign Key

But now you're talking all about a criteria query, so that's a whole other ball of wax. I'd ask if you've tried a criteria query on just one, unjoined, table? It's pretty easy if you've got a good tutorial. Once you understand how to do an Example or Restrictions Criteria on one table or class, don't it with associated classes is a lead pipe cinch.

Tutorial on the Criteria API

Let us know the problems you run into when you try this, and we'll help you out!

-Cameron McKenzie

[subhashchandra medhiassam]

hi,
corresponding to the two tables, i am having two classes:
a1:

 public class a1 {
 
   private int identity;
   private String name;
   public int getIdentity() {
       return identity;
   }
   public void setIdentity(int identity) {
       this.identity = identity;
   }
   public String getName() {
       return name;
   }
   public void setName(String name) {
       this.name = name;
   }
   
   
   
 
}
   

and b2:

  public class b2 {
 
    private int id;
    private String address;
    
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    
    
 
}  

My hibernate.cfg.xml looks like the following:

 <?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
 
<hibernate-configuration>
<session-factory>
      <property name="hibernate.connection.driver_class">
 
com.mysql.jdbc.Driver</property>
      <property name="hibernate.connection.url">
 
jdbc:mysql://localhost/hibernatetutorial</property>
      <property name="hibernate.connection.username">root</property>
      <property name="hibernate.connection.password">root</property>
      <property name="hibernate.connection.pool_size">10</property>
      <property name="show_sql">true</property>
      <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
      <property name="hibernate.hbm2ddl.auto">update</property>
      <!-- Mapping files -->
      <mapping resource="join.hbm.xml"/>
</session-factory>
</hibernate-configuration>   

and join.hbm.xml looks the follwoing:

<?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>
  <class name="a1" table="a1">
     <id name="identity" column="identity" type="int">
     </id>
     <property name="name" column="name" type="string" />  
 </class>
 <class name="b2" table="b2">
     <id name="id" column="id" type="int">
     </id>
     <property name="address" column="name" type="string" />  
 </class>
</hibernate-mapping>    

In a third class i have to write the logic for creating the join of the two tables. Kindly guide me as to how i should proceed for creating the third class.

Thanks,
Subhash

PS: I don't want to use annotations
[ September 16, 2008: Message edited by: subhashchandra medhiassam ]

[subhashchandra medhiassam]

Hi,
Could anybody kindly give me the code to join these two tables using hibernate?

I would like to use either of Criteria API or HQL queries.
Kindly do let me know.

Thanks,
Subhash
[ September 18, 2008: Message edited by: subhashchandra medhiassam ]

[subhashchandra medhiassam]

@Paul Sturrock

The article( http://www.javalobby.org/articles/hibernatequery102/ ) prescribed by you discusses performing an inner join. But what i am looking for is joining two different tables and not necessarily an inner join.

Could anybody kindly help me with this? I am very,very new to Hibernate.I need help.

Thanks,
Subhash

[ September 19, 2008: Message edited by: subhashchandra medhiassam ]
[ September 19, 2008: Message edited by: subhashchandra medhiassam ]

[subhashchandra medhiassam]

The tables mentioned by me are unrelated to each other. They don't have pk/fk relationship.

And i want to use Criteria queries.

Could anybody kindly give me some code examples?

Thanks,
Subhash

[Paul Sturrock]

The tables mentioned by me are unrelated to each other. They don't have pk/fk relationship

So why perform a join? Why are you trying to relate unrelated tables? Could you not just use two queries?

The only valid join in these circumstances is a UNION which isn't yet supported in HQL, so I would be surprised if you could do it with Criteria.

[subhashchandra medhiassam]

Hi all,
I am modifying my question.

I have two tables in MYSQL: department and employee.

department has columns departmentId(type int) and name(type varchar).
employee has columns employeeId(type int), name(type varchar), job(type varchar) and departmentId(type int). The attribute departmentId of table employee acts as the foreign key for table department.

Now, i want to join the two tables department and employee using Criteria API. how should i proceed?

Thanks,
Subhash

[subhashchandra medhiassam]

Corresponding to the two tables i have two classes: employee.java and department.java.

employee.java looks like the following:

 
public class employee {
 
    /**
     * @param args
     */
    private int employeeId;
    private String name;
    private String job;
    private int departmentId;
    
    public int getDepartmentId() {
        return departmentId;
    }
    public void setDepartmentId(int departmentId) {
        this.departmentId = departmentId;
    }
    public int getEmployeeId() {
        return employeeId;
    }
    public void setEmployeeId(int employeeId) {
        this.employeeId = employeeId;
    }
    public String getJob() {
        return job;
    }
    public void setJob(String job) {
        this.job = job;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    
    
}

department.java looks like the following:

    
public class department {
 
    /**
     * @param args
     */
   private int departmentId;
   private String name;
   
   public int getDepartmentId() {
       return departmentId;
   }
   public void setDepartmentId(int departmentId) {
       this.departmentId = departmentId;
   }
   public String getName() {
       return name;
   }
   public void setName(String name) {
       this.name = name;
   }   
 
}
 

Now in a third class i am trying to join the two tables. The code fragment looks like this:

  
public static void main(String[] args) {
Session session = null;
    try {
      // This step will read hibernate.cfg.xml and prepare hibernate for use
      SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
      session = sessionFactory.openSession();
      
      Criteria criteria = session.createCriteria(employee.class).add(Restrictions.like("job", "%pr%"));
      criteria.setFetchMode("department", FetchMode.EAGER);
      List list = criteria.list();
      
      ----------
              ----------      
      
    }catch(Exception e){
e.printStackTrace();
    }
 
   
    }  

Is this List object going to contain the values obtained through the joining of tables employee and department? If not then how should i retrieve the values obtained through joining tables employee and department.

Kindly help.

Thanks,
Subhash

[Paul Sturrock]

The class Department has no association with Employee, so the fetch is meaningless. There may be a relationship in the data model, if there is and you want this relationship to be reflected in your object model you will need to map the association.

See the Hibernate documentation on associations to learn how to do this.
[ September 23, 2008: Message edited by: Paul Sturrock ]

[subhashchandra medhiassam]

My mapping file join.hbm.xml looks like the following:

 
<?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>
  <class name="employee" table="employee">
     <id name="employeeId" column="employeeId" type="int">
     <generator class="native"/>
     </id>
     <property name="name" column="name" type="string" /> 
     <property name="job" column="job" type="string" /> 
     <property name="departmentId" column="departmentId" type="int" />
     <many-to-one name="departmentId" column="departmentId" not-null="true" />
 </class>  
 <class name="department" table="department">
     <id name="departmentId" column="departmentId" type="int">
     <generator class="native"/>
     </id>
     <property name="name" column="name" type="string" />  
 </class>
 
</hibernate-mapping> 

When i run the program, however, i am getting the follwoing message:


org.hibernate.MappingException: An association from the table employee refers to an unmapped class: int
at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:932)
at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:888)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1036)
at Criteria2.main(Criteria2.java:19)

Why is it so? Where did i go wrong? Could anybody kindly bail me out?

Thanks,
Subhash

[Paul Sturrock]

Reading the Hibernate documentation on associations really would help.


OK, lets look at what you have here:

<class name="employee" table="employee">
     <id name="employeeId" column="employeeId" type="int">
     <generator class="native"/>
     </id>
     <property name="name" column="name" type="string" /> 
     <property name="job" column="job" type="string" /> 
     <property name="departmentId" column="departmentId" type="int" />
     <many-to-one name="departmentId" column="departmentId" not-null="true" />
 </class>  

This is a mapping for a class called employee, which has the following properties and types:

employeeId (int)

name (String)

job (String)

departmentId (int)

departmentId (department)

The last two look suspect - Hibernate is complaining because you have two properties mapped to the same field one should be read only.

If you think about it, based on what you have, I'd expect a class that looked like this:

public class employee {
   private int employeeId;
   private String name;
   private String job;
   private int departmentId;
   private department departmentId;
 
   // getters/setters
   public department getDepartment() {
      return departmentId;
   }
 
   public int getDepartment() {
      return departmentId;
   }
 
}

You will see that this class in not valid Java.

What you've defined here:

<many-to-one name="departmentId" column="departmentId" not-null="true" />

is known as a unidirectional many-to-one association. If you look in the documentation for an example you'll find a good right at the top of the section on mapping associations.

[subhashchandra medhiassam]

Now, i have changed my hbm.xml file so that it looks like the following:

 <?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>
  <class name="employee" table="employee">
     <id name="employeeId" column="employeeId" type="int">
     <generator class="native"/>
     </id>
     <property name="name" column="name" type="string" /> 
     <property name="job" column="job" type="string" /> 
     <many-to-one name="departmentId" column="departmentId" not-null="true" />
   </class>  
 <class name="department" table="department">
     <id name="departmentId" column="departmentId" type="int">
     <generator class="native"/>
     </id>
     <property name="name" column="name" type="string" />  
 </class>
</hibernate-mapping>   

When i run my main class which looks like the following:

   import java.util.List;
 
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Restrictions;
 
public class Criteria2 {
 
    /**
     * @param args
     */
    public static void main(String[] args) {
Session session = null;
    try {
      // This step will read hibernate.cfg.xml and prepare hibernate for use
      SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
      session = sessionFactory.openSession();
      
      Criteria criteria = session.createCriteria(employee.class).add(Restrictions.like("job", "%pr%"));
      criteria.setFetchMode("department", FetchMode.JOIN);
      List list = criteria.list();
      
      for (int i = 0; i < list.size(); i++) {
employee e = (employee)list.get(i);
//department d = (department)list.get(i);
System.out.println(e.getDepartmentId() + " " );//+ d.getName()

      }
      
      
    }catch(Exception e){
e.printStackTrace();
    }
 
   
    }
 
}
 

i am getting the following message:

 org.hibernate.MappingException: An association from the table employee refers to an unmapped class: int
at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:932)
at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:888)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1036)
at Criteria2.main(Criteria2.java:19)   

Where did i go wrong? What should i do? Kindly help.

Thanks,
Subhash
[ September 25, 2008: Message edited by: subhashchandra medhiassam ]

[Paul Sturrock]

<many-to-one name="departmentId" column="departmentId" not-null="true" />

You have elected not to use the class attribute in your many-to-one element which means the type at the other end of the association is determined by reflection. So my guess is your mapped employee class has a property of departmentId whose type is an int?

[subhashchandra medhiassam]

My employee class looks like the following:

public class employee {
 
    /**
     * @param args
     */
    private int employeeId;
    private String name;
    private String job;
    private int departmentId;
    
    public int getDepartmentId() {
        return departmentId;
    }
    public void setDepartmentId(int departmentId) {
        this.departmentId = departmentId;
    }
    public int getEmployeeId() {
        return employeeId;
    }
    public void setEmployeeId(int employeeId) {
        this.employeeId = employeeId;
    }
    public String getJob() {
        return job;
    }
    public void setJob(String job) {
        this.job = job;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    
    
}
    

I have a property departmentId of type int.

Kindly help.

Thanks,
Subhash

[Paul Sturrock]

If you read the documentation for many-to-one you will see that you can use the class attribute to define the type the association maps to. As I said above if you don't use this attribute Hibernate determines the type by reflection - based on the type of the property this association maps to. So, given the type of the property departmentId is int, Hibernate is trying to create an association to a mapped class called "int". There isn't one, so it fails.

[subhashchandra medhiassam]

Okay,I have changed my hbm.xml file so that it now looks like the following:

 <?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>
  <class name="employee" table="employee">
     <id name="employeeId" column="employeeId" type="int">
     <generator class="native"/>
     </id>
     <property name="name" column="name" type="string" /> 
     <property name="job" column="job" type="string" /> 
     <many-to-one name="departmentId" column="departmentId" class="department" not-null="true" />
   </class>  
 <class name="department" table="department">
     <id name="departmentId" column="departmentId" type="int">
     <generator class="native"/>
     </id>
     <property name="name" column="name" type="string" />  
 </class>
</hibernate-mapping>   

But when i run my program now, i am getting the following message:

  Hibernate: select this_.employeeId as employeeId0_, this_.name as name0_0_, this_.job as job0_0_, this_.departmentId as departme4_0_0_ from employee this_ where this_.job like ?
org.hibernate.PropertyAccessException: exception setting property value with CGLIB (set hibernate.cglib.use_reflection_optimizer=false for more info) setter of employee.setDepartmentId
at org.hibernate.tuple.PojoTuplizer.setPropertyValuesWithOptimizer(PojoTuplizer.java:197)
at org.hibernate.tuple.PojoTuplizer.setPropertyValues(PojoTuplizer.java:167)
at org.hibernate.persister.entity.BasicEntityPersister.setPropertyValues(BasicEntityPersister.java:2879)
at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:113)
at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:452)
at org.hibernate.loader.Loader.doQuery(Loader.java:406)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:203)
at org.hibernate.loader.Loader.doList(Loader.java:1499)
at org.hibernate.loader.Loader.list(Loader.java:1482)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1246)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)
at Criteria2.main(Criteria2.java:24)
Caused by: net.sf.cglib.beans.BulkBeanException: department$$EnhancerByCGLIB$$489ce592 cannot be cast to java.lang.Number
at employee$$BulkBeanByCGLIB$$65e516ae.setPropertyValues(<generated> :wink: 
at org.hibernate.tuple.PojoTuplizer.setPropertyValuesWithOptimizer(PojoTuplizer.java:194)
... 12 more
Caused by: java.lang.ClassCastException: department$$EnhancerByCGLIB$$489ce592 cannot be cast to java.lang.Number
... 14 more  

Kindly help.

Thanks,
Subhash

[Paul Sturrock]

Did you change the type of your departmentId property?

[subhashchandra medhiassam]

No, i didn't. It is still of type "int" in the classes and also in the mapping file.

[Paul Sturrock]

It is not of type int in the mapping file. You've (correctly) changed the class the association maps to to be of the type department.

 <many-to-one name="departmentId" column="departmentId" class="department" not-null="true" />

[subhashchandra medhiassam]

But you asked about the property departmentId.what should i do now?

Kindly help.

Thanks,
Subhash
[ September 25, 2008: Message edited by: subhashchandra medhiassam ]

[Paul Sturrock]

Yes, I did. And I posted the bit of your mapping file that maps the associated class department to this property. You need to fix your employee class so it correctly reflects the object model you have mapped in your mapping file.

[subhashchandra medhiassam]

Well, i changed my employee class and made the type of the property departmentId as department. When i run my program, it is executing properly.

Now, my question is that how do i retrieve the values from the list? The list would be having joined values from both the tables, employee and department.

Also, suppose i want to join the two tables in such a way that i keep the type of field departmentId in employee class as int only then what modifications should i bring in the mapping file?


Kindly help.
Thanks,
Subhash

[subhashchandra medhiassam]

Okay,
I am able to retrieve the values of the table department from the join of the two tables. Thanks a lot for the help, Paul.

But i have a question.Is it necessary that in a many-to-one association, to have a join of the two classes the class corresponding to the "many" portion of the relation should have a property of type of the class corresponding to the "one" type of the relation?

Kindly do let me know.

Thanks,
Subhash

[Paul Sturrock]

Now, my question is that how do i retrieve the values from the list? The list would be having joined values from both the tables, employee and department.

Its just a List - treat it as you would any List in Java.

Also, suppose i want to join the two tables in such a way that i keep the type of field departmentId in employee class as int only then what modifications should i bring in the mapping file?

Not sure I see a reason to do this, what are you trying to acheive?

[subhashchandra medhiassam]

I have changed the employee class's departmentId field's type to department.

Then should i also not change the table employee? what should be the type of the column "departmentId" of table employee??

I mean to say that departmentId column in employee table is having type int though in the class employee i have changed the type of property departmentId to department. Isn't it an error? Then how should the table employee be changed?

Kindly advise.

Thanks,
Subhash
[ September 25, 2008: Message edited by: subhashchandra medhiassam ]

[Paul Sturrock]

Then should i also not change the table employee? what should be the type of the column "departmentId" of table employee??

No. Your object model should not be influencing your ER model. This is one of the purposes of an ORM tool.

I mean to say that departmentId column in employee table is having type int though in the class employee i have changed the type of property departmentId to department. Isn't it an error?

No. This is one of the basic reasons why you would use Hibernate. As I have said a good few times before, I think you would really benefit from reading the Hibernate documentation. You need to understand what the tool you are using is for; a lot of the answers for your questions will follow from that.

[subhashchandra medhiassam]

My table employee looks like the following:

Column Type
-------------- -----------------
employeeId int
name varchar
job varchar
departmentId int


How should i modify the table?

Kindly help.

Thanks,
Subhash
[ September 25, 2008: Message edited by: subhashchandra medhiassam ]

[Paul Sturrock]

Like I said in my previous post, you shouldn't.

[subhashchandra medhiassam]

okay.

Now in my main program, i am retrieving the values like this:

 List list = criteria.list();
      
      
      for (int i = 0; i < list.size(); i++) {
employee e = (employee)list.get(i);
System.out.println(e.getJob());

      }   

The above code prints all the values of column job available in table employee.
To print the values of fields of the department table obtained through join, what i have to do is use a line of code like this:

  System.out.println(e.getDepartmentId().getName()); 

The above line prints out the values in the column name of table department.

Similarly, i can get the values of the departmentId of table department by using the following line of code:

System.out.println(e.getDepartmentId().getDepartmentId());

I can also get the values of table employee, obtained through join, by using the following statements:

System.out.println(e.getJob());
      System.out.println(e.getName());
      System.out.println(e.getEmployeeId());

It all works fine.

But when i try to get the departmentId of table employee by using the following statement

System.out.println(e.getDepartmentId());

, i get some garbage values. Why is it so?

Kindly help.

Thanks,
subhash
[ September 25, 2008: Message edited by: subhashchandra medhiassam ]

[Paul Sturrock]

But when i try to get the departmentId of table employee by using the following statement:

System.out.println(e.getDepartmentId());

i get some garbage values. Why is it so?

Your employee class has a property called departmentId which is of the type department. As with any type in Java, if you call:

System.out.println(myType);

the toString() method is called on the object "myType". Unless you have implemented a toString() method for your department class you will be using the one supplied by Object.

[subhashchandra medhiassam]

Thanks a lot, Paul. You have been extremely helpful. Your suggestions have helped me a lot. In the future if i need any help, i shall bother you again.

Thank you once again,
Subhash