背景
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 {
/**
* tableName
*/
public static final String TABLE_NAME = "u_user_tbl";
/**
* UUID
*/
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<RoleEntity> roles;
/**
* 分组
*/
@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "a_emp_group_tbl", //中间表的表名
joinColumns = {@JoinColumn(name = "emp_id")}) //本表的主键列明
@ApiModelProperty(value = "分组")
private Set<Long> groups;
}
Hpl语句
/**
* 获取HQL和拼装参数
*
* @param findDTO
* @param params
* @return
*/
private String getHql(MemberFindDTO findDTO, Map<String, Object> 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