spring boot jpa 子查询 springboot jpa 条件查询

转载

mob6454cc6dcf7f 2023-10-19 13:21:09

文章标签 spring boot jpa 子查询 spring java spring boot List 文章分类 spring boot 后端开发

Spring Data JPA实现简单条件查询

我们知道，只需定义一个继承JpaRepository接口的接口即可使用Spring Data JPA建立数据访问层。因此，自定义数据访问接口完全继承了JpaRepository的接口方法，但更重要的是，在自定义的数据访问接口中，可以根据查询关键字定义查询方法，这些查询方法符合它的命名规则，一般是根据持久化实体类的属性来命名。

在Spring Data JPA中，使用Top和First关键字限制查询结果数量。

下面学习Spring Boot Web应用中如何使用Spring Data JPA进行简单条件查询。

1-在MySQL的管理工具Navicat中创建数据库springbootjpa.当然也可以直接SQL代码创建，一样的。

spring boot jpa 子查询 springboot jpa 条件查询_java

2-使用Maven创建基于Thymeleaf和Spring Data JPA的Spring Boot Web应用,在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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>ch6_1</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <!--配置SpringBoot的核心启动器-->
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.4.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <!--添加starter模块-->
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!--JPA依赖模块-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!--添加MySQL依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.45</version>

        </dependency>
        <!-- 添加JDBC依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>


</project>

4-在全局配置文件中配置数据源配置信息，在src/main/java/resources目录下创建全局配置文件application.properties，在该文件中配置数据源，同时需要指定数据源的位置，否则会url错误，

server.port=8080
#数据源信息配置
spring.datasource.url=jdbc:mysql://localhost:3306/springbootjpa?characterEncoding=utf8&useSSL=false
#数据库用户名
spring.datasource.username = root
#数据库密码
spring.datasource.password = 123456
#数据库驱动
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#指定数据库类型
spring.jpa.database = MYSQL
#指定是否在日志中显示SQL语句
spring.jpa.show-sql = true
#指定自动创建，更新数据库表等配置
spring.jpa.hibernate.ddl-auto = update
#让控制器输出JSON字符串格式更美观
spring.jackson.serialization.indent-output = true
#上传文件时候，默认上传文件大小是1MB，max-file-size设置单个文件上传大小
spring.servlet.multipart.max-file-size=50MB
#默认总文件大小为10MB，max-request-size设置总上传文件的大小
spring.servlet.multipart.max-request-size=500MB

5-创建持久化实体类，在src/main/java下创建com.entity包，在该包中创建实体模型类。

import javax.persistence.*;
import java.io.Serializable;

/**
 * 创建持久化类，由持久化类映射产生数据表
 */
@Entity
@Table(name = "user_table")
public class MyUser implements Serializable {
    private static final long serialVersionUID = 1L ;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id ;
    private String uname ;
    private String usex ;
    private int age ;

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUsex() {
        return usex;
    }

    public void setUsex(String usex) {
        this.usex = usex;
    }
}

6-创建数据访问层，在src/main/java目录下创建com.repository包，在该包中创建UserReposotory接口，继承JpaRepository接口。

import com.entity.MyUser;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface UserRepository extends JpaRepository<MyUser, Integer> {
    public MyUser findByUname(String uname) ;
    public List<MyUser> findByUnameLike(String uname) ;
}

7-创建业务层，在src/main/java目录下创建com.service包，在该包中创建接口和实现类。

import com.entity.MyUser;

import java.util.List;

public interface UserService {
    public void saveAll() ;
    public List<MyUser>  findAll() ;
    public MyUser findByUname(String uname) ;
    public List<MyUser> findByUnameLike(String uname) ;
    public MyUser getOne(int id) ;
}

import com.entity.MyUser;
import com.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.ArrayList;
import java.util.List;

@Service

public class UserServiceImpl implements UserService {
    @Autowired
    private UserRepository userRepository ;
    @Override
    public void saveAll() {
        MyUser user1 = new MyUser() ;
        user1.setUname("王国栋");
        user1.setAge(100);
        user1.setUsex("男");
        MyUser user2 = new MyUser() ;
        user2.setUname("何卓忆");
        user2.setUsex("女") ;
        user2.setAge(18);
        MyUser user3 = new MyUser() ;
        user3.setUname("唐乃乔");
        user3.setAge(120);
        user3.setUsex("男");
        List<MyUser> users = new ArrayList<>() ;
        users.add(user1) ;
        users.add(user2) ;
        users.add(user3) ;
        userRepository.saveAll(users) ; //调用父接口中的方法，而不是接口中的方法
    }

    @Override
    public List<MyUser> findAll() {
        //调用父接口中的方法findAll()
        return userRepository.findAll();
    }

    @Override
    public MyUser findByUname(String uname) {
        return userRepository.findByUname(uname);
    }

    @Override
    public List<MyUser> findByUnameLike(String uname) {
        return userRepository.findByUnameLike("%" + uname + "%");
    }

    @Override
    public MyUser getOne(int id) {
        //调用父接口中的方法getOne
        return userRepository.getOne(id);
    }
}

8-创建控制类，在src/main/java目录下创建com.contoller包，在该包中创建控制器类。

import com.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
public class UserTestController {

    @Autowired
    private UserService userService ;

    @RequestMapping("/save")
    @ResponseBody
    public String save(){
        userService.saveAll();
        return "保存成功啦！！！" ;
    }
    @RequestMapping("/findByUname")
    public String findByUname(String uname, Model model){
        model.addAttribute("title" , "根据用户名查询一个用户") ;
        model.addAttribute("auser", userService.findByUname(uname)) ;
        return "showAuser" ;
    }
    @RequestMapping("/getOne")
    public String getOne( Model model){
        model.addAttribute("title", "根据用户id查询一个用户") ;
        model.addAttribute("auser", userService.getOne(1)) ;
        return "showAuser" ;
    }
    @RequestMapping("/findAll")
    public String findAll(Model model){
        model.addAttribute("title", "查询所有用户") ;
        model.addAttribute("allUsers", userService.findAll()) ;
        return "showAll" ;
    }
    @RequestMapping("/findByUnameLike")
    public String findByUnameLike(Model model){
        model.addAttribute("title", "根据用户名模糊查询所有用户") ;
        model.addAttribute("allUsers", userService.findByUnameLike("王")) ;
        return "showAll" ;
    }
}

9-整理脚本样式静态文件，在static目录下引入BootStrap框架

spring boot jpa 子查询 springboot jpa 条件查询_spring boot_02

10-创建View视图页面，在src/main/resources/templates目录下创建两个视图页面showAll.html和showAuser.html视图。

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <title>显示查询结果</title>
    <!--引入BootStrap框架-->
    <link rel = "stylesheet" th:href = "@{css/bootstrap.min.css}"/>
    <link rel = "stylesheet" th:href = "@{css/bootstrap-theme.min.css}"/>
</head>
<body>
<!--面板-->
<div class = "panel panel-primary">
    <div class = "panel-heading">
        <h3 class = "panel-title">Spring Data JPA 简单查询</h3>
    </div>
</div>
<!--容器-->
<div class = "container">
    <div class = "panel panel-primary">
        <div class = "panel-heading">
            <h3 class = "panel-title"><span th:text = "${title}"></span></h3>
        </div>
        <div class = "panel-body">
            <div class = "table table-responsive">
                <table class = "table table-bordered table-hover">
                    <tbody class = "text-center">
                    <tr th:each = "user:${allUsers}">
                        <td>
                            <span th:text = "${user.id}"></span>
                        </td>
                        <td>
                            <span th:text = "${user.uname}"></span>
                        </td>
                        <td>
                            <span th:text = "${user.usex}"></span>
                        </td>
                        <td>
                            <span th:text = "${user.age}"></span>
                        </td>
                    </tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
</body>
</html>

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <title>显示查询结果</title>
    <!--引入BootStrap框架-->
    <link rel = "stylesheet" th:href = "@{css/bootstrap.min.css}"/>
    <link rel = "stylesheet" th:href = "@{css/bootstrap-theme.min.css}"/>
</head>
<body>
<!--面板-->
<div class = "panel panel-primary">
    <div class = "panel-heading">
        <h3 class = "panel-title">Spring Data JPA 简单查询</h3>
    </div>
</div>
<!--容器-->
<div class = "container">
    <div class = "panel panel-primary">
        <div class = "panel-heading">
            <h3 class = "panel-title"><span th:text = "${title}"></span></h3>
        </div>
        <div class = "panel-body">
            <div class = "table table-responsive">
                <table class = "table table-bordered table-hover">
                    <tbody class = "text-center">
                    <tr>
                        <td>
                            <span th:text = "${auser.id}"></span>
                        </td>
                        <td>
                            <span th:text = "${auser.uname}"></span>
                        </td>
                        <td>
                            <span th:text = "${auser.usex}"></span>
                        </td>
                        <td>
                            <spam th:text = "${auser.age}"></spam>
                        </td>
                    </tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
</body>
</html>

11-在com包下创建启动类，并运行。

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;


@SpringBootApplication
public class TestApplication {
    public static void main(String[] args) {
        SpringApplication.run(TestApplication.class, args) ;
    }
}

12-访问如下四个地址：
http://localhost:8080/save http://localhost:8080/findAll
http://localhost:8080/getOne
http://localhost:8080/findByUnameLike

分别实现保存数据到数据库，查询数据表的所有数据，根据用户id查询数据，根据用户名模糊查询数据。

13-运行结果

spring boot jpa 子查询 springboot jpa 条件查询_spring boot_03