{"id":1437,"date":"2020-11-01T15:17:52","date_gmt":"2020-11-01T06:17:52","guid":{"rendered":"https:\/\/www.skyer9.pe.kr\/wordpress\/?p=1437"},"modified":"2020-11-01T15:17:52","modified_gmt":"2020-11-01T06:17:52","slug":"spring-boot-jpa-with-procedure","status":"publish","type":"post","link":"https:\/\/www.skyer9.pe.kr\/wordpress\/?p=1437","title":{"rendered":"Spring Boot JPA With Procedure"},"content":{"rendered":"<h1>Spring Boot JPA With Procedure<\/h1>\n<h2>NamedStoredProcedureQuery \uc0dd\uc131<\/h2>\n<pre><code class=\"language-java\">@NamedStoredProcedureQuery(\n        name = Menus.NamedQuery_GetMenuList,\n        procedureName = &quot;getMenuList&quot;,\n        resultClasses = Menus.class,\n        parameters = {\n                @StoredProcedureParameter(name = &quot;_pageNo&quot;, type = Integer.class, mode = ParameterMode.IN),\n                @StoredProcedureParameter(name = &quot;_pageSize&quot;, type = Integer.class, mode = ParameterMode.IN),\n                @StoredProcedureParameter(name = &quot;_cid&quot;, type = String.class, mode = ParameterMode.IN),\n                @StoredProcedureParameter(name = &quot;RESULT&quot;, type = Integer.class, mode = ParameterMode.OUT),\n        }\n)\n@Getter\n@NoArgsConstructor\n@Entity\n@Table(name = &quot;tbl_menu&quot;)\npublic class Menus extends BaseTimeEntity {\n\n    public static final String NamedQuery_GetMenuList = &quot;getMenuList&quot;;\n\n    @Id\n    private String cid;\n\n    @Column(nullable = false)\n    private String pid;\n\n    @Column(length = 128, nullable = false)\n    private String menuName;\n\n    @Column\n    private String useyn;\n\n    @Builder\n    public Menus(String pid, String menuName, String useyn) {\n        this.pid = pid;\n        \/\/ this.cid = UUID.randomUUID().toString();\n        this.cid = ULID.random();\n        this.menuName = menuName;\n        this.useyn = useyn;\n    }\n\n    @Builder\n    public Menus(String pid, String menuName) {\n        this.pid = pid;\n        \/\/ this.cid = UUID.randomUUID().toString();\n        this.cid = ULID.random();\n        this.menuName = menuName;\n    }\n\n    public void update(String pid, String menuName) {\n        this.pid = pid;\n        this.menuName = menuName;\n    }\n\n    public void delete() {\n        this.useyn = &quot;N&quot;;\n    }\n}<\/code><\/pre>\n<h2>StoredProcedureQuery \uc0dd\uc131<\/h2>\n<pre><code class=\"language-java\">@RequiredArgsConstructor\n@Service\npublic class MenusPagingService {\n\n    @PersistenceContext\n    private EntityManager entityManager;\n\n    @Transactional(readOnly = true)\n    public Page&lt;MenusListResponseDto&gt; search(Pageable pageable, MenusSearchDto menusSearchDto) {\n\n        StoredProcedureQuery spq =\n                entityManager.createNamedStoredProcedureQuery(Menus.NamedQuery_GetMenuList);\n        spq.setParameter(&quot;_pageNo&quot;, 0);\n        spq.setParameter(&quot;_pageSize&quot;, 3);\n        spq.setParameter(&quot;_cid&quot;, &quot;&quot;);\n        spq.execute();\n\n        @SuppressWarnings(&quot;unchecked&quot;)\n        List&lt;MenusListResponseDto&gt; results = toDto(spq.getResultList());\n        int totalCount = (int) spq.getOutputParameterValue(&quot;RESULT&quot;);\n\n        return new PageImpl&lt;&gt;(results, pageable, totalCount);\n    }\n\n    private List&lt;MenusListResponseDto&gt; toDto(List&lt;Menus&gt; posts) {\n        return posts.stream()\n                .map(MenusListResponseDto::new)\n                .collect(Collectors.toList());\n    }\n}<\/code><\/pre>\n<h2>\ud504\ub85c\uc2dc\uc800<\/h2>\n<pre><code class=\"language-sql\">DROP PROCEDURE IF EXISTS getMenuList;\nDELIMITER $$\nCREATE procedure getMenuList(IN _pageNo INT, IN _pageSize INT, IN _cid VARCHAR(64), OUT RESULT INT)\nBEGIN\n    DECLARE _OFFSET INT;\n\n    SET _OFFSET = _pageNo * _pageSize;\n\n    SELECT COUNT(*)\n    INTO RESULT\n    from\n        tbl_menu m\n    where\n        1 = 1\n        AND (_cid = &#039;&#039; OR _cid = m.cid);\n\n    select *\n    from\n        tbl_menu m\n    where\n        1 = 1\n        AND (_cid = &#039;&#039; OR _cid = m.cid)\n    ORDER BY\n        cid DESC\n    LIMIT _OFFSET, _pageSize;\nend$$\nDELIMITER ;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Spring Boot JPA With Procedure NamedStoredProcedureQuery \uc0dd\uc131 @NamedStoredProcedureQuery( name = Menus.NamedQuery_GetMenuList, procedureName = &quot;getMenuList&quot;, resultClasses = Menus.class, parameters = { @StoredProcedureParameter(name = &quot;_pageNo&quot;, type = Integer.class, mode = ParameterMode.IN), @StoredProcedureParameter(name = &quot;_pageSize&quot;, type = Integer.class, mode = ParameterMode.IN), @StoredProcedureParameter(name = &quot;_cid&quot;, type = String.class, mode = ParameterMode.IN), @StoredProcedureParameter(name = &quot;RESULT&quot;, type = Integer.class, mode =\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.skyer9.pe.kr\/wordpress\/?p=1437\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1437","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1437","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1437"}],"version-history":[{"count":3,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1437\/revisions"}],"predecessor-version":[{"id":1440,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1437\/revisions\/1440"}],"wp:attachment":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1437"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1437"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1437"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}