QueryDSL @OneToOne Join-FetchMode with Hibernate

Question

Suppose we have a simple entity "Customer" which has a OneToOne relationship to an entity "Address". The foreign key is on the address side.

@Entity
public class Customer extends EntityBase {
    @Column(name = "name", nullable = true)
    private String name;

    @OneToOne(mappedBy = "customer")
    private Address address;

    // getter, setter, ...
}

@Entity
public class Address extends EntityBase {
    @OneToOne(optional = false)
    private Customer customer;

    @Column(nullable = true)
    private String street;
    @Column(nullable = true)
    private String zip;
    @Column(nullable = true)
    private String city;

    // getter, setter, ...
}

If you now load all Customer entities using hibernate and print the resulting queries to the console, you can see that Hibernate internally fires only a single query.

session.createCriteria(Customer.class).list();

What Hibernate does:

select
    this_.id as id1_1_1_,
    this_.name as name2_1_1_,
    address2_.id as id1_0_0_,
    address2_.city as city2_0_0_,
    address2_.customer_id as customer5_0_0_,
    address2_.street as street3_0_0_,
    address2_.zip as zip4_0_0_ 
from
    Customer this_ 
left outer join
    Address address2_ 
        on this_.id=address2_.customer_id

If you load the Customer entity with QueryDSL it will run one count query (what is expected and okay), one select query for the Customer entity and one query for each customer in the resultset. This means, if I want to load 1000 customers it will run 1002 SQL queries. This is a lot of network traffic and slows down the application.

new HibernateQuery<Customer>(session).from(QCustomer.customer).fetchResults();

What Hibernate with QueryDSL does:

select
    count(customer0_.id) as col_0_0_
from
    Customer customer0_

select
    customer0_.id as id1_1_,
    customer0_.name as name2_1_
from
    Customer customer0_

select
    address0_.id as id1_0_1_,
    address0_.city as city2_0_1_,
    address0_.customer_id as customer5_0_1_,
    address0_.street as street3_0_1_,
    address0_.zip as zip4_0_1_,
    customer1_.id as id1_1_0_,
    customer1_.name as name2_1_0_
from
    Address address0_
inner join
    Customer customer1_
        on address0_.customer_id=customer1_.id
where
    address0_.customer_id=?

Question:
Is it possible to set something like a global FetchMode for QueryDSL queries. In Hibernate you can specify this with @Fetch(FetchMode.JOIN) but unfortunately this is ignored by QueryDSL.

So my destination is to load 1000 customers with QueryDSL and only run 2 queries (count + select).

I already know that there is a way to specify something like this:

new HibernateQuery<Customer>(session)
    .from(QCustomer.customer)
    .leftJoin(QCustomer.customer.address).fetchJoin()
    .fetchResults();

But this is error-prone because you have to specify it in every query and I don't want to declare every join by myself. QueryDSL already does it automatically when using predicates:

new HibernateQuery<Customer>(session)
    .from(QCustomer.customer)
    .where(QCustomer.customer.address.street.in("Musterstraße 12"))
    .fetchResults();

So I want to use the above expression to load my customers, but I don't want to fire thousands of requests to my database and I also don't want to declare every join by myself. Is this possible?

I pushed an example project here: https://github.com/MatWein/testproject

score 0 · Answer 1 · answered Jan 14 '19 at 08:16

0

Once I have the same problem. But i am working with Hibernate JPA criteria Query. If you want to get your result in one Query then one way Is to use

@OneToOne(fetch=FetchType.EAGER)
@JoinColumn(name = "address_id", insertable = false, updatable = false, referencedColumnName = "id")
private Address address;

Or i have a solution with Criteria Query. May be it helps you to convert it into DSL.

create a root of Customer class.

Root<Customer> root = . . .
Join<Customer, Address> join = (Join<Customer, Address>)root.fetch(Customer_.address);

for reference see my Question

answered Jan 14 '19 at 08:16

Khalid Shah

3,132
3
20
39

The problem here is the combination of @OneToOne with mapped by on Address field of the Customer. We already have the "EAGER" value set (it is the default value of the annotation). When executing a hibernate criteria or loading by ID, the left-join is automatically added (see the initial question above). But when loading via QueryDSL, it executes a single query to load the Customer than N queries to load the Addresses. The goal is to configure QueryDSL to provide this information automatically. – Nathan Jan 14 '19 at 08:41

QueryDSL @OneToOne Join-FetchMode with Hibernate

1 Answers1