Mybatis mapping

one-on-one

//Custom mapping
<resultMap type="User" id="userRoleResult">
    <result column="roleName" property="userRoleResult">
</resultMap>
<select id="getUserList5" resultMap="userRoleResult">
    select
    	su.id,
		su.userCode,
		su.userName,
		su.gender,
		su.phone,
		sr.roleName
     FROM
        smbma_user su
        LEFT JOIN smbms_role sr ON su.userRole=sr.id

resuleMap property

​ id:
Unique identification of the resultMap
​ type:
java entity class
resultMap child element
​ id
Generally, it corresponds to the primary key of a given row in the database
​ result
A property mapped to a "simple type" in a java bean
​ association
Attributes mapped to a "complex type" of a javabean, applicable to one to many
​ collection
Map to a complex type property of a java bean, such as a collection

<resulrMap type="User" id="userRoleResult">
    <id column="id" property="id"/>
    <result column="roleName" property="userRoleName"/>
   	SELECT
    	su.id,
		su.userCode,
		su.userName,
		su.gender,
		su.phone,
		sr.roleName
     FROM
        smbma_user su
        LEFT JOIN smbms_role sr ON su.userRole=sr.id

explain:
Column: the order in the current column is converted to the attribute value in the object
The id is the same as the result, but the primary key is more efficient
When you want to query multiple information of another table, you cannot directly add the information in multiple tables to the current class. You can encapsulate the value of the object you want to query into an object and take out the information of another table without breaking the encapsulation.

association

• Complex type associations, one-to-one
• Internal thimble
  Mapping a nested javabean property
• attribute
  Property: the property of the entity object that maps the database column
  javaType: full Java class name or alias
  resultMap: reference external resultMap
• Child element
  • id
  • result
    Property: the property (object name) of the entity object that maps the database column
    Column: database column name or alias
    You cannot use the same alias (multi table query)

In the above case, javaType can be extracted

<mapper namespace="com.kgc.dao.UserMapper">
    <resultMap type="User" id="userRoleResult">
    	//Primary key mapping
    	<id column="id" property="id"/>
    	<result column="userName" property="userName"/>
    	//Assign a value to the attribute role object in the User
    	<association property="role" resultMap="roleResult">
    </resultMap>
    
    //Assignment in object
    <resultMap type="Role" id="roleResult">
    	<id column="roleId" property="roleCode">
    	<result column="roleCode" property="roleCode"/>
    	<result column="doleName" property="roleName"/>
    </restultMap>
    
    
    
    <select id="getUserList1" resultMap="userRoleResult">
    SELECT
    	su.id,
		su.userCode,
		su.userName,
		su.gender,
		su.phone,
		sr.roleName
     FROM
        smbma_user su
        LEFT JOIN smbms_role sr ON su.userRole=sr.id
    
    

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-wwkzvicw-16406729280) (C: \ users \ L \ appdata \ roaming \ typora \ user images \ image-20211009223304715. PNG)]

The primary key mapping can only map the desired result after mapping
    

mybatis cache

• L1 cache
  Scope: in the same and sql session
  Default on
  The second query directly gets the data in a sentence cache

• L2 cache
  Scope: get in the same namespace

  Opening conditions:
  In mapper The corresponding statement must be configured in the XML file

  <cache exiction="FIFO" flushInterval="60000"
      size="512" readOnly="true"/>
  

  If a tag cannot be used, it is: Usercahe = "false";

  mapper:namespace
  select

  • id

  • parameterType

  • resultType/resultMap

    insert.update,delete

    • id
    • parameterType

  Dynamic sql statement query

  Based on OGNL expression

  Multi condition query completion logic

  The main elements used to implement dynamic SQL are

  • if
    1. Just use the if statement

  <select if="getUserList1" resultType="User">
      SELECT * FROM smbms_user
      WHERE 1=1
      <if test="userRole!=null">
          AND userRole=#{userRole}
  	</if>
  	<if test="userName!=null and userName!=''">
          AND userName LIKE CONCAT('%',#{userName},'%')
      </if>
  </select>
  

  1. With if and where

     SELECT * FROM smbms_user AND userRole=#{userRole} AND userName LIKE CONCAT('%',#{userName},'%')

  • trim
    Note: it can be used to dynamically replace where and set

    Use the where tag to automatically delete prefixes using the tag prefixOverrides

    Similarly, using trim can replace the set tag

    The essence of the set tag: delete the suffix "," in the statement to ensure the correct use of the update statement

    I Using trim to replace the where tag

  <select id="getUserList3" resultType="User">
      SELECT * FROM smbms_user
      <trim prefix="WHERE" prefixOverrides="AND">
      	<if test="userRole!=null">
              AND userRole= #{userRole}
  		</if>
          <if test!="userName!=null" and userName!=''">
              AND userName LIKE CONCAT('%',#{userName},'%')
          </if>
      </trim>
  </select>
  

  II Using trim to replace the where tag

  <mapper namespace="com,kgc.dao.UserMapper">
     update smbms_user
     <trim prefix="set" suffixOverrides="," suffix="WHERE id=#{id}">
      #suffixOverrides identifies the suffix that handles ","
          <if test="userName!=null and userName!=''">
              userName=#{userName},
          </if>
          <if test="userPassword!=null and userPassword!=''">
              userPassword=#{userPassword},
          </if>
          <if test="gender!=null">
              gender=#{gender},
          </if>
     </trim>
          WHERE
          	id=#{id}
  </update>
  

  • where
  • Set: the set tag can automatically delete the redundant "," to ensure the normal query of the database,

  Only for dynamic processing of tags

  <mapper namespace="com,kgc.dao.UserMapper">
     update smbms_user
     <set>
          <if test="userName!=null and userName!=''">
              userName=#{userName},
          </if>
          <if test="userPassword!=null and userPassword!=''">
              userPassword=#{userPassword},
          </if>
          <if test="gender!=null">
              gender=#{gender},
          </if>
     </set>
          WHERE
          	id=#{id}
  </update>
  

  • foreach

  I Normal array

  <mapper namespace="com.kgc.dao.UserMapper">
      <select id="getUserList1" resultType="User">
      SELECT
      	*
      FROM
      	smbms_user
      WHERE
      	id IN
      <foreach collection="array" item="id" open"(",separator=",",close=")">
      #{id}
  	</foreach>
    </select>
  </mapper>
  

  II Dynamic array

  <mapper namespace="com.kgc.dao.UserMapper">
      <select id="getUserList1" resultType="User">
      SELECT
      	*
      FROM
      	smbms_user
      WHERE
      	id IN
      #The parameters passed in are collections
      <foreach collection="List" item="id" open"(",separator=",",close=")">
      #{id}
  	</foreach>
    </select>
  </mapper>
  

  III Multiple array

  <mapper namespace="com.kgc.dao.UserMapper">
      <select id="getUserList1" resultType="User">
      SELECT
      	*
      FROM
      	smbms_user
      WHERE
      	id IN
      #The parameter passed in is the KEY value of the parameter in the List in the map
      <foreach collection="idList" item="id" open"(",separator=",",close=")">
      #{id}
  	AND gender=2
  	</foreach>
    </select>
  </mapper>
  

  • choose

  Query of dynamic sql conditional statements

  The choose tag is equivalent to the switch statement in java

  <mapper namespace="com.kgc.dao.UserMapper">
      <select id="getUserList3" resultType="User">
      SELECT
      	*
      FROM
      	smbms_user
      <where>
      	<choose>
      		<when test="userName != null and userName!=''">
      			AND userName LIKE CONCAT('%',#{userName},'%')
      		</when>
      		<when test="userRole!=null">
      			AND userRole=#{userRole}
  			</when>
              <otherwise>
                  AND gender=#{gender}
              </otherwise>
      	</choose>
      </where>
    </select>
  </mapper>