Hibernate Criteria on Multiple Tables

Hibernate Criteria JOIN API allows users to perform join operation. Suppose you have two tables Club and Team. The relational model is shown below in Figure 25.1

Figure 25.1 Figure 25.1

Club table contains data as shown in Figure 25.2

Figure 25.2 Figure 25.2

Team table contains data as shown in Figure 25.3

Figure 25.3 Figure 25.3

Suppose you have to perform a operation like

select club.clubid, club.name, team.clubid, team.teamid, team.teamname 
from Club club, Team team 
where club.clubid=team.clubid;

Then you can write this statement using Criteria in a very simple way

Criteria criteria = session.createCriteria(Club.class);
    criteria.setFetchMode("Team", FetchMode.JOIN);
    List list = criteria.list();

Now Suppose you have to apply restriction on join operation such as

select club.clubid, club.name, team.clubid, team.teamid, team.teamname 
from Club club, Team team 
where club.clubid=team.clubid and
club.name='Arsenal'
and team.teamname='Team A'; 

Then you can write the above statement in criteria as

Criteria criteria = session.createCriteria(Club.class,"club")
    .createAlias("club.team","team")
    .add(Restrictions.eq("club.name", "Arsenal"))
    .add(Restrictions.eq("team.teamname", "Team A"));
    List list = criteria.list();


Sql Script for Sample Code given below


Use the following Sql Script for creating table and inserting record.

CREATE TABLE club (
  clubId INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  PRIMARY KEY (ClubId)
);
   
CREATE TABLE  team (
  teamid INT(10) UNSIGNED NOT NULL,
  clubid INT(10) UNSIGNED NOT NULL,
  teamname VARCHAR(10) NOT NULL,
  PRIMARY KEY (teamid, clubid),
  FOREIGN KEY (clubid) REFERENCES club (clubid));

insert into club values(1,'Arsenal');
insert into team values(1,1,'Team A');
You can download the source code of this example here.

9 Responses to Hibernate Criteria on Multiple Tables

  1. Thiago Sacramento March 24, 2013 at 12:10 pm #

    Very simple example. Thanks!

  2. Lão Dương May 20, 2013 at 10:31 am #

    please help me.
    i don’t know type of the list when call :
    List list = criteria.list();
    and so how to access the elements ?
    my english is not good . perhaps i can’t not express exactly what i want to ask
    any way , thank for this post

    • Mukesh May 31, 2013 at 10:25 am #

      simply use the iterator to access the element.
      see example in details below:

      Iterator itr =list.iterator();
      while(itr.hasNext())
      {
      Object[] rows = (Object[]) itr.next();
      sop( rows[0]);
      sop( rows[1]); // upto no of select column
      }

  3. June 11, 2013 at 6:35 am #

    very helpful for me
    thankyou

  4. srinivas July 26, 2013 at 9:41 am #

    Please explain what this method does exactly?

    .createAlias(“club.team”,”team”)

  5. sreekanth August 1, 2013 at 10:18 am #

    this is my hql query if it possible to write Hibernate criteria. can you please help how can i write criteria

    String hql=”from AAAA where vdID in”+
    “( select vdid from BBB where vdid NOT in”+
    “(select vdid from BBB where loanid in”+
    “(select loanid from CCC where isActive=1) and loanid not IN”+
    “(select loanid from DDD where paidamount != 0)))”;

  6. Muthu September 16, 2013 at 11:03 am #

    Excellent tutorial. Helped a lot.

  7. Karmadip September 28, 2013 at 11:05 am #

    in join it is mandatory to have relation between both tables??
    and i found class cast exception…

  8. S October 11, 2013 at 4:57 pm #

    Thanks for this – it was tough to find how to perform “..where club.clubid=team.clubid and club.name=’Arsenal’ and team.teamname=’Team A’; “

Leave a Reply