Mybatis Mapper XML file - insert, update, delete detailed explanation (insert, update and delete)

Mybatis Mapper XML file - insert, update and delete

Data modification statements (insert, update, and delete) are very similar in implementation

<insert
  
  parameterType=""
  flushCache="true"
  statementType="PREPARED"
  keyProperty=""
  keyColumn=""
  useGeneratedKeys=""
  timeout="20">

<update
  
  parameterType=""
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

<delete
  
  parameterType=""
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

Insert, Update and Delete properties

Examples of inserting, updating, and deleting statements

<insert >
  insert into Author (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})
</insert>

<update >
  update Author set
    username = #{username},
    password = #{password},
    email = #{email},
    bio = #{bio}
  where id = #{id}
</update>

<delete >
  delete from Author where id = #{id}
</delete>

As mentioned earlier, insert statements are relatively richer, with some additional properties and child elements that can handle key generation in many ways.

If your database supports automatic generation of key fields (such as MySQL and SQL Server), then you can simply set useGeneratedKeys="true" and set keyProperty as the target property to complete the automatic generation of keys.

For example, if the above Author table uses the automatic column type as id, the statement will be modified as follows:

<insert  useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username,password,email,bio)
  values (#{username},#{password},#{email},#{bio})
</insert>

If your database also supports multi-row insertion operations, you can pass an author list or author array and get the automatically generated key values.

<insert  useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username, password, email, bio) values
  <foreach item="item" collection="list" separator=",">
    (#{}, #{}, #{}, #{})
  </foreach>
</insert>

For databases that do not support automatic column generation, or may not yet support automatic key generation in JDBC drivers, MyBatis provides another way to handle key generation.

Here is a simple (a little silly) example that will generate a random ID (which may not be quite doing in practice, but this demonstrates the flexibility of MyBatis and it doesn't mind that):

<insert >
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
  </selectKey>
  insert into Author
    (id, username, password, email,bio, favourite_section)
  values
    (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})
</insert>

In the example above,​selectKeyThe statement will be executed first, the id attribute of the Author object will be set, and then called​insert​Sentence. This allows for behavior similar to the database's automatic key generation without increasing the complexity of Java code.

The description of the `selectKey` element is as follows:

<selectKey
  keyProperty="id"
  resultType="int"
  order="BEFORE"
  statementType="PREPARED">

selectKey property

As a special case, some databases allow INSERT, UPDATE, or DELETE statements to return result sets (such as the RETURNING clause of PostgreSQL and MariaDB, or the OUTPUT clause of MS SQL Server).

This type of statement must be written as <select> to map the returned data.

<select  resultType=""
      affectData="true" flushCache="true">
  insert into Author (username, password, email, bio)
  values (#{username}, #{password}, #{email}, #{bio})
  returning id, username, password, email, bio
</select>

sql

This element can be used to define reusable SQL code snippets and can be included in other statements. It can be parameterized when static (in the loading phase). Different attribute values ​​can vary in the included instance. For example:

<sql > ${alias}.id,${alias}.username,${alias}.password </sql>

This SQL fragment can then be included in another statement, for example:

<select  resultType="map">
  select
    <include ref><property name="alias" value="t1"/></include>,
    <include ref><property name="alias" value="t2"/></include>
  from some_table t1
    cross join some_table t2
</select>

The attribute value can also be used in the included `refid` attribute or the attribute value within the included clause, for example:

<sql >
  ${prefix}Table
</sql>

<sql >
  from
    <include ref/>
</sql>

<select  resultType="map">
  select
    field1, field2, field3
  <include ref>
    <property name="prefix" value="Some"/>
    <property name="include_target" value="sometable"/>
  </include>
</select>

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.