测试环境搭建
主表 Dimension
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@TableName(value = "sys_dimension", subTables = {"sys_dimension_unit"})
public class Dimension extends BaseComponentEntity {
@NotBlank(message = "维度不能为空")
@Length(max = 32, message = "维度不能超过32个字符")
private String code;
@NotBlank(message = "名称不能为空")
@Length(max = 32, message = "名称不能超过32个字符")
private String name;
@NotNull(message = "分类不能为空")
private DimensionCategoryEnum category;
}
@TableName
添加属性 subTables = {"sys_dimension_unit"}
表示子表是 sys_dimension_unit
那么,当删除的时候就会级联删除子表的数据。子表的外键是约定大于配置,采用“表名小写_id”。本例中的外键是 dimension_id
子表 Unit
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@TableName("sys_dimension_unit")
public class Unit extends BaseComponentEntity {
@NotBlank(message = "单位code不能为空")
@Length(max = 32, message = "维度不能超过32个字符")
private String code;
@NotBlank(message = "名称不能为空")
@Length(max = 32, message = "名称不能超过32个字符")
private String name;
@NotNull(message = "分子不能为空")
private Integer n;
@NotNull(message = "分母不能为空")
private Integer d;
private Integer e;
private BigDecimal k;
@NotNull(message = "维度id不能为空")
private Long dimensionId;
}
dimensionId
表示外键,对应的表字段是 dimension_id
。
编程式级联查询
- selectAsSubTable
public void selectAsSubTable(List<Map<String, Object>> masterData, String property, String refColumnName) {
Map<Long, List<T>> refColumnNameMap = this.groupByColumnName(refColumnName, (Collection)masterData.stream().map((rowx) -> {
return rowx.get("id");
}).collect(Collectors.toSet()));
Iterator var5 = masterData.iterator();
while(var5.hasNext()) {
Map<String, Object> row = (Map)var5.next();
List<T> subTableList = (List)refColumnNameMap.get(row.get("id"));
row.put(property, CollectionUtils.isEmpty(subTableList) ? Collections.emptyList() : subTableList);
}
}
子表操作,通过从masterData中获取Id值后,获取对应的子表数据。property
作为可以放到Map中。
这个一般配合主表的查询。
public Grid<Map<String, Object>> list(String code, String name) {
Grid<Map<String, Object>> grid = GridUtils.list(warehouseDAO.getSelectSQL() + " WHERE name like :name AND code = :code",
Params.builder(2).pv("code", code).pv("name", name).pv(EntityConstants.LOGIC_DELETE_COLUMN_NAME, false).build());
warehouseSpaceDAO.selectAsSubTable(grid.getRows(), "spaces", "warehouse_id");
return grid;
}
- groupByColumnName
public Map<Long, List<T>> groupByColumnName(String refColumnName, Collection<?> refValues) {
Map<Long, List<T>> refColumnNameMap = (Map)this.selectByParams(Params.builder(1).pv("refColumnName", refValues).build(), refColumnName + " IN (:refColumnName)").stream().collect(Collectors.groupingBy((t) -> {
return (Long)this.getPropertyValue(t, (String)this.columnNameToPropertyNameMap.get(refColumnName));
}));
return refColumnNameMap;
}
根据字段进行分组,并通过 Map
进行收集。