springboot学习-分页/排序/多表查询的例子

最近喜欢上了springboot，真是个好的脚手架。今天继续学习分页/排序/多表查询等复杂功能。按步骤记录如下. 按步骤做的发现不可用，最终还是用的jdbctemplate解决。这也是一次经验。总计在最后。

1.maven依赖

首先从https://start.spring.io/ 选择需要的maven依赖

2. 配置好H2数据库

spring.h2.console.enabled=true  -- /h2-console 就可以访问数据库了。spring.datasource.name=user-profile -- 定义数据库名，或者说数据源名称。
spring.datasource.generate-unique-name=false --trueh会自动生成随机名称，false用定义的name

3. 数据库表创建并插入数据

约定创建表的脚本在src/main/resroucs/schema.sql

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    profile_id BIGINT
);

CREATE TABLE profiles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    address VARCHAR(255),
    phone_number VARCHAR(255)
);

添加数据是data.sql,要实现分页，数据要多一些，插入8条数据，准备5条分一页。

INSERT INTO profiles (address, phone_number) VALUES ('123 Main St', '555-1234');
INSERT INTO users (username, profile_id) VALUES ('john_doe', 1);
INSERT INTO profiles (address, phone_number) VALUES ('222 qian St', '555-2222');
INSERT INTO users (username, profile_id) VALUES ('qianer', 2);
INSERT INTO profiles (address, phone_number) VALUES ('333 zhang St', '555-3333');
INSERT INTO users (username, profile_id) VALUES ('zhangsan', 3);
INSERT INTO profiles (address, phone_number) VALUES ('444 li St', '555-4444');
INSERT INTO users (username, profile_id) VALUES ('lisi', 4);
INSERT INTO profiles (address, phone_number) VALUES ('555 wang St', '555-5555');
INSERT INTO users (username, profile_id) VALUES ('wangwu', 5);
INSERT INTO profiles (address, phone_number) VALUES ('666 zhao St', '555-6666');
INSERT INTO users (username, profile_id) VALUES ('zhaoliu', 6);
INSERT INTO profiles (address, phone_number) VALUES ('777 tian St', '555-7777');
INSERT INTO users (username, profile_id) VALUES ('tianqi', 7);
INSERT INTO profiles (address, phone_number) VALUES ('888 tian St', '555-8888');
INSERT INTO users (username, profile_id) VALUES ('gongba', 8);

4. 编写JAVA Entities

用户和档案个一个class

// User.java
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;

@Table("users")
public class User {
    @Id
    private Long id;
    private String username;
    private Long profileId; // Reference to Profile

    // getters and setters
}

// Profile.java
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;

@Table("profiles")
public class Profile {
    @Id
    private Long id;
    private String address;
    private String phoneNumber;

    // getters and setters
}

5. 创建一个DTO类用于jion result

DTO的意思是Data Transfer Object, 用于存放join的查询结果。

public class UserProfileDTO {
    private String username;
    private String address;
    private String phoneNumber;

    // constructor, getters, and setters
}

6. 创建一个Repository类，其中查询是定制方法

使用@Query定制查询，并增加pagination and sorting:

import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Page;
import java.util.List;

public interface UserRepository extends CrudRepository<User, Long> {

    @Query("SELECT u.username, p.address, p.phoneNumber " +
           "FROM users u JOIN profiles p ON u.profileId = p.id " +
           "ORDER BY p.id")
    Page<UserProfileDTO> findUsersWithProfiles(Pageable pageable);
}

7. 创建Service类

service类中其实可以做一些转换，这里简化，直接返回

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

@Service
public class UserProfileService {

    @Autowired
    private UserRepository userRepository;

    public Page<UserProfileDTO> getUsersWithProfiles(Pageable pageable) {
        return userRepository.findUsersWithProfiles(pageable);
    }
}

8. 创建Controller类暴露接口

目的就是expose the service

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserProfileController {

    @Autowired
    private UserProfileService userProfileService;

    @GetMapping("/users-with-profiles")
    public Page<UserProfileDTO> getUsersWithProfiles(
            @RequestParam int page,
            @RequestParam int size,
            @RequestParam String sortBy) {
        Pageable pageable = PageRequest.of(page, size, Sort.by(sortBy));
        return userProfileService.getUsersWithProfiles(pageable);
    }
}

这个返回的是一个json格式的对象。

如果和thymeleaf配合，需要修改一下返回值。

分页的原理有点难，还需要进一步分析研究。

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;

@Controller
public class UserProfileController {

    @Autowired
    private UserProfileService userProfileService;

    @GetMapping("/users-with-profiles")
    public String getUsersWithProfiles(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "id") String sortBy,
            Model model) {
        Page<UserProfileDTO> userProfilesPage = userProfileService.getUsersWithProfiles(PageRequest.of(page, size, Sort.by(sortBy)));
        model.addAttribute("userProfiles", userProfilesPage.getContent());
        model.addAttribute("page", page);
        model.addAttribute("size", size);
        model.addAttribute("sortBy", sortBy);
        model.addAttribute("totalPages", userProfilesPage.getTotalPages());
        return "users";
    }
}

9. 创建动态HTML

需要放在 src/main/resources/templates目录下，users.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title>Users with Profiles</title>
</head>
<body>
    <h1>Users with Profiles</h1>
    <table>
        <thead>
            <tr>
                <th>Username</th>
                <th>Address</th>
                <th>Phone Number</th>
            </tr>
        </thead>
        <tbody>
            <tr th:each="userProfile : ${userProfiles}">
                <td th:text="${userProfile.username}"></td>
                <td th:text="${userProfile.address}"></td>
                <td th:text="${userProfile.phoneNumber}"></td>
            </tr>
        </tbody>
    </table>
    <div>
        <a th:href="@{|/users-with-profiles?page=${page - 1}&size=${size}&sortBy=${sortBy}|}" th:if="${page > 0}">Previous</a>
        <a th:href="@{|/users-with-profiles?page=${page + 1}&size=${size}&sortBy=${sortBy}|}" th:if="${page < totalPages - 1}">Next</a>
    </div>
</body>
</html>

10. 解决错误，切换JdbcTemplate

上面看似完美的实现，运行起来就报错了。

the program run and report the exception:
Caused by: java.lang.UnsupportedOperationException: Page queries are not supported using string-based queries; Offending method: public abstract org.springframework.data.domain.Page dev.zzz.repository.UserRepository.findUsersWithProfiles(org.springframework.data.domain.Pageable)
	at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.<init>(StringBasedJdbcQuery.java:172) ~[spring-data-jdbc-3.4.0.jar:3.4.0]
	at org.springframework.data.jdbc.repository.support.JdbcQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JdbcQueryLookupStrategy.java:168) ~[spring-data-jdbc-3.4.0.jar:3.4.0]
	at org.springframework.data.jdbc.repository.support.JdbcQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JdbcQueryLookupStrategy.java:255) ~[spring-data-jdbc-3.4.0.jar:3.4.0]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:116) ~[spring-data-commons-3.4.0.jar:3.4.0]
	... 59 common frames omitted

原因是：The exception you’re encountering is due to the fact that Spring Data JDBC does not support pagination with string-based queries directly. To handle pagination and sorting with custom queries, you need to use a different approach. Here’s how you can achieve this:

Custom Repository Interface

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

public interface CustomUserRepository {
    Page<UserProfileDTO> findUsersWithProfiles(Pageable pageable);
}

Custom Repository Implementation

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.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class CustomUserRepositoryImpl implements CustomUserRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private final RowMapper<UserProfileDTO> rowMapper = (rs, rowNum) -> new UserProfileDTO(
            rs.getString("username"),
            rs.getString("address"),
            rs.getString("phoneNumber")
    );

    @Override
    public Page<UserProfileDTO> findUsersWithProfiles(Pageable pageable) {
        String query = "SELECT u.username, p.address, p.phoneNumber " +
                       "FROM users u JOIN profiles p ON u.profileId = p.id " +
                       "ORDER BY " + pageable.getSort().toString().replace(":", " ") +
                       " LIMIT " + pageable.getPageSize() +
                       " OFFSET " + pageable.getOffset();

        List<UserProfileDTO> userProfiles = jdbcTemplate.query(query, rowMapper);

        String countQuery = "SELECT COUNT(*) FROM users u JOIN profiles p ON u.profileId = p.id";
        Long total = jdbcTemplate.queryForObject(countQuery, Long.class);

        return new PageImpl<>(userProfiles, pageable, total);
    }
}

这部分代码是关键，里面有几个坑：

首先运行查询，提示：

org.h2.jdbc.JdbcSQLSyntaxErrorException: Ambiguous column name "ID"; SQL statement:
SELECT u.username, p.address, p.phoneNumber FROM users u JOIN profiles p ON u.profileId = p.id ORDER BY id  ASC LIMIT 5 OFFSET 0 [90059-232]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:644) ~[h2-2.3.232.jar:2.3.232]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) ~[h2-2.3.232.jar:2.3.232]
	at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.3.232.jar:2.3.232]
	at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.3.232.jar:2.3.232]
	at org.h2.expression.ExpressionColumn.mapColumn(ExpressionColumn.java:197) ~[h2-2.3.232.jar:2.3.232]
	at org.h2.expression.ExpressionColumn.mapColumns(ExpressionColumn.java:175) ~[h2-2.3.232.jar:2.3.232]

关联表，最好不要都叫ID，无法区分，改为：

"ORDER BY p." + pageable.getSort().toString().replace(":", " ") +

指定了profiles表的id

然后又提示错误：找不到列：phoneNumber,profileid，经检查对象中的名字和数据库不一样，数据库有下划线，jdbcTemplate是不允许隐式转换的，所以必须和表中的字段名一致。修改后就可以了。

Update the UserRepository Interface

import org.springframework.data.repository.CrudRepository;

public interface UserRepository extends CrudRepository<User, Long>, CustomUserRepository {
}

11. 界面不美观，bootstrap加上渲染

https://getbootstrap.com/docs/5.3/getting-started/introduction/

下面一段，不知道能否使用。

head中增加：

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">

<table class="table table-dark">

效果如下：