Hibernate N+1 query issue when fetching @OneToOne associations with JPA Criteria and @LazyToOne

#1

I have an Entity A which contains a @OneToOne relation to the Entity B and Entity C. Entity B and Entity C have, in turn, a @OneToOne relation to Entity B1 and Entity C1:

 A
 | 
 |--B  @OneToOne
    |
    |--B1   @OneToOne
 |--C
    |
    |--C1   @OneToOne

I need to select all data of Entity A with some fields of Entity B and Entity C. Fields of Entity B1 and C1 have to be ignored.

Hibernate, by default, consider all *ToOne relations, as EAGER and the only way to configure *ToOne with Lazy loading is to use the annotation @LazyToOne(LazyToOneOption.NO_PROXY) in conjunction with hibernate compile instrumentation.

So, to execute only a single query and load only wished relations:

  1. I Configured all the @OneToOne relation with @LazyToOne(LazyToOneOption.NO_PROXY)
  2. I used Criteria API to retrieve A entities, fetching B and C relation (the wished reltions).
// The 'A' Entity (that I want to load with criteria)
@Entity
@Table(name = "users", schema = "test_db")   
public class UserEntity
{
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name = "USER_ID")
    private Integer userId;

    @Column(name = "NAME")
    private String name;

    @Column(name = "SURNAME")
    private String surname;

    @Column(name = "EMAIL")
    private String email;

    // The B Entity Relation (wished)
    @OneToOne
    @JoinColumn(name = "BADGE_ID")
    @LazyToOne(LazyToOneOption.NO_PROXY)
    private BadgeEntity badge;

    // The C Entity Relation (wished)
    @OneToOne
    @JoinColumn(name = "ADDRESS_ID")
    @LazyToOne(LazyToOneOption.NO_PROXY)
    private AddressEntity address;

    // Getters and Setters
}

// The B Entity (relation that I eventually want to load fetching LEFT on father) 
@Entity
@Table(name = "badges", schema = "test_db")
public class BadgeEntity
{    

   @Id
   @GeneratedValue(strategy= GenerationType.IDENTITY)
   @Column(name = "BADGE_ID", nullable = false)
   private Integer badgeId;

   @Column(name = "CODE", nullable = false, length = 10)
   private String code;

   @Column(name = "DESCRIPTION", nullable = false, length = 255)
   private String description;

   // The B1 Entity Relation (not wished)
   @OneToOne
   @JoinColumn(name = "BADGE_TYPE_ID")
   @LazyToOne(LazyToOneOption.NO_PROXY)
   private BadgeTypeEntity badgeType;

   // Getters and Setters 
}

// The C Entity (relation that I eventually want to load fetching LEFT on father)   
@Entity
@Table(name = "addresses", schema = "test_db")
public class AddressEntity
{

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name = "ADDRESS_ID")
    private Integer addressId;

    @Column(name = "DESCRIPTION")
    private String description;

    @Column(name = "CITY")
    private String city;

    @Column(name = "STATE")
    private String state;

    // The C1 Entity Relation (not wished)
    @OneToOne
    @JoinColumn(name = "ADDRESS_TYPE_ID")
    @LazyToOne(LazyToOneOption.NO_PROXY)
    private AddressTypeEntity addressType;

     // Getters and Setters 
 }

// The B1 Entity (relation that I don't want to load) 
@Entity
@Table(name = "badge_types", schema = "test_db")
public class BadgeTypeEntity
{

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name = "BADGE_TYPE_ID")
    private Integer badgeTypeId;

    @Column(name = "CODE")
    private String code; 

    // Getters and Setters 
}    

// The C1 Entity (relation that I don't want to load) 
@Entity
@Table(name = "address_types", schema = "test_db")
public class AddressTypeEntity {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name = "ADDRESS_TYPE_ID")
    private Integer addressTypeId;

    @Column(name = "CODE")
    private String code;

    // Getters and Setters 
}    

/**
*
*   Load all UserEntities executing only single query,
*   fetching RIGHT on BadgeEntity and AddressEntity (@OneToOne relations), 
*   avoiding load of nested BadgeTypeEntity and AddressTypeEntity
*
**/
@Override
public List<UserEntity> getUserByFilter()
{
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<UserEntity> cq = cb.createQuery(UserEntity.class);
    Root<UserEntity> user = cq.from(UserEntity.class);

    // Fetch User --> Address (wished relation, without nested @OneToOne relations)
    Fetch<UserEntity, AddressEntity> addressFetch = user.fetch(UserEntity_.address, JoinType.LEFT);
    // Fetch User --> Badge (wished relation, without nested @OneToOne relations) 
    Fetch<UserEntity, BadgeEntity> badgeFetch = user.fetch(UserEntity_.badge, JoinType.LEFT);

    List<Predicate> predicates = new ArrayList<Predicate>();

    cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()]))).distinct(true);
    TypedQuery<UserEntity> query = em.createQuery(cq);
    List<UserEntity> result = query.getResultList();

    return result;
}


@Test
@Transactional
public void getUserByFilter_Test()
{
    try
    {
        UserEntity eUser = userCustomRepository.getUserByFilter().get(0);
        // At this point, one single query is executed
        System.out.println("\n********************************");
        System.out.println("Name    : " + eUser.getName());
        System.out.println("Surname : " + eUser.getSurname());
        System.out.println("Email   : " + eUser.getEmail());
        System.out.println("********************************\n");
        // Calling get on alrready fetched relation, cause execution of an extra query!
        BadgeEntity eBadge = eUser.getBadge();
        System.out.println("Badge Code : " + eBadge.getCode());
        Assert.assertNotNull(eUser);
    }
    catch (Exception ex)
    {
        ex.printStackTrace();
        Assert.fail(ex.getMessage());
    }
}    

Executing the query method, I’m expecting the execution of a single query,on User table, fetching right on Badge and Address tables.

This query, is actually executed, but, if I try to access a relation of UserEntity (badge, for example), a strange extra query is executed:

Hibernate: 
    select
        distinct userentity0_.user_id as user_id1_6_0_,
    addressent1_.address_id as address_1_1_1_,
    badgeentit2_.badge_id as badge_id1_3_2_,
    userentity0_.email as email2_6_0_,
    userentity0_.name as name3_6_0_,
    userentity0_.surname as surname4_6_0_,
    addressent1_.city as city2_1_1_,
    addressent1_.description as descript3_1_1_,
    addressent1_.state as state4_1_1_,
    badgeentit2_.code as code2_3_2_,
    badgeentit2_.description as descript3_3_2_ 
from
    users userentity0_ 
left outer join
    addresses addressent1_ 
        on userentity0_.address_id=addressent1_.address_id 
left outer join
    badges badgeentit2_ 
        on userentity0_.badge_id=badgeentit2_.badge_id 
where
    1=1

Name : Jhon Surname : Doe Email : jhon.doe@gmail.com

-- STRANGE EXTRA QUERY I DON'T KNOW WHY EXECUTED  
Hibernate: 
    select
        userentity_.address_id as address_5_6_,
        userentity_.badge_id as badge_id6_6_ 
    from
        users userentity_ 
    where
        userentity_.user_id=?
0 Likes

#2

Hibernate, by default, consider all *ToOne relations, as EAGER and the only way to configure *ToOne with Lazy loading is to use the annotation @LazyToOne(LazyToOneOption.NO_PROXY) in conjunction with hibernate compile instrumentation.

That’s only true for parent-side @OneToOne(mappedBy="parent"). Client-side @OneToOne and @ManyToOne can be load lazily by setting the fetch attribute to FetchType.LAZY.

So, your associations become:

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "BADGE_ID")
private BadgeEntity badge;

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "ADDRESS_ID")
private AddressEntity address;

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "BADGE_TYPE_ID")
private BadgeTypeEntity badgeType;

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "ADDRESS_TYPE_ID")
private AddressTypeEntity addressType;

Try it like that.

0 Likes

#3

Yes, it works!

But I have to disable bytecode enhancement.

With this enabled, hibernate seems to ignore LEFT FETCH configured into criteria.
So, for every record, are executed several query to get childs.

Unfortunately, disabling bytecode enhancement, i can’t use anymore:

@Basic(fetch=LAZY)
@Lob
@Column(name="CONTENT")
private content;

But this is a minor issue.

Thank you!

0 Likes

#4

No, it does not. The fetch clause in JPQL and Criteria API override the mapping fetch strategy.

Unfortunately, disabling bytecode enhancement, I can’t use anymore:

You should enable bytecode enhancement again to benefit from attribute lazy loading. It should not interfere with lazy associations. If it does, it’s an issue and you need to replicate it and open a Jira issue.

0 Likes

#5

I executed many test and I confirm that, with bytecode enhancement, it doesn’t work as expected
Disabling enhancement, it works rightly.

I created a little test-case with spring boot to replicate the problem.
I will open an issue on JIRA.

0 Likes

#6

Jira Issue created:
https://hibernate.atlassian.net/browse/HHH-13203
Thank you for your help!

0 Likes