I used Spring Boot and QueryDSL.
When called findAllByWriterGroupByClient method in ClientMemoRepositoryImpl.java, [generated query 1] generated once and [generated query 2] generated several times.
Additionally, when read result of this query as Tuple in ClientMemoServiceImpl.java, [generated query 3] is generated many times.
- ClientMemoRepositoryImpl.java
@Override
public List<Tuple> findAllByWriterGroupByClient(String searchKeyword, Long writerId, boolean hasAdminRole) {
QClientMemo qClientMemo1 = new QClientMemo("cm1");
QClientMemo qClientMemo2 = new QClientMemo("cm2");
JPAQuery<Tuple> memoDtoJPAQuery = qf.select(
JPAExpressions.selectFrom(qClientMemo1)
.where(qClientMemo1.clientId.eq(qClientMemo.clientId).and(
qClientMemo1.createdDate.eq(
JPAExpressions
.select(qClientMemo2.createdDate.max())
.from(qClientMemo2)
.where(qClientMemo2.clientId.eq(qClientMemo.clientId))
)
)
),
new CaseBuilder()
.when(qClientMemo.createdDate.gt(LocalDateTime.now().minusDays(7)))
.then(1)
.otherwise(0).sum()
)
.from(qClientMemo);
if ((!hasAdminRole) && writerId != null) {
memoDtoJPAQuery = memoDtoJPAQuery.where(qClientMemo.writer.id.eq(writerId));
}
if (searchKeyword != null)
memoDtoJPAQuery = memoDtoJPAQuery.where(
qClientMemo.title.contains(searchKeyword)
.or(qClientMemo.content.contains(searchKeyword))
.or(qClientMemo.clientId.clientName.contains(searchKeyword))
.or(qClientMemo.writer.name.contains(searchKeyword))
);
return memoDtoJPAQuery
.groupBy(qClientMemo.clientId)
.orderBy(OrderByNull.DEFAULT)
.fetch();
}
- generated query 1
select
(select
clientmemo1_.id
from
client_memo clientmemo1_
where
clientmemo1_.client_id=clientmemo0_.client_id
and clientmemo1_.created_date=(
select
max(clientmemo2_.created_date)
from
client_memo clientmemo2_
where
clientmemo2_.client_id=clientmemo0_.client_id
)
) as col_0_0_, sum(case
when clientmemo0_.created_date>? then ?
else 0
end) as col_1_0_
from
client_memo clientmemo0_
group by
clientmemo0_.client_id
order by
null asc
- generated query 2
select
[all fields of client_memo entity]
from
client_memo clientmemo0_
where
clientmemo0_.id=?
- generated query 3
select
[all fields of client entity]
from
client client0_
where
client0_.id=?
- ClientMemoServiceImpl.java
List<Tuple> clientMemos = clientMemoRepository.findAllByWriterGroupByClient(
readClientMemoDto.getSearchKeyword(),
readClientMemoDto.getUserId(),
hasAdminRole
);
clientMemos.forEach(clientMemo -> {
Map<String, Object> result = new HashMap<>();
Integer newCnt = clientMemo.get(1, Integer.class);
if (newCnt != null) {
result.put("newMemoNum", newCnt);
}
MemoDto memoDto = new MemoDto();
ClientMemo memo = clientMemo.get(0, ClientMemo.class);
if (memo != null) {
memoDto.ofClientMemo(memo);
result.put("memoDetail", memoDto);
}
results.add(result);
});
- ClientMemo.java
@Entity
@Table(name = "client_memo")
@Getter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@DynamicInsert
public class ClientMemo {
@JsonIgnore
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(name = "title", nullable = false)
private String title;
@Lob
@Column(name = "content")
private String content;
@JsonIgnore
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="client_id")
private Client clientId;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="writer")
private User writer;
@Column(name = "created_date")
private LocalDateTime createdDate;
@Column(name = "updated_date")
private LocalDateTime updatedDate;
@Column(name = "is_admin")
private boolean isAdmin;
}
- Client.java
@Entity
@Table(name = "client")
@Getter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@DynamicInsert
public class Client {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(name = "client_name", nullable = false)
private String clientName;
@Column(name = "client_phone_num", nullable = false)
private String clientPhoneNum;
@Column(name = "service_start_time")
private LocalDateTime serviceStartTime;
@Column(name = "service_end_time")
private LocalDateTime serviceEndTime;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "media_id")
private Media media;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "person_charge_id")
private User personCharge;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "normal_memo")
private ClientMemo normalMemo;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "admin_memo")
private ClientMemo adminMemo;
@Column(name = "status", columnDefinition = "varchar(20) default 'UNCONTRACTED'")
@Enumerated(EnumType.STRING)
private ClientStatus status;
@Column(name = "is_deleted", nullable = false)
private boolean isDeleted;
}
All FetchType of Data Relationship are FetchType.LAZY.
I don't understand why occur this problem and why some people say that better using FetchType.LAZY than FetchType.EAGER.
Do I understand QueryDSL or SpringBoot correctly?
Thanks
