sharp-database中的TableGenerator根据实体对象生成表

1. 创建实体对象

Task.java

@SuperBuilder
@Getter
@Setter
@Table(value = "t_task", comment = "任务表")
@NoArgsConstructor
public class Task extends BasePureEntity {

    @Column(nullable = true, comment = "任务名称")
    private String taskName;

    @Column(comment = "完成时间")
    private LocalDateTime completeTime;

    private Integer costHours;

    private Boolean complete;

    private Long assignUserId;

    @Column(comment = "用户状态")
    private UserStatusEnum userStatus;

}

2. 执行代码

@SpringBootTest
public class TableGeneratorTest {

    @Autowired
    private TableGenerator tableGenerator;

    @Test
    public void createTable() {
        tableGenerator.createTable(Task.class);
    }

}

生成sql，并执行

create table t_task
(
    id bigint not null comment '主键'
        primary key,
    task_name varchar(32) not null comment '任务名称',
    complete_time datetime null comment '完成时间',
    cost_hours int null,
    complete bit null,
    assign_user_id bigint null,
    user_status varchar(16) null comment '用户状态',
    created_by bigint null,
    created_at datetime null,
    updated_by bigint null,
    updated_at datetime null,
    is_deleted bit null
)
comment '任务表';

sharp-formflow中自定义表单

自定义表单

自定义表单顾名思义就是由用户定义表单，由用户觉定标签和组件，决定要填写什么格式的数据。

创建表单

方式一

创建组件

http://localhost:8080/form/configurers

[{
    "label": "姓名",
    "cpnType": "TEXT",
    "validators": [{
        "min": 0,
        "max": 16,
        "message": "长度范围 0 - 16 个字符",
        "validatorType": "LENGTH"
    }, {
        "required": true,
        "message": "必填项",
        "validatorType": "REQUIRED"
    }],
    "defaultValue": "Rick",
    "placeholder": "请输入姓名"
}, {
    "label": "年龄",
    "cpnType": "NUMBER_TEXT",
    "validators": [{
        "min": 18,
        "max": 100,
        "message": "大小范围是18 - 100",
        "validatorType": "SIZE"
    }, {
        "required": true,
        "message": "必填项",
        "validatorType": "REQUIRED"
    }],
    "defaultValue": "18",
    "placeholder": "请输入年龄"
}, {
    "label": "兴趣爱好(单选)",
    "cpnType": "SELECT",
    "validators": [{
        "required": true,
        "message": "必填项",
        "validatorType": "REQUIRED"
    }],
    "options": ["足球", "篮球", "乒乓球", "羽毛球"],
    "defaultValue": "足球",
    "placeholder": "请输入兴趣爱好"
}, {
    "label": "信息收集",
    "cpnType": "TABLE",
    "validators": [],
    "placeholder": "请输入信息收集",
    "additionalInfo": {
        "labels": ["姓名", "年龄"]
    }
}, {
    "label": "兴趣爱好(多选)",
    "cpnType": "CHECKBOX",
    "validators": [],
    "options": ["足球", "篮球", "乒乓球", "羽毛球"],
    "defaultValue": "[\"足球\", \"篮球\"]",
    "placeholder": "请输入兴趣爱好"
}]

创建表单

http://localhost:8080/form/configurers

{
    "name": "人员信息登记表"
}

表单关联组件

http://localhost:8080/forms/488683635382583296/configs

["488675379486556160","488675379490750464","488675379490750465","488675379490750466","488675379494944768"]

方式二

创建表单和组件并关联

{
    "form": {
        "name": "我的第N个表单"
    },
    "configs": [{
        "label": "姓名",
        "cpnType": "TEXT",
        "validators": [{
            "min": 0,
            "max": 16,
            "message": "长度范围 0 - 16 个字符",
            "validatorType": "LENGTH"
        }, {
            "required": true,
            "message": "必填项",
            "validatorType": "REQUIRED"
        }],
        "defaultValue": "Rick",
        "placeholder": "请输入姓名"
    }, {
        "label": "年龄",
        "cpnType": "NUMBER_TEXT",
        "validators": [{
            "min": 18,
            "max": 100,
            "message": "大小范围是18 - 100",
            "validatorType": "SIZE"
        }, {
            "required": true,
            "message": "必填项",
            "validatorType": "REQUIRED"
        }],
        "defaultValue": "18",
        "placeholder": "请输入年龄"
    }, {
        "label": "兴趣爱好(单选)",
        "cpnType": "SELECT",
        "validators": [{
            "required": true,
            "message": "必填项",
            "validatorType": "REQUIRED"
        }],
        "options": ["足球", "篮球", "乒乓球", "羽毛球"],
        "defaultValue": "足球",
        "placeholder": "请输入兴趣爱好"
    }, {
        "label": "信息收集",
        "cpnType": "TABLE",
        "validators": [],
        "placeholder": "请输入信息收集",
        "additionalInfo": {
            "labels": ["姓名", "年龄"]
        }
    }, {
        "label": "兴趣爱好(多选)",
        "cpnType": "CHECKBOX",
        "validators": [],
        "options": ["足球", "篮球", "乒乓球", "羽毛球"],
        "defaultValue": "[\"足球\", \"篮球\"]",
        "placeholder": "请输入兴趣爱好"
    }]
}

查看表单

http://localhost:8080/forms/ajax/488686638231617536

{
    "form": {
        "id": "488686638231617536",
        "name": "我的第N个表单"
    },
    "instanceId": null,
    "propertyList": [
        {
            "id": 488686640483958784,
            "name": "ZghRwhvcGu",
            "configurer": {
                "id": "488686639036923904",
                "label": "姓名",
                "cpnType": "TEXT",
                "validators": [
                    {
                        "min": 0,
                        "max": 16,
                        "message": "长度范围 0 - 16 个字符",
                        "validatorType": "LENGTH"
                    },
                    {
                        "required": true,
                        "message": "必填项",
                        "validatorType": "REQUIRED"
                    }
                ],
                "options": null,
                "defaultValue": "Rick",
                "placeholder": "请输入姓名",
                "additionalInfo": null
            },
            "value": "Rick",
            "validatorProperies": {
                "Required.required": true,
                "Length.min": 0,
                "Length.max": 16
            }
        },
        {
            "id": 488686640483958785,
            "name": "VnmnWkziwe",
            "configurer": {
                "id": "488686639036923905",
                "label": "年龄",
                "cpnType": "NUMBER_TEXT",
                "validators": [
                    {
                        "min": 18,
                        "max": 100,
                        "message": "大小范围是18 - 100",
                        "validatorType": "SIZE"
                    },
                    {
                        "required": true,
                        "message": "必填项",
                        "validatorType": "REQUIRED"
                    }
                ],
                "options": null,
                "defaultValue": "18",
                "placeholder": "请输入年龄",
                "additionalInfo": null
            },
            "value": 18,
            "validatorProperies": {
                "Size.min": 18,
                "Size.max": 100,
                "Required.required": true
            }
        },
        {
            "id": 488686640488153088,
            "name": "WIRyhGtAhh",
            "configurer": {
                "id": "488686639036923906",
                "label": "兴趣爱好(单选)",
                "cpnType": "SELECT",
                "validators": [
                    {
                        "required": true,
                        "message": "必填项",
                        "validatorType": "REQUIRED"
                    }
                ],
                "options": [
                    "足球",
                    "篮球",
                    "乒乓球",
                    "羽毛球"
                ],
                "defaultValue": "足球",
                "placeholder": "请输入兴趣爱好",
                "additionalInfo": null
            },
            "value": "足球",
            "validatorProperies": {
                "Required.required": true
            }
        },
        {
            "id": 488686640488153089,
            "name": "EsaBSGeoNF",
            "configurer": {
                "id": "488686639041118208",
                "label": "信息收集",
                "cpnType": "TABLE",
                "validators": [],
                "options": null,
                "defaultValue": null,
                "placeholder": "请输入信息收集",
                "additionalInfo": {
                    "labels": [
                        "姓名",
                        "年龄"
                    ]
                }
            },
            "value": null,
            "validatorProperies": {}
        },
        {
            "id": 488686640488153090,
            "name": "dCrOsUvnkb",
            "configurer": {
                "id": "488686639041118209",
                "label": "兴趣爱好(多选)",
                "cpnType": "CHECKBOX",
                "validators": [],
                "options": [
                    "足球",
                    "篮球",
                    "乒乓球",
                    "羽毛球"
                ],
                "defaultValue": "[\"足球\", \"篮球\"]",
                "placeholder": "请输入兴趣爱好",
                "additionalInfo": null
            },
            "value": [
                "足球",
                "篮球"
            ],
            "validatorProperies": {}
        }
    ],
    "method": "POST",
    "actionUrl": "488686638231617536"
}

填写表单数据

我的模版是是基于 thymeleaf jQuery Bootstrap 实现的。
浏览器访问：http://localhost:8080/forms/page/488686638231617536

http://xhope.top/wp-content/uploads/2021/11/1122.png
数据填写完成后，会生实例id

实例id查看实例数据

488693516797902848 就是实例id

http://localhost:8080/forms/page/488686638231617536/488693516797902848

sharp-database中的BaseDAOImpl实现多表级联@OneToMany（二）

目标

本文章主要介绍级联中的「插入」「查询」「删除」操作。

环境搭建

  • Project.java
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@TableName("p_project")
public class Project extends BaseComponentEntity {

    private String title;

    private String description;

    private Long ownerId;

    private String coverUrl;

    @ManyToOne(value = "project_group_id", parentTable = "p_project_group")
    private ProjectGroup projectGroup;
}

@ManyToOne 是子表注解，它有2个属性，value 表示引用外键的字段名，parentTable 引用的父表名。

  • ProjectGroup.java
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@TableName("p_project_group")
public class ProjectGroup extends BaseComponentEntity {

    private String title;

    @OneToMany(subTable = "p_project", reversePropertyName = "projectGroup", cascadeSaveOrUpdate = true)
    private List<Project> projectList;

}

@OneToMany 是父表注解，属性是 List 集合。它有2个属性，reversePropertyName 表示子对象中该对象的属性名，subTable 关联的子表名。cascadeSaveOrUpdate 开启级联更新。

测试

级联插入

@Test
public void save() {
    ProjectGroup projectGroup = ProjectGroup.builder().title("我的组").projectList(Lists.newArrayList(Project.builder().title("我的项目").ownerId(11L).build())).build();
    projectGroupService.save(projectGroup);

    Project project = Project.builder().title("我的项目-2").ownerId(11L).build();
    project.setProjectGroup(projectGroup);
    projectService.save(project);
}

projectGroupService.save 可以级联插入；projectService.save单个插入，但是属性需要关联对象 ProjectGroup，需要对象中的 id 信息。

级联查询

@Test
public void select() {
    ProjectGroup projectGroup = projectGroupService.getByTitle("我的组").get(0);
    Assert.assertEquals("我的项目", projectGroup.getProjectList().get(0).getTitle());
    Assert.assertEquals("我的项目-2", projectGroup.getProjectList().get(1).getTitle());

    Project project = projectService.findById(486880981274755072L).get();
    Assert.assertEquals("我的组", project.getProjectGroup().getTitle());
}

级联删除

@Test
public void deleteLogically() {
    projectGroupService.deleteLogically(486678250836623360L);
    Assert.assertEquals(false, projectService.findById(486678250924703744L).isPresent());
}

@Test
public void deleteHardly() {
    projectGroupService.delete(486678250836623360L);
}

sharp-database中的BaseDAOImpl实现多表级联（一）

测试环境搭建

主表 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 进行收集。

sharp-database中的BaseDAOImpl实现多租户的功能

添加ConditionAdvice实现

sharp-database 提供了默认的 ConditionAdvice 实现，自动过滤逻辑删除的数据。

public class DefaultConditionAdvice implements ConditionAdvice {
    public DefaultConditionAdvice() {
    }

    public Map<String, Object> getCondition() {
        return Params.builder(1).pv("is_deleted", false).build();
    }
}

可以添加自己的实现，将租户字段添加到查询条件中。

@Bean
public ConditionAdvice conditionAdvice() {
    return () -> {
        final DefaultConditionAdvice defaultConditionAdvice = new DefaultConditionAdvice();
        Map<String, Object> params = Params.builder().pv("tenant_id", UserInfoHolder.get().getTenantId()).build();
        params.putAll(defaultConditionAdvice.getCondition());
        return params;
    };
}