[hibernate] 프로시저 조회 call procedure List<Object> to List<Map<String, Object[]>>
프로그램 자료/Java & Spring 2018. 7. 24. 15:352018/07/24 - [프로그램 자료/MySQL & MariaDB] - [MySQL & MariaDB] 동적 프로시저 dynamic procedure - 기간 포함
[개발환경]
spring boot 2.0
hibernate 5.2.7 final
mariadb 10.0.x
[개요]
프로시저를 사용한 게시물 조회를 가정할 때 동적으로 게시물을 검색하도록은 만들었는데,
hibernate가 procedure에서 두 개 이상의 result를 반환시 첫 번째 result만 취하는 문제가 있었다.
그래서 proc에서 out param을 사용하고
추가적으로 hibernate로 proc 사용시 List<Object> 넘어오기에 쓰기가 싫어서 List<Map<String, Object[]>> 로 해주는 헬퍼클래스를 만들었다.
setContent 만 사용하던가, setTotalCount 같이 사용하던가 하면 된다.
DELIMITER $$
USE `demoDB`$$
DROP PROCEDURE IF EXISTS usp_test$$
CREATE DEFINER='demo_user'@'%' PROCEDURE usp_test
(
in pOffset int,
in pSize int,
out totalCount int
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '테스트'
BEGIN
SET iPageOffset = IFNULL(iPageOffset, 0);
SET iPageSize = IFNULL(iPageSize, 10);
SELECT SQL_CALC_FOUND_ROWS *
FROM table_name t
WHERE 1 = 1
AND condition = true
ORDER BY CASE WHEN iOrderStr = 'CreateDate ASC' THEN t.CreateDate END ASC,
CASE WHEN iOrderStr = 'CreateDate DESC' THEN t.CreateDate END DESC,
CASE WHEN iOrderStr = 'UpdateDate ASC' THEN t.UpdateDate END ASC,
CASE WHEN iOrderStr = 'UpdateDate DESC' THEN t.UpdateDate END DESC,
LIMIT iPageOffset, iPageSize;
SET totalCount := FOUND_ROWS();
END$$
DELIMITER ;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Getter
@Setter
public class CustomResultList {
private List<Map<String, Object>> content;
private int page = 1;
private int pageSize = 10;
private int offset = 0;
private int totalCount = 0;
private int totalPages;
private int begin;
private int end;
public CustomResultList() {
}
public CustomResultList(int page) throws RuntimeException {
this.setPage(page);
}
public CustomResultList(int page, int pageSize) throws RuntimeException {
this.setPage(page);
this.setPageSize(pageSize);
}
public static List<Map<String, Object>> ConvertContents(List<Object[]> contents, String... columnNameArgs) {
List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
for (Object[] obj : contents) {
Map<String, Object> row = new HashMap<String, Object>();
for (int i = 0; i < columnNameArgs.length; i++) {
if (obj[i] != null && obj[i].getClass() == String.class) {
// 프로시저 인젝션 처리
row.put(columnNameArgs[i]
, SpecialCharacterListener.removeSomeTagCharacter((String) obj[i]));
} else {
row.put(columnNameArgs[i], obj[i]);
}
}
rows.add(row);
}
return rows;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
this.totalPages = (int)Math.ceil((double) totalCount / (double) this.pageSize);
this.begin = Math.max(1, this.page - 4);
this.end = Math.min(this.page + 5,
this.totalPages == 0 ? 1 : this.totalPages);
}
public void setPage(int page) throws RuntimeException {
if (page < 1)
throw new RuntimeException("1보다 작은 수를 page에 할당 할 수 없습니다.");
this.offset = (page - 1) * this.pageSize;
this.page = page;
}
public void setPageSize(int pageSize) throws RuntimeException {
if (pageSize < 1)
throw new RuntimeException("1보다 작은 수를 pageSize에 할당 할 수 없습니다.");
this.offset = (this.page - 1) * pageSize;
this.pageSize = pageSize;
}
@SuppressWarnings("unchecked")
public void setContent(List resultList, String... columnNameArgs) {
try {
log.debug("contents size : {}", resultList.size());
this.content = ConvertContents(resultList, columnNameArgs);
this.setTotalCount(resultList.size());
} catch (RuntimeException ex) {
log.debug(ex.getStackTrace().toString());
throw ex;
}
}
}
import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.stereotype.Service;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Service("workLogService")
public class WorkLogServiceImpl implements WorkLogService {
public CustomResultList findAll() {
Session session = em.unwrap(Session.class);
try {
CustomResultList result = new CustomResultList(1);
StoredProcedureQuery query = session.createStoredProcedureQuery("usp_test");
query.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(3, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(4, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(5, int.class, ParameterMode.IN);
query.registerStoredProcedureParameter(6, int.class, ParameterMode.IN);
query.registerStoredProcedureParameter(7, String.class, ParameterMode.IN);
query.registerStoredProcedureParameter(8, int.class, ParameterMode.OUT);
query.setParameter(1, "");
query.setParameter(2, "");
query.setParameter(3, "");
query.setParameter(4, "");
query.setParameter(5, result.getOffset());
query.setParameter(6, result.getPageSize());
query.setParameter(7, "");
result.setContent(query.getResultList(), "columnName1", "columnName2", "columnName3");
result.setTotalCount((int) query.getOutputParameterValue(8));
return result;
} catch (RuntimeException he) {
he.printStackTrace();
throw he;
} finally {
session.close();
}
}
}
