JPA:LEFT JOIN 在hql语句中使用

• 背景
• • 表结构
  • Hpl语句

Springboot 项目中,人员表关联公司的组织架构树的,人员可以再多个部门。这时候就需要连表查询，使用left join，因为jpa知道两张表中的关系，所以不需要使用on 。

@Data
@Entity
@ApiModel(value = "企业员工实体类")
@Accessors(chain = true)
@javax.persistence.Table(name = MemberEntity.TABLE_NAME, indexes = {@Index(name = "index_mobile", columnList = "mobile")})
@org.hibernate.annotations.Table(appliesTo = MemberEntity.TABLE_NAME, comment = "公司人员表") //表注释
public class UserEntity extends baseEntity implements Serializable {

    
    public static final String TABLE_NAME = "u_user_tbl";

    
    private static final long serialVersionUID = 959562203894894703L;
    
    @NotBlank(message = "姓名不能为空")
    @Size(min = 0, max = 64, message = "姓名名过长")
    @ApiModelProperty(value = "姓名", required = true)
    @Column(length = 64)
    private String name;

    
    @ManyToMany(fetch = FetchType.EAGER)//立即从数据库中进行加载数据;
    @JoinTable(name = "m_user_role_tbl", //中间表的表名
            joinColumns = {@JoinColumn(name = "userId")}, //本表的主键
            inverseJoinColumns = {@JoinColumn(name = "roleId")}) //所映射表的主键
    @Where(clause = "enable = 0")
    private Set roles;

    
    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "a_emp_group_tbl",     //中间表的表名
            joinColumns = {@JoinColumn(name = "emp_id")})  //本表的主键列明
    @ApiModelProperty(value = "分组")
    private Set groups;
}

    
    private String getHql(MemberFindDTO findDTO, Map params) {
        StringBuffer startHql = new StringBuffer("from MemberEntity t ");
        StringBuffer hql = new StringBuffer("");
        hql.append(" where t.enable = :enable ");
        params.put("enable", findDTO.getEnable());

        // left join查询 g.id 不用疑惑.就是他
        if (findDTO.getGroup() != null) {
            startHql.append(" left join t.groups g ");
            hql.append(" and g.id = :groupId ");
            params.put("groupId", findDTO.getGroup());
        }

        // 排序
        String orderBy = StringUtils.isNotBlank(findDTO.getOrderBy()) ? findDTO.getOrderBy() : "id desc";
        hql.append(" order by t." + orderBy);
        startHql.append(hql).toString();
        log.info("企业sql语句: {}",startHql.toString());
        return startHql.toString();
    }

打印出来的结果:

 from UserEntity t  left join t.groups g  where t.enable = :enable  and g.id = :groupId order by t.id desc