If mybatis writes in batch, modify if yes, add if no, and judge the field of null selective writing

Time:2020-2-13

If there is data in a single line, modify it or add it if there is no data

The table creation statement of this case is:

-- auto-generated definition
create table contact_type
(
    sid varchar(50) not null
        primary key,
    name varchar(50) default '' null,
    Status int default 1 null comment 'status, default 1 means valid, 0 means frozen',
    seq float default 0 null,
    create_time datetime default CURRENT_TIMESTAMP null
)
Comment 'transaction unit type';

So the primary key is a string type, not an auto increment type. Written inMybatisOfxmlThe SQL statements in the file are as follows:

<insert id="saveOne" parameterType="com.ccsoft.femis.model.ContactType">
    <!--<selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER">-->
      <!--SELECT LAST_INSERT_ID()-->
    <!--</selectKey>-->

    insert into contact_type
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="create_time != null"> create_time, </if>
      <if test="name != null"> name, </if>
      <if test="seq != null"> seq, </if>
      <if test="sid != null"> sid, </if>
      <if test="status != null"> status, </if>
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="create_time != null"> #{create_time},</if>
      <if test="name != null"> #{name},</if>
      <if test="seq != null"> #{seq},</if>
      <if test="sid != null"> #{sid},</if>
      <if test="status != null"> #{status},</if>
    </trim>
    ON DUPLICATE KEY UPDATE
    <trim suffixOverrides=",">
      <if test="create_time != null"> create_time = #{create_time}, </if>
      <if test="name != null"> name = #{name}, </if>
      <if test="seq != null"> seq = #{seq}, </if>
      <if test="sid != null"> sid = #{sid}, </if>
      <if test="status != null"> status = #{status}, </if>
    </trim>
  </insert>

You can see that because of the<if test=...>IfJavaIf some properties of the object passed from the end are not set, resulting in the property being empty in the object, the final execution will be performedSQLThe field does not exist in the statement. The primary key field created by the test table is not self increasing, so the abovexmlHead inSQLThe statement (return the primary key field value of the new row) has been commented out. If you indicate that there is an auto increase field, you can get the auto increase field value of the new row. Through injavaIn the middle test, it is found that the above statement will return 1 after adding a new line successfully, and 2 after modifying successfully (there are doubts here, if you have clear reasons, please follow the post). If there is an unset property whose corresponding field on the database side is set to be non empty and there is no default value, which leads to adding or modifying failure, an exception will be reportedjava.sql.SQLException

If the data written in batch is yes, modify it or not, then add it. At the same time, judge the blank optional write field

The data table is still the one on it, which is directly pasted out and written inMybatisOfXMLDocumentSQLYes,

<insert id="saveBatch" parameterType="java.util.List">
    <!--<selectKey resultType="java.lang.String" keyProperty="sid" order="AFTER">-->
      <!--SELECT LAST_INSERT_ID()-->
    <!--</selectKey>-->

    <foreach collection ="list" item="ele" index= "index" separator =";">
      insert into contact_type
      <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="ele.create_time != null"> create_time, </if>
        <if test="ele.name != null"> name, </if>
        <if test="ele.seq != null"> seq, </if>
        <if test="ele.sid != null"> sid, </if>
        <if test="ele.status != null"> status, </if>
      </trim>
      values
      <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="ele.create_time != null"> #{ele.create_time},</if>
        <if test="ele.name != null"> #{ele.name},</if>
        <if test="ele.seq != null"> #{ele.seq},</if>
        <if test="ele.sid != null"> #{ele.sid},</if>
        <if test="ele.status != null"> #{ele.status},</if>
      </trim>
      ON DUPLICATE KEY UPDATE
      <trim suffixOverrides=",">
        <if test="ele.create_time != null"> create_time = #{ele.create_time}, </if>
        <if test="ele.name != null"> name = #{ele.name}, </if>
        <if test="ele.seq != null"> seq = #{ele.seq}, </if>
        <if test="ele.sid != null"> sid = #{ele.sid}, </if>
        <if test="ele.status != null"> status = #{ele.status}, </if>
      </trim>
    </foreach>
  </insert>

In the above codeSQLThe outermost layer of the statementforThe advantage of recycling is thatList<ContactType>When a collection of type is passed to write data, it can be added or modified. For example, there are three rows of data. The first three rows correspond to the attributes of the primary key fieldsidSet toNULL, a new row will be added to the database, and the second row of data is setsid, and the value has a corresponding row in the database, the data on the row in the database will be modified. But there is also a problem with this approach, which is to return toJavaThe data of is always 1, because each object is composed ofSQLThe interval symbol used between statements is;, the final number of rows returned is the lastSQLThe number of rows affected by the statement. If you have any other opinions, please follow the post.

pit

Because of theSQLThere are multiple statements on the database side, which need to beJavaSetting in the string connecting to the database&allowMultiQueries=true