SpringBoot JPA使用Specification多表查询LEFT JOIN

最新推荐文章于 2024-09-04 21:32:42 发布

小石潭记丶

最新推荐文章于 2024-09-04 21:32:42 发布

阅读量1.4w

点赞数 8

分类专栏： JPA springboot

本文链接：https://blog.csdn.net/qq_33371766/article/details/111083968

版权

springboot 同时被 2 个专栏收录

100 篇文章 11 订阅

订阅专栏

JPA

8 篇文章 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=明

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展示。