mysql中动态sql的一次实际应用
一、前言
本次实际应用中，使用到了如下几个要点：
mysql的动态建表;
mysql的多表插入;
mysql的多表更新;
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<String, List<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<String, List<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%';