Mybatis之动态sql(批量插入)

1. 需要用到的依赖

        <!-- 操作mysql --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.48</version><scope>runtime</scope></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.0</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.21</version></dependency><!-- 操作mysql -->

2. Spring Boot 配置文件

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://xxx.xxx.xx.xxx:4000/tpcc?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driverClassName=com.mysql.jdbc.Driver

3. 代码部分

@Mapper
@Repository
public interface MysqlMapper {/*** 批量插入竞争实况分析表** @param competitionList 实体*/@InsertProvider(type = BatchInsertCompetition.class, method = "batchInsertCompetition")void batchInsertCompetition(@Param("competition") List<Competition> competitionList);class BatchInsertCompetition {public String batchInsertCompetition(Map<String, List<Competition>> map) {List<Competition> compList = map.get("competition");StringBuilder sql = new StringBuilder();sql.append("insert ignore into ").append(compList.get(0).getTableName()).append(" ");sql.append("(tenant_id,business_line_id,keyword_name,province_name,province_code,city_name,city_code," +"device_name,first_raw_url,first_page_url,first_domain,first_advertiser_id,second_raw_url," +"second_page_url,second_domain,second_advertiser_id,third_raw_url,third_page_url,third_domain," +"third_advertiser_id,fetch_time,delete_flag) values");for (int i = 0; i < compList.size(); i++) {sql.append("(#{competition[+").append(i).append("].tenantId},").append("#{competition[+").append(i).append("].businessLineId},").append("#{competition[+").append(i).append("].keywordName},").append("#{competition[+").append(i).append("].provinceName},").append("#{competition[+").append(i).append("].provinceCode},").append("#{competition[+").append(i).append("].cityName},").append("#{competition[+").append(i).append("].cityCode},").append("#{competition[+").append(i).append("].deviceName},").append("#{competition[+").append(i).append("].firstRawUrl},").append("#{competition[+").append(i).append("].firstPageUrl},").append("#{competition[+").append(i).append("].firstDomain},").append("#{competition[+").append(i).append("].firstAdvertiserId},").append("#{competition[+").append(i).append("].secondRawUrl},").append("#{competition[+").append(i).append("].secondPageUrl},").append("#{competition[+").append(i).append("].secondDomain},").append("#{competition[+").append(i).append("].secondAdvertiserId},").append("#{competition[+").append(i).append("].thirdRawUrl},").append("#{competition[+").append(i).append("].thirdPageUrl},").append("#{competition[+").append(i).append("].thirdDomain},").append("#{competition[+").append(i).append("].thirdAdvertiserId},").append("#{competition[+").append(i).append("].fetchTime},").append("#{competition[+").append(i).append("].deleteFlag})");if (i < compList.size() - 1) {sql.append(",");} else {sql.append(";");}}System.out.println(String.format("【insert competition sql】 :: %s", sql.toString()));return sql.toString();}}
}

4. 本质就是在拼凑批量插入的原生sql语句：insert ignore into (a, b, c) values (1, 2, 3), (4, 5, 6);