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

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>

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

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 '元数据<下拉框使用>';

配置文件信息为

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

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);
    }
}

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;
}

@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;
}

@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;
}

@ApiModel("表头及筛选条件VO")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Alias("HeaderVO")
public class HeaderVO {
    @ApiModelProperty("表头")
    private List<TupleVO> header;
    @ApiModelProperty("筛选条件")
    private List<FilterVO> filter;
}

@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;

}

@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;
}

@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;
}

@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;
}

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

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

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

    @ApiModelProperty("Y轴")
    private String value;
}

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("导入失败");
    }
}

Service

public interface FilterService extends IService<FilterInfo> {

}

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

}

@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;
    }
}

Mapper

@Mapper
public interface FilterMapper extends BaseMapper<FilterInfo> {

}

@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);

}

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>