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 {@Idprivate 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 {@Idprivate 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 {@Autowiredprivate 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 {@Autowiredprivate 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 {@Autowiredprivate 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 {@Autowiredprivate JdbcTemplate jdbcTemplate;private final RowMapper<UserProfileDTO> rowMapper = (rs, rowNum) -> new UserProfileDTO(rs.getString("username"),rs.getString("address"),rs.getString("phoneNumber"));@Overridepublic 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">

效果如下：