{"id":4270,"date":"2024-07-26T14:42:15","date_gmt":"2024-07-26T07:42:15","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4270"},"modified":"2024-07-29T07:52:43","modified_gmt":"2024-07-29T00:52:43","slug":"java-sql-server-pagination","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/java-sql-server\/java-sql-server-pagination\/","title":{"rendered":"Java SQL Server: Pagination"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to break up a large data set into smaller pages in Java by executing a SELECT<\/code> statement with FETCH<\/code> and OFFSET<\/code> clauses.<\/p>\n\n\n\n

This tutorial begins where the Selecting Data from a Table in Java tutorial<\/a> left off.<\/p>\n\n\n\n

Pagination data #<\/a><\/h2>\n\n\n\n

The pagination technique allows you to break a large result set into smaller ones. In SQL Server, you can use the FETCH and OFFSET<\/a> clauses or ROW_NUMBER()<\/a> function to perform pagination.<\/p>\n\n\n\n

We’ll show you how to paginate data retrieved from the Authors<\/code> table using a SELECT<\/code> statement with the FETCH<\/code> and OFFSET<\/code> clauses.<\/p>\n\n\n\n

Step 1. Modify the AuthorDB<\/code> class and define a find()<\/code> method:<\/p>\n\n\n

public<\/span> List<Author> find<\/span>(int<\/span> limit, int<\/span> offset)<\/span> throws<\/span> DBException <\/span>{\n\n  var<\/span> sql = \"SELECT AuthorID, FirstName, LastName, BirthDate \"<\/span> +\n                  \"FROM Authors   \"<\/span> +\n                  \"ORDER BY AuthorID \"<\/span> +\n                  \"OFFSET ? ROWS FETCH FIRST ? ROWS ONLY\"<\/span>;\n\n  var<\/span> authors = new<\/span> ArrayList<Author>();\n\n  try<\/span> (var<\/span> statement = connection.prepareStatement(sql)) {\n    \/\/ Set values to parameters<\/span>\n    statement.setInt(1<\/span>, offset);\n    statement.setInt(2<\/span>, limit);\n\n    try<\/span> (var<\/span> resultset = statement.executeQuery()) {\n      while<\/span> (resultset.next()) {\n        authors.add(createAuthorFrom(resultset));\n      }\n    }\n\n    return<\/span> authors;\n\n  } catch<\/span>(SQLException e) {\n    throw<\/span> new<\/span> DBException(e.getMessage());\n  }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, define the find()<\/code> method that accepts two parameters limit<\/code> and offset<\/code>. The limit<\/code> specifies the maximum number of Author<\/code> objects to return and the offset<\/code> determines the number of  rows to skip before starting to retrieve the Author<\/code> objects:<\/p>\n\n\n
public<\/span> List<Author> find<\/span>(int<\/span> limit, int<\/span> offset)<\/span> throws<\/span> DBException <\/span>{<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
The find()<\/code> method returns a list of Author<\/code> objects and throws a DBException<\/code> if any error occurs while selecting data.<\/p>\n\n\n\n
Second, construct a SELECT<\/code> statement that retrieves data from the Authors<\/code> table with two parameters (offset <\/code>and limit<\/code>):<\/p>\n\n\n
var<\/span> sql = \"SELECT AuthorID, FirstName, LastName, BirthDate \"<\/span> +\n                  \"FROM Authors   \"<\/span> +\n                  \"ORDER BY AuthorID \"<\/span> +\n                  \"OFFSET ? ROWS FETCH FIRST ? ROWS ONLY\"<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, initialize an empty list of Author<\/code> objects:<\/p>\n\n\n
var<\/span> authors = new<\/span> ArrayList<Author>();<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, create a PreparedStatement<\/code> object with the SELECT<\/code> statement by calling the prepareStatement<\/code> method of the Connection<\/code> object:<\/p>\n\n\n
try<\/span> (var<\/span> statement = connection.prepareStatement(sql)) {<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, set the values to the statement:<\/p>\n\n\n
statement.setInt(1<\/span>, offset);\nstatement.setInt(2<\/span>, limit);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, execute the query, create an Author<\/code> object from each row of the result set, and add it to the authors<\/code> list:<\/p>\n\n\n
try<\/span> (var<\/span> resultset = statement.executeQuery()) {\n  while<\/span> (resultset.next()) {\n    authors.add(createAuthorFrom(resultset));\n  }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, return the authors<\/code> list:<\/p>\n\n\n
return<\/span> authors;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, throw a new DBException<\/code> if any error occurs while selecting data:<\/p>\n\n\n
} catch<\/span>(SQLException e) {\n  throw<\/span> new<\/span> DBException(e.getMessage());\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the main()<\/code> method of the Main<\/code> class to use the find()<\/code> method to get the first five authors from the Authors<\/code> table:<\/p>\n\n\n
import<\/span> java.sql.SQLException;\n\npublic<\/span> class<\/span> Main<\/span> <\/span>{\n    public<\/span> static<\/span> void<\/span> main<\/span>(String[] args)<\/span> <\/span>{\n        try<\/span> (var<\/span> connection = SQLServerConnection.connect()) {\n            var<\/span> authorDB = new<\/span> AuthorDB(connection);\n            \/\/ Find the first 5 authors<\/span>\n            var<\/span> authors = authorDB.find(5<\/span>, 0<\/span>);\n            for<\/span>(var<\/span> author: authors) {\n                System.out.println(author.getAuthorId() + \" - \"<\/span> + author.getFirstName() + \", \"<\/span> + author.getLastName());\n            }\n        } catch<\/span> (SQLException | DBException e) {\n            System.err.println(e.getMessage());\n        }\n    }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Run the Java program. It’ll show the following authors in the output:<\/p>\n\n\n
1<\/span> - John, Doe\n2<\/span> - Lisa, Rodriguez\n3<\/span> - Susan, Williams\n4<\/span> - Anthony, Martinez\n5<\/span> - Linda, Johnson<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to get the next five authors, you can change the offset<\/code> argument to 5 as follows:<\/p>\n\n\n
var<\/span> authors = authorDB.find(5<\/span>, 5<\/span>);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Rerun the Java program, you’ll get the following output:<\/p>\n\n\n
6<\/span> - Daniel, Hall\n7<\/span> - Thomas, Williams\n8<\/span> - Richard, Martin\n9<\/span> - Barbara, King\n10<\/span> - Jessica, Smith<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use a SELECT<\/code> statement with the OFFSET<\/code> and FETCH<\/code> clauses to implement the pagination feature.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t