2018/06/27 - [프로그램 자료/Java & Spring] - [hibernate] Convert Page < Object[] > to List < Map < String, Object > >

2018/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();
        }
    }
} 

[hibernate] 프로시저 조회 call procedure List<Object> to List<Map<String, Object[]>>