JPA+Hibernate...Instead of single query, association executes multiple queries for join

Question

I have two class named Banneers and BannerGroup.

I have created bidirectional relationship. In my case, A BannerGroup can have many Banners and Many Banners belongs within a same BannerGroup.

Mysql tables are...

mysql> desc Banners;
+------------------+--------------+------+-----+---------+-----------------------------+
| Field            | Type         | Null | Key | Default | Extra                       |
+------------------+--------------+------+-----+---------+-----------------------------+
| banner_id        | int(11)      | NO   | PRI | NULL    | auto_increment              | 
| banner_name      | varchar(255) | YES  |     | NULL    |                             | 
| banner_group_id  | int(11)      | YES  | MUL | NULL    |                             | 
+------------------+--------------+------+-----+---------+-----------------------------+

mysql> desc Banner_Group;
+---------------------+--------------+------+-----+----------------+-----------------------------+
| Field               | Type         | Null | Key | Default        | Extra                       |
+---------------------+--------------+------+-----+----------------+-----------------------------+
| group_id            | int(11)      | NO   | PRI | NULL           | auto_increment              | 
| group_name          | varchar(255) | NO   | MUL | NULL           |                             | 
+---------------------+--------------+------+-----+----------------+-----------------------------+

My model class is like this...

Class Banners
{
    private Integer bannerId;
    private String bannerName;
    private BannerGroup bannerGroupDO;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name = "banner_id")
    public Integer getBannerId() {
        return bannerId;
    }

    public void setBannerId(Integer bannerId) {
        this.bannerId = bannerId;
    }

    @Column(name = "banner_name")
    public String getBannerName() {
        return bannerName;
    }

    public void setBannerName(String bannerName) {
        this.bannerName = bannerName;
    }

    @ManyToOne(fetch = FetchType.EAGER, optional=false)
    @JoinColumn(name = "banner_group_id",  nullable = false)
    public BannerGroup getAffiliateBannerGroup() {
        return bannerGroupDO;
    }

    public void setAffiliateBannerGroup(BannerGroup bannerGroupDO) {
        this.bannerGroupDO = bannerGroupDO;
    }
}

Class BannerGroup{

    private int group_id;
    private int group_name;
    private List<Banners> banners;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name = "group_id")
    public Integer getGroupId() {
        return groupId;
    }

    public void setGroupId(Integer groupId) {
        this.groupId = groupId;
    }

    @Column(name = "group_name")
    public String getGroupName() {
        return groupName;
    }

    public void setGroupName(String groupName) {
        this.groupName = groupName;
    }

    @OneToMany(mappedBy="bannerGroupDO", fetch = FetchType.LAZY)
    public List<Banners> getBanners() {
        return banners;
    }

    public void setBanners(List<Banners> banners) {
    this.banners = banners;
    }
}

Now, when i am trying to get Banners(which is eagerly loaded BannerGroup)

Query q = session.createQuery("from com.abc.xyz.model.Banners where bannerId = 123");

I got this...

Hibernate: select b.banner_id as banner1_294_, b.banner_name as banner2_294_ from Banners b where b.banner_id=?
Hibernate: select bg.group_id as group1_295_0_, bg.group_name as group9_295_0_ from Banner_Group bg where bg.group_id=?

What i want: Hibernate should execute single query instead of multiple(two) queries. I want that hibernate should fire a "inner join" query... something like...

select b.*, bg.* from Banners b INNER JOIN Banner_Group bg ON b.banner_group_id=bg.group_id;

My questions are...

1.) Why hibernate executes multiple queries instead of single query, eventhough i have specified association. ?

2.) What suould i suppose to do for this problem?

I do not want to execute JPQL/HQL. I mean to say, i want to get behaviour form association only. I just want a result by executing query like... from com.abc.xyz.model.Banners where bannerId = 123;

3.) I know that... Result of executing two queries individually and a single query is same. But i think this affects on perfomance of DB. or may be its a bruden for DB. Am i wrong?

After the answer of Radim Köhler... i have Edited my code in Banners.java ...(still problem is not solved)

@ManyToOne(fetch = FetchType.EAGER, optional=false)
    @JoinColumn(name = "banner_group_id",  nullable = false)
    @Fetch(FetchMode.JOIN)
    public AffiliateBannerGroup getAffiliateBannerGroup() {
        return affiliateBannerGroup;
    }

    public void setAffiliateBannerGroup(AffiliateBannerGroup affiliateBannerGroup) {
        this.affiliateBannerGroup = affiliateBannerGroup;
    }

score 1 · Answer 1 · edited May 23 '17 at 12:10

1

The mapping missing is the

@Fetch(FetchMode.JOIN)

see more here: JPA eager fetch does not join.

The EAGER setting, is instructing Hibernate in a different way: Do not wait to load Entity and its Reference. So, even if it is not used in the further processing, the reference is loaded. LAZY loading, is in most scenarios prefered and most appropriate way

edited May 23 '17 at 12:10

Community

1
1

answered Oct 26 '13 at 11:00

Radim Köhler

122,561
47
239
335

I have checked... i put @Fetch(FetchMode.JOIN) in my Banners class. (Because i am trying to load object of Banner class.) But same result... executing two individual queries... (I am checking this in mysql's general log.) – Manan Shah Oct 26 '13 at 11:11
upvote for you answer... because i am not aware of FetchMode. Your answer has given me a new direction. – Manan Shah Oct 26 '13 at 11:13
1

In general, I would strongly suggest, to use LAZY and fetch modes SELECT instead of JOIN... inside of your mapping. That will bring you advantage in scenarios, when you go for entity indirectly (via reference). When querying, (session.createCriteria) you have more freedom and more abilities how to effectively load the data. you can join even more tables and ask for only few columns (projections)... but the mapping itself, should be LAZY and not eager. I would say ;) Also, try to check batch-size setting – Radim Köhler Oct 26 '13 at 11:59
1

@MananShah Try to check this link: http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/queryhql.html#queryhql-joins. Your HQL should explicit join. Also here is nice explanation: http://stackoverflow.com/a/4695237/1679310 – Radim Köhler Oct 26 '13 at 12:20

JPA+Hibernate...Instead of single query, association executes multiple queries for join

1 Answers1