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.

17 Responses to Hibernate Criteria on Multiple Tables