vlambda博客
学习文章列表

mysql中动态sql的一次实际应用

一、前言

本次实际应用中,使用到了如下几个要点:

  1. mysql的动态建表;

  2. mysql的多表插入;

  3. mysql的多表更新;

  4. mysql的多表删除;

二、使用场景

2.1 动态建表

要求建立多个表,例如电压、电流等表,这些表的字段是完全一样的,只有表名不一样,那么我们就可以把表名等字段放在基准表中,然后从基准表中获得所有的表名字段,以list的形式传入mybatis中,进行循环动态拼接创建。

1void createTelemetryTable(List<String> list);
 1<update id="createTelemetryTable" parameterType="java.util.List">
2        <foreach item="item" index="index" collection="list">
3            CREATE TABLE if not exists `${item}` (
4              sys_id bigint NOT NULL,
5              mpnt_id bigint NOT NULL,
6              data_date DATE NOT NULL,
7              data_point int NOT NULL,
8              data_item_id bigint NOT NULL,
9              val DECIMAL(10,2) NOT NULL,
10              KEY `${item}_sys_id` (`sys_id`),
11              KEY `${item}_mpnt_id` (`mpnt_id`),
12              KEY `${item}_data_date` (`data_date`),
13              KEY `${item}_data_point` (`data_point`),
14              KEY `${item}_data_item_id` (`data_item_id`)
15            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
16        </foreach>
17    </update
>

部分截图如下:

2.2 多表批量插入

应用场景就是实际使用中,拿到一批数据,然后这批数据对应到多个不同表,那么最好的方式就是通过map映射,例如:Map<表名,List<数据>>。那么这时候就需要自己来写动态sql了。

其中需要了解的两个mybatis注解就是:@Insert 和@InsertProvider 都是用来在实体类的Mapper类中的注解保存方法的SQL语句。区别就是@Insert是直接配置sql语句,而@InsertProvider则是通过sql工厂类及对应的方法生产sql语句,这种方法的好处就是可以写出更为复杂的sql。

@InsertProvider的注解方式为:

1@InsertProvider(type = BaseMapperEnhanceImpl.class,method = "insertCalBatchTable")
2void insertBatch(@Param("tables"Map<StringList<CalcDateCurve>> ycTable);

说明:type指明sql工厂类,method是工厂类里对应的方法。

多表批量插入的代码如下:

 1/**
2 * 计算服务的批量插入:参数名称必须传 list ,例 @Param("list")
3**/

4    public String insertCalBatchTable(Map map){
5        Map<String,List> table = (Map<String,List>) map.get("tables");
6        String sql = table.keySet().stream().map(i -> insertBatch((Collection) table.get(i),i)).collect(Collectors.joining());
7        return sql;
8    }
9
10/**
11计算服务批量插入
12**/

13    private String insertBatch(Collection col,String table){
14        if(CollectionUtil.isEmpty(col)){
15            throw new RuntimeException("saveAll parameter size is zero or null");
16        }
17        Object t = null;
18        ArrayList list;
19        if(col instanceof List){
20            list = (ArrayList) col;
21            t = list.get(0);
22        }else{
23            throw new RuntimeException("Only list or set types are allowed");
24        }
25        Class cls = t.getClass();
26        Field[] fields = cls.getDeclaredFields();
27        StringBuffer sql = new StringBuffer();
28        sql.append("INSERT INTO `"+table+"` ");
29        sql.append("(");
30        for (Field field :fields) {
31            field.setAccessible(true);
32            TableField tableField = field.getAnnotation(TableField.class);
33            if(ObjectUtil.isNotNull(tableField)){
34                sql.append(tableField.value()+",");
35            }
36        }
37        sql.delete(sql.length()-1,sql.length());
38        sql.append(") VALUES ");
39        for (int i = 0; i < list.size(); i++) {
40            sql.append(list.get(i).toString());
41            if (i < list.size() - 1) {
42                sql.append(",");
43            }
44        }
45        sql.append(";");
46        return sql.toString();
47    }

说明:可以看下这个插入对象的实体类:需要在待插入的字段上加上@TableField注解,然后通过重写toString来获取sql的插入语句:然后sql会自动把这些值给拼接上去。最后通过这个注解将这条动态语句插入到数据库中。

1  @Override
2    public String toString() {
3        return "("+this.getSysId()+","+this.getMpntId()+",'"+this.getDataDate()+"',"+this.getDataPoint()+","this.getDataItemId()+","this.getVal()+")";
4    }

2.3 多表批量更新

应用场景与上同理,对多个表进行批量更新。传参形式依旧为如下:Map<表名,List<数据>>。同理,在实体类中写了个获取update sql的方法:

 1@Data
2@NoArgsConstructor
3public class CalcDateCurve {
4
5    @TableField(value = "sys_id")
6    private long sysId;
7
8    @TableField(value = "mpnt_id")
9    private long mpntId;
10
11    @TableField(value = "data_date")
12    private Date dataDate;
13
14    @TableField(value = "data_point")
15    private Integer dataPoint;
16
17    @TableField(value = "data_item_id")
18    private long dataItemId;
19
20    @TableField(value = "val")
21    private BigDecimal val;
22
23    private String key;
24
25    @Override
26    public String toString() {
27        return "("+this.getSysId()+","+this.getMpntId()+",'"+this.getDataDate()+"',"+this.getDataPoint()+","this.getDataItemId()+","this.getVal()+")";
28    }
29
30    public String updateSql() {
31        return "val ="+this.getVal()+" where sys_id="+this.getSysId() +" and mpnt_id="+this.getMpntId() +" and data_date = '"+this.getDataDate()+"' and data_point="+this.getDataPoint()+" and data_item_id="+this.getDataItemId() ;
32    }
33}

mapper 层:

1 @UpdateProvider(type = BaseMapperEnhanceImpl.class,method = "updateCalBatchTable")
2    void updateBatch(@Param("tables"Map<StringList<CalcDateCurve>> ycTable);

工厂类方法:

 1/**
2计算服务的批量更新
3**/

4    private String updateBatch(Collection col,String table)  {
5        if(CollectionUtil.isEmpty(col)){
6            throw new RuntimeException("saveAll parameter size is zero or null");
7        }
8        Object t = null;
9        ArrayList list;
10        if(col instanceof List){
11            list = (ArrayList) col;
12            t = list.get(0);
13        }else{
14            throw new RuntimeException("Only list or set types are allowed");
15        }
16        Class cls = t.getClass();
17
18        Field[] fields = cls.getDeclaredFields();
19        StringBuffer sql = new StringBuffer();
20        for (int i = 0; i < list.size(); i++) {
21            try {
22                sql.append("UPDATE `"+table+"` SET ");
23                sql.append(list.get(i).getClass().getDeclaredMethod("updateSql").invoke(t));
24            } catch (NoSuchMethodException e) {
25                e.printStackTrace();
26            } catch (IllegalAccessException e) {
27                e.printStackTrace();
28            } catch (InvocationTargetException e) {
29                e.printStackTrace();
30            }
31        }
32        sql.append(";");
33        return sql.toString();
34    }

说明:这块用到了反射该类来获取该类的某个方法,也是自己在项目中第一次用到反射的东西。

2.4 批量删除表

这个小玩意也记录下来,手动去清除表:查出所有语句遍历执行也是可以改进的。

1Select CONCAT'drop table ', table_name, ';' )
2FROM information_schema.tables
3Where  table_name LIKE 'calc%';