我如何在Spring jdbcTemplate中实现分页 [英] how can i implement a pagination in spring jdbcTemplate
本文介绍了我如何在Spring jdbcTemplate中实现分页的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的以下实施方案
@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实现中,我列出了所有用户地址,试图以限制方式列出
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);
}
这是我的观点,
<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的新手,
I have hardcoded the pagination part, do any one have idea, how to do pagination. i am newbie to java jdbcTemplate,
推荐答案
只要您的数据库支持 LIMIT 和 OFFSET ,就可以完成此操作.
This can be done as long as your database supports LIMIT and OFFSET.
在此处中给出了一个示例.关键代码如下所示(您可以忽略流利的builder子句):
An example is given here. The critical code is shown below (you can ignore the fluent builder clauses):
@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);
}
}
这篇关于我如何在Spring jdbcTemplate中实现分页的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文