如何在Spring JdbcTemplate中实现分页功能？

Question

以下是我的DAO实现：

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

在上述的 DAO 实现中，我列出了所有用户地址，尝试使用 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);
    }

这是我的视图部分。

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

我已经在代码中硬编码了分页部分，有没有人有想法，如何实现动态分页。我是java jdbcTemplate的新手。

- javailike

Answer 1

只要您的数据库支持LIMIT和OFFSET，就可以实现这一点。 这里提供了一个示例（点击此处查看）。 关键代码如下所示（您可以忽略流畅构建器子句）：

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

- Erica Kane

---

非常有用的解释在您的Gist上。 - catch23

不是我的代码片段，我只是贴了链接。很高兴它有帮助。 - Erica Kane

我只是好奇，如果有人在findDemoByPage方法内部的这两个查询之间进行干预并删除或添加了auditing表中的某些行，会发生什么。如果存在这种可能性，难道我们不应该将findDemoByPage方法包装在一个具有SERIALIZABLE隔离级别的事务中，以避免幻读吗？ - escudero380

一如既往，这取决于您的使用情况，但那肯定可以工作。 - Erica Kane

为了更容易理解---我们首先获取总数，然后设置限制和偏移量来获取实际列表。最后，根据以上两个查询创建一个页面！ - undefined

Answer 2

我同意@Erica Kane使用LIMIT和OFFSET。

然而，如果数据库不支持LIMIT和OFFSET，则可以使用ROW_NUMBER()

例如 - SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY id) as RRN FROM user_addresses as T1 ) WHERE RRN between :start and :end; :start和:end您可以给出任何数字以获取结果。 1到100等。 如果总行数少于结束号码，则会简单地返回任何存在的行。

以下是我发现的关于ROW_NUMBER()的一些最佳链接，其中包含了很好的解释-

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/

- Pratidnya

Answer 3

我在寻找其他东西时偶然发现了这个问题，并注意到它还没有得到解答，所以想要发表我的意见。

您可以创建一个包含Pagination对象和pageId的包装器（Request）对象。

Request

• Pagination pagination
• int pageId（任何业务相关数据/ SQL参数）
• 任何领域对象

Pagination

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

- Isuru W

Answer 4

在分页方面，您无需创建自己的实现逻辑。使用Spring的PagedListHolder，它非常适用和可配置。

这里有一个示例实现：Spring Pagination Example。

- Artegon

---

你的建议有一个小问题。问题在于如何实现数据库查询的分页，这意味着我们要避免从数据库中检索大量数据，经过网络并将它们存储在内存中，当我们只能使用其中的一小部分时。你的方法是在内存中对集合进行分页，更具体地说是列表。但是，这个想法恰恰是不需要创建这些列表。因此，除非我漏掉了什么，否则恐怕这个解决方案提议是矛盾的。 - Edwin Dalorzo

在某种程度上，是的。但问题不仅仅是这个吧，是吗？它提到了使用 JDBCTemplate 进行分页，这意味着这是对数据库查询进行分页。如果你的 JDBCTemplate 发出一个带有5000条记录的查询，那么在数据库级别进行分页比将5000条记录通过网络传输、存储在内存中只用 PageListHolder 返回前100条，然后当用户请求第二页时再重复这个过程更高效，不是吗？更不用说以这种方式做会浪费大量资源了。 - Edwin Dalorzo

你说得完全正确，当然，处理程序有自己的限制。但是原始问题是关于从JDBCTemplate分页结果（没有提到你提到的要求）的“分页”，处理程序是为此目的而设计的，它可能是正确答案之一。所以你是对的，但我认为你超出了原始问题的范围... - Artegon

我想我们已经超出必要的讨论范围了，但为了澄清一下，你可以看到原始问题中有数据库查询语句，其中你可以看到OP正在尝试在数据库级别上进行分页，限制我们获取的结果 select * FROM user_addresses order by id desc limit "+(pageid-1)+"。OP的问题确切地是关于如何在JDBCTemplate中正确地执行此操作。因此，在我们提供任何答案时，我们不能简单地忽略它。 - Edwin Dalorzo

@user1315357，正如Edwin建议的那样，我正在寻找分页的想法，以便给我带来有限制的记录。 - javailike