SpringBoot JPA使用Specification多表查询LEFT JOIN

雨点打透心脏的1/2处 2022-12-28 00:44 440阅读 0赞

### 1.Student ###

package com.frank.jpaSpecification.entity;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import javax.persistence.*;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 18:32
     * @Description: ${todo}
     */
    @Entity
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Table(name = "t_student")
    public class Student {
        @Id
        private Long id;
    
        private String name;
    
        private String mobile;
    
        private Integer age;
    
        @ManyToOne
        private Company company;
    }

### 2.Company ###

package com.frank.jpaSpecification.entity;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.Table;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 18:36
     * @Description: ${todo}
     */
    @Entity
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Table(name = "t_company")
    public class Company {
        @Id
        @GeneratedValue
        private Long id;
        private String name;
        private String code;
        private String address;
    }

### 3.StudentRepository ###

package com.frank.jpaSpecification.repository;
    
    import com.frank.jpaSpecification.entity.Student;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.repository.PagingAndSortingRepository;
    import org.springframework.stereotype.Component;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:21
     * @Description: ${todo}
     */
    @Component
    public interface StudentRepository extends PagingAndSortingRepository<Student, Integer>, JpaSpecificationExecutor<Student> {
    }

### 4.StudentService ###

package com.frank.jpaSpecification.service;
    
    import com.frank.jpaSpecification.entity.PageStudentRequest;
    import com.frank.jpaSpecification.entity.Student;
    import com.frank.jpaSpecification.repository.StudentRepository;
    import com.frank.jpaSpecification.specification.StudentSpecification;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.PageRequest;
    import org.springframework.stereotype.Service;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:22
     * @Description: ${todo}
     */
    @Service
    public class StudentService {
    
        @Autowired
        private StudentRepository studentRepository;
    
        @Autowired
        private StudentSpecification studentSpecification;
    
        public Page<Student> getStudentList(PageStudentRequest studentRequest) {
            PageRequest pageRequest = PageRequest.of(studentRequest.getPageNumber(), studentRequest.getPageSize());
            return studentRepository.findAll(studentSpecification.getStudentSpecification(studentRequest), pageRequest);
        }
    
    }

### 5.StudentSpecification ###

package com.frank.jpaSpecification.specification;
    
    import com.frank.jpaSpecification.entity.*;
    import org.apache.commons.lang.StringUtils;
    import org.springframework.data.jpa.domain.Specification;
    import org.springframework.stereotype.Component;
    import org.springframework.util.CollectionUtils;
    
    import javax.persistence.criteria.Join;
    import javax.persistence.criteria.JoinType;
    import javax.persistence.criteria.Predicate;
    import java.time.LocalDate;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:27
     * @Description: ${todo}
     */
    @Component
    public class StudentSpecification {
    
        /**
         * root 就是mobile实例  root.get("name") name是属性名 不是数据库字段名
         * @param paramRequest
         * @return
         * */
        public Specification<Student> getStudentSpecification(PageStudentRequest paramRequest) {
            return (root, criteriaQuery, criteriaBuilder) -> {
                // http://localhost:8080/student?companyName=北京&pageNumber=0&pageSize=5&name=明
                /*
                // 普通的or
                Predicate namePre = criteriaBuilder.like(root.get("name"), "%" + paramRequest.getName() + "%");
                Predicate companyPre = criteriaBuilder.like(root.get("company").get("name"), "%" + paramRequest.getCompanyName() + "%");
                return criteriaBuilder.or(namePre, companyPre);
                */
    
                // 使用左连接查询
                Join<Student, Company> companyJoin = root.join("company", JoinType.LEFT);
    
                Predicate namePre = criteriaBuilder.like(root.get("name"), "%" + paramRequest.getName() + "%");
                Predicate companyPre = criteriaBuilder.like(companyJoin.get("name"), "%" + paramRequest.getCompanyName() + "%");
    
                return criteriaBuilder.or(namePre, companyPre);
    
            };
        }
    
    }

### 6.PageStudentRequest 请求体 ###

package com.frank.jpaSpecification.entity;
    
    import lombok.Data;
    import org.springframework.format.annotation.DateTimeFormat;
    
    import java.time.LocalDate;
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:26
     * @Description: ${todo}
     */
    @Data
    public class PageStudentRequest {
        private Integer pageSize = 10;
        private Integer pageNumber = 1;
        private String name;
        private String companyName;
    }

### 7.StudentController ###

package com.frank.jpaSpecification.controller;
    
    import com.frank.jpaSpecification.entity.PageStudentRequest;
    import com.frank.jpaSpecification.entity.Student;
    import com.frank.jpaSpecification.service.StudentService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Page;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 19:02
     * @Description: ${todo}
     */
    @RestController
    @RequestMapping("/student")
    public class StudentController {
    
        @Autowired
        private StudentService studentService;
    
        @GetMapping
        public Page<Student> index(PageStudentRequest pageStudentRequest) {
            return studentService.getStudentList(pageStudentRequest);
        }
    
    }

### 8.测试 ###

[**http://localhost:8080/student?companyName=北京&pageNumber=0&pageSize=5&name=明**][http_localhost_8080_student_companyName_pageNumber_0_pageSize_5_name]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70][]

**student和company的sql**

-- --------------------------------------------------------
    -- 主机:                           127.0.0.1
    -- 服务器版本:                        5.6.40 - MySQL Community Server (GPL)
    -- 服务器操作系统:                      Win64
    -- HeidiSQL 版本:                  8.2.0.4675
    -- --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    -- 导出 test 的数据库结构
    CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */;
    USE `test`;
    
    
    -- 导出  表 test.t_company 结构
    CREATE TABLE IF NOT EXISTS `t_company` (
      `id` bigint(20) NOT NULL,
      `address` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      `code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    
    -- 正在导出表  test.t_company 的数据：~1 rows (大约)
    DELETE FROM `t_company`;
    /*!40000 ALTER TABLE `t_company` DISABLE KEYS */;
    INSERT INTO `t_company` (`id`, `address`, `code`, `name`) VALUES
    	(1, '重庆', '023', '重庆沙坪坝科技公司'),
    	(2, '成都', '028', '成都高新区有限公司'),
    	(3, '北京', '021', '北京朝阳区科技公司');
    /*!40000 ALTER TABLE `t_company` ENABLE KEYS */;
    
    
    -- 导出  表 test.t_student 结构
    CREATE TABLE IF NOT EXISTS `t_student` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `age` int(11) DEFAULT NULL,
      `mobile` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      `company_id` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `FK3k0qejwy2hdw7n0qauh6vfk7l` (`company_id`),
      CONSTRAINT `FK3k0qejwy2hdw7n0qauh6vfk7l` FOREIGN KEY (`company_id`) REFERENCES `t_company` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    
    -- 正在导出表  test.t_student 的数据：~0 rows (大约)
    DELETE FROM `t_student`;
    /*!40000 ALTER TABLE `t_student` DISABLE KEYS */;
    INSERT INTO `t_student` (`id`, `age`, `mobile`, `name`, `company_id`) VALUES
    	(1, 18, '18000000000', '小花', 1),
    	(2, 21, '18000000000', '小花', 2),
    	(3, 23, '18000000010', '小明', 2),
    	(4, 17, '18000000000', '小花', 3),
    	(5, 22, '18000000000', '小猪', 1),
    	(6, 25, '18000000055', '小花', 1),
    	(7, 26, '18000000000', '小六', 3),
    	(8, 20, '18000000785', '小花', 1),
    	(9, 19, '18000000000', '小菊', 2),
    	(10, 28, '18000004521', '小花', 1),
    	(11, 19, '18000000000', '小军', 1);
    /*!40000 ALTER TABLE `t_student` ENABLE KEYS */;
    /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

自此左连接查询完毕，左连接查询： 就是已左边的表为基准，先将左表查出的数据显示出来，再显示关联的右表中查询出的数据，没有关联数据则字段值以null展示。

[http_localhost_8080_student_companyName_pageNumber_0_pageSize_5_name]: http://localhost:8080/student?companyName=%E5%8C%97%E4%BA%AC&pageNumber=0&pageSize=5&name=%E6%98%8E
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70]: /images/20221120/fd3aa430cf8d4f35ad9a756c8e66c187.png