Spring Data JPA Specification一对多，多对一。jpa多表字段查询

Spring Data JPA Specification一对多，多对一。jpa多表字段查询
用Specification动态查询 实现多条件复杂查询及多表联查

在使用jpa Specification查询时查询条件涉及关联的N张表字段。
其主要是配置一对多，多对一关联实体
查询示例：

@JoinColumn标签中的
insertable =false ,updatable = false属性是为了查询时使用sbbm字段来查询。如果不加hibernate，updatable 为false状态查询时会报找不到属性异常（在查询时hibernate分不清哪个熟悉对应表中的字段，@Column标签为属性对应表中的字段）
实体类：VioViodataDO

@Data
@Entity
@Table ( name ="VIODATA" )
@ToString(exclude = {"basicDeviceEntity"})
public class VioViodataDO  implements Serializable {

	private static final long serialVersionUID =  3701028601741542348L;

	/**
	 * 主键
	 */
   	@Column(name = "ID" )
    @Id
    @GeneratedValue
    @GenericGenerator(name = "uuid", strategy = "uuid")
	private String id;
		/**
	 * 时间
	 */
   	@Column(name = "WFSJ" )
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
	private Timestamp wfsj;

   	@Column(name = "sbbm" )
	private String sbbm
    /**
     * 关联设备表
     */
    @ManyToOne(fetch = FetchType.EAGER,cascade = {CascadeType.REFRESH})
    @JoinColumn(name = "sbbm",referencedColumnName = "sbbh",insertable =false ,updatable = false)
    private BasicDeviceEntity basicDeviceEntity;

实体类：

@Data
@Entity
@Table(name = "BASICDEVICE")
@ToString(exclude = {"basicPointEntityList","sysDepartDO"})
public class BasicDeviceEntity implements Serializable {

/**
     * 主键
     */

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(name = "ID")
    private String id;

    @Column(name = "BMID")
    private String bmid;

    @Column(name = "CDH")
    private String cdh;

    @Column(name = "SBMC")
    private String sbmc;

    @Column(name = "SBCS")
    private String sbcs;

    @Column(name = "SBLX")
    private String sblx;

    @Column(name = "SFGDWZ")
    private String sfgdwz;

    @Column(name = "SBBH")
    private String sbbh;

    @Column(name = "LYSBBH")
    private String lysbbh;
/**
*以下为设备表关联位置表的多对一的关系，在查询时同时也会查出该表的类容
*/
    @ManyToOne(cascade = {CascadeType.MERGE, CascadeType.REFRESH})
    @JoinTable(name = "BASIC_DEVICE_POINT", joinColumns = @JoinColumn(name = "sbid"),
            inverseJoinColumns = @JoinColumn(name = "dwid"))
/**
sysDepartDO为当前设备实体对部门表做的多对一关系映射，添加updatable和insertable属性是应为在查询VIODATA表数据时要通过bmid去做查询条件如果不加hibernate分不清是使用sysDepartDO匹配表字段还是bmid去匹配。（报错：找不到对应的属性）
*/
    @ManyToOne(cascade = {CascadeType.ALL}, fetch = FetchType.LAZY)
    @JoinColumn(name = "BMID",referencedColumnName = "id" , updatable = false, insertable = false)
    private SysDepartDO sysDepartDO;

Controller层代码：

 @GetMapping(value = "/vehicleIllegal")
    public RestResponse vehicleIllegal(IllegalQueryDTO illegalQuery) {
return videoInspectionService.vehicleIllegal(illegalQuery);
    }

Serervicel:

public RestResponse vehicleIllegal(IllegalQueryDTO illegalQuery) {

        Sort sort = new Sort(Sort.Direction.DESC, "wfsj");
        PageRequest pageRequest = PageRequest.of(illegalQuery.getPage(), illegalQuery.getSize(), sort);
        Specification specification = (Specification) (root, query, cb) -> {
            //增加筛选条件
            Predicate predicate = cb.conjunction();

            if (!com.alibaba.druid.util.StringUtils.isEmpty(illegalQuery.getHpzl())) {
                predicate.getExpressions().add(cb.equal(root.get("hpzl"), illegalQuery.getHpzl()));
            }
            if (!com.alibaba.druid.util.StringUtils.isEmpty(illegalQuery.getHphm())) {
                predicate.getExpressions().add(cb.like(root.get("hphm"), "%"+illegalQuery.getHphm()+"%"));
            }
            if (!com.alibaba.druid.util.StringUtils.isEmpty(illegalQuery.getHpzl())) {
                predicate.getExpressions().add(cb.equal(root.get("wfsj"), illegalQuery.getWfsj()));
            }
            if (!com.alibaba.druid.util.StringUtils.isEmpty(illegalQuery.getHphm())) {
                predicate.getExpressions().add(cb.equal(root.get("wfxw"), illegalQuery.getWfxw()));
            }
            if (org.apache.commons.lang3.StringUtils.isNotEmpty(illegalQuery.getKssj())) {
                predicate.getExpressions().add(cb.greaterThanOrEqualTo(root.get("sj"), DateUtils.string2Timestamp(illegalQuery.getKssj())));
            }
            if (org.apache.commons.lang3.StringUtils.isNotEmpty(illegalQuery.getJssj())) {
                predicate.getExpressions().add(cb.lessThanOrEqualTo(root.get("sj"), DateUtils.string2Timestamp(illegalQuery.getJssj())));
            }
            Join join = root.join("basicDeviceEntity", JoinType.LEFT);
            //从表条件，夸表查询字段
            if (!com.alibaba.druid.util.StringUtils.isEmpty(illegalQuery.getSblx())) {
                predicate.getExpressions().add(cb.equal(join.get("sblx"), illegalQuery.getSblx()));
            }

                
                ArrayList<Predicate> arrayList = new ArrayList();
                CriteriaBuilder.In<String> in = cb.in(join.get("bmid"));
                for (String id : illegalQuery.getDeptId()) {
                    in.value(id);
                }
                in.value("");
                arrayList.add(in);
                Predicate[] p = new Predicate[arrayList.size()];
                Predicate[] predicates = arrayList.toArray(p);
                predicate.getExpressions().add(cb.and(predicates));

            return predicate;
        };
        // 获取分页列表
        Page<VioViodataDO> patrolResultPage = videoVioViodataDao.findAll(specification, pageRequest);

        // 获取全部列表信息
        try { JSONObject dataObject = new JSONObject();
        dataObject.put("VioViodataDO", patrolResultPage.getContent());
        dataObject.put("total", patrolResultPage.getTotalElements());
        // 返回查询成功结果
        return RestResponseGenerator.genOkResult(dataObject, "success");
        
    }

结果集：

{
    "code": 200,
    "message": "success",
    "data": {
        "total": 4,
        "VioViodataDO": [
            {
                "id": "202011010000000008863602",
                "cjjg": "349909000010",

                "basicDeviceEntity": {
                    "id": "a2d07e00646b4abb01646d7c37510246",

                    "sysDepartDO": {
                        "createname": "admin",

                        "children": [
                            {
                                "createname": "admin",
                            
                                "parentdepartid": "a2d07e45622369050162238901cf0001",
                                "children": [],
                                "firstDepart": false
                            }
                        ],
                        "firstDepart": false
                    }
                }
            }，