{"id":27475,"date":"2024-03-16T08:32:33","date_gmt":"2024-03-16T08:32:33","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/"},"modified":"2024-03-22T11:07:40","modified_gmt":"2024-03-22T11:07:40","slug":"how-to-solve-the-pagination-issue-in-shardingjdbc","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/","title":{"rendered":"How to solve the pagination issue in shardingjdbc?"},"content":{"rendered":"<p>Sharding-JDBC is a Java-based distributed database middleware that helps developers quickly implement database sharding and provide pagination functionality.<\/p>\n<p>When using Sharding-JDBC for pagination, you can achieve pagination queries by setting pagination parameters. The specific steps are as follows:<\/p>\n<ol>\n<li>First, configure the pagination plugin in the Sharding-JDBC configuration file. You can use the ShardingSphere-provided ShardingPaginationInterceptor plugin, which enables physical pagination functionality.<\/li>\n<li>Use PageHelper in the code for pagination queries. PageHelper is a pagination plugin provided by MyBatis, which can intercept query statements and handle pagination.<\/li>\n<\/ol>\n<p>Here is an example code using Sharding-JDBC for pagination.<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">public<\/span> <span class=\"hljs-keyword\">class<\/span> <span class=\"hljs-title class_\">PaginationExample<\/span> {\r\n\r\n    <span class=\"hljs-keyword\">public<\/span> <span class=\"hljs-keyword\">static<\/span> <span class=\"hljs-keyword\">void<\/span> <span class=\"hljs-title function_\">main<\/span><span class=\"hljs-params\">(String[] args)<\/span> {\r\n        <span class=\"hljs-comment\">\/\/ \u8bbe\u7f6e\u5206\u9875\u53c2\u6570<\/span>\r\n        <span class=\"hljs-type\">int<\/span> <span class=\"hljs-variable\">pageNum<\/span> <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-number\">1<\/span>; <span class=\"hljs-comment\">\/\/ \u5f53\u524d\u9875\u7801<\/span>\r\n        <span class=\"hljs-type\">int<\/span> <span class=\"hljs-variable\">pageSize<\/span> <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-number\">10<\/span>; <span class=\"hljs-comment\">\/\/ \u6bcf\u9875\u663e\u793a\u7684\u8bb0\u5f55\u6570<\/span>\r\n        \r\n        <span class=\"hljs-comment\">\/\/ \u4f7f\u7528PageHelper\u8fdb\u884c\u5206\u9875<\/span>\r\n        PageHelper.startPage(pageNum, pageSize);\r\n        \r\n        <span class=\"hljs-comment\">\/\/ \u6267\u884c\u67e5\u8be2\u64cd\u4f5c<\/span>\r\n        List&lt;User&gt; userList = userDao.selectUserList();\r\n        \r\n        <span class=\"hljs-comment\">\/\/ \u83b7\u53d6\u5206\u9875\u7ed3\u679c<\/span>\r\n        PageInfo&lt;User&gt; pageInfo = <span class=\"hljs-keyword\">new<\/span> <span class=\"hljs-title class_\">PageInfo<\/span>&lt;&gt;(userList);\r\n        \r\n        <span class=\"hljs-comment\">\/\/ \u8f93\u51fa\u7ed3\u679c<\/span>\r\n        System.out.println(<span class=\"hljs-string\">\"\u603b\u8bb0\u5f55\u6570\uff1a\"<\/span> + pageInfo.getTotal());\r\n        System.out.println(<span class=\"hljs-string\">\"\u603b\u9875\u6570\uff1a\"<\/span> + pageInfo.getPages());\r\n        System.out.println(<span class=\"hljs-string\">\"\u5f53\u524d\u9875\u7801\uff1a\"<\/span> + pageInfo.getPageNum());\r\n        System.out.println(<span class=\"hljs-string\">\"\u6bcf\u9875\u8bb0\u5f55\u6570\uff1a\"<\/span> + pageInfo.getPageSize());\r\n        System.out.println(<span class=\"hljs-string\">\"\u67e5\u8be2\u7ed3\u679c\uff1a\"<\/span> + pageInfo.getList());\r\n    }\r\n}\r\n<\/code><\/pre>\n<p>The method PageHelper.startPage(pageNum, pageSize) in the example code above intercepts and processes subsequent query operations for pagination. The pagination result information can be obtained through the PageInfo object.<\/p>\n<p>It&#8217;s important to note that due to Sharding-JDBC&#8217;s sharding based on physical partitions, there may be limitations when conducting paginated queries. For example, if you need to retrieve the top N records from all shards, you may need to query each shard individually and then merge the results. In this scenario, you can use the BroadcastTableRule provided by Sharding-JDBC to address this issue.<\/p>\n<p>In conclusion, using Sharding-JDBC for pagination queries can be achieved by setting pagination parameters and utilizing pagination plugins. When performing sharded queries, it is essential to be aware of certain limitations and choose the appropriate solution based on the specific circumstances.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sharding-JDBC is a Java-based distributed database middleware that helps developers quickly implement database sharding and provide pagination functionality. When using Sharding-JDBC for pagination, you can achieve pagination queries by setting pagination parameters. The specific steps are as follows: First, configure the pagination plugin in the Sharding-JDBC configuration file. You can use the ShardingSphere-provided ShardingPaginationInterceptor plugin, [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-27475","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to solve the pagination issue in shardingjdbc? - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to solve the pagination issue in shardingjdbc?\" \/>\n<meta property=\"og:description\" content=\"Sharding-JDBC is a Java-based distributed database middleware that helps developers quickly implement database sharding and provide pagination functionality. When using Sharding-JDBC for pagination, you can achieve pagination queries by setting pagination parameters. The specific steps are as follows: First, configure the pagination plugin in the Sharding-JDBC configuration file. You can use the ShardingSphere-provided ShardingPaginationInterceptor plugin, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-16T08:32:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-22T11:07:40+00:00\" \/>\n<meta name=\"author\" content=\"William Carter\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"William Carter\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/\"},\"author\":{\"name\":\"William Carter\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0\"},\"headline\":\"How to solve the pagination issue in shardingjdbc?\",\"datePublished\":\"2024-03-16T08:32:33+00:00\",\"dateModified\":\"2024-03-22T11:07:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/\"},\"wordCount\":237,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/\",\"name\":\"How to solve the pagination issue in shardingjdbc? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-16T08:32:33+00:00\",\"dateModified\":\"2024-03-22T11:07:40+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to solve the pagination issue in shardingjdbc?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0\",\"name\":\"William Carter\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g\",\"caption\":\"William Carter\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/williamcarter\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to solve the pagination issue in shardingjdbc? - Blog - Silicon Cloud","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/","og_locale":"en_US","og_type":"article","og_title":"How to solve the pagination issue in shardingjdbc?","og_description":"Sharding-JDBC is a Java-based distributed database middleware that helps developers quickly implement database sharding and provide pagination functionality. When using Sharding-JDBC for pagination, you can achieve pagination queries by setting pagination parameters. The specific steps are as follows: First, configure the pagination plugin in the Sharding-JDBC configuration file. You can use the ShardingSphere-provided ShardingPaginationInterceptor plugin, [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-16T08:32:33+00:00","article_modified_time":"2024-03-22T11:07:40+00:00","author":"William Carter","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"William Carter","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/"},"author":{"name":"William Carter","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0"},"headline":"How to solve the pagination issue in shardingjdbc?","datePublished":"2024-03-16T08:32:33+00:00","dateModified":"2024-03-22T11:07:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/"},"wordCount":237,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/","name":"How to solve the pagination issue in shardingjdbc? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-16T08:32:33+00:00","dateModified":"2024-03-22T11:07:40+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-solve-the-pagination-issue-in-shardingjdbc\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to solve the pagination issue in shardingjdbc?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0","name":"William Carter","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g","caption":"William Carter"},"url":"https:\/\/www.silicloud.com\/blog\/author\/williamcarter\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/27475","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=27475"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/27475\/revisions"}],"predecessor-version":[{"id":61708,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/27475\/revisions\/61708"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=27475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=27475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=27475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}