jpa 去重_spring boot jpa 表关联查询分组 group by 去重

使用jpa操作多张表进行关联查询时，有重复数据需要分组去重

1)确定主表：将有重复数据的表格作为主表，表明关系

public class AttendanceRuleTypeItem implements Serializable {

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

private Long itemId;

private String name;

private Integer code;

private String dictionaryCode;

@OneToMany

@JoinColumn(name = "typesCode",referencedColumnName = "code")

private List attendanceRules;

}

2.副表两张

public class AttendanceRuleModel {

@Id

@GenericGenerator(name = "guid", strategy = "guid")

@GeneratedValue(generator = "guid")

private String id;

//规则类型code

private Integer ruleCode;

//请假类型code

private Integer typesCode;

//扣罚天数code

private Integer resultNumberCode;

private String rankName;

@OneToOne

@JoinColumn(name = "resultNumberCode", referencedColumnName = "code", insertable = false, updatable = false,

foreignKey = @ForeignKey(value =ConstraintMode.NO_CONSTRAINT ))

private AttendanceRuleTypeItem resultNumber;

@OneToMany(cascade = {CascadeType.PERSIST,CascadeType.REFRESH,CascadeType.MERGE})

@JoinColumn(name = "rule_id",referencedColumnName = "id",foreignKey = @ForeignKey(value =ConstraintMode.NO_CONSTRAINT ))

private List departmentRelation;

@OneToMany(cascade = {CascadeType.PERSIST,/*CascadeType.REMOVE,*/CascadeType.REFRESH})

@JoinColumn(name = "attendance_rule_id",referencedColumnName = "id",foreignKey = @ForeignKey(value =ConstraintMode.NO_CONSTRAINT ))

private List relationAttendanceRanks;

}

public class RelationAttendanceDepartment extends BaseEntity {

@Id

@GeneratedValue(generator = "guid")

@GenericGenerator(strategy = "guid",name = "guid")

private String ruleDeptId;

@Column(name = "rule_id")

private String ruleId;

private Integer departmentId;

public RelationAttendanceDepartment(Integer departmentId) {

this.departmentId = departmentId;

}

public RelationAttendanceDepartment(String ruleId, Integer departmentId) {

this.ruleId = ruleId;

this.departmentId = departmentId;

}

}

3)。确定每张表的关系之后  表连接查询

public PageResultVO> ruleList(QueryPageVO attendance) {

Specification reSpec = (Specification) (root, cq, cb) -> {

Join ruleJoin = root.join("attendanceRules", JoinType.LEFT); //表关联查询 AttendanceRuleTypeItem 为主表

Join deptJoin = ruleJoin.join("departmentRelation", JoinType.LEFT); //两张副表之间的关联关系

Predicate predicate = cb.conjunction();

predicate = cb.and(predicate, cb.equal(deptJoin.get("departmentId"), attendance.getQuery().getId())); //拼接副表中的查询条件

predicate = cb.and(predicate, cb.equal(root.get("dictionaryCode"), "1002")); //拼接朱表中的查询条件

predicate = cb.and(predicate, cb.equal(ruleJoin.get("ruleCode"), 2001)); //拼接副表中的查询条件

cq.where(predicate);

return cq.getRestriction();

};

Page ruleTypeItemPage = ruleTypeItemRepository.findAll(Specification

.where(reSpec)

.and(distinct()), PageRequest.of(attendance.getPages().getPage(), attendance.getPages().getSize()));

List attendanceRuleResultVOS = ruleTypeItemPage.stream().map(r->{

AttendanceRuleResultVO ruleResultVO =new AttendanceRuleResultVO();

ruleResultVO.setTypesName(r.getName());

List ruleVOS = r.getAttendanceRules().stream().map(a-> new RuleVO(a.getId(),getRankName(a),

a.getResultNumber().getName())).collect(Collectors.toList());

ruleResultVO.setRule(ruleVOS);

return ruleResultVO;

}).collect(Collectors.toList());

return new PageResultVO(ruleTypeItemPage.getTotalPages(),(int)ruleTypeItemPage.getTotalElements(),null, attendanceRuleResultVOS);

}

//去重

public Specification distinct() {

return (Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder criteriaBuilder) -> {

criteriaQuery.distinct(true);

return criteriaQuery.getRestriction();

};

}

来源：https://www.cnblogs.com/xiaoxiaoliu/p/10190065.html