mybatis 批量插入mysql数据并根据唯一索引做自动去重判断的插入语句

最新推荐文章于 2024-12-27 09:02:08 发布

xxz_phoebe

最新推荐文章于 2024-12-27 09:02:08 发布

阅读量4.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql springboot

本文链接：https://blog.csdn.net/Alice_qixin/article/details/84970501

mysql 同时被 2 个专栏收录

27 篇文章

订阅专栏

springboot

21 篇文章

订阅专栏

本文介绍如何使用MyBatis操作MySQL数据库实现批量插入数据，并通过唯一索引判断数据是否重复，若重复则更新，不重复则新增。具体包括在DAO层定义批量插入方法，及在XML中实现具体的SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

声明：

此处为mybatis 操作mysql数据库实现批量插入。再根据表的唯一索引来判断数据是否重复，如果重复则更新，如果不重复则新增的xml写法

注意：

首先在表中创建可以确认数据唯一性的唯一索引：

例如：UNIQUE KEY `unionIndex` (`keyword_id`,`the_day`) USING BTREE,

dao层

import com.rrc.marketing.core.po.sem.SemKeywordReportBaiduEntity;
import com.rrc.marketing.core.po.sem.SemKeywordTypeBaiduEntity;
import com.rrc.marketing.core.util.MyMapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @Auther:qixin
 * @Date: 2018/10/28 14:52
 * @Description:
 */
public interface SemKeywordReportBaiduDao extends MyMapper<SemKeywordReportBaiduEntity> {
    //批量插入并去重
    void duplicateInsertList(@Param("semKeywordReportBaiduEntityList") List<SemKeywordReportBaiduEntity> semKeywordReportBaiduEntityList);
}

然后再xml写如下代码

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.rrc.marketing.core.dao.sem.SemKeywordReportBaiduDao" >

    <insert id="duplicateInsertList" parameterType="java.util.List">
        INSERT INTO sem_keyword_report_baidu (
        fr, channel_id, channel_name,
        account_id, account_name, campaign_id,
        campaign_name,campaign_city,
        adgroup_id, adgroup_name,
        keyword_id,keyword,
        cost,cost_real,click,impression,
        device,report_level,report_type,
        the_day,the_week,the_month,
        version,create_date,update_date
        )VALUES
        <foreach collection="semKeywordReportBaiduEntityList" item="item" index="index" separator=",">
            (
            #{item.fr,jdbcType=VARCHAR}, #{item.channelId,jdbcType=INTEGER}, #{item.channelName,jdbcType=VARCHAR},
            #{item.accountId,jdbcType=BIGINT}, #{item.accountName,jdbcType=VARCHAR}, #{item.campaignId,jdbcType=BIGINT},
            #{item.campaignName,jdbcType=VARCHAR},#{item.campaignCity,jdbcType=VARCHAR},
            #{item.adgroupId,jdbcType=BIGINT}, #{item.adgroupName,jdbcType=VARCHAR},
            #{item.keywordId,jdbcType=BIGINT}, #{item.keyword,jdbcType=VARCHAR},
            #{item.cost,jdbcType=DECIMAL},#{item.costReal,jdbcType=DECIMAL},#{item.click,jdbcType=INTEGER},#{item.impression,jdbcType=INTEGER},
            #{item.device,jdbcType=VARCHAR},#{item.reportLevel,jdbcType=INTEGER},#{item.reportType,jdbcType=INTEGER},
            #{item.theDay,jdbcType=VARCHAR},#{item.theWeek,jdbcType=VARCHAR},#{item.theMonth,jdbcType=VARCHAR},
            #{item.version,jdbcType=INTEGER}, #{item.createDate,jdbcType=TIMESTAMP},#{item.updateDate,jdbcType=TIMESTAMP}
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
        fr = VALUES(fr),channel_id = VALUES(channel_id),channel_name = VALUES(channel_name),
        account_id = VALUES(account_id),account_name = VALUES(account_name),campaign_id = VALUES(campaign_id),
        campaign_name = VALUES(campaign_name),campaign_city = VALUES(campaign_city),adgroup_id = VALUES(adgroup_id),adgroup_name = VALUES(adgroup_name),
        keyword = VALUES(keyword),cost = VALUES(cost),cost_real = VALUES(cost_real),
        click = VALUES(click),impression = VALUES(impression),device = VALUES(device),
        report_level = VALUES(report_level),report_type = VALUES(report_type),the_day = VALUES(the_day),
        the_week = VALUES(the_week),the_month = VALUES(the_month),version = VALUES(version),
        create_date = VALUES(create_date),update_date = VALUES(update_date)
    </insert>

</mapper>

感谢观看