Spring boot使用一个接口实现任意一张表的增删改查

作者：Cpp五条 | 2024-05-15 21:31:10

踩

文章目录

Spring boot使用一个接口实现任意一张表的增删改查

本文旨在：

（需要做额外的操作创建对应表和再一个配置表里配置相关的表）
使用一个接口的方式，下载任意一个表的Excel模板
使用一个接口的方式，将任意的excel中的数据导入到对应的表里面
使用一个接口的方式，将任意表中的数据查询出来并展示
使用一个接口的方式，将任意表中的数据查询出来并根据表中字段自动生成过滤条件展示
使用一个接口的方式，对于任意表中的数据进行新增
使用一个接口的方式，对任意表中的任意数量数据删除
使用一个接口的方式，对任意表中的任意一条数据进行修改

这个是我之前开发项目的时候遇到自定义数据CRUD时写的解决方案，因为觉得不错，所以把其中的业务部份的逻辑都剔除了，提取出了其中的重要的部分，用于帮助分享

0、讲解部分

首先为了保证数据的唯一性，我默认每一张自动定义的表里面肯定有一个相同的属性名字的属，这个属性一定是主键

另外就是我会单独的存储不同表的过滤条件。这个过滤条件可以手动加，也可以写接口帮助加。在自动创建表提交表单时就可以往过滤的表中存储这样的过滤字段

不过由于时间原因，我并没有写通过一个接口的方式可以创建这样的表的接口，如果大家有空的话可以写一下帮我加上代码

我会把带代码提交到GitHub中，是免费开源的

相应的这个博客解答了我在这个博客中抛给大家的问题《Spring boot结合easy excel实现低代码量的Excel导入导出》

1、准备工作

引入maven依赖

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.10</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.83</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <!--引入Knife4j的官方start包,该指南选择Spring Boot版本<3.0,开发者需要注意-->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-openapi2-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>

        <!-- 参数校验依赖 -->
        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.25</version>
            <scope>compile</scope>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

其中主要是一下一些配置信息：

druid、knife4j、fastjson、mybatis-plus、动态数据源、MySQL驱动、lombok、hutool

我们使用的是spring boot 是2.5.6版本。

初始化SQL

create schema IF NOT EXISTS report collate utf8mb4_general_ci;
use
report;

create table if not exists report.filter_info
(
    id          bigint            not null comment '主键',
    table_code  varchar(100)      null comment '表编码',
    column_code varchar(100)      null comment '字段码',
    column_name varchar(100)      null comment '字段名称',
    type        varchar(32)       null comment '类型',
    dim_class   varchar(100)      null comment '对应维度值',
    sort        int               null comment '排序',
    is_required tinyint default 0 null comment '是否必须'
    ) comment '表筛选项表';

create table if not exists report.meta_info_mapping
(
    sys_code      varchar(100) null comment '元数据编码',
    sys_name      varchar(100) null comment '元数据名称',
    sys_dim_class varchar(100) null comment '元数据类型'
    ) comment '元数据<下拉框使用>';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

配置文件信息为

spring:
  application:
    name: xxxx
  datasource:
    dynamic:
      # ?????
      primary: master
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/report?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
          username: root
          password: 123456
      druid:
        initialSize: 10
        minIdle: 10
        maxActive: 200
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        validation-query: SELECT 'x'
        test-on-borrow: false
        test-on-return: false
        test-while-idle: true
        time-between-eviction-runs-millis: 60000  #???????????????????????????????
        min-evictable-idle-time-millis: 300000    #??????????????????????
        filters: stat,wall
        wall:
          comment-allow: true
          multiStatementAllow: true
          noneBaseStatementAllow: true
knife4j:
  enable: true

# mybatis config
mybatis-plus:
  mapper-locations: mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

2、代码实现

前后端对接包装类

@Getter
@Setter
@SuppressWarnings("ALL")
@Accessors(chain = true)
public class Result<T> {
    public static final String DEF_ERROR_MESSAGE = "系统繁忙，请稍候再试";
    public static final String HYSTRIX_ERROR_MESSAGE = "请求超时，请稍候再试";
    public static final int SUCCESS_CODE = 200;
    public static final int FAIL_CODE = 500;
    public static final int TIMEOUT_CODE = 503;
    /**
     * 统一参数验证异常
     */
    public static final int VALID_EX_CODE = 400;
    public static final int OPERATION_EX_CODE = 400;
    /**
     * 调用是否成功标识，0：成功，-1:系统繁忙，此时请开发者稍候再试 详情见[ExceptionCode]
     */
    @ApiModelProperty(value = "响应编码:0/200-请求处理成功")
    private int state;

    /**
     * 调用结果
     */
    @ApiModelProperty(value = "响应数据")
    private T data;

    /**
     * 结果消息，如果调用成功，消息通常为空T
     */
    @ApiModelProperty(value = "提示消息")
    private String msg = "SUCCESS";

    /**
     * 附加数据
     */
    // @ApiModelProperty(value = "附加数据")
    // private Map<Object, Object> extra;

    /**
     * 响应时间
     */
    @ApiModelProperty(value = "响应时间戳")
    private long timestamp = System.currentTimeMillis();

    /**
     * 系统报错时，抛出的原生信息
     */
    @ApiModelProperty(value = "异常消息")
    private String errorMsg = null;

    private Result() {
        this.timestamp = System.currentTimeMillis();
    }

    public Result(int code, T data, String msg) {
        this.state = code;
        this.data = data;
        this.msg = msg;
        this.timestamp = System.currentTimeMillis();
    }

    public Result(int code, T data, String msg, String errorMsg) {
        this(code, data, msg);
        this.errorMsg = errorMsg;
    }

    public Result(String message, Object data, String msg) {
    }

    public Result(String message, Object data, String message1, String errorMsg) {
    }

    public static <E> Result<E> result(int code, E data, String msg) {
        return new Result<>(code, data, msg);
    }

    public static <E> Result<E> result(int code, E data, String msg, String errorMsg) {
        return new Result<>(code, data, msg, errorMsg);
    }

    /**
     * 请求成功消息
     *
     * @param data 结果
     * @return RPC调用结果
     */
    public static <E> Result<E> success(E data) {
        return new Result<>(SUCCESS_CODE, data, "SUCCESS");
    }

    public static Result<Boolean> success() {
        return new Result<>(SUCCESS_CODE, true, "SUCCESS");
    }


    public static <E> Result<E> successDef(E data) {
        return new Result<>(SUCCESS_CODE, data, "SUCCESS");
    }

    public static <E> Result<E> successDef() {
        return new Result<>(SUCCESS_CODE, null, "SUCCESS");
    }

    public static <E> Result<E> successDef(E data, String msg) {
        return new Result<>(SUCCESS_CODE, data, msg);
    }

    /**
     * 请求成功方法 ，data返回值，msg提示信息
     *
     * @param data 结果
     * @param msg  消息
     * @return RPC调用结果
     */
    public static <E> Result<E> success(E data, String msg) {
        return new Result<>(SUCCESS_CODE, data, msg);
    }

    /**
     * 请求失败消息
     *
     * @param msg
     * @return
     */
    public static <E> Result<E> fail(int code, String msg) {
        return new Result<>(code, null, (msg == null || msg.isEmpty()) ? DEF_ERROR_MESSAGE : msg);
    }

    public static <E> Result<E> fail(int code, String msg, String errorMsg) {
        return new Result<>(code, null, (msg == null || msg.isEmpty()) ? DEF_ERROR_MESSAGE : msg, errorMsg);
    }

    public static <E> Result<E> fail(String msg) {
        return fail(OPERATION_EX_CODE, msg);
    }

    public static <E> Result<E> fail(String msg, Object... args) {
        String message = (msg == null || msg.isEmpty()) ? DEF_ERROR_MESSAGE : msg;
        return new Result<>(OPERATION_EX_CODE, null, String.format(message, args));
    }

    public static <E> Result<E> fail(Exception exception, String errorMsg) {
        if (exception == null) {
            return fail(DEF_ERROR_MESSAGE);
        }
        return new Result<>(exception.getMessage(), null, exception.getMessage(), errorMsg);
    }

    /**
     * 请求失败消息，根据异常类型，获取不同的提供消息
     *
     * @param throwable 异常
     * @return RPC调用结果
     */
    public static <E> Result<E> fail(Throwable throwable) {
        String msg = throwable != null ? throwable.getMessage() : DEF_ERROR_MESSAGE;
        return fail(FAIL_CODE, msg, msg);
    }

    public static <E> Result<E> validFail(String msg) {
        return new Result<>(VALID_EX_CODE, null, (msg == null || msg.isEmpty()) ? DEF_ERROR_MESSAGE : msg);
    }

    public static <E> Result<E> validFail(String msg, Object... args) {
        String message = (msg == null || msg.isEmpty()) ? DEF_ERROR_MESSAGE : msg;
        return new Result<>(VALID_EX_CODE, null, String.format(message, args));
    }

    public static <E> Result<E> validFail(Exception exceptionCode) {
        return new Result<>(exceptionCode.getMessage(), null,
                (exceptionCode.getMessage() == null || exceptionCode.getMessage().isEmpty()) ? DEF_ERROR_MESSAGE : exceptionCode.getMessage());
    }

    public static <E> Result<E> timeout() {
        return fail(TIMEOUT_CODE, HYSTRIX_ERROR_MESSAGE);
    }

    public Boolean getIsSuccess() {
        return this.state == SUCCESS_CODE || this.state == 0;
    }

    @Override
    public String toString() {
        return JSON.toJSONString(this);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187

model部分

@ApiModel("筛选条件DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class FilterDTO {
    @ApiModelProperty("字段code")
    private String columnCode;
    @ApiModelProperty("值")
    private String value;
    @ApiModelProperty("类型-INPUT:输入框;TIME:时间选择器;TIMEAREA:时间段选择器;SELECT:下拉选择;NUMBER:数字")
    private String type;
    @ApiModelProperty("起始时间(时间、时间段专用)")
    private String startTime;
    @ApiModelProperty("结束时间(时间段专用)")
    private String endTime;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

@Data
@Builder
@NoArgsConstructor
@ToString(callSuper = true)
@Accessors(chain = true)
@TableName("filter_info")
@AllArgsConstructor
public class FilterInfo {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键")
    @TableId(value = "id")
    protected Long id;

    /**
     * 表编码
     */
    private String tableCode;

    /**
     * 字段码
     */
    private String columnCode;

    /**
     * 字段名称
     */
    private String columnName;

    /**
     * 类型
     */
    private String type;

    /**
     * 对应维度值
     */
    private String dimClass;

    /**
     * 排序
     */
    private Integer sort;
    /**
     * 是否必须
     */
    private String isRequired;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

@ApiModel("筛选条件VO")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class FilterVO {
    @ApiModelProperty("字段code")
    private String columnCode;
    @ApiModelProperty("字段名称")
    private String columnName;
    @ApiModelProperty("类型-INPUT:输入框;DATE:日期选择器;DATERANGE:日期段;DATETIME：时间选择器;DATETIMERANGE:时间段;SELECT:下拉选择;NUMBER:数字")
    private String type;
    @ApiModelProperty("下拉字典")
    private List<TupleVO> info;
    @ApiModelProperty("是否必须")
    private String isRequired;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

@ApiModel("表头及筛选条件VO")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Alias("HeaderVO")
public class HeaderVO {
    @ApiModelProperty("表头")
    private List<TupleVO> header;
    @ApiModelProperty("筛选条件")
    private List<FilterVO> filter;
}
1
2
3
4
5
6
7
8
9
10
11

@ApiModel("新增数据DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ReportAddDTO {
    @ApiModelProperty(value = "表编码", required = true)
    @NotBlank(message = "表编码不能为空")
    private String tableCode;

    @ApiModelProperty(value = "内容", required = true)
    @NotEmpty(message = "内容不能为空")
    private LinkedHashMap<String, String> info;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

@ApiModel("删除数据DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ReportDelDTO {
    @ApiModelProperty(value = "表编码", required = true)
    @NotBlank(message = "表编码不能为空")
    private String tableCode;

    @ApiModelProperty(value = "记录的id值", required = true)
    @NotEmpty(message = "id不能为空")
    private List<String> ids;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

@ApiModel("修改数据DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ReportEditDTO {
    @ApiModelProperty(value = "表编码", required = true)
    @NotBlank(message = "表编码不能为空")
    private String tableCode;

    @ApiModelProperty(value = "内容", required = true)
    @NotEmpty(message = "内容不能为空")
    private LinkedHashMap<String, String> info;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

@ApiModel("上报分页列表DTO")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ReportListDTO {
    @ApiModelProperty("页码")
    private Integer page;

    @ApiModelProperty("每页数量")
    private Integer size;

    public Integer getPage() {
        return page == null ? 1 : page;
    }

    public Integer getSize() {
        return size == null ? 10 : size;
    }

    @ApiModelProperty(value = "表编码", required = true)
    @NotBlank(message = "表编码不能为空")
    private String tableCode;

    @ApiModelProperty("筛选项")
    private List<FilterDTO> filter;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

@ApiModel("三元组VO")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TupleVO {

    @ApiModelProperty("X轴")
    private String key;

    @ApiModelProperty("图例")
    private String item;

    @ApiModelProperty("Y轴")
    private String value;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Controller

@Slf4j
@Api(tags = "首页模块")
@RestController
@Validated
public class MyController {

    @Resource
    private MyService myService;

    @ApiOperationSupport(order = 1)
    @GetMapping("/getHeader")
    @ApiOperation("获取表头及筛选条件")
    public Result<HeaderVO> getHeader(@NotBlank(message = "表编码不能为空")
                                      @RequestParam("tableCode") String tableCode) {
        return Result.success(myService.getHeader(tableCode));
    }

    @ApiOperationSupport(order = 2)
    @PostMapping("/getData")
    @ApiOperation("分页获取表数据")
    public Result<IPage<LinkedHashMap<String, String>>> getData(@Valid @RequestBody ReportListDTO param) {
        return Result.success(myService.getData(param));
    }

    @ApiOperationSupport(order = 3)
    @PostMapping("/addOne")
    public Result<?> addOne(@Valid @RequestBody ReportAddDTO param) {
        return myService.addOne(param) ? Result.success(null, "操作成功") : Result.fail("操作失败");
    }

    @ApiOperation("修改一条数据")
    @ApiOperationSupport(order = 4)
    @PostMapping("/edit")
    public Result<?> edit(@Valid @RequestBody ReportEditDTO param) {
        return myService.edit(param) ? Result.success(null, "操作成功") : Result.fail("操作失败");
    }

    @ApiOperation("删除记录(支持批量)")
    @ApiOperationSupport(order = 5)
    @PostMapping("/del")
    public Result<?> del(@Valid @RequestBody ReportDelDTO param) {
        return myService.del(param) ? Result.success(null, "操作成功") : Result.fail("操作失败");
    }

    @ApiOperation("下载导入模板")
    @ApiOperationSupport(order = 6)
    @GetMapping("/getTemplate")
    public void getTemplate(@ApiParam(value = "表编码", required = true)
                            @NotBlank(message = "表编码不能为空")
                            @RequestParam("tableCode") String tableCode, HttpServletResponse response) {
        myService.getTemplate(tableCode, response);
    }

    @ApiOperation("数据导入")
    @ApiOperationSupport(order = 7)
    @PostMapping("/importData")
    public Result<?> importData(@ApiParam(value = "表编码", required = true)
                                @NotBlank(message = "表编码不能为空")
                                @RequestParam("tableCode") String tableCode,
                                @ApiParam(value = "上传的文件", required = true)
                                @NotNull(message = "上传文件不能为空") MultipartFile file) {
        return myService.importData(tableCode, file) ? Result.success(null, "导入成功") : Result.fail("导入失败");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

Service

public interface FilterService extends IService<FilterInfo> {

}
1
2
3

@Slf4j
@Service
public class FilterServiceImpl extends ServiceImpl<FilterMapper, FilterInfo> implements FilterService {

}
1
2
3
4
5

@Slf4j
@Service
public class MyService {

    private static final String ONLY_KEY = "only_key";

    @Resource
    private MyMapper dao;

    @Resource
    private FilterServiceImpl filterInfoService;

    public void getTemplate(String tableCode, HttpServletResponse response) {
        try (ExcelWriter writer = ExcelUtil.getWriter(true)) {
            response.setCharacterEncoding("UTF-8");
            List<List<String>> data = new ArrayList<>();
            List<String> result = dao.getTemplate(tableCode);
            data.add(result);
            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(data, true);
            ServletOutputStream out = null;
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(tableCode, "UTF-8") + ".xlsx");
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public HeaderVO getHeader(String tableCode) {
        HeaderVO result = new HeaderVO();
        List<FilterInfo> headers = filterInfoService.list(new LambdaQueryWrapper<FilterInfo>()
                .eq(FilterInfo::getTableCode, tableCode)
                .orderByAsc(FilterInfo::getSort)
        );
        List<TupleVO> headList = new ArrayList<>();
        List<FilterVO> filterList = new ArrayList<>();
        for (FilterInfo header : headers) {
            //存放表头信息
            headList.add(new TupleVO(header.getColumnCode(), header.getIsRequired(), header.getColumnName()));
            //获取特殊组件字典信息
            List<TupleVO> dimList = new ArrayList<>();
            if (header.getType().equals("select")) {
                dimList = dao.getDimList(header.getDimClass());
            }
            filterList.add(FilterVO.builder()
                    .columnCode(header.getColumnCode())
                    .columnName(header.getColumnName())
                    .type(header.getType())
                    .isRequired(header.getIsRequired())
                    .info(dimList)
                    .build());
        }
        result.setHeader(headList);
        result.setFilter(filterList);
        return result;
    }

    public IPage<LinkedHashMap<String, String>> getData(ReportListDTO param) {
        return dao.getData(new Page<>(param.getPage(), param.getSize()), param);
    }

    public boolean addOne(ReportAddDTO param) {
        Assert.isTrue(!param.getInfo().isEmpty(), "参数列表不能为空");
        Assert.isTrue(param.getInfo().containsKey(ONLY_KEY), "主键不存在");
        param.getInfo().put(ONLY_KEY, IdUtil.simpleUUID());
        return dao.addOne(param);
    }

    @Transactional(rollbackFor = Exception.class)
    public boolean edit(ReportEditDTO param) {
        Assert.isTrue(!param.getInfo().isEmpty(), "参数列表不能为空");
        Assert.isTrue(param.getInfo().containsKey(ONLY_KEY), "主键不存在");
        String id = param.getInfo().get(ONLY_KEY);
        param.getInfo().remove(ONLY_KEY);
        return dao.edit(id, param);
    }

    @Transactional(rollbackFor = Exception.class)
    public boolean del(ReportDelDTO param) {
        return dao.del(param);
    }


    public boolean importData(String tableCode, MultipartFile file) {
        try {
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
            List<List<Object>> list = reader.read();

            int index = -1;
            for (int k = 0; k < list.get(0).size(); k++) {
                if (ONLY_KEY.equals(list.get(0).get(k))) {
                    index = k;
                    break;
                }
            }
            Assert.isTrue(index >= 0, "缺少主键");
            //去掉标题行
            list.remove(0);
            for (int i = 0; i < list.size(); i++) {
                for (int j = 0; j < list.get(i).size(); j++) {
                    if (j == index) {
                        list.get(i).set(j, IdUtil.simpleUUID());
                        break;
                    }
                }
            }
            dao.importData(tableCode, list);
        } catch (Exception e) {
            log.info(e.getMessage());
            return false;
        }
        return true;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

Mapper

@Mapper
public interface FilterMapper extends BaseMapper<FilterInfo> {

}
1
2
3
4

@Mapper
public interface MyMapper {

    List<String> getTemplate(@Param("tableCode") String tableCode);

    List<TupleVO> getDimList(@Param("dimClass") String dimClass);

    IPage<LinkedHashMap<String, String>> getData(Page<LinkedHashMap<String, String>> objectPage, @Param("param") ReportListDTO param);

    boolean addOne(@Param("param") ReportAddDTO param);

    void importData(@Param("tableCode") String tableCode, @Param("list") List<List<Object>> list);

    boolean edit(@Param("id") String id, @Param("param") ReportEditDTO param);

    boolean del(@Param("param") ReportDelDTO param);

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Mapper的xml部分

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.chashugu.lowcodecrud.MyMapper">

    <select id="getTemplate" resultType="java.lang.String">
        select column_name
        FROM information_schema.columns
        WHERE table_schema = 'xxxx'
          AND table_name = #{tableCode}
    </select>

    <select id="getDimList" resultType="com.chashugu.lowcodecrud.model.TupleVO">
        select sys_code `key`, sys_name 'value'
        from meta_info_mapping
        where sys_dim_class = #{dimClass}
        order by cast(sys_code as signed)
    </select>

    <select id="getData" resultType="java.util.LinkedHashMap">
        select t1.*
        from ${param.tableCode} t1
        <where>
            <if test="param.filter != null">
                <foreach collection="param.filter" item="item" separator=" ">
                    <choose>
                        <when test="item.columnCode == 'push_flag'">
                        </when>
                        <when test="item.columnCode == 'approval_flag'">
                        </when>
                        <otherwise>
                            <choose>
                                <when test="item.type == 'DATE'">
                                    <if test="item.startTime != null">
                                        and date(t1.${item.columnCode}) = date(#{item.startTime})
                                    </if>
                                </when>
                                <when test="item.type == 'DATERANGE'">
                                    <if test="item.startTime != null and item.endTime != null">
                                        and date(t1.${item.columnCode}) between date(#{item.startTime}) and
                                        date(#{item.endTime})
                                    </if>
                                </when>
                                <when test="item.type == 'DATETIME'">
                                    <if test="item.startTime != null">
                                        and str_to_date(t1.${item.columnCode},'%Y%m%d %H%i%s') =
                                        timestamp(#{item.startTime})
                                    </if>
                                </when>
                                <when test="item.type == 'DATETIMERANGE'">
                                    <if test="item.startTime != null and item.endTime != null">
                                        and str_to_date(t1.${item.columnCode},'%Y%m%d %H%i%s') between
                                        timestamp(#{item.startTime}) and timestamp(#{item.endTime})
                                    </if>
                                </when>
                                <otherwise>
                                    <if test="item.value != null and item.value != ''">
                                        and t1.${item.columnCode}=#{item.value}
                                    </if>
                                </otherwise>
                            </choose>
                        </otherwise>
                    </choose>

                </foreach>
            </if>
        </where>
    </select>

    <insert id="addOne">
        insert into ${param.tableCode}
        (
        <foreach collection="param.info" index="col" separator=",">
            ${col}
        </foreach>
        )
        values
        (
        <foreach collection="param.info" item="value" separator=",">
            #{value}
        </foreach>
        )
    </insert>

    <update id="edit">
        update ${param.tableCode}
        set
        <foreach collection="param.info" index="key" item="value" separator=",">
            ${key}=#{value}
        </foreach>
        where only_key=#{id}
    </update>

    <delete id="del">
        delete from ${param.tableCode}
        where only_key in
        <foreach collection="param.ids" open="(" close=")" item="item" separator=",">
            #{item}
        </foreach>
    </delete>

    <insert id="importData">
        insert into ${tableCode}
        values
        <foreach collection="list" item="item" separator=",">
            (
            <foreach collection="item" item="data" separator=",">
                #{data}
            </foreach>
            )
        </foreach>
    </insert>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/575092