Home How to join multiple queryDSL tables
Reply: 1

How to join multiple queryDSL tables

PLAYMAKER Published in 2017-12-07 18:03:52Z

I have some tables and I want to get result using queryDSL join, but haven't found any examples on multiple joins using queryDSL.

I have these tables:

  • Account table: accountId (PK) | email | password

  • account_profile table: accountId (PK)(fk to account) | nickname

  • Community table: articleId (PK) | accountId (fk to account) | title | content

Now I want below JPQL to be queryDSL code

select r from community r join r.account.profile a where a.nickname = :nickname

I have entity metamodels - QAccount, QAccountProfile, QCommunity

Additionally, I have to get the result with pagination, so the query should be called with pageable object.

Here is my work that doesn't work yet.

JPAQuery</*What generic type expected?*/> query = new JPAQuery</*???*/>(entityManager);
Predicate predicate = query.from(QCommunity.community).join(/*join directly accountProfile? or account? is it QEntity or real entity?*/);

// where should I place nickname matching condition ?


list = (repository.findAll(predicate, pageable)).getContent();

Where should I place the nickname matching condition?

EDIT: Appended entity information


@Table(name="account", uniqueConstraints={
public class Account implements Serializable{

    private static final long serialVersionUID = 1L;

    @Column(name="account_seq", nullable=false, unique=true)
    private Integer accountId;

    @Column(name="email", nullable=false, unique=true)
    private String email;

    private String password;

    @OneToOne(cascade=CascadeType.ALL, mappedBy="account")
    private AccountProfile profile;

    @OneToOne(cascade=CascadeType.ALL, mappedBy="account")
    private AccountSecurity security;


@Table(name="account_profile", uniqueConstraints={
public class AccountProfile implements Serializable{

    private static final long serialVersionUID = 1L;

    @JoinColumn(name="account_seq", referencedColumnName="account_seq")
    private Account account;

    @Column(name="nickname", nullable=false)
    private String nickname;



@Table(name="community", uniqueConstraints = {
public class Community {

    @Column(name="article_seq", nullable=false, unique=true)
    private Long articleId;

    @JoinColumn(name="account_seq", referencedColumnName="account_seq")
    private Account account;

    @Column(name="title", nullable=false)
    private String title;

    @Column(name="content", nullable=false)
    private String content;

    private Date date;

    @Column(name="read_cnt", nullable=false)
    private int readCount;

    private String attachUrl;

    private String attachFileName;

    @OneToMany(cascade=CascadeType.ALL, mappedBy="article")
    private Set<CommunityReply> replies;


To help others who is facing the problem like me, I am gonna post my working code. the code is searching any community articles with matching specific nickname.

    private EntityManager entityManager;

    private List<Community> getList(int pageNo, String keyword, int rowsOnPage){

        int offset = (pageNo -1) * rowsOnPage;
        int limit = rowsOnPage;

        JPAQuery<Community> query = new JPAQuery<Community>(entityManager);

        QCommunity qCommunity = QCommunity.community;
        QAccount qAccount = QAccount.account;
        QAccountProfile qAccountProfile = QAccountProfile.accountProfile;

        return query
            .innerJoin(qCommunity.account ,qAccount)
            .innerJoin(qAccount.profile, qAccountProfile)
lzagkaretos Reply to 2017-12-08 08:00:33Z

First of all, declare a custom extended base repository class for QueryDSL queries.

First the interface:

public interface ExtendedQueryDslJpaRepository<T, ID extends Serializable> 
        extends JpaRepository<T, ID>, QueryDslPredicateExecutor<T> {

    <T1> Page<T1> findAll(JPQLQuery jpqlQuery, Pageable pageable);

And then the implementation:

public class ExtendedQueryDslJpaRepositoryImpl<T, ID extends Serializable>
        extends QueryDslJpaRepository<T, ID> implements ExtendedQueryDslJpaRepository<T, ID> {

    private static final EntityPathResolver DEFAULT_ENTITY_PATH_RESOLVER = SimpleEntityPathResolver.INSTANCE;

    private final EntityPath<T> path;
    private final PathBuilder<T> builder;
    private final Querydsl querydsl;

    private EntityManager entityManager;

    public ExtendedQueryDslJpaRepositoryImpl(JpaEntityInformation<T, ID> entityInformation, EntityManager entityManager) {
        this(entityInformation, entityManager, DEFAULT_ENTITY_PATH_RESOLVER);

    public ExtendedQueryDslJpaRepositoryImpl(JpaEntityInformation<T, ID> entityInformation, 
           EntityManager entityManager, EntityPathResolver resolver) {

        super(entityInformation, entityManager);
        this.path = resolver.createPath(entityInformation.getJavaType());
        this.builder = new PathBuilder(this.path.getType(), this.path.getMetadata());
        this.querydsl = new Querydsl(entityManager, this.builder);
        this.entityManager = entityManager;

    public <T1> Page<T1> findAll(JPQLQuery jpqlQuery, Pageable pageable) {

        // Count query
        final JPQLQuery<?> countQuery = jpqlQuery;

        // Apply pagination
        JPQLQuery<T1> query = querydsl.applyPagination(pageable, jpqlQuery);

        // Run query
        return PageableExecutionUtils.getPage(query.fetch(), pageable, countQuery::fetchCount);

Define the new class as base for base and repositories in a @Configuration class.

@EnableJpaRepositories(basePackageClasses = ..., repositoryBaseClass = ExtendedQueryDslJpaRepositoryImpl.class)

Your repositories then should extend from the new interface (which of course extends JpaRepository):

public interface CommunityRepository extends ExtendedQueryDslJpaRepository<Community, Long> {

Then, you can try the following code:

String nickname = "nick";

QAccount account = QAccount.account;
QAccountProfile accountProfile = QAccountProfile.accountProfile;
QCommunity community = QCommunity.community;

JPQLQuery query = new JPAQuery(entityManager);

BooleanBuilder predicate = new BooleanBuilder();

// select r from community r join r.account.profile a where a.nickname = :nickname
     .join(community.account, account)
     .join(account.accountProfile, accountProfile)

repository.findAll(query, pageable);

Hope that helps.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.360898 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO