声明:
此处为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>
感谢观看