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,

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);
    }
}

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/