Criteria API结合Specification动态查询主表和复合主键从表数据？

Question

Criteria API 实现主表和复合主键的从表的左外连接查询，并使用Specification动态拼接条件

有两个表 t_com,t_kbn
t_com有如下字段
com_id,com_no,com_kbn1,com_kbn2,com_kbn3,com_kbn4,com_kbn5,com_name 所有字段都是String类型 其中com_id,com_no为联合主键
t_kbn是字典表，有如下字段
kbn,kbn_id,kbn_name,kbn_name2 所有字段都是String类型 其中kbn和kbn_id为联合主键
t_com表的com_kbn1,com_kbn2,com_kbn3,com_kbn4,com_kbn5和t_kbn的kbn_id有关联，
用Criteria API结合Specification 实现主表 t_com的com_kbn1,com_kbn2,com_kbn3,com_kbn4,com_kbn5 左外连接t_kbn的kbn_id进行查询，
动态拼接查询条件com_name，返回t_com的所有字段和t_kbn的kbn_name字段

Answer 1

项目目录结构

src/
├── main/
│   ├── java/
│   │   └── com/
│   │       └── example/
│   │           ├── Application.java
│   │           ├── config/
│   │           │   └── JpaConfig.java
│   │           ├── controller/
│   │           │   └── ComController.java
│   │           ├── entity/
│   │           │   ├── Com.java
│   │           │   └── Kbn.java
│   │           ├── repository/
│   │           │   ├── ComRepository.java
│   │           │   └── KbnRepository.java
│   │           ├── service/
│   │           │   └── ComService.java
│   │           └── specification/
│   │               └── ComSpecification.java
│   └── resources/
│       └── application.properties
└── test/
    └── java/
        └── com/
            └── example/
                └── ApplicationTests.java

代码文件

Application.java

package com.example;

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

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

JpaConfig.java

package com.example.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@EnableJpaRepositories(basePackages = "com.example.repository")
public class JpaConfig {
}

ComController.java

package com.example.controller;

import com.example.entity.Com;
import com.example.service.ComService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class ComController {

    @Autowired
    private ComService comService;

    @GetMapping("/coms")
    public List<Com> getComs(@RequestParam(required = false) String comName) {
        return comService.findComs(comName);
    }
}

Com.java

package com.example.entity;

import javax.persistence.*;
import java.util.Objects;

@Entity
@Table(name = "t_com")
public class Com {

    @EmbeddedId
    private ComId id;

    @Column(name = "com_kbn1")
    private String comKbn1;

    @Column(name = "com_kbn2")
    private String comKbn2;

    @Column(name = "com_kbn3")
    private String comKbn3;

    @Column(name = "com_kbn4")
    private String comKbn4;

    @Column(name = "com_kbn5")
    private String comKbn5;

    @Column(name = "com_name")
    private String comName;

    // Getters and setters

    @Embeddable
    public static class ComId {
        @Column(name = "com_id")
        private String comId;

        @Column(name = "com_no")
        private String comNo;

        // Getters and setters, equals, and hashCode
    }
}

Kbn.java

package com.example.entity;

import javax.persistence.*;
import java.util.Objects;

@Entity
@Table(name = "t_kbn")
public class Kbn {

    @EmbeddedId
    private KbnId id;

    @Column(name = "kbn_name")
    private String kbnName;

    @Column(name = "kbn_name2")
    private String kbnName2;

    // Getters and setters

    @Embeddable
    public static class KbnId {
        @Column(name = "kbn")
        private String kbn;

        @Column(name = "kbn_id")
        private String kbnId;

        // Getters and setters, equals, and hashCode
    }
}

ComRepository.java

package com.example.repository;

import com.example.entity.Com;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface ComRepository extends JpaRepository<Com, Com.ComId>, JpaSpecificationExecutor<Com> {
}

KbnRepository.java

package com.example.repository;

import com.example.entity.Kbn;
import org.springframework.data.jpa.repository.JpaRepository;

public interface KbnRepository extends JpaRepository<Kbn, Kbn.KbnId> {
}

ComService.java

package com.example.service;

import com.example.entity.Com;
import com.example.repository.ComRepository;
import com.example.specification.ComSpecification;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ComService {

    @Autowired
    private ComRepository comRepository;

    public List<Com> findComs(String comName) {
        return comRepository.findAll(ComSpecification.comNameContains(comName));
    }
}

ComSpecification.java

package com.example.specification;

import com.example.entity.Com;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.JoinType;

public class ComSpecification {

    public static Specification<Com> comNameContains(String comName) {
        return (root, query, criteriaBuilder) -> {
            if (comName == null || comName.isEmpty()) {
                return criteriaBuilder.conjunction();
            }
            return criteriaBuilder.like(root.get("comName"), "%" + comName + "%");
        };
    }

    public static Specification<Com> joinWithKbn() {
        return (root, query, criteriaBuilder) -> {
            root.join("comKbn1", JoinType.LEFT).on(criteriaBuilder.equal(root.get("comKbn1"), "kbnId"));
            root.join("comKbn2", JoinType.LEFT).on(criteriaBuilder.equal(root.get("comKbn2"), "kbnId"));
            root.join("comKbn3", JoinType.LEFT).on(criteriaBuilder.equal(root.get("comKbn3"), "kbnId"));
            root.join("comKbn4", JoinType.LEFT).on(criteriaBuilder.equal(root.get("comKbn4"), "kbnId"));
            root.join("comKbn5", JoinType.LEFT).on(criteriaBuilder.equal(root.get("comKbn5"), "kbnId"));
            return criteriaBuilder.conjunction();
        };
    }
}

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/yourdatabase
spring.datasource.username=yourusername
spring.datasource.password=yourpassword
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

ApplicationTests.java

package com.example;

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class ApplicationTests {

    @Test
    void contextLoads() {
    }
}