how can i implement a pagination in spring jdbcTemplate

Question

here is my following dao implementaion

@Override
    public List<UserAddress> getAddresses(int pageid,int total) {

        String sql = "select * FROM user_addresses order by id desc limit "+(pageid-1)+","+total;
        List<UserAddress> userAddresses  = jdbcTemplate.query(sql, new RowMapper<UserAddress>() {
            @Override
            public UserSessionLog mapRow(ResultSet rs, int rowNum) throws SQLException {
                UserAddress userAdd = new UserAddress();
                userAdd.setId(rs.getInt("id"));
                userAdd.setId(rs.getString("city"));
                return userSession;
            }
        });
        return userAddresses;
    }

in the above dao implementaion, i list all the user addresses, trying to list with limit

@RequestMapping("/userAddresses/{pageid}")
       public ModelAndView userAddresses(@PathVariable int pageid) {
        int total=5;  
        if(pageid==1){}  
        else{  
            pageid=(pageid-1)*total+1;  
        }  
         List<UserAddress> listAddresses = userAddressFacade.getAddresses(pageid,total);
         return new ModelAndView("userAddresses", "listAddresses", listAddresses);
    }

this is my view part,

<table class="table table-condensed">
        <thead>
            <tr>
                <th>Address1</th>
                <th>City</th>
            </tr>
        </thead>
        <tbody>
            <c:if test="${not empty addresses}">
                <c:forEach var="address" items="${addresses}">
                    <tr>
                        <td>${address.address1}</td>
                        <td>${address.city}</td>
                    </tr>
                </c:forEach>
            </c:if>
        </tbody>
    </table>

     <br/>  
   <a href="/pro/userAddress/1">1</a>   
   <a href="/pro/userAddress/2">2</a>   
   <a href="/pro/userAddress/3">3</a>  

I have hardcoded the pagination part, do any one have idea, how to do pagination. i am newbie to java jdbcTemplate,

Portion

Answer 1

This can be done as long as your database supports LIMIT and OFFSET.

An example is given here. The critical code is shown below (you can ignore the fluent builder clauses):

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class DemoRepository {
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public DemoRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<Demo> findDemo() {
        String querySql = "SELECT name, action, operator, operated_at " +
                "FROM auditing " +
                "WHERE module = ?";
        return jdbcTemplate.query(querySql, new Object[]{Module.ADMIN_OPERATOR.getModule()}, (rs, rowNum) ->
                Demo.builder()
                        .rowNum(rowNum)
                        .operatedAt(rs.getTimestamp("operated_at").toLocalDateTime())
                        .operator(rs.getString("operator"))
                        .action(rs.getString("action"))
                        .name(rs.getString("name"))
                        .build()
        );
    }

    public Page<Demo> findDemoByPage(Pageable pageable) {
        String rowCountSql = "SELECT count(1) AS row_count " +
                "FROM auditing " +
                "WHERE module = ? ";
        int total =
                jdbcTemplate.queryForObject(
                        rowCountSql,
                        new Object[]{Module.ADMIN_OPERATOR.getModule()}, (rs, rowNum) -> rs.getInt(1)
                );

        String querySql = "SELECT name, action, operator, operated_at " +
                "FROM auditing " +
                "WHERE module = ? " +
                "LIMIT " + pageable.getPageSize() + " " +
                "OFFSET " + pageable.getOffset();
        List<Demo> demos = jdbcTemplate.query(
                querySql,
                new Object[]{Module.ADMIN_OPERATOR.getModule()}, (rs, rowNum) -> Demo.builder()
                        .rowNum(rowNum)
                        .operatedAt(rs.getTimestamp("operated_at").toLocalDateTime())
                        .operator(rs.getString("operator"))
                        .action(rs.getString("action"))
                        .name(rs.getString("name"))
                        .build()
        );

        return new PageImpl<>(demos, pageable, total);
    }
}

Jeanne

very useful explanation at your Gist. – Briseno

Not my gist, I just posted the link. Glad it was helpful. – Jeanne

I'm just curious, what happens if someone intervenes in between those two queries inside the findDemoByPage method and deletes or adds some rows in the auditing table. If there is such possibility, shouldn't we warp the findDemoByPage method in a transaction with SERIALIZABLE isolation level to avoid phantom reads? – Kootenay

As always it depends on your use case but that would certainly work. – Jeanne

For easier understanding--- We are getting the total first, then the actual list with limit and offset set. And at the end creating a page from the above 2 queries! – Turbine

Answer 2

I agree with @Erica Kane for use of LIMIT and OFFSET.

However, If Database is not supporting LIMIT and OFFSET then you can use ROW_NUMBER()

for example -
SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY id) as RRN FROM user_addresses as T1 ) WHERE RRN between :start and :end; :start and :end you can give whatever number you wish to fetch result. 1 to 100 etc.
If Total rows are less than end number then it will simply return whatever rows present.

Some of the best link I found regarding ROW_NUMBER() with great explanation-

https://blog.sqlauthority.com/2011/08/12/sql-server-tips-from-the-sql-joes-2-pros-development-series-ranking-functions-rank-dense_rank-and-row_number-day-12-of-35/

https://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/

https://blog.sqlauthority.com/2008/03/12/sql-server-2005-find-nth-highest-record-from-database-table-using-ranking-function-row_number/

https://blog.sqlauthority.com/2015/09/03/sql-server-whats-the-difference-between-row_number-rank-and-dense_rank-notes-from-the-field-096/

Aetiology

Answer 3

I came across this while I was searching for something else, and noticed this has not been answered, so thought to post my 2cents. You can create a wrapper (Request) object containing Pagination object and pageId.

Request

• Pagination pagination
• int pageId (any business related data/ SQL parameter)
• ANY DOMAIN OBJECT

Pagination

• int start (Use to set OFFSET property in the SQL)
• int size (Use to set the FETCH NEXT property in the SQL)

Dioptric

Answer 4

You don't have to create own implementation logic for pagination. Use Spring's PagedListHolder, it's suitable and configurable for pagination purposes.

Here you can see an example implementation: Spring Pagination Example.

Philbrick

There is a small problem with your suggestion. The question is how to implement pagination of database queries which implies that precisely what we want to avoid is having to retrieve large amounts of data from the database, through the network and store them in memory when we can only use a small portion of that. Your approach however is to paginate collections in memory, more specifically lists. But the idea is precisely not having to create those lists. And so, unless I'm missing something here, I'm afraid this solution proposal is oxymoronic. – Chevron

Original question is about "pagination" and for this purpose is PagedListHolder designed, isn't it? – Philbrick

To certain extend, yes. But the question is not solely about that, is it? It says pagination with JDBCTemplate, which has the implication that this is pagination of database queries. If your JDBCTemplate emits a query that brings 5000 records, it would be more efficient to paginate at database level, than bringing 5000 records over the network, store them in memory to just use the PageListHolder to return the first 100, and then repeat it all over again when the user requests the second page, don't you think? Not to mention the incredible amount of resources being wasted if we did it that way. – Chevron

You're totally true, od course, handler has own limitations. But original question is about "pagination" the result from JDBCTemplate (no mention about requirements you mentioned) handler is designed for this purpose, it could be one of corect answers. So you're true but you go IMHO behind the original question... – Philbrick

I suppose we have already taken the discussion beyond necessary, but just to clarify, you can see that the original question has database query in it where you can see the OP is trying to paginate at database level limiting the results we get select * FROM user_addresses order by id desc limit "+(pageid-1)+". The OP's question is precisely about how can we do this correctly with JDBCTemplate. So, we cannot simply overlook that in whatever answer we give. – Chevron

@user1315357, as Edwin suggested i am looking for the pagination idea, that gives me records with limit. – Portion

@Portion - I totally understand, good luck and share the result you'll find, please. – Philbrick

i don't even know why PagedListHolder even exists. why shouldn't it be removed? – Deconsecrate