背景

之前使用JPA做动态分页都是使用的原生sql的IF实现。

乱吧？！乱是其次，最重要的是这么写无论如何都不会走索引的，数据量巨大时，慢的你怀疑人生。
最近有空研究了下使用@ManytoMany等等表关系注解实现联表查询。

开始

建资源组实体

@Entity(name = "base_resource_group")
@ApiModel(value = "资源组实体类")
public class ResourceGroupEntity implements Serializable {

    @Id
    @Column(name = "id",columnDefinition="int(11) COMMENT '资源组表'")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "group_name",columnDefinition="varchar(40) COMMENT '资源组名'")
    @ApiModelProperty(value = "资源组名")
    private String groupName;


....................................
}

资源实体

@Entity(name = "base_resources")
@ApiModel(value = "资源实体类")
public class ResourceEntity implements CycleLoadEntity  {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", columnDefinition="int(11) COMMENT '资源表'")
    private Integer id;

    @ManyToOne(cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_id", columnDefinition="int(11) COMMENT '父级id'")
    @JsonIgnoreProperties({"children"})
    private ResourceEntity parent;

    @OneToMany(mappedBy = "parent", fetch = FetchType.LAZY)
    @JsonIgnoreProperties({"parent"})
    private List<ResourceEntity> children;

    @ManyToOne(cascade = {CascadeType.PERSIST})
    @JoinColumn(name = "group_id")
    private ResourceGroupEntity group;

    @Column(name = "res_name", columnDefinition="varchar(40) COMMENT '资源名称'")
    @ApiModelProperty(value = "资源名称")
    private String resName;

    @Column(name = "res_link", columnDefinition="varchar(80) COMMENT '资源连接'")
    @ApiModelProperty(value = "资源连接")
    private String resLink;

    @Column(name = "description", columnDefinition="varchar(80) COMMENT '描述'")
    @ApiModelProperty(value = "描述")
    private String description;

    @Column(name = "enabled", columnDefinition="int(1) COMMENT '是否可用：0：可用；1：不可用'")
    @ApiModelProperty(value = "是否可用：0：可用；1：不可用")
    private Integer enabled;

    @Column(name = "is_open", columnDefinition="int(1) COMMENT '是否放开：0：不放开；1：放开'")
    @ApiModelProperty(value = "是否放开：0：不放开；1：放开")
    private Integer isOpen;

    @ManyToMany(mappedBy = "resources", cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
    @JsonIgnoreProperties({"resources"})
    private Set<RoleEntity> roles;

    @Transient
    private boolean expand;

    @Transient
    private String title;

    @Transient
    private boolean checked;
    ...............................
    }

资源组与资源是一对多关系。

动态联查分页

    @Autowired
    ResourceRepository resourceRepository;
    @Autowired
    ResourceGroupRepository resourceGroupRepository;

    public Page<ResourceEntity> page(ResourcePageReqDTO resourcePageReqDTO) {
        Pageable pageable = PageUtil.getPage(resourcePageReqDTO);
        Specification<ResourceEntity> querySpecific = (root, criteriaQuery, criteriaBuilder) -> {
            List<Predicate> predicates = Lists.newArrayList();
            //这里是重点，表关联起来
            Join<ResourceEntity, ResourceGroupEntity> group = root.join("group", JoinType.LEFT);
            Join<ResourceEntity, ResourceEntity> resJoin = root.join("parent", JoinType.LEFT);
            
            predicates.add(criteriaBuilder.isNull(resJoin.get("id")));
            predicates.add(criteriaBuilder.equal(root.get("enabled"), 0));
            if (resourcePageReqDTO.getResGroup() != null) {
                predicates.add(criteriaBuilder.equal(group.get("id"), resourcePageReqDTO.getResGroup()));
            }
            if (!StringUtils.isEmpty(resourcePageReqDTO.getResName())) {
                predicates.add(criteriaBuilder.like(root.get("resName"), "%" + resourcePageReqDTO.getResName() + "%"));
            }
            if (!StringUtils.isEmpty(resourcePageReqDTO.getResLink())) {
                predicates.add(criteriaBuilder.like(root.get("resLink"), "%" + resourcePageReqDTO.getResLink() + "%"));
            }
            Predicate[] predicate = new Predicate[predicates.size()];
            criteriaQuery.where(criteriaBuilder.and(predicates.toArray(predicate)));
            criteriaQuery.orderBy(criteriaBuilder.desc(root.get("id")));
            return criteriaQuery.getRestriction();
        };
        return resourceRepository.findAll(querySpecific, pageable);
    }

使用了表关系注解，那就涉及到了关系的删除、添加等，删除对象之前一定得先把关系删掉，写法和传统有些区别的，这类文章介绍很多就不写了。期间使用懒加载遇到了失效的问题，郁闷了半天，原来idea在debug模式下是不会懒加载的，都是即时加载。