How to join multiple queryDSL tables
Solution 1
First of all, declare a custom extended base repository class for QueryDSL queries.
First the interface:
@NoRepositoryBean
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;
}
@Override
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.
@Configuration
@EnableJpaRepositories(basePackageClasses = ..., repositoryBaseClass = ExtendedQueryDslJpaRepositoryImpl.class)
Your repositories then should extend from the new interface (which of course extends JpaRepository):
@Repository
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();
predicate.and(accountProfile.nickname.eq(nickname));
// select r from community r join r.account.profile a where a.nickname = :nickname
query.from(community)
.join(community.account, account)
.join(account.accountProfile, accountProfile)
.where(predicate);
repository.findAll(query, pageable);
Hope that helps.
Solution 2
I found one solution as
QEntity qEntity1 = new QEntity("qEntity1");
QEntity qEntity2 = new QEntity("qEntity2");
so while querying you can use
new JPAQueryFactory(entityManager).from(qSampleBO)
.innerJoin(qEntity1).on(qEntity1.id.eq(qSampleBO.address.id))
.innerJoin(qEntity2).on(qEntity2.id.eq(qSampleBO.secondary_address.id))
...
Admin
Updated on July 18, 2022Comments
-
Admin almost 2 years
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
Account.java
@Entity @Table(name="account", uniqueConstraints={ @UniqueConstraint(columnNames="account_seq"), @UniqueConstraint(columnNames="email") }) @DynamicInsert @DynamicUpdate @Data @EqualsAndHashCode @ToString(includeFieldNames=true) @RequiredArgsConstructor(staticName="of") @NoArgsConstructor public class Account implements Serializable{ private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy=GenerationType.IDENTITY) @Column(name="account_seq", nullable=false, unique=true) private Integer accountId; @Column(name="email", nullable=false, unique=true) @NonNull private String email; @NonNull private String password; @OneToOne(cascade=CascadeType.ALL, mappedBy="account") private AccountProfile profile; @OneToOne(cascade=CascadeType.ALL, mappedBy="account") private AccountSecurity security; }
AccountProfile.java
@Entity @Table(name="account_profile", uniqueConstraints={ @UniqueConstraint(columnNames={"account_seq"}), @UniqueConstraint(columnNames={"nickname"}) }) @DynamicInsert @DynamicUpdate @Data @EqualsAndHashCode @ToString(includeFieldNames=true) @RequiredArgsConstructor(staticName="of") @NoArgsConstructor public class AccountProfile implements Serializable{ private static final long serialVersionUID = 1L; @Id @OneToOne(cascade=CascadeType.ALL) @JoinColumn(name="account_seq", referencedColumnName="account_seq") private Account account; @Column(name="nickname", nullable=false) @NonNull private String nickname; }
Community.java
@Entity @Table(name="community", uniqueConstraints = { @UniqueConstraint(columnNames="article_seq") }) @DynamicInsert @DynamicUpdate @Data @NoArgsConstructor @EqualsAndHashCode @ToString(includeFieldNames=true) public class Community { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) @Column(name="article_seq", nullable=false, unique=true) private Long articleId; @ManyToOne(cascade=CascadeType.ALL) @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; @Temporal(TemporalType.TIMESTAMP) @Column(name="reg_dt") private Date date; @Column(name="read_cnt", nullable=false) private int readCount; @Column(name="attach_url") private String attachUrl; @Column(name="attach_filename") private String attachFileName; @OneToMany(cascade=CascadeType.ALL, mappedBy="article") private Set<CommunityReply> replies; }
EDIT: PROBLEM SOLVED
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.
@PersistenceContext 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 .from(qCommunity) .innerJoin(qCommunity.account ,qAccount) .innerJoin(qAccount.profile, qAccountProfile) .where(qAccountProfile.nickname.like("%"+keyword+"%")) .orderBy(qCommunity.articleId.desc()) .offset(offset) .limit(limit) .fetch(); }