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