[JSP] 중간 팀 프로젝트 : 동적 쿼리 사용

동적 쿼리

 

정적쿼리는 기존에 사용한 방식대로 고정되어 있는 SQL문을 사용하는 것이고,

동적쿼리는 상황에 맞게 쿼리를 조합하여 사용할 수 있는 방식이다.

 

이번 중간 프로젝트에서 전체 페이지 수를 반환하는 간단한 기능에 대한 쿼리문을 동적쿼리로 변경해 사용해 보았다.

전체 페이지 수는 기능에 따라 다르게 나타날 수 있기 때문에 각각 쿼리문을 작성해야 하는데,

반복되는 쿼리가 발생되므로 동적 쿼리를 사용했다.

 

 

▼ 기능별로 각각 작성된 SQL문

-- 3. 전체 페이지 수를 반환하는 select문 (페이지네이션 사용)
-- [전체 검색 - 기본]
SELECT CEIL(COALESCE(COUNT(PRODUCT_NUM),0)/9.0)AS PRODUCT_TOTAL_PAGE FROM PRODUCT;
-- 아래는 AND~ 쿼리 사용
-- [키워드 검색]
SELECT CEIL(COALESCE(COUNT(PRODUCT_NUM),0)/9.0)AS PRODUCT_TOTAL_PAGE FROM PRODUCT WHERE PRODUCT_NAME LIKE '%'||?||'%';
-- [필터링 검색]
SELECT CEIL(COALESCE(COUNT(PRODUCT_NUM),0)/9.0)AS PRODUCT_TOTAL_PAGE FROM PRODUCT
WHERE PRODUCT_LOCATION = COALESCE(?,PRODUCT_LOCATION) AND PRODUCT_CATEGORY = COALESCE(?,PRODUCT_CATEGORY);

위 내용을 보면 반복되는 쿼리가 있음을 알 수 있다.

이를 동적쿼리로 바꾼다면

 

▼ 동적쿼리로 변경한 SQL문 (DAO 상단에 배치한 SQL문 코드)

// 전체 데이터 개수를 반환 (전체 페이지 수 - 기본)
	private final String PRODUCT_TOTAL_PAGE = "SELECT CEIL(COALESCE(COUNT(PRODUCT_NUM),0)/9.0)AS PRODUCT_TOTAL_PAGE FROM PRODUCT WHERE 1=1";
	// 전체 데이터 개수를 반환 (검색어 사용 페이지수 )
	private final String PRODUCT_SEARCH_PAGE = "AND PRODUCT_NAME LIKE '%'||?||'%'";
	// 전체 데이터 개수를 반환 (필터링 검색 페이지 수)
	private final String PRODUCT_FILTERING_PAGE = "AND PRODUCT_LOCATION = COALESCE(?,PRODUCT_LOCATION) AND PRODUCT_CATEGORY = COALESCE(?,PRODUCT_CATEGORY)";

 

이 SQL문을 보면

1. WHERE 1=1

2. AND로 시작하는 SQL문

 

이 두 가지 특징을 볼 수 있다.

 

 

WHERE 1=1

무조건 true 임을 나타내는 구문이다.

 

동적쿼리는 쿼리를 조합해 사용할 수 있는데, 이때 WHERE 1=1 구문을 사용하게 되면,

뒤에 AND로 시작하는 어떤 구문이 와도 조합해서 사용할 수 있다.

 

 

 

예시로 설명을 하면, 만약 위의 3가지 SQL문이 하나의 기능이라면?

아래와 같은 쿼리가 될 수 있다.

SELECT CEIL(COALESCE(COUNT(PRODUCT_NUM),0)/9.0)AS PRODUCT_TOTAL_PAGE FROM PRODUCT
WHERE PRODUCT_NAME LIKE '%'||?||'%'
AND PRODUCT_LOCATION = COALESCE(?,PRODUCT_LOCATION) AND PRODUCT_CATEGORY = COALESCE(?,PRODUCT_CATEGORY);

 

이때 만약 중간의 WHERE절이 빠지게 된다면? WHERE절 없이 바로 AND가 오기 때문에 정상적인 동작을 하지 못한다.

따라서 어떤 AND 조건이 붙어도 정상적으로 쿼리가 실행될 수 있고, 만약 요구된 조건이 없어도 실행될 수 있도록 

WHERE 1=1을 사용한다.

 

 

 

▼ DAO : selectOne의 수정된 일부 코드

else if(productDTO.getProduct_condition().equals("PRODUCT_PAGE_COUNT")) {
	// 페이지네이션에 사용하기 위해 전체 페이지 수 반환
	System.out.println("model.ProductDAO.selectOne 전체 상품 페이지 수 반환 쿼리 읽기 if문 시작");
	if(productDTO.getProduct_searchKeyword()!=null && !productDTO.getProduct_searchKeyword().isEmpty()) {
		System.out.println("model.ProductDAO.selectOne 키워드 검색 페이지 수 반환 쿼리 읽기 시작");
		// 키워드 검색
		// 만약 검색어가 null이 아니고, 빈 문자열도 아닐 때 (검색어가 있을 때)
		// 동적 쿼리를 사용하여 기본 + AND 쿼리 
		pstmt=conn.prepareStatement(PRODUCT_TOTAL_PAGE + " " +PRODUCT_SEARCH_PAGE);
		// 검색어를 찾기 위해 파라미터에 값을 넣어준다.
		pstmt.setString(1, productDTO.getProduct_searchKeyword()); // 검색어
	}
	else if(productDTO.getProduct_location() != null && !productDTO.getProduct_location().isEmpty()
	|| productDTO.getProduct_category() != null && !productDTO.getProduct_category().isEmpty()) {
		System.out.println("model.ProductDAO.selectOne 필터링 검색 페이지 수 반환 쿼리 읽기 시작");
		// 필터링 검색
		pstmt=conn.prepareStatement(PRODUCT_TOTAL_PAGE + " " +PRODUCT_FILTERING_PAGE);
		// 특정 조건을 검색하기 위해 파라미터에 값을 넣어준다.
		pstmt.setString(1, productDTO.getProduct_location()); // 상품 장소 (바다/민물)
		pstmt.setString(2, productDTO.getProduct_category()); // 상품 유형 (낚시배/낚시터/낚시카페/수상)
	}
	else {
		System.out.println("model.ProductDAO.selectOne 전체 페이지 수 반환 쿼리 읽기 시작");
		// 전체 검색
		pstmt=conn.prepareStatement(PRODUCT_TOTAL_PAGE);
	}

DAO에서 prepareStatement에서 사이에 " " 을 넣어주는 이유는

쿼리를  읽어올 때 공백이 없다면 "WHERE 1=1AND.." 처럼 WHERE절과 AND 조건이 붙어서 인식되기 때문에,

공백을 넣어주었다.

 

 

 

이렇게 동적쿼리를 사용한다면 필요한 조건에 맞게 유동적으로 쿼리를 실행할 수 있으며,

조건이 추가되어도 쉽게 기능을 추가할 수 있기 때문에 유지보수에 용이하다는 장점이 있다.