\ucc38\uc870<\/a><\/p>\n \uc544\ub798\uc758 \ubc29\uc2dd\uc73c\ub85c repository \uc5d0\uc11c \ud504\ub85c\uc2dc\uc800\ub97c \ud638\ucd9c\ud560 \uc218 \uc788\ub2e4. JDBC URL \uc5d0 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub97c master \ub85c \uc124\uc815\ud55c \uacbd\uc6b0, \uacb0\uacfc\uac12\uc774 \uc5d4\ud130\ud2f0\uac00 \uc544\ub2cc \uacbd\uc6b0, JPA \uc5d0\uc11c \ud504\ub85c\uc2dc\uc800(procedure) \ud638\ucd9c \ucc38\uc870 \ud504\ub85c\uc2dc\uc800 \uc0dd\uc131 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\u2026 Read More »<\/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":[29],"tags":[],"class_list":["post-1520","post","type-post","status-publish","format-standard","hentry","category-spring-boot-2-5"],"_links":{"self":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1520","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=1520"}],"version-history":[{"count":6,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1520\/revisions"}],"predecessor-version":[{"id":5585,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1520\/revisions\/5585"}],"wp:attachment":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1520"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\ud504\ub85c\uc2dc\uc800 \uc0dd\uc131<\/h2>\n
DROP PROCEDURE IF EXISTS getUserList;\nDELIMITER $$\nCREATE 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)\nBEGIN\n DECLARE _OFFSET INT;\n\n SET _OFFSET = _pageNo * _pageSize;\n\n SELECT COUNT(*)\n INTO RESULT\n FROM\n db_users.tbl_users u\n WHERE\n 1 = 1\n AND (_userId = '' OR u.user_id = _userId)\n AND (_userName = '' OR u.user_name = _userName)\n AND (_email = '' OR u.email = _email)\n AND (_useyn = '' OR u.useyn = _useyn);\n\n SELECT *\n FROM\n db_users.tbl_users u\n WHERE\n 1 = 1\n AND (_userId = '' OR u.user_id = _userId)\n AND (_userName = '' OR u.user_name = _userName)\n AND (_email = '' OR u.email = _email)\n AND (_useyn = '' OR u.useyn = _useyn)\n ORDER BY\n u.user_key DESC\n LIMIT _OFFSET, _pageSize;\nEND $$\nDELIMITER ;<\/code><\/pre>\n\uc5d4\ud130\ud2f0 \uc218\uc815<\/h2>\n
NamedStoredProcedureQuery<\/code> \uc5b4\ub178\ud14c\uc774\uc158\uc744 \ucd94\uac00\ud574 \uc900\ub2e4. resultClasses<\/code> \uc5d0 \ub9ac\ud134\ub418\ub294 \uac12\uc744 \uc800\uc7a5\ud560 \ud074\ub798\uc2a4\ub97c \uc9c0\uc815\ud574 \uc900\ub2e4.<\/p>\n@NamedStoredProcedureQuery(\n name = Users.NamedQuery_GetUserList,\n procedureName = "db_users.getUserList",\n resultClasses = Users.class,\n parameters = {\n @StoredProcedureParameter(name = "_pageNo", type = Integer.class, mode = ParameterMode.IN),\n @StoredProcedureParameter(name = "_pageSize", type = Integer.class, mode = ParameterMode.IN),\n @StoredProcedureParameter(name = "_userId", type = String.class, mode = ParameterMode.IN),\n @StoredProcedureParameter(name = "_userName", type = String.class, mode = ParameterMode.IN),\n @StoredProcedureParameter(name = "_email", type = String.class, mode = ParameterMode.IN),\n @StoredProcedureParameter(name = "_useyn", type = String.class, mode = ParameterMode.IN),\n @StoredProcedureParameter(name = "RESULT", type = Integer.class, mode = ParameterMode.OUT),\n }\n)\n@Getter\n@NoArgsConstructor\n@Entity\n@Table(name = "tbl_users", catalog = "db_users")\npublic class Users extends BaseTimeEntity {\n\n public static final String NamedQuery_GetUserList = "getUserList";\n\n \/\/ ......\n}<\/code><\/pre>\n\uc11c\ube44\uc2a4 \ucd94\uac00<\/h2>\n
public class UsersService {\n\n \/\/ ......\n\n @PersistenceContext\n private final EntityManager entityManager;\n\n @Transactional(readOnly = true)\n @Cacheable(value="Users")\n public ApiResponseWithPaging search(Pageable pageable, UsersRequestDto usersRequestDto) {\n StoredProcedureQuery spq =\n entityManager.createNamedStoredProcedureQuery(Users.NamedQuery_GetUserList);\n spq.setParameter("_pageNo", pageable.getPageNumber());\n spq.setParameter("_pageSize", pageable.getPageSize());\n spq.setParameter("_userId", usersRequestDto.getUserId());\n spq.setParameter("_userName", usersRequestDto.getUserName());\n spq.setParameter("_email", usersRequestDto.getEmail());\n spq.setParameter("_useyn", usersRequestDto.getUseyn());\n spq.execute();\n\n @SuppressWarnings("unchecked")\n List<Object> results = toDto(spq.getResultList());\n int totalCount = (int) spq.getOutputParameterValue("RESULT");\n\n return new ApiResponseWithPaging(ResponseCode.OK, results, pageable, totalCount);\n }\n \/\/ ......\n}<\/code><\/pre>\nrepository \uc5d0 \ucd94\uac00<\/h2>\n
\nOUTPUT \uc744 \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub2e4.
\n\uac80\uc0c9\uacb0\uacfc\uc5d0 totalCount \ub4f1 OUTPUT \ud574\uc57c \ud560 \ub370\uc774\ud0c0\ub97c \ucd94\uac00\ud558\uba74,
\n\ub370\uc774\ud0c0\ub97c \ubc1b\uc544 \uc62c \uc218 \uc788\ub2e4.<\/p>\nSELECT *, @totalCount as totalCount\nFROM \ud14c\uc774\ube14\uba85<\/code><\/pre>\n
\n\ubaa8\ub4e0 \ub17c\ub9ac \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 \ud504\ub85c\uc2dc\uc800\ub97c \ud638\ucda0\ud560 \uc218 \uc788\ub2e4.<\/p>\n@Repository\npublic interface OrderMasterRepository extends JpaRepository<OrderMaster, Integer> {\n\n Optional<OrderMaster> findByOrderserial(String orderserial);\n\n \/\/ \ub300\uad04\ud638 \uc55e\ub4a4\uc5d0 \uacf5\ubc31\ubb38\uc790\uac00 \uc788\uc5b4\uc57c \ud55c\ub2e4.(MySQL)\n @Query(value = "{ CALL db_order.dbo.usp_Ten_Order_GetOrderMasterListJpa(:pageNo, :pageSize, :userid, :orderserial) }", nativeQuery = true)\n List<OrderMaster> GetOrderMasterList(\n @Param("pageNo") Integer pageNo,\n @Param("pageSize") Integer pageSize,\n @Param("userid") String userid,\n @Param("orderserial") String orderserial);\n}<\/code><\/pre>\nrepository \ub9ac\ud134\uac12\uc774 Entity \uac00 \uc544\ub2d0 \uacbd\uc6b0<\/h2>\n
\n\uc544\ub798\ucc98\ub7fc interface \ub97c \ucd94\uac00\ud574 \uc900\ub2e4.<\/p>\npublic interface OrderMasterRepository extends JpaRepository<OrderMaster, Integer> {\n\n \/\/ MS-SQL\n @Query(value = "exec [db_order].[dbo].[usp_Refine_Order]", nativeQuery = true)\n ResponseOnly RefineOrders();\n\n public static interface ResponseOnly {\n String getResult();\n String getMessage();\n }\n}<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"