mobynote · March 7, 2018 03:17 · Mar 7, 2018
diff --git a/DemoRepository b/DemoRepository
@@ -0,0 +1,65 @@
+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);
+    }
+}
diff --git a/DemoRepositoryTest b/DemoRepositoryTest
@@ -0,0 +1,17 @@
+@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));
+
+}