java 我如何在 spring jdbcTemplate 中实现分页

how can i implement a pagination in spring jdbcTemplate

提问by javailike

here is my following dao implementaion

这是我的以下 dao implementationaion

@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

在上面的dao实现中，我列出了所有用户地址，试图以限制列出

@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,

我已经对分页部分进行了硬编码，有没有人有想法，如何进行分页。我是 java jdbcTemplate 的新手，

回答by Erica Kane

This can be done as long as your database supports LIMITand OFFSET.

只要您的数据库支持LIMIT和OFFSET ，就可以做到这一点。

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

这里给出了一个例子。关键代码如下所示（您可以忽略 fluent builder 子句）：

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

回答by Isuru W

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.

我在寻找其他东西时遇到了这个问题，并注意到这个问题没有得到回答，所以想发布我的 2cents。您可以创建一个包含 Pagination 对象和 pageId 的包装器 (Request) 对象。

Request

要求

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

• 分页分页
• int pageId（任何与业务相关的数据/SQL 参数）
• 任何域对象

Pagination

分页

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

• int start（用于在 SQL 中设置 OFFSET 属性）
• int size（用于设置 SQL 中的 FETCH NEXT 属性）

回答by Artegon

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

您不必为分页创建自己的实现逻辑。使用 Spring 的PagedListHolder，它适用于分页目的且可配置。

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

在这里您可以看到一个示例实现：Spring Pagination Example。