Profile loading

mapper configuration file details

For the statement in the mapper configuration file, the element is described.

DQL

1. id: the unique identifier of a statement in mapper.xml. IDS can be repeated under different mapper.xml.
2. parameterType: type of input parameter.
3. resultType: returns the type encapsulated in the result set.
4. resultMap: the resultMap reference defined in mapper.xml.
5. userCache: whether to save L2 cache for query results.
6. Flush cache: after executing sql, the L1 cache and L2 cache of the current namespace will be emptied.
7. databaseId: label database vendor

DML

The differences between DQL and DQL are as follows

1. flushCache: true by default
2. useGeneratedKeys: if enabled, Mybatis will use the getGeneratedKeys method at the bottom of jdbc to get the value of the self incrementing primary key and apply it to insert and update.
3. keyProperty: used in conjunction with useGeneratedKeys to specify the property name of the incoming parameter object and apply it to insert and update.
4. Keycolumn: set the effective value of useGeneratedKeys to correspond to the column name in the database table, and apply it to insert and update.

Usegenerated keys: when inserting data, the self incremented id of the database is used as the primary key. If this attribute is true, the primary key can not be passed. mybatis will use the getGeneratedKeys method at the bottom to help us find out the id, put it into the id attribute and backfill it to the entity class.

By default, the primary key is used when the first column of a table is created in the database. However, there are exceptions. You need to specify the id of the entity class through keyProperty and the primary key id of the table in the database through keycolumn.

Cache in mapper.xml

L1 cache

By default, Mybatis only turns on the sqlsession based L1 cache. L2 cache is not enabled by default.

L2 cache

Cache based on SqlSessionFactroy level. A namepce corresponds to a L2 cache. If you need to enable the L2 cache for namepce, you need to declare a < cache > tag in the corresponding mapper.xml.

To enable the L2 cache, our pojo needs to implement the Serializable interface. To fetch cached data, perform a deserialization operation. Because L2 cache data may be stored in memory, it may be stored on disk. If we want to get this cache, we need to deserialize it. Therefore, POJOs in mybatis implement the Serializable interface.

Writing dynamic sql with mapper.xml

if tag

We do this sometimes.

where 1=1
<if test="xxx">
and xx=#{xx}
</if>

This is written to avoid the problem of an and if there is only one condition in the where statement.

We can also write:
Define the < where > tag, which will help us construct where and remove the first unnecessary and. However, the and tag should be written in front of the < if > tag.

If the habits are written later, the last condition will be followed by an and. How to deal with it?

<where>
<if test="xxx">
xx=#{xx} and
</if>
</where>

1. Don't write that.....
2. Use < trim > tags
   (1) prefix: appends the specified content to the entire label
   (2) prefixOverrides: remove the first specified keyword in the tag body
   (3) suffix: append the specified content to the end of the entire label
   (4) Suffix overrides: remove the last specified keyword in the tag body

<trim prefix="where" suffix="" suffixOverrides="and">
<if test="xxx">
xx=#{xx} and
</if>
</where>

choose, when, otherwise tag

If there are multiple conditions in a query, only one condition will be effective at a time. In this case, you need to use these three tags.

The meaning of expression is: if() else if() else()

 <choose>
        <when test="id != null and id != ''">
            where id = #{id}
        </when>
        <when test="name != null and name != ''">
            where name like concat('%', #{name}, '%')
        </when>
        <otherwise>
            where tel = #{tel}
        </otherwise>
    </choose>

set tag

In the update statement, using the set tag can help us remove the comma after the last set.

<set>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="tel != null and tel != ''">
            tel = #{tel},
        </if>
    </set>
    where id =#{id}

The comma after the last tel=#{tel} and the set tag will help us remove it.

foreach

It is mainly used in scenes where in is used. where xxx in();

where id in
 <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>

Batch update

 Department department = new Department();
    department.setName("Testing department");
    //Entity class to Map tool class
    BeanMap beanMap = BeanMap.create(department);

    Map<String, Object> departmentMap = new HashMap<>(2);
    departmentMap.put("id", "123456");
    departmentMap.put("beanMap", beanMap);
    sqlSession.update("dynamic.updateByMap", departmentMap);

<update id="updateByMap" parameterType="map">
    update test
    <foreach collection="beanMap" index="key" item="value" open="set " separator=",">
        <if test="value != null">
            ${key} = #{value}
        </if>
    </foreach>
    where id = #{id}
</update>

When foreach circulates the Map, the key of the key value pair is index and the value is item.

sql tag

There are some general sql statements, which can be extracted through the < sql > tag.

<sql id="columns">
    id, name, sex
</sql>

The following sql can obtain the corresponding columns by referencing sql.

<select id="testSql" resultType="map">
    select <include refid="columns"/> from test2
</select>

SQL statement fragments can also receive the parameter values passed by the < include > tag< The include > tag is not completely self closing. It has a < property > tag that can be written.

<sql id="columns">
    name, tel
    <if test="${testValue} != null and ${testValue} == true">
        , id
    </if>
</sql>

<select id="testSql" resultType="map">
    select 
    <include refid="columns">
      <property name="testValue" value="true"/>
    </include>
     from test2
</select>