一、前言

本次實際應用中，使用到了如下幾個要點：

mysql的動態建表;

mysql的多表插入;

mysql的多表更新;

mysql的多表刪除;

二、使用場景

2.1 動態建表

要求建立多個表，例如電壓、電流等表，這些表的字段是完全一樣的，只有表名不一樣，那么我們就可以把表名等字段放在基準表中，然后從基準表中獲得所有的表名字段，以list的形式傳入mybatis中，進行循環動態拼接創建。

void createTelemetryTable(List list);

復制代碼

CREATE TABLE if not exists `${item}` (

sys_id bigint NOT NULL,

mpnt_id bigint NOT NULL,

data_date DATE NOT NULL,

data_point int NOT NULL,

data_item_id bigint NOT NULL,

val DECIMAL(10,2) NOT NULL,

KEY `${item}_sys_id` (`sys_id`),

KEY `${item}_mpnt_id` (`mpnt_id`),

KEY `${item}_data_date` (`data_date`),

KEY `${item}_data_point` (`data_point`),

KEY `${item}_data_item_id` (`data_item_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

復制代碼

部分截圖如下：

2.2 多表批量插入

應用場景就是實際使用中，拿到一批數據，然后這批數據對應到多個不同表，那么最好的方式就是通過map映射，例如：Map>。那么這時候就需要自己來寫動態sql了。

其中需要了解的兩個mybatis注解就是：@Insert 和@InsertProvider 都是用來在實體類的Mapper類中的注解保存方法的SQL語句。區別就是@Insert是直接配置sql語句，而@InsertProvider則是通過sql工廠類及對應的方法生產sql語句，這種方法的好處就是可以寫出更為復雜的sql。

@InsertProvider的注解方式為：

@InsertProvider(type = BaseMapperEnhanceImpl.class,method = "insertCalBatchTable")

void insertBatch(@Param("tables") Map> ycTable);

復制代碼

說明：type指明sql工廠類，method是工廠類里對應的方法。

多表批量插入的代碼如下：

/**

* 計算服務的批量插入：參數名稱必須傳 list ，例 @Param("list")

**/

public String insertCalBatchTable(Map map){

Map table = (Map) map.get("tables");

String sql = table.keySet().stream().map(i -> insertBatch((Collection) table.get(i),i)).collect(Collectors.joining());

return sql;

}

/**

* 計算服務批量插入

**/

private String insertBatch(Collection col,String table){

if(CollectionUtil.isEmpty(col)){

throw new RuntimeException("saveAll parameter size is zero or null");

}

Object t = null;

ArrayList list;

if(col instanceof List){

list = (ArrayList) col;

t = list.get(0);

}else{

throw new RuntimeException("Only list or set types are allowed");

}

Class cls = t.getClass();

Field[] fields = cls.getDeclaredFields();

StringBuffer sql = new StringBuffer();

sql.append("INSERT INTO `"+table+"` ");

sql.append("(");

for (Field field :fields) {

field.setAccessible(true);

TableField tableField = field.getAnnotation(TableField.class);

if(ObjectUtil.isNotNull(tableField)){

sql.append(tableField.value()+",");

}

}

sql.delete(sql.length()-1,sql.length());

sql.append(") VALUES ");

for (int i = 0; i < list.size(); i++) {

sql.append(list.get(i).toString());

if (i < list.size() - 1) {

sql.append(",");

}

}

sql.append(";");

return sql.toString();

}

復制代碼

說明：可以看下這個插入對象的實體類：需要在待插入的字段上加上@TableField注解，然后通過重寫toString來獲取sql的插入語句：然后sql會自動把這些值給拼接上去。最后通過這個注解將這條動態語句插入到數據庫中。

@Override

public String toString() {

return "("+this.getSysId()+","+this.getMpntId()+",'"+this.getDataDate()+"',"+this.getDataPoint()+","+ this.getDataItemId()+","+ this.getVal()+")";

}

復制代碼

2.3 多表批量更新

應用場景與上同理，對多個表進行批量更新。傳參形式依舊為如下：Map>。同理，在實體類中寫了個獲取update sql的方法：

@Data

@NoArgsConstructor

public class CalcDateCurve {

@TableField(value = "sys_id")

private long sysId;

@TableField(value = "mpnt_id")

private long mpntId;

@TableField(value = "data_date")

private Date dataDate;

@TableField(value = "data_point")

private Integer dataPoint;

@TableField(value = "data_item_id")

private long dataItemId;

@TableField(value = "val")

private BigDecimal val;

private String key;

@Override

public String toString() {

return "("+this.getSysId()+","+this.getMpntId()+",'"+this.getDataDate()+"',"+this.getDataPoint()+","+ this.getDataItemId()+","+ this.getVal()+")";

}

public String updateSql() {

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

}

}

復制代碼

mapper 層：

@UpdateProvider(type = BaseMapperEnhanceImpl.class,method = "updateCalBatchTable")

void updateBatch(@Param("tables") Map> ycTable);

復制代碼

工廠類方法：

/**

* 計算服務的批量更新

**/

private String updateBatch(Collection col,String table) {

if(CollectionUtil.isEmpty(col)){

throw new RuntimeException("saveAll parameter size is zero or null");

}

Object t = null;

ArrayList list;

if(col instanceof List){

list = (ArrayList) col;

t = list.get(0);

}else{

throw new RuntimeException("Only list or set types are allowed");

}

Class cls = t.getClass();

Field[] fields = cls.getDeclaredFields();

StringBuffer sql = new StringBuffer();

for (int i = 0; i < list.size(); i++) {

try {

sql.append("UPDATE `"+table+"` SET ");

sql.append(list.get(i).getClass().getDeclaredMethod("updateSql").invoke(t));

} catch (NoSuchMethodException e) {

e.printStackTrace();

} catch (IllegalAccessException e) {

e.printStackTrace();

} catch (InvocationTargetException e) {

e.printStackTrace();

}

}

sql.append(";");

return sql.toString();

}

復制代碼

說明：這塊用到了反射該類來獲取該類的某個方法，也是自己在項目中第一次用到反射的東西。

2.4 批量刪除表

這個小玩意也記錄下來，手動去清除表：查出所有語句遍歷執行也是可以改進的。

Select CONCAT( 'drop table ', table_name, ';' )

FROM information_schema.tables

Where table_name LIKE 'calc%';

復制代碼

總結

以上是生活随笔為你收集整理的mysql动态sql是什么,mysql中动态sql的一次实际应用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。