- 背景
- 表结构
- 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 SetHpl语句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, Mapparams) { 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
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)