상황
환경 정보
MySQL: 5.7
JPA: 5.6.15 Final
QueryDSL: 5.0.0먼저 DB에 Data(이름,위도, 경도)가 저장되어 있다고 가정한다.
현재 모바일 사용자의 위도와 경도 정보와 검색 반경(미터)이 Request로 들어온다.
서버에서 해당 사용자의 위도와 경도를 기준으로 반경(미터)내에 Data를 조회한다.
또한 단어가 같이 요청으로 들어온다면 해당 단어가 포함된 이름도 필터링해서 보여줘야 한다.
즉, 동적 쿼리로 만들 필요가 있다는 것이다.
그리고 현재 사용자의 위치에서 가까운 순으로 정렬한 후 페이징 처리하여 리스트로 보여줘야 한다.
사용할 DTO
// 요청 DTO
@Data
@AllArgsConstructor
public class NearbyClientSearchRequest {
private LocationDto location; // 위도 경도
private Double radius; // 반경(미터)
public NearbyClientSearchRequest() {
location = new LocationDto();
}
public void setLatitude(double latitude) {
this.location.setLatitude(latitude);
}
public void setLongitude(double longitude) {
this.location.setLongitude(longitude);
}
}// 응답 DTO
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ClientResponse {
private Long clientId;
private Long bundleId; // 현재 상황에서 중요한 데이터 아님
private String clientName;
private String phoneNumber;
private AddressDto address;
private LocationDto location;
private Double distance; // m 기준
}MySQL: ST_Distance_Sphere 사용하기
구조 잡기
ST_Distance_Sphere라는 MySQL에 NativeSQL을 사용하기 때문에 DB가 변경되면 쿼리문을 사용할 수 없게 됩니다.
DB가 변경되어도 QueryDSL의 코드를 유지하기 위해서 "NativeSQL을 생성하는 생성기"가 필요하다고 판단했습니다.
다음과 같이 구조를 잡고 인터페이스와 구현체를 만들었습니다.
이렇게 NativeSQL을 분리해주면 DB가 변경되어도 새로운 DB 구현체를 생성해서 붙혀주면 되기 때문에 QueryDSL의 변경을 없애거나 최소화 할 수 있다고 생각했습니다.
실제 코드
NativeSqlCreator
// 인터페이스
public interface NativeSqlCreator {
NumberExpression<Double> createCalcDistanceSQL(Double longitudeCond, Double latitudeCond, NumberPath<Double> dbLongitude, NumberPath<Double> dbLatitude);
}// 구현체
@Component
public class MysqlNativeSqlCreator implements NativeSqlCreator {
public NumberExpression<Double> createCalcDistanceSQL(Double latitudeCond, Double longitudeCond,
NumberPath<Double> dbLatitude, NumberPath<Double> dbLongitude) {
return Expressions.numberTemplate(Double.class,"ST_Distance_Sphere({0}, {1})",
Expressions.stringTemplate("POINT({0}, {1})",
latitudeCond,
longitudeCond
),
Expressions.stringTemplate("POINT({0}, {1})",
dbLatitude,
dbLongitude
)
);
}
}QueryDSL
private final JPAQueryFactory query;
private final NativeSqlCreator mysqlNativeSQLCreator;
// query 수행과 관련된 메서드만 표시
public Slice<ClientResponse> findClientByConditions(NearbyClientSearchRequest locationSearchCond, String wordCond, Pageable pageable) {
List<ClientResponse> result = query.select(
Projections.constructor(ClientResponse.class,
client.id,
Expressions.asNumber(1L), // 임시 데이터
client.name,
client.phoneNumber,
getAddressDto(),
getLocationDto(),
Expressions.asNumber(1d) // 임시 distance
)
)
.from(client)
.where(
nameContains(wordCond), // 동적 쿼리 - 위치 계산과는 상관없음
isClientInRadius(locationSearchCond) // 반경 내에 Client 필터링
)
.orderBy(distanceAsc(locationSearchCond), client.createdDate.desc())
.offset(pageable.getOffset())
.limit(pageable.getPageSize() + 1)
.fetch();
boolean hasNext = result.size() > pageable.getPageSize();
if (hasNext) {
result.remove(pageable.getPageSize());
}
return new SliceImpl<>(result, pageable, hasNext);
}
// 반경 내에 Client가 있는지
private BooleanExpression isClientInRadius(NearbyClientSearchRequest locationSearchCond) {
if(isLocationSearchCondEmpty(locationSearchCond)) {
return null;
}
LocationDto currentLocation = locationSearchCond.getLocation();
return getCalcDistanceNativeSQL(currentLocation)
.loe(locationSearchCond.getRadius());
}
// NativeSQL 생성
private NumberExpression<Double> getCalcDistanceNativeSQL(LocationDto currentLocation) {
return mysqlNativeSQLCreator.createCalcDistanceSQL(
currentLocation.getLongitude(), currentLocation.getLatitude(),
client.location.longitude, client.location.latitude
);
}생성된 JPQL 쿼리문
이제 해당 쿼리를 실행해보면 다음과 같은 JPQL이 나갑니다.
Hibernate:
select
client0_.client_id as col_0_0_,
client0_.name as col_1_0_,
client0_.phone_number as col_2_0_,
client0_.province as col_3_0_,
client0_.city as col_4_0_,
client0_.district as col_5_0_,
client0_.detail as col_6_0_,
client0_.latitude as col_7_0_,
client0_.longitude as col_8_0_
from
client client0_
where
-- 거리와 상관없는 SQL문은 생략
and ST_Distance_Sphere(POINT(?,?),POINT(client0_.longitude,client0_.latitude))<=?
order by
ST_Distance_Sphere(POINT(?,
?),
POINT(client0_.longitude,
client0_.latitude)) asc,
client0_.created_date desc limit ?지금 당장의 문제점
QueryDSL의 코드를 잘 살펴보면 Select 구문에서 distance 필드를 초기화해주지 않고 임의로 값을 넣어주고 있습니다.
만약 임시로 값을 넣어주던 Expressions.asNumber(1d)코드를 getCalcDistanceNativeSQL(locationCond.getLocation)으로 바꾸게 되면 다음과 같은 에러가 발생합니다.
org.hibernate.QueryException: No data type for node
현재 Where절과 OrderBy절에서는 MySQL에서 사용하는 ST_Distance_Sphere를 문제없이 사용했습니다.
하지만 Select절에 해당 함수를 사용하는 순간 문제가 발생했습니다.
이 다음 내용의 일부는 ChatGPT에게 얻은 답변이기 때문에 정확하지 않을 수 있습니다. 개인적으로 합리적인 이유인 것 같아서 글에 포함시켰습니다.
왜 where절과 orderBy절에는 문제가 없었을까요?
WHERE 절과 ORDER BY 절은 데이터베이스 쿼리의 조건을 필터링하거나 정렬하는 데 사용됩니다.
이들 절에서 사용되는 함수는 일반적으로 데이터베이스 서버에서 처리됩니다.
Hibernate는 이들 절에서 사용되는 함수를 데이터베이스 쿼리로 전달하고 데이터베이스 서버에서 실행되므로, 해당 함수가 데이터베이스에서 인식될 수 있다면 문제 없이 사용할 수 있습니다.
Select절에서 사용하는 것은 무엇이 문제일까요?
Select 절에서 특정 함수를 사용하려면 Hibernate의 Dialect에 해당 함수가 등록되어 있어야 합니다.
Select 절에서 사용하는 함수는 Hibernate의 Dialect에 의해 처리되므로 Dialect이 해당 함수를 인식할 수 있어야 합니다.
따라서 WHERE 절과 ORDER BY 절에 있는 함수는 데이터베이스 서버에서 처리되기 때문에 Hibernate의 Dialect에 등록할 필요가 없습니다. 다만, SELECT 절에서 사용되는 특정 함수는 Hibernate의 Dialect에 등록되어야 하므로 Hibernate이 해당 함수를 인식하고 쿼리를 올바르게 생성할 수 있습니다.
예시
aaaccc라는 존재하지도 않는 function을 Select절에서 실행했을 때는
ST_Distance_Sphere를 Select절에서 실행했을 때와 같은 오류가 발생합니다.
이 aaaccc라는 임의의 function을 이번에는 Where절에 넣어서 실행해봤습니다.
SQL 문법오류인 SQLGrammarException이 발생했습니다.
Select절의 경우는 DB의 결과값을 스프링 서버에서 받고 엔티티(또는 DTO) 객체로 변환해야 합니다. 결과를 받아서 직접 객체로 변환해야 하기 때문에 타입이 중요해집니다.
그래서 Dialect에 등록되어 있는 function을 사용해야 합니다.
Select를 제외한 곳은 엔티티 객체 변환과 연관이 없습니다.
DB에서 내부적으로 처리를 하고 스프링 서버에는 Select 형식에 맞는 결과를 내려줄 뿐입니다.
즉 Select를 제외한 곳에서는 Dialect에 등록되어 있지 않아도 DB에서 처리만 되면 상관없습니다.
그래서 Select 절이 아니라면 Dialect에 등록되어 있지 않는 해당 DB의 방언을 사용할 수 있습니다.
문제 해결
ST_Distance_Sphere를 Dialect에 등록하면 문제가 해결됩니다.
MySQLCustomDialect라는 클래스를 생성합니다.
package com.map.gaja.global.config;
import org.hibernate.dialect.MySQL57Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;
public class MySQLCustomDialect extends MySQL57Dialect {
public MySQLCustomDialect() {
super();
this.registerFunction("ST_Distance_Sphere",
new StandardSQLFunction("ST_Distance_Sphere", StandardBasicTypes.DOUBLE));
}
}이제 application.yml에서 해당 커스텀 Dialect를 등록해줍니다.
spring:
jpa:
properties:
hibernate:
dialect: com.map.gaja.global.config.MySQLCustomDialect이제 정상적으로 select절에도 ST_Distance_Sphere가 포함되어서 쿼리가 나가고 DTO의 distance 필드 또한 초기화되어서 잘 나옵니다.
테스트 코드 및 결과
거리순(m단위)으로도 잘 정렬되어서 나온 것을 확인할 수 있습니다.
해당 쿼리의 결과로 나온 데이터를 다음 사이트에서 값을 넣어 검증해보면 거리가 정확하게 나오는 것을 확인할 수 있습니다.
지도 좌표 사이 거리를 구해주는 사이트
남은 문제...
아직 쿼리문 최적화에 대한 문제가 남아있습니다.
이 내용은 다음 글에서 다루도록 하겠습니다.
출처 및 참고
https://velog.io/@hyunho058/Querydsl-SQL-Function
