- Insert – map insert statements.
- Update – map update statements.
- Delete – map delete statements.
- select – map query statement
- sql – repeatable sentence blocks that can be referenced by other statements
- resultMap – describes how to load objects from the database result set. It is the most complex and powerful element
- selectKey and include
Select
Simple example
<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM PERSON WHERE ID = #{id}
</select>Properties of the select tag
<select id="selectPerson" parameterType="int" parameterMap="deprecated" resultType="hashmap" resultMap="personResultMap" flushCache="false" useCache="true" timeout="10" fetchSize="256" statementType="PREPARED" resultSetType="FORWARD_ONLY">
Explain key or common attributes
| id | A unique identifier in the namespace that can be used to reference this statement. |
| parameterType | The fully qualified name or alias of the class that will be passed into the parameters of this statement. This property is optional because MyBatis can infer the parameters of the specific incoming statement through the type handler. The default value is unset. |
| resultType | The fully qualified name or alias of the class that you expect to return results from this statement. Note that if a collection is returned, it should be set to the type contained in the collection, not the type of the collection itself. Only one can be used between resultType and resultMap at the same time. |
| resultMap | A named reference to an external resultMap. Result mapping is the most powerful feature of MyBatis. If you understand it thoroughly, many complex mapping problems can be solved. Only one can be used between resultType and resultMap at the same time. |
The attributes of insert, update and delete are basically the same, but there are several attributes that need attention. (usage will also be discussed in the following example)
| useGeneratedKeys | (only applicable to insert and update) this will make MyBatis use the getGeneratedKeys method of JDBC to retrieve the primary key generated inside the database (such as the auto increment field of relational database management systems such as MySQL and SQL Server). The default value is false. |
| keyProperty | (only applicable to insert and update) specify the attribute that can uniquely identify the object. MyBatis will use the return value of getGeneratedKeys or the selectKey sub element of the insert statement to set its value. The default value is unset. If there is more than one generated column, multiple attribute names can be separated by commas. |
| keyColumn | (only applicable to insert and update) set the column name of the generated key value in the table. In some databases (such as PostgreSQL), when the primary key column is not the first column in the table, it must be set. If there is more than one generated column, multiple attribute names can be separated by commas. |
The following are examples of insert, update and delete statements:
<insert id="insertAuthor">
insert into Author (id,username,password,email,bio)
values (#{id},#{username},#{password},#{email},#{bio})
</insert>
<update id="updateAuthor">
update Author set
username = #{username},
password = #{password},
email = #{email},
bio = #{bio}
where id = #{id}
</update>
<delete id="deleteAuthor">
delete from Author where id = #{id}
</delete>As mentioned earlier, the configuration rules of the insert statement are richer. There are some additional attributes and sub elements in the insert statement to handle the generation of the primary key, and a variety of generation methods are provided.
First, if your database supports fields that automatically generate primary keys (such as MySQL and SQL Server), you can set useGeneratedKeys = "true" and then set keyProperty as the target property. For example, if the above Author table has used automatic generation on the id column, the statement can be modified to:
<insert id="insertAuthor" 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, you can also pass in an Author array or collection and return the automatically generated primary key.
<insert id="insertAuthor" useGeneratedKeys="true"
keyProperty="id">
insert into Author (username, password, email, bio) values
<foreach item="item" collection="list" separator=",">
(#{item.username}, #{item.password}, #{item.email}, #{item.bio})
</foreach>
</insert>For databases that do not support automatic generation of primary key columns and JDBC drivers that may not support automatic generation of primary keys, MyBatis has another method to generate primary keys.
Here is a simple (and silly) example, which can generate a random ID (it is not recommended to be used in practice. This is just to show the flexibility and tolerance of MyBatis in dealing with problems):
<insert id="insertAuthor">
<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 above example, the statement in the selectKey element will be run first, and the id of the Author will be set, and then the insert statement will be called. This enables the database to automatically generate a primary key similar behavior, while maintaining the simplicity of Java code.
The description of the selectKey element is as follows:
<selectKey keyProperty="id" resultType="int" order="BEFORE" statementType="PREPARED">
| attribute | describe |
|---|---|
| keyProperty | The target property to which the result of the selectKey statement should be set. If more than one column is generated, multiple attribute names can be separated by commas. |
| keyColumn | Returns the column name of the generated column property in the result set. If more than one column is generated, multiple attribute names can be separated by commas. |
| resultType | Type of result. Usually MyBatis can be inferred, but for more accuracy, there will be no problem writing it. MyBatis allows any simple type to be used as the type of primary key, including string. If you generate more than one column, you can use an Object or Map that contains the desired attributes. |
| order | Can be set to BEFORE or AFTER. If it is set to BEFORE, it will first generate the primary key, set the keyProperty, and then execute the insert statement. If it is set to AFTER, the insert statement is executed first, and then the statement in the selectKey - this is similar to the behavior of Oracle database. There may be embedded index calls inside the insert statement. |
| statementType | As before, MyBatis supports mapping statements of state, PREPARED and CALLABLE types, representing Statement, PreparedStatement and CallableStatement types respectively. |
sql
This element can be used to define reusable SQL code fragments for use in other statements. Parameters can be determined statically (during loading), and different parameter values can be defined in different include elements. For example:
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>This SQL fragment can be used in other statements, for example:
<select id="selectUsers" resultType="map">
select
<include refid="userColumns"><property name="alias" value="t1"/></include>,
<include refid="userColumns"><property name="alias" value="t2"/></include>
from some_table t1
cross join some_table t2
</select>You can also use the attribute value in the refid attribute or internal statement of the include element, for example:
<sql id="sometable">
${prefix}Table
</sql>
<sql id="someinclude">
from
<include refid="${include_target}"/>
</sql>
<select id="select" resultType="map">
select
field1, field2, field3
<include refid="someinclude">
<property name="prefix" value="Some"/>
<property name="include_target" value="sometable"/>
</include>
</select>See the mybatis help document for details https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#
The resultmap tag is a concise understanding of the great God https://blog.csdn.net/xiaoliangknow/article/details/119379092?spm=1001.2014.3001.5501