JPA 에서 프로시저(procedure) 호출

참조

프로시저 생성

DROP PROCEDURE IF EXISTS getUserList;
DELIMITER $$
CREATE procedure getUserList(IN _pageNo INT, IN _pageSize INT, IN _userId VARCHAR(32), IN _userName VARCHAR(64), IN _email VARCHAR(64), IN _useyn VARCHAR(1), OUT RESULT INT)
BEGIN
    DECLARE _OFFSET INT;

    SET _OFFSET = _pageNo * _pageSize;

    SELECT COUNT(*)
    INTO RESULT
    FROM
        db_users.tbl_users u
    WHERE
        1 = 1
        AND (_userId = '' OR u.user_id = _userId)
        AND (_userName = '' OR u.user_name = _userName)
        AND (_email = '' OR u.email = _email)
        AND (_useyn = '' OR u.useyn = _useyn);

    SELECT *
    FROM
        db_users.tbl_users u
    WHERE
        1 = 1
        AND (_userId = '' OR u.user_id = _userId)
        AND (_userName = '' OR u.user_name = _userName)
        AND (_email = '' OR u.email = _email)
        AND (_useyn = '' OR u.useyn = _useyn)
    ORDER BY
        u.user_key DESC
    LIMIT _OFFSET, _pageSize;
END $$
DELIMITER ;

엔터티 수정

NamedStoredProcedureQuery 어노테이션을 추가해 준다. resultClasses 에 리턴되는 값을 저장할 클래스를 지정해 준다.

@NamedStoredProcedureQuery(
        name = Users.NamedQuery_GetUserList,
        procedureName = "db_users.getUserList",
        resultClasses = Users.class,
        parameters = {
                @StoredProcedureParameter(name = "_pageNo", type = Integer.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "_pageSize", type = Integer.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "_userId", type = String.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "_userName", type = String.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "_email", type = String.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "_useyn", type = String.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "RESULT", type = Integer.class, mode = ParameterMode.OUT),
        }
)
@Getter
@NoArgsConstructor
@Entity
@Table(name = "tbl_users", catalog = "db_users")
public class Users extends BaseTimeEntity {

    public static final String NamedQuery_GetUserList = "getUserList";

    // ......
}

서비스 추가

public class UsersService {

    // ......

    @PersistenceContext
    private final EntityManager entityManager;

    @Transactional(readOnly = true)
    @Cacheable(value="Users")
    public ApiResponseWithPaging search(Pageable pageable, UsersRequestDto usersRequestDto) {
        StoredProcedureQuery spq =
                entityManager.createNamedStoredProcedureQuery(Users.NamedQuery_GetUserList);
        spq.setParameter("_pageNo", pageable.getPageNumber());
        spq.setParameter("_pageSize", pageable.getPageSize());
        spq.setParameter("_userId", usersRequestDto.getUserId());
        spq.setParameter("_userName", usersRequestDto.getUserName());
        spq.setParameter("_email", usersRequestDto.getEmail());
        spq.setParameter("_useyn", usersRequestDto.getUseyn());
        spq.execute();

        @SuppressWarnings("unchecked")
        List<Object> results = toDto(spq.getResultList());
        int totalCount = (int) spq.getOutputParameterValue("RESULT");

        return new ApiResponseWithPaging(ResponseCode.OK, results, pageable, totalCount);
    }
    // ......
}

repository 에 추가

아래의 방식으로 repository 에서 프로시저를 호출할 수 있다.
OUTPUT 을 사용할 수 없다.
검색결과에 totalCount 등 OUTPUT 해야 할 데이타를 추가하면,
데이타를 받아 올 수 있다.

SELECT *, @totalCount as totalCount
FROM 테이블명

JDBC URL 에 데이타베이스를 master 로 설정한 경우,
모든 논리 데이타베이스의 프로시저를 호춠할 수 있다.

@Repository
public interface OrderMasterRepository extends JpaRepository<OrderMaster, Integer> {

    Optional<OrderMaster> findByOrderserial(String orderserial);

    // 대괄호 앞뒤에 공백문자가 있어야 한다.(MySQL)
    @Query(value = "{ CALL db_order.dbo.usp_Ten_Order_GetOrderMasterListJpa(:pageNo, :pageSize, :userid, :orderserial) }", nativeQuery = true)
    List<OrderMaster> GetOrderMasterList(
            @Param("pageNo") Integer pageNo,
            @Param("pageSize") Integer pageSize,
            @Param("userid") String userid,
            @Param("orderserial") String orderserial);
}

repository 리턴값이 Entity 가 아닐 경우

결과값이 엔터티가 아닌 경우,
아래처럼 interface 를 추가해 준다.

public interface OrderMasterRepository extends JpaRepository<OrderMaster, Integer> {

    // MS-SQL
    @Query(value = "exec [db_order].[dbo].[usp_Refine_Order]", nativeQuery = true)
    ResponseOnly RefineOrders();

    public static interface ResponseOnly {
        String getResult();
        String getMessage();
    }
}