Hibernate OneToOne automatic join fetching (resolving n+1 problem)

Question

we have a n+1 select problem with Hibernate 3.3.

For simplicity's sake, I'll just do a short abstract example.

Suppose we have the following simple classes:

class MainEntity {
  @Id
  public Long id; //we have a table generator create this id

  @OneToOne ( mappedBy ="main" )
  public SubEntity subEntity;
}

class SubEntity {
 @Id
 @Column( name = "mainId" ) //note that this is the same column as the join column below
 public Long mainId; //in order to have the exact same id as the corresponding MainEntity

 @OneToOne ( fetch = FetchType.LAZY )
 @JoinColumn ( name = "mainId", insertable = false, updatable = false, nullable = false )
 public MainEntity main; //this is used for navigation and queries (" ... subentity.main = :x")
}

So as you can see SubEntity has a relation to MainEntity that is expressed by two properties, where the mainId property is the one responsible for managing the relation/foreign key.

This works quite well and perfectly fits our needs.

However, there's one problem with eagerly loading the SubEntity along with the MainEntity.

Suppose I have a query that returns a collection of MainEntity. With the current setup, Hibernate will issue n + 1 selects: the query itself + n selects for each SubEntity.

Of course I could add a join fetch to the query, but I'd rather like Hibernate to do that automatically. Thus I tried adding @Fetch( FetchMode.JOIN ), but that didn't do anything.

I would also have no problem using @Fetch( FetchMode.SUBSELECT ), which should reduce the select statements to 2 - the original query and a select for the sub entities (at least that's what happens on another property annotated with @CollectionOfElements and @Fetch( FetchMode.SUBSELECT )).

So the question is: how would I tell Hibernate to automatically join fetch or use a single select in order to eagerly load the sub entities? Am I missing something?

Thanks in advance,

Thomas

PS: One thing that might be a problem might be the mappedBy = "main" which doesn't reference the actual id column, but I can't change it to mappedBy = "id".

score 8 · Accepted Answer · answered Aug 11 '11 at 15:49

8

If you want to shared primary keys between MainEntity and SubEntity use PrimaryKeyJoinColumn and MapsId annotation.

By using PrimaryKeyJoinColumn the entity is loaded by joining the MainEntity table with the SubEntity table using the same primary key. It should resolve the n+1 problems.

The MapsId annotation ask Hibernate to copy the identifier from another associated entity in our example will copy the SubEntity.mainEntity.id to SubEntity.id.

@Entity
public class MainEntity {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "main_Id")
    private Long id;

    @OneToOne(cascade = CascadeType.ALL)
    @PrimaryKeyJoinColumn
    private SubEntity  subEntity ;
}


@Entity
public class SubEntity 
{
    @Id @Column(name="main_Id_FK") Long id;

    @MapsId 
    @OneToOne
    @JoinColumn(name = "main_Id_FK")    
    @PrimaryKeyJoinColumn
    private MainEntity mainEntity;        

}

Hibernate Reference Documentation:

PrimaryKeyJoinColumn
MapsId

answered Aug 11 '11 at 15:49

Joel Hudon

3,145
1
20
13

1

I wasn't aware of the `@MapsId`, thanks for that. I'll try it out. Even in the case the n+1 problem would not be solved, I'd still prefer that approach to our current one. Just a question: would that allow me to actually just set the id without having a reference to `MainEntity`? We have cases where missing `SubEntity` instances are created and we only have the id of the corresponding `MainEntity`. If possible, I'd like to rather not have to load `MainEntity` for just setting the reference. In other words: what would happen if `id` had a value and `mainEntity`was `null`? – Thomas Aug 12 '11 at 08:21
I just had a look at the `@MapsId` documentation you linked, and unfortunately it seems that this annotation was introduced with Hibernate 3.5 (and thus JPA 2). However, we're currently stuck with JBoss 4.2.3 (which doesn't support JPA 2) and thus can't use Hibernate 3.5 (we're working on migration to JBoss 6, but that'll take a while). – Thomas Aug 12 '11 at 12:21
1

@Thomas To Answer your first comment. How to create a `SubEntity` with only the Id of the `MainEntity`. You need to use the `EntityManager.getReference` to get a reference to `MainEntity` without loading it and set it to the `SubEntiy.main` relation without loading the Main. – Joel Hudon Aug 12 '11 at 15:11
1

Without `@MapsID` you will have to manually assign the `SubEntity.id` before saving it and set the `SubEntity.main` relation `insertable = false,updatable =false,nullable = false` like in your example. You can continue to use the `@PrimaryKeyJoinColumn@ on `MainEntity.subEntity` this should correct the select problems. – Joel Hudon Aug 12 '11 at 15:14
yes, I forgot about `EntityManager.getReference(...)` - thanks for reminding me. If I understand you correctly, I'd just have to use `@PrimaryKeyColumn` and leave the rest of the code unchanged, right? I'll try that and post the results. – Thomas Aug 15 '11 at 07:16
1

I tested now, but without real success. I still get the n+1 selects when using queries without fetch join. Adding `@PrimaryKeyJoinColumn` didn't work as didn't adding `insertable = false, updatable = false` to either `SubEntity.main` or `SubEntity.id`. – Thomas Aug 17 '11 at 11:52

score 1 · Answer 2 · answered Aug 11 '11 at 13:06

1

There are three options to avoid the questions n +1:

 Lot size

 subselect

 Make a LEFT JOIN in the query

Here FAQ1 Here FAQ2

answered Aug 11 '11 at 13:06

Nook

534
2
7
14

1

Both links lead me to the same page, any more specific links? Additionally: `Lot Size`: I guess you mean `Batch size` - doesn't work here, `subselect` - I tried that but as already mentioned, it didn't work, `left join` - I'd rather do a fetch join, but as stated, I'd like to not have to add it to each query. – Thomas Aug 11 '11 at 13:12

Hibernate OneToOne automatic join fetching (resolving n+1 problem)

2 Answers2