Spring data jpa querydsl projection with joins

Question

I'd like to optimize a queryDSL + Spring data query. Currently I am using a BooleanBuilder as the predicate, which would be fine, but it joins too many tables. I don't need all the columns from the tables and I don't need some of the tables at all. I believe using a projection would reduce the number of tables joined.

I tried with using a Projections.bean() and also with extending MappingProjection, but both approaches result in not using joins but selecting from multiple tables which results in less rows than what's needed.

My data structure consists of a Booking entity and some related entites like User, so looks something like the following:

@Entity
public class Booking {

    @ManyToOne
    @JoinColumn(name = "userId", nullable = false)
    private User endUser;
}

@Entity
public class User {

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "endUser", fetch = FetchType.LAZY)
    private List<Booking> bookings;
}

I implemented a custom queryDSL projection repository as described here: Spring Data JPA and Querydsl to fetch subset of columns using bean/constructor projection

I'm trying a projection like the following:

Projections.bean(Booking.class,
                 booking.uuid,
                 Projections.bean(User.class,
                                  booking.endUser.uuid
                 ).as(booking.endUser.getMetadata().getName()
);

The sql generated by the current solution looks something like this:

select (...)
from booking booking0_, 
     user user12_
where booking0_.user_id=user12_.id

So, how can I make QueryDSL join the tables instead of selecting from all of them? Am I on the right path to try to optimize the query? Does the projection make sense?

Are you sure that a join would be any faster than the where clauses? See https://stackoverflow.com/questions/1129923/is-a-join-faster-than-a-where or https://stackoverflow.com/questions/2509987/which-sql-query-is-faster-filter-on-join-criteria-or-where-clause — Ryan Dawson, Jul 24 '18 at 13:05
@RyanDawson thank you for your comment. My problem of the where clause is not speed, but the results. They were wrong: fewer rows. — vargen_, Jul 24 '18 at 17:18

score 1 · Accepted Answer · answered Jul 24 '18 at 09:23

1

I ended up creating a DB view, making an Entity for it and then querying that with querydsl. This is really simple, straightforward and the performance is good too.

This is probably where ORMs are just not capable enough.

answered Jul 24 '18 at 09:23

vargen_

2,590
3
24
30

Spring data jpa querydsl projection with joins

1 Answers1