Spring Boot JPA 条件查询 + 分页 + 排序
常规方法(返回User数组)
- pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.8</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
- application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/example
spring.datasource.username=root
spring.datasource.password=123456
- User entity
package com.example.demo.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "user")
public class User {
@Id
private long id;
@Column(name = "name", length = 64)
private String name;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
- User repository
package com.example.demo.dao;
import com.example.demo.model.User;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface UserRepository extends JpaRepository<User, Long> {
List<User> findAllByNameLike(String name, Pageable pageable);
}
- User service
package com.example.demo.service;
import com.example.demo.dao.UserRepository;
import com.example.demo.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
private UserRepository userRepository;
public List<User> findAllByName(String name, int page, int size) {
//实现分页和排序
Pageable firstPage = PageRequest.of(page, size, Sort.by("id").descending());
return userRepository.findAllByNameLike("%" + name + "%", firstPage);
}
}
- User controller
package com.example.demo.controller;
import com.example.demo.model.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/api/")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/users")
public List<User> list(@RequestParam(value = "name") String name,
@RequestParam(value = "page") int page,
@RequestParam(value = "size") int size) {
return userService.findAllByName(name, page, size);
}
}
常规(返回User的Page)
- 同上
- 同上
- 同上
- User Repository
Page<User> findByNameContaining(@Param("name") String name, Pageable pageable);
- User Service
public Page<User> findAllByName(String name, int page, int size) {
Pageable firstPage = PageRequest.of(page, size, Sort.by("id").descending());
return userRepository.findByNameContaining(name, firstPage);
}
- 同上
Raw SQL(返回User数组)
- 同上
- 同上
- 同上
- User Repository
package com.example.demo.dao;
import com.example.demo.model.User;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM User u WHERE u.name like %:name% ORDER BY id ASC LIMIT :page, :size",
nativeQuery = true)
List<User> findAllByNameLike(@Param("name") String name, @Param("page") Integer page, @Param("size") Integer size);
}
- User Service
package com.example.demo.service;
import com.example.demo.dao.UserRepository;
import com.example.demo.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
private UserRepository userRepository;
public List<User> findAllByName(String name, int page, int size) {
return userRepository.findAllByNameLike(name, page, size);
}
}
- User Controller同上
Raw SQL(返回User的Page)
- 同上
- 同上
- 同上
- User Repository
@Query(value = "SELECT * FROM User u WHERE u.name like %:name%",
countQuery = "SELECT count(*) FROM User u WHERE u.name like %:name%",
nativeQuery = true)
Page<User> findByNameContaining(@Param("name") String name, Pageable pageable);
- User Service
public Page<User> findAllByName(String name, int page, int size) {
Pageable firstPage = PageRequest.of(page, size, Sort.by("id").descending());
return userRepository.findByNameContaining(name, firstPage);
}
- 同上
Response示例
User的数组:
[
{
"id": 8,
"name": "tom6"
},
{
"id": 7,
"name": "tom5"
},
{
"id": 6,
"name": "tom4"
},
{
"id": 5,
"name": "tom3"
}
]
User的Page:
{
"content": [
{
"id": 4,
"name": "tom2"
},
{
"id": 1,
"name": "tom"
}
],
"pageable": {
"sort": {
"empty": false,
"sorted": true,
"unsorted": false
},
"offset": 4,
"pageSize": 4,
"pageNumber": 1,
"paged": true,
"unpaged": false
},
"last": true,
"totalElements": 6,
"totalPages": 2,
"number": 1,
"size": 4,
"sort": {
"empty": false,
"sorted": true,
"unsorted": false
},
"first": false,
"numberOfElements": 2,
"empty": false
}
参考
https://www.baeldung.com/spring-data-jpa-pagination-sorting
https://www.baeldung.com/spring-data-jpa-query