Created
March 7, 2018 03:17
-
-
Save mobynote/595b61d72a1a0363dc80b7eb785faef9 to your computer and use it in GitHub Desktop.
Use jdbcTemplate implement a pagination in spring
This file contains hidden or 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 hidden or 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