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();
}