springboot + mybatis 페이징처리

Question

controller@GetMapping("/post/listForm/{userId}")public String list(@PathVariable Integer userId, @RequestParam(name="page", required = false) Integer page, Model model, @RequestParam(name="keyword", required = false) String keyword) {User principal = (User) session.getAttribute("principal");if (page == null) {page = 0;}Integer startNum = page * 5;if (keyword == null || keyword.isEmpty()) {PagingRespDto paging = postRepository.paging(page, userId, null);paging.makeBlockInfo();model.addAttribute("postCount", postRepository.postCount(userId, null)); // 전체게시글 개수model.addAttribute("paging", paging); // 페이징model.addAttribute("postList",postRepository.findAllPost(userId,null, startNum));model.addAttribute("categoryList", categoryRepository.findByUserId(userId));model.addAttribute("user", userRepository.findById(userId));if (principal != null) {model.addAttribute("userImg", userRepository.findById(principal.getUserId()));}}if (principal != null) {List postList = postRepository.findAllPost(principal.getUserId(), keyword,startNum);model.addAttribute("postList", postList); // 블로그 전체게시글PagingRespDto paging = postRepository.paging(page, userId, keyword);paging.makeBlockInfoByPostAll(keyword);model.addAttribute("postCount", postRepository.postCount(userId, keyword)); // 전체게시글 개수model.addAttribute("paging", paging); // 페이징model.addAttribute("categoryList", categoryRepository.findByUserId(userId)); // 사이드바 카테고리 이동 => 공통model.addAttribute("user", userRepository.findById(userId));model.addAttribute("userImg", userRepository.findById(principal.getUserId()));}else {List postList = postRepository.findAllPost(userId, keyword,startNum);model.addAttribute("postList", postList); // 블로그 전체게시글PagingRespDto paging = postRepository.paging(page, userId, keyword);paging.makeBlockInfoByPostAll(keyword);model.addAttribute("postCount", postRepository.postCount(userId, keyword)); // 전체게시글 개수model.addAttribute("paging", paging); // 페이징model.addAttribute("categoryList", categoryRepository.findByUserId(userId)); // 사이드바 카테고리 이동 => 공통}return "/post/listForm";}xmlrepositorypublic PagingRespDto paging(@Param("page") Integer page, @Param("userId") Integer userId, @Param("keyword") String keyword);PagingRespDtopackage shop.mtcoding.tstory.dto.paging;import lombok.Getter;import lombok.Setter;@Setter@Getterpublic class PagingRespDto {private Integer blockCount; // 상수 한페이지에 페이지 넘수 개수(5) 1-5, 6-10private Integer currentBlock;private Integer startPageNum;private Integer lastPageNum;private Integer totalCount;private Integer totalPage;private Integer currentPage;private boolean isLast;private boolean isFirst;private String keyword;public void makeBlockInfo() {this.blockCount = 5;this.currentBlock = currentPage / blockCount;this.startPageNum = 1 + blockCount * currentBlock; // 1this.lastPageNum = 5 + blockCount * currentBlock; // 5if (totalPage < lastPageNum) {this.lastPageNum = totalPage;}}public void makeBlockInfoByPostAll(String keyword) {this.keyword = keyword;this.blockCount = 5;this.currentBlock = currentPage / blockCount;this.startPageNum = 1 + blockCount * currentBlock; // 1this.lastPageNum = 5 + blockCount * currentBlock; // 5if (totalPage < lastPageNum) {this.lastPageNum = totalPage;}}public void makeBlockInfoByCategoryPostAll(String keyword) {this.keyword = keyword;this.blockCount = 5;this.currentBlock = currentPage / blockCount;this.startPageNum = 1 + blockCount * currentBlock; // 1this.lastPageNum = 5 + blockCount * currentBlock; // 5if (totalPage < lastPageNum) {this.lastPageNum = totalPage;}}} 인데 왜 There was an unexpected error (type=Internal Server Error, status=500).### Error querying database. Cause: org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "CURRENTPAGE"; SQL statement: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p [50004-214] ### The error may exist in file [C:\Users\shim5\OneDrive\바탕 화면\Springboot-Mybatis-Final-Tstory\bin\main\mapper\post.xml] ### The error may involve shop.mtcoding.tstory.model.post.PostRepository.paging ### The error occurred while executing a query ### SQL: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p ### Cause: org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "CURRENTPAGE"; SQL statement: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p [50004-214] ; uncategorized SQLException; SQL state [HY004]; error code [50004]; Unknown data type: "CURRENTPAGE"; SQL statement: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p [50004-214]; nested exception is org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "CURRENTPAGE"; SQL statement: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p [50004-214]org.springframework.jdbc.UncategorizedSQLException: ### Error querying database. Cause: org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "CURRENTPAGE"; SQL statement: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p [50004-214] ### The error may exist in file [C:\Users\shim5\OneDrive\바탕 화면\Springboot-Mybatis-Final-Tstory\bin\main\mapper\post.xml] ### The error may involve shop.mtcoding.tstory.model.post.PostRepository.paging ### The error occurred while executing a query ### SQL: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p ### Cause: org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "CURRENTPAGE"; SQL statement: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p [50004-214] ; uncategorized SQLException; SQL state [HY004]; error code [50004]; Unknown data type: "CURRENTPAGE"; SQL statement: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p [50004-214]; nested exception is org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "CURRENTPAGE"; SQL statement: SELECT p.totalCount, p.totalPage, p.currentPage, (CASE currentPage WHEN 0 THEN 1 ELSE 0 END) AS first, (CASE currentPage WHEN totalPage-1 THEN 1 ELSE 0 END) AS last FROM ( SELECT COUNT() AS totalCount, CEILING(COUNT()/5) AS totalPage, ? AS currentPage, 0 AS first, 0 AS last FROM post_tb WHERE user_id = ? ) p [50004-214]스프링부트를 독학하고 있습니다 페이징 처리 중 이런 오류가 발생합니다 오류 수정 방법 알려 주실 수 있을까요?

포기해.. · Accepted Answer

public PagingRespDto paging(@Param("page") Integer page, @Param("userId") Integer userId, @Param("keyword") String keyword);PostRepository파일의 paging 함수의 파라미터를 PageReqDto 로 바꾸고 controller도 postRepository.paging(page, userId, keyword); <-- PageReqDto 로 바꿔야죠객체를 파라미터로 넘기도록 수정해서 돌려보세요

Z6 · Answer

(CASE p.currentPage WHEN 0 THEN 1 ELSE 0 END) AS first,(CASE p.currentPage WHEN p.totalPage-1 THEN 1 ELSE 0 END) AS last인거아닐까요? 저기에는 p를 안쓰신 이유가 따로 있으신가요?저도 초보라서 질문해봅니다.

포기해.. · Answer

<select id="paging" resultType="shop.mtcoding.tstory.dto.paging.PagingRespDto">파라미터 타입이 명시가 안되있네요

포기해.. · Answer

파라미터가 여러개인 경우 객체를 넘겨서 그 객채의 변수값을 참조해야죠. update Notice SET title = #{title}, writerId = #{writerId}, content = #{content}, hit = ${hit}, pub = #{pub}, files = #{files} where id = #{id}

springboot + mybatis 페이징처리

4개의 답변

기술 목록