In some scenarios, a set of data needs to be inserted into the database and updated if it already exists. If the database is mysql, we can use the on duplicate key update statement instead of dealing with insert and update separately.

 

I've read many posts before. Table structure and entity class are either humps or lowercase. In my project, table structure is named by underlining, which leads to confusion when I use it at the beginning. So I also paste table structure and DO structure to make a comparison.

I. table structure (underline)

DROP TABLE IF EXISTS planflow;
CREATE TABLE planflow
(
	plan_no            	    int             DEFAULT 0          NOT NULL ,
	flow_no             	    int             DEFAULT 0          NOT NULL ,
	plan_type                   char            DEFAULT ' '        NOT NULL ,
	plan_invest                 decimal(20,4)   DEFAULT 0.0        NOT NULL ,
	plan_profit                 decimal(20,4)   DEFAULT 0.0        NOT NULL ,
	plan_balance                decimal(20,4)   DEFAULT 0.0        NOT NULL 
);
ALTER TABLE planflow ADD PRIMARY KEY(plan_no,flow_no);

II. DO (hump)

@EqualsAndHashCode
@ToString
@Table(name="planflow")
public class PlanFlowDO {
    @Id
    @Column(name = "plan_no")
    private Integer planNo;

    @Id
    @Column(name = "flow_no")
    private Integer flowNo;

    @Column(name = "plan_type")
    private String planType;

    @Column(name = "plan_invest")
    private BigDecimal planInvest;

    @Column(name = "plan_profit")
    private BigDecimal planProfit;

    @Column(name = "plan_balance")
    private BigDecimal planBalance;
    
   /**
    *   get()And set() methods omitted
    */
}

III. insert or update (single item)

Note: after the on duplicate key update statement, two values before and after the equal sign use table fields, for example, plan ﹣ type = values (plan ﹣ type)

<insert id="insertOrUpdatePlan" parameterType="com.happy.hhome.bean.plan.PlanFlowDO">
        insert into planflow
        (
            plan_no       ,
            flow_no        ,
            plan_type      ,
            plan_invest    ,
            plan_profit    ,
            plan_balance
        )
        values (
            #{planNo}    ,
            #{flowNo}    ,
            #{planType}  ,
            #{planInvest},
            #{planProfit},
            #{planBalance}
        )
        on duplicate key update
            plan_type     = values(plan_type) ,
            plan_invest   = values(plan_invest) ,
            plan_profit   = values(plan_profit) ,
            plan_balance  = values(plan_balance)
</insert>

IV. insert or update (batch)

Just use < foreach > to generate values() statement. Similarly, after the on duplicate key update statement, two values before and after the equal sign use table fields, for example: plan_type = values(plan_type)

<insert id="insertOrUpdatePlanList" parameterType="com.happy.hhome.bean.plan.PlanFlowDO">
        insert into planflow
        (
            plan_no       ,
            flow_no        ,
            plan_type      ,
            plan_invest    ,
            plan_profit    ,
            plan_balance
        )
        values
        <foreach collection="list" item="item" index= "index" separator="," >
        (
            #{item.planNo}    ,
            #{item.flowNo}    ,
            #{item.planType}  ,
            #{item.planInvest},
            #{item.planProfit},
            #{item.planBalance}
        )
        </foreach>
        on duplicate key update
            plan_type     = values(plan_type) ,
            plan_invest   = values(plan_invest) ,
            plan_profit   = values(plan_profit) ,
            plan_balance  = values(plan_balance)
</insert>