-
-
Save vmptk/87f485da8ba3d99d4e33db7574c67305 to your computer and use it in GitHub Desktop.
Use jdbcTemplate implement a pagination in spring
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package com.domain; | |
import com.domain.Module; | |
import org.springframework.beans.factory.annotation.Autowired; | |
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; | |
import java.util.List; | |
@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); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
@Test | |
public void should_return_demo_page() { | |
int count = 5; | |
int page = 1; | |
int size = 3; | |
int totalPages = (int) Math.ceil((double) count / size); | |
int contentSize = page + 1 < totalPages ? size : count - size * page; | |
save(count); | |
Pageable pageable = new PageRequest(page, size); | |
Page<Demo> dataPage = repository.findDemoByPage(pageable); | |
assertThat((int) dataPage.getTotalElements(), equalTo(count)); | |
assertThat(dataPage.getTotalPages(), equalTo(totalPages)); | |
assertThat(dataPage.getContent().size(), equalTo(contentSize)); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment