@Repository
public class UserRepositorySupport extends QuerydslRepositorySupport {
private final JPAQueryFactory jpaQueryFactory;
public UserRepositorySupport(JPAQueryFactory jpaQueryFactory) {
super(User.class);
this.jpaQueryFactory = jpaQueryFactory;
}
}
public interface UserRepositoryCustom {
}
@RequiredArgsConstructor
public class UserRepositoryImpl implements UserRepositoryCustom{
private final JPAQueryFactory jpaQueryFactory;
}
@RequiredArgsConstructor
@Repository
public class UserQueryRepository {
private final JPAQueryFactory jpaQueryFactory;
}
import com.example.demo.business.user.entity.QUser;
// 위처럼 import 되는데 아래처럼 변경하면 QUser.user 를 user로 사용이 가능함
import static com.example.demo.business.user.entity.QUser.user;
public List<User> findByName(String name) {
return jpaQueryFactory.selectFrom(user)
.where(user.name.eq(name))
.fetch();
}
// left, right, inner 조인 동일함 .on()도 사용이 가능
public User joinBook() {
return jpaQueryFactory.selectFrom(user)
.join(user.book, book)
.fetchFirst();
}
public User joinBook2() {
return jpaQueryFactory.selectFrom(user)
.join(user.book, book)
.on(user.age.gt(30))
.fetchFirst();
}
select user0_.id as id1_1_, user0_.age as age2_1_, user0_.book_id as book_id5_1_, user0_.email as email3_1_, user0_.name as name4_1_
from user user0_
inner join book book1_
on user0_.book_id=book1_.id
limit ?
select user0_.id as id1_1_, user0_.age as age2_1_, user0_.book_id as book_id5_1_, user0_.email as email3_1_, user0_.name as name4_1_
from user user0_
inner join book book1_
on user0_.book_id=book1_.id and (user0_.age>?)
limit ?
public List<User> orderByName(){
return jpaQueryFactory.selectFrom(user)
.orderBy(user.name.asc())
.fetch();
}
public List<String> groupByName(){
return jpaQueryFactory.select(user.name).from(user)
.groupBy(user.name)
.fetch();
}
public List<UserForm.Response.Count> selectSubQuery(){
return jpaQueryFactory
.select(
Projections.fields(
UserForm.Response.Count.class,
user.name,
ExpressionUtils.as(
JPAExpressions.select(book.count())
.from(book)
.where(book.author.eq(user.name)),
"num")
)
)
.from(user)
.fetch();
}
select user0_.name as col_0_0_, (
select count(book1_.id)
from book book1_
where book1_.author=user0_.name
) as col_1_0_
from user user0_
public List<Book> whereSubQuery(String name){
return jpaQueryFactory
.selectFrom(book)
.where(book.author.eq(
JPAExpressions
.select(user.name)
.from(user)
.where(user.name.eq(name))
)
)
.fetch();
}
select book0_.id as id1_0_, book0_.author as author2_0_, book0_.price as price3_0_, book0_.title as title4_0_
from book book0_
where book0_.author=(
select user1_.name from user user1_ where user1_.name=?
)
public User dynamicQuery(String name, int age){
return jpaQueryFactory
.selectFrom(user)
.where(
eqName(name),
gtAge(age)
)
.fetchFirst();
}
private BooleanExpression eqName(String name) {
if (StringUtils.isEmpty(name)) {
return null;
}
return user.name.eq(name);
}
private BooleanExpression gtAge(int age) {
if (age == 0) {
return null;
}
return user.age.gt(age);
}
왜? QueryDSL은 SQL의 exist가 아닌 count를 사용함!!
public Boolean exist(Long id) {
Integer fetchOne = jpaQueryFactory
.selectOne()
.from(user)
.where(
user.id.eq(id)
)
.fetchFirst();
return fetchOne != null;
}
조회 결과가 없으면 null을 반환하기 때문에, null 체크를 통해 엔티티 존재 여부를 반환
MySQL에서 쿼리가 인덱스를 타지 않았을 때, Group By를 실행하면 FileSort가 반드시 발생한다.
그런데 정렬이 필요없는 경우에도 대량의 데이터를 정렬한다면 성능 손실이 크다.
이를 해결하기 위해 MySQL에선 order by null을 사용하면 Filesort가 제거되는 기능을 지원
페이징일 경우엔 Order by null을 사용할 수 없음.
import com.querydsl.core.types.NullExpression;
import com.querydsl.core.types.Order;
import com.querydsl.core.types.OrderSpecifier;
public class OrderByNull extends OrderSpecifier {
public static final OrderByNull DEFAULT = new OrderByNull();
private OrderByNull() {
super(Order.ASC, NullExpression.DEFAULT, NullHandling.Default);
}
}
// 사용
...
.orderBy(OrderByNull.DEFAULT)
...
쿼리를 충족시키는데 필요한 모든 컬럼을 갖고 있는 인덱스를 말함.
select / where / order by / group by 등에서 사용되는 모든 컬럼이 인덱스에 포함된 상태
NoOffset방식과 더불어 페이징 조회 성능을 향상시키고 가장 보편적인 방법
빨간 네모 박스 부분 커버링 인덱스를 사용한 쿼리
JPQL은 from절의 서브쿼리를 지원하지 않기 때문에, from 서브쿼리를 사용하는 경우 커버링 인덱스를 적용할 수 없음.
으이ㅏ...감사합니다 .....!!😍