Spring Boot JPA With Procedure

NamedStoredProcedureQuery 생성

@NamedStoredProcedureQuery(
        name = Menus.NamedQuery_GetMenuList,
        procedureName = "getMenuList",
        resultClasses = Menus.class,
        parameters = {
                @StoredProcedureParameter(name = "_pageNo", type = Integer.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "_pageSize", type = Integer.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "_cid", type = String.class, mode = ParameterMode.IN),
                @StoredProcedureParameter(name = "RESULT", type = Integer.class, mode = ParameterMode.OUT),
        }
)
@Getter
@NoArgsConstructor
@Entity
@Table(name = "tbl_menu")
public class Menus extends BaseTimeEntity {

    public static final String NamedQuery_GetMenuList = "getMenuList";

    @Id
    private String cid;

    @Column(nullable = false)
    private String pid;

    @Column(length = 128, nullable = false)
    private String menuName;

    @Column
    private String useyn;

    @Builder
    public Menus(String pid, String menuName, String useyn) {
        this.pid = pid;
        // this.cid = UUID.randomUUID().toString();
        this.cid = ULID.random();
        this.menuName = menuName;
        this.useyn = useyn;
    }

    @Builder
    public Menus(String pid, String menuName) {
        this.pid = pid;
        // this.cid = UUID.randomUUID().toString();
        this.cid = ULID.random();
        this.menuName = menuName;
    }

    public void update(String pid, String menuName) {
        this.pid = pid;
        this.menuName = menuName;
    }

    public void delete() {
        this.useyn = "N";
    }
}

StoredProcedureQuery 생성

@RequiredArgsConstructor
@Service
public class MenusPagingService {

    @PersistenceContext
    private EntityManager entityManager;

    @Transactional(readOnly = true)
    public Page<MenusListResponseDto> search(Pageable pageable, MenusSearchDto menusSearchDto) {

        StoredProcedureQuery spq =
                entityManager.createNamedStoredProcedureQuery(Menus.NamedQuery_GetMenuList);
        spq.setParameter("_pageNo", 0);
        spq.setParameter("_pageSize", 3);
        spq.setParameter("_cid", "");
        spq.execute();

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

        return new PageImpl<>(results, pageable, totalCount);
    }

    private List<MenusListResponseDto> toDto(List<Menus> posts) {
        return posts.stream()
                .map(MenusListResponseDto::new)
                .collect(Collectors.toList());
    }
}

프로시저

DROP PROCEDURE IF EXISTS getMenuList;
DELIMITER $$
CREATE procedure getMenuList(IN _pageNo INT, IN _pageSize INT, IN _cid VARCHAR(64), OUT RESULT INT)
BEGIN
    DECLARE _OFFSET INT;

    SET _OFFSET = _pageNo * _pageSize;

    SELECT COUNT(*)
    INTO RESULT
    from
        tbl_menu m
    where
        1 = 1
        AND (_cid = '' OR _cid = m.cid);

    select *
    from
        tbl_menu m
    where
        1 = 1
        AND (_cid = '' OR _cid = m.cid)
    ORDER BY
        cid DESC
    LIMIT _OFFSET, _pageSize;
end$$
DELIMITER ;