MyBatis学习笔记（二）——标签使用

不念不忘少年蓝@ 2022-06-03 04:37 224阅读 0赞

### resultMap手动映射 ###

当数据库表中的字段名称与pojo的实体类的名称不同的时候，使用resultMap：  
示例代码：

<mapper namespace="com.zrxjuly.mybatis.mapper.OrderMapper">
    
        
        <resultMap type="Orders" id="orders">
            <result column="user_id" property="userId"/>
        </resultMap>    
    
        
        <select id="selectOrder" resultMap="orders">
            select * from orders
        </select>
    </mapper>

## 动态sql ##

通过MyBatis提供的各种标签方法实现动态拼接sql。

### if标签 ###

示例代码：

<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
        select * from user 
        where 1=1
        <if test="sex != null and sex != ''">
            sex=#{sex} 
        </if>
        <if test="username != null and username != ''">
            and username=#{username}
        </if> 
     </select>

注：where后的1=1的含义：如果sex为空的话，则语句就会变成`select * from user where and username=#{username}` ，这样就会报sql语法错误。1=1意思是true

### where标签 ###

不用where 1=1的方法，直接用where标签：

<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
        select * from user
        
        <where>
            <if test="sex != null and sex != ''">
                sex=#{sex} 
            </if>
            <if test="username != null and username != ''">
                and username=#{username}
            </if> 
         </where>
     </select>

### sql片段 ###

若sql语句中多次出现重复部分，可以提取出来公用：

<sql id="selectA">
        select * from user
      </sql>
    
      <select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
        <include refid="selectA" />
        
        <where>
            <if test="sex != null and sex != ''">
                sex=#{sex} 
            </if>
            <if test="username != null and username != ''">
                and username=#{username}
            </if> 
         </where>
      </select>

### foreach标签 ###

当要求根据多个参数进行查询时，用foreach循环遍历多个值：

<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
        select * from user
        <where>
            id in 
            <foreach collection="idList" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

参数为Integer\[\]类型：

<select id="selectUserInteger" resultType="User">
        select * from user
        <where>
            id in 
            <foreach collection="array" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

参数为`List<Integer>`类型:

<select id="selectUserList" resultType="User">
        select * from user
        <where>
            id in 
            <foreach collection="list" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

参数为类集合：`List<User>`

<delete id="deleteBatch" parameterType="User">
        DELETE FROM userinfo
              WHERE id IN 
        <foreach collection="list" item="userList" open="(" close=")" separator=",">
            #{userList.id}
        </foreach>
    </delete>