Mybatis Input output mapping and dynamic SQL Review

  • 2020-06-07 04:26:28
  • OfStack

1. Input mapping

Specify the type of the input parameter through parameterType, which can be a simple type, pojo wrapper class, HashMap, and so on

1. Enter a simple type


<select id="findUserById" parameterType="int" resultType="com.mybatis.po.User"> 
    select * from user where id=#{id} 
</select> 

2. Enter the pojo wrapper class


<select id="findUserById" parameterType="om.mybatis.po.User" resultType="com.mybatis.po.User"> 
    select * from user where username like  ' %{user.username}%' 
</select>

The Pojo class creates an extended entity for a single 1 entity, an extended class for the User class -User and a composite entity for the order entity, based on business requirements.

3. Enter HashMap type


<select id="findUserById" parameterType="hashmap" resultType="com.mybatis.po.User"> 
    select * from user where id=#{id} and username like  ' %{username}%' 
</select> 

The parameters id and username correspond to ES29en-ES30en in hashmap

2. Output mapping

1. resultType type output

The output mapping is done using resultType, which can only be successfully mapped if the column name is queried and the attribute name in pojo is 1. For single-table queries, using this type of output when cascading queries requires recreating the associated pojo extension class for mapping.

The pojo object will not be created if the queried column name does not match the attribute name in pojo. The pojo object is created as long as the queried column name and the attribute in pojo have a 1. A query field that failed to map is returned empty.

2. resultMap type output

If the queried column name does not correspond to the pojo attribute name, resultMap can be used to define a mapping relationship between the resultMap column name and the pojo attribute name. The output is mapped.

1) Define resultMap


<!--  define resultMap 
 will SELECT id id_,username username_ FROM USER  and User Class 1 Mapping relation    
type : resultMap Final mapped java Object type , You can use aliases  
id : resultMap The only 1 logo  
 --> 
 <resultMap type="user" id="userResultMap"> 
  <!-- id Represents only in the query result set 1 logo   
  column : Query out of the column name  
  property : type The specified pojo The name of an attribute in a type  
   In the end resultMap right column and property As a 1 Mapping relation   (Correspondence)  
  --> 
  <id column="id_" property="id"/> 
  <!-- result : Definition for a common name mapping  
  column : Query out of the column name  
  property : type The specified pojo The name of an attribute in a type  
   In the end resultMap right column and property As a 1 Mapping relation   (Correspondence)  
   --> 
  <result column="username_" property="username"/> 
 </resultMap> 

2) Use resultMap as the output mapping type of statement


<!--  use resultMap Do output mapping  
resultMap : Specifies the definition resultMap the id If this resultMap On the other mapper File, the front needs to be added namespace 
--> 
<select id="findUserByIdResultMap" parameterType="int" resultMap="userResultMap"> 
  SELECT id id_,username username_ FROM USER WHERE id=#{value} 
</select> 

(3) dynamic SQL

The Mybatis core flexibly operates the sql statement, evaluates the expression, and performs flexible splicing and assembly of sql.

1. Dynamic SQL example

First, create the pojo class, provide the mapper mapping file corresponding to the pojo, and configure the crud method separately


<update id="updateByExampleSelective" parameterType="map" > 
  update items 
  <set > 
   <if test="record.id != null" > 
    id = #{record.id,jdbcType=INTEGER}, 
   </if> 
   <if test="record.name != null" > 
    name = #{record.name,jdbcType=VARCHAR}, 
   </if> 
   <if test="record.price != null" > 
    price = #{record.price,jdbcType=REAL}, 
   </if> 
   <if test="record.pic != null" > 
    pic = #{record.pic,jdbcType=VARCHAR}, 
   </if> 
   <if test="record.createtime != null" > 
    createtime = #{record.createtime,jdbcType=TIMESTAMP}, 
   </if> 
   <if test="record.detail != null" > 
    detail = #{record.detail,jdbcType=LONGVARCHAR}, 
   </if> 
  </set> 
  <if test="_parameter != null" > 
   <include refid="Update_By_Example_Where_Clause" /> 
  </if> 
 </update> 

2. Fragment of SQL

The judgment conditions in SQL are encapsulated to improve reuse

1) Define sql fragment


<!--  define sql fragment  
  id : sql Fragments of wei  1 logo  
   It is best based on a single table sql Clip. This is what it says sql Fragment reusability is high  
   in sql Don't include it in the clip  where 
   --> 
  <sql id="query_user_where"> 
    <if test="userCustom!=null"> 
      <if test="userCustom.sex!=null and userCustom.sex!=''"> 
        and user.sex = #{userCustom.sex} 
      </if> 
      <if test="userCustom.username!=null and userCustom.username!=''"> 
        and user.username LIKE '%${userCustom.username}%' 
      </if> 
      <if test="ids!=null"> 
      <!--  use  foreach Traverse the incoming ids 
      collection : Specify input   Collection properties in an object  
      item : In each traversal generated object  
      open : Starts a string that traverses the collation  
      close : Ends the traversal splice of a string  
      separator : A string that needs to be spliced between two objects being traversed  
       --> 
       <!--  Use the implementation below sql Stitching:  
       AND (id=1 OR id=10 OR id=16)  
       --> 
      <foreach collection="ids" item="user_id" open="AND (" close=")" separator="or"> 
        <!--  Each traversal needs to be spliced string  --> 
        id=#{user_id} 
      </foreach> 
      <!--  implementation   "  and id IN(1,10,16) "Joining together  --> 
      <!-- <foreach collection="ids" item="user_id" open="and id IN(" close=")" separator=","> 
         Each traversal needs to be spliced string  
        #{user_id} 
      </foreach> --> 
      </if> 
    </if> 
  </sql> 

2) Reference to sql fragment


<!--  Comprehensive query of user information  
  #{userCustom.sex}: Take out the pojo The gender value in the wrapper object  
  ${userCustom.username} Out: pojo The user name in the wrapper object  
   --> 
<select id="findUserList" parameterType="cn.itcast.mybatis.po.UserQueryVo" resultType="cn.itcast.mybatis.po.UserCustom"> 
  SELECT * FROM USER 
  <!-- 
  where Can automatically remove the condition of the first 1 a and 
   --> 
  <where> 
    <!--  reference sql fragment   the id If the refid The specified id Is not this mapper In the file, you need the front edge namespace --> 
    <include refid="query_user_where"></include> 
    <!--  There are other references to be made here sql fragment  --> 
  </where> 
</select> 

Note: Note when using dynamic sql

1. The difference between #{} and ${}

#{} represents a placeholder symbol. #{} accepts input parameters of type pojo, hashmap. When using #{} to receive simple type parameters, #{} can be written as value or something else. When receiving the pojo object value, write the property value of the object, such as object.attribute.attribute.attribute.attribute. The way to get.

${}${} represents 1 splice symbol that receives input parameters of type pojo, hashmap. Accept simple type, only value in ${}. Accept the pojo object the same as #{}. Note that the $splice will refer to sql injection, so ${} is not recommended.

2. where tag is used. When the first and condition is empty, it will be automatically skipped. So you can keep sql syntax correct by not adding where 1=1.

3. Return the use of the primary key ES126en-ES127en element after sql is executed & Use of the SELECT LAST_INSERT_ID() function

1)id is a self-augmenting type


<!--  Add user   
parameterType : Specify input   The parameter type is pojo (including   User information)  
#{} Specified in the pojo Attribute name received pojo The property value of the object, mybatis through OGNL Gets the property value of the object  
--> 
<insert id="insertUser" parameterType="cn.itcast.mybatis.po.User"> 
  <!--  
   Returns the primary key of the inserted data, and returns to user In the object  
  SELECT LAST_INSERT_ID() : get just insert The value of the primary key entered is only applicable to the primary key  
  keyProperty : Sets the primary key value of the query to parameterType Which property of the specified object  
  order : SELECT LAST_INSERT_ID() Execution order, relative to insert Statement about the order in which it is executed  
  resultType : specify SELECT LAST_INSERT_ID() Result type of  
   --> 
  <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer"> 
    SELECT LAST_INSERT_ID() 
  </selectKey> 
  insert into user(username,birthday,sex,address) value(#{username},#{birthday},#{sex},#{address})     
</insert> 

2) id non-additive, uuid type -mysql select uuid() function


<!--  
 use mysql the uuid () Generate the primary key  
 Execution process:  
 First of all by uuid() Get the primary key, set the primary key to user The object's id Properties of the  
 Second in insert When executed, from user Extract from object id Attribute values  
 --> 
<selectKey keyProperty="id" order="BEFORE" resultType="java.lang.String"> 
  SELECT uuid() 
</selectKey> 
insert into user(id,username,birthday,sex,address) value(#{id},#{username},#{birthday},#{sex},#{address}) 

Related articles: