springboot学习-一个完整分页查询的例子

继续学习springboot 3.4.0，今天做一个完整的例子：

springboot3.4.0+JDBC API+spring data jdbc+H2 database+Thymeleaf,

https://start.spring.io/

1.配置数据库

H2 Database缺省是内存数据库，要手工改为文件数据看，一遍数据能长期保存。

application.properties配置文件甚至数据库的相关信息

spring.application.name=runnerq
spring.h2.console.enabled=true
spring.datasource.name=runnerq
spring.datasource.generate-unique-name=false
spring.datasource.url=jdbc:h2:file:./data/runnerq;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=sa
spring.datasource.password=
#start execute schema.sql
spring.sql.init.mode=always

配置后启动，就可以通过h2-console查看数据库了

当然这个RUN数据库是resource/schema.sql建立的，这个名字是约定的，不要写错了。

CREATE TABLE IF NOT EXISTS Run (
    id INT NOT NULL,
    title varchar(250) NOT NULL,
    started_on timestamp NOT NULL,
    completed_on timestamp NOT NULL,
    miles INT NOT NULL,
    location varchar(10) NOT NULL,
    version INT,
    PRIMARY KEY (id)
);

为啥最后有个version字段？ 说是spring data jdbc需要，也不知道啥作用。参考视频：

https://www.youtube.com/watch?v=31KTdfRH6nY&t=12s

这个例子基本也是在这个视频基础上改的。感谢dan(spring的公司broadcom员工,broadcom收购了VMWARE)。

2.创建实体类

实体类与数据库表字段一一对应，原来都是有@data这样的lombok注解，其实可以直接用record.

package dev.zzz.runnerq.entity;

import jakarta.validation.constraints.NotEmpty;
import jakarta.validation.constraints.Positive;
import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.Version;

import java.time.LocalDateTime;

public record Run(
        @Id
        Integer id,
        @NotEmpty
        String title,
        LocalDateTime startedOn,
        LocalDateTime completedOn,
        @Positive
        Integer miles,
        Location location,
        @Version
        Integer version
) {
}

啥时候用class,啥时候用record的？我还是不太了解，需要进一步学习。

3. 创建Repository接口

经过验证，分页查询和全量查询不能同时支持，所以创建了两个Repository

全量：

package dev.zzz.runnerq.repository;

import dev.zzz.runnerq.entity.Run;
import org.springframework.data.repository.ListCrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface RunRepository extends ListCrudRepository<Run,Integer> {
}

分页：

package dev.zzz.runnerq.repository;

import dev.zzz.runnerq.entity.Run;
import org.springframework.stereotype.Repository;
import org.springframework.data.repository.PagingAndSortingRepository;

@Repository
public interface PageRunRepository extends PagingAndSortingRepository<Run, Integer> {
}

定义接口就行了，不需要实现，这个确实很神奇。spring boot data帮助实现完成了。

2024.11.26早晨一想,完全没有比喻定义两个接口，搞成一个就行了，接口支持多继承：

package dev.zzz.runnerq.repository;

import dev.zzz.runnerq.entity.Run;
import org.springframework.data.repository.ListCrudRepository;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface AllRunRepository extends ListCrudRepository<Run,Integer>, PagingAndSortingRepository<Run, Integer> {
}

4. 定义Controller类

Controller也定义了两套，一个用于Rest返回json格式，一个用于view显示

REST：

package dev.zzz.runnerq.controller;

import dev.zzz.runnerq.entity.Run;
import dev.zzz.runnerq.repository.RunRepository;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class RunRestController {

    private final RunRepository runRepository;

    public RunRestController(RunRepository runRepository) {
        this.runRepository = runRepository;
    }

    @GetMapping("/hello")
    String home() {
        return "hello, Runnerq";
    }

    @GetMapping("/api/runs")
    List<Run> findAll() {
        return runRepository.findAll();
    }

}

VIEW：

package dev.zzz.runnerq.controller;

import dev.zzz.runnerq.entity.Run;
import dev.zzz.runnerq.repository.PageRunRepository;
import dev.zzz.runnerq.repository.RunRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

@Controller
public class RunViewController {
    private final PageRunRepository pageRunRepository;
    private final RunRepository runRepository;

    public RunViewController(PageRunRepository pageRunRepository, RunRepository runRepository) {
        this.pageRunRepository = pageRunRepository;
        this.runRepository = runRepository;
    }

    @GetMapping("/runs")
    public String getRuns(Model model) {
        List<Run> runs = runRepository.findAll();
        model.addAttribute("runs", runs);
        return "runs";
    }

    @GetMapping("/pageruns")
    public String getPageRuns(Model model,
                              @RequestParam(defaultValue = "0") int page,
                              @RequestParam(defaultValue = "5") int size) {
        Pageable pageable = PageRequest.of(page,size);
        Page<Run> runPage = pageRunRepository.findAll(pageable);
        model.addAttribute("runPage", runPage);
        return "pageruns";
    }
}

5. REST接口用swagger测试。

上次文章介绍了如何引入swagger 3,这里不赘述

使用真方便，验证REST接口不用启动postman了，直接swagger测试。

6. VIEW需要编写thymeleaf页面

thymeleaf也是两个，一个分页，一个不分页

不分页

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Run List</title>
</head>
<body>
<h1>Run List</h1>
<table border="1">
    <thead>
    <tr>
        <th>ID</th>
        <th>title</th>
        <th>startedOn</th>
        <th>location</th>
    </tr>
    </thead>
    <tbody>
    <tr th:each="run : ${runs}">
        <td th:text="${run.id}"></td>
        <td th:text="${run.title}"></td>
        <td th:text="${run.startedOn}"></td>
        <td th:text="${run.location}"></td>
    </tr>
    </tbody>
</table>
</body>
</html>

分页

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Run List</title>
</head>
<body>
<h1>Run List</h1>
    <table border="1">
        <thead>
        <tr>
            <th>ID</th>
            <th>title</th>
            <th>startedOn</th>
            <th>location</th>
        </tr>
        </thead>
        <tbody>
        <tr th:each="run : ${runPage.content}">
            <td th:text="${run.id}"></td>
            <td th:text="${run.title}"></td>
            <td th:text="${run.startedOn}"></td>
            <td th:text="${run.location}"></td>
        </tr>
        </tbody>
    </table>
    <div>
        <span th:text="'Page ' + (${runPage.number} + 1) + ' of ' + ${runPage.totalPages}"></span>
        <a th:href="@{/pageruns(page=${runPage.number - 1}, size=${runPage.size})}" th:if="${runPage.hasPrevious()}" th:text="'Previous'"></a>
        <a th:href="@{/pageruns(page=${runPage.number + 1}, size=${runPage.size})}" th:if="${runPage.hasNext()}" th:text="'Next'"></a>
    </div>
</body>
</html>

7. 效果

分页查询效果不错

完美！

springboot真是大大简化了开发，比dropwizard强太多了。