• Mybatis的动态SQL详解

    使用动态SQL完成多条件查询

    动态SQL是MyBatis的一个强大的特性。动态SQL基于OGNL的表达式。实现动态SQL的元素如下。

    • if：利用if实现简单的条件选择
    • choose(when,otherwise)：相当于Java中的switch语句，通常与when和otherwise搭配
    • where：简化SQL语句中where的条件判断。
    • set：解决动态更新语句 trim：可以灵活地去除多余的关键字
    • foreach：迭代一个集合，通常用于in条件

    使用if+where实现条件查询

    1.if 多添加查询
    修改UserMapper.xml文件

    <select id="getUserList" resultMap="userList">
    SELECT u.*,r.roleName FROM USER u,Role r WHERE u.userRole=r.id
    <if test="userRole!=null">
        AND u.userRole=#{userRole}
    </if>
    <if test="userName!=null and userName!=''">
        AND u.userName LIKE concat('%',#{userName},'%')  
    </if> 
 </select>
 注：尽量避免用*此处作为演示

    修改接口中的方法

    List<User> getUserList(@Param("userRole")Integer roleId,@Param("userName")String userName);

    编写测试

    SqlSession sqlSession=null;
try{
    sqlSession=MyBatisUtil.createSqlSession();
    List<User> userList=sqlSession.getMapper(UserMapper.class).getUserList(2,null);
    for (User user: userList) {
        System.out.println(user.getUserName());
    }
}catch (Exception ex){
    ex.printStackTrace();
}finally {
    MyBatisUtil.closeSqlSession(sqlSession);
}

    2.where

    where元素标签会自动识别标签内是否有返回值，若有，就插入一个where，此外，若该标签返回的内容是and或者or卡头的，会自动剔除

    <!--注意开启自动映射-->
<select id="getUserList" resultType="User">
    SELECT * FROM USER
    <where>
        <if test="userName!=null and userName!=''">
            AND userName LIKE concat('%',#{userName},'%')
        </if>
        <if test="userRole!=null">
            AND userRole=#{userRole}
        </if>
    </where>
</select>

    编写测试

    SqlSession sqlSession=null;
try{
    sqlSession=MyBatisUtil.createSqlSession();
    Integer roleId=1;
    String userName="";     List<User> 
userList=sqlSession.getMapper(UserMapper.class).getUserList(roleId,userName);
    for (User user: userList) {
        System.out.println(user.getUserName());
    }
}catch (Exception ex){
    ex.printStackTrace();
}finally {
    MyBatisUtil.closeSqlSession(sqlSession);
}

    3. 使用if+trim实现多条件查询

    trim会自动识别其标签内是否有返回值，加入前缀或后缀

    <select id="getUserList" resultType="User">
    SELECT * FROM USER
    <trim prefix="where" prefixOverrides="and | or">
        <if test="userName!=null and userName!=''">
            AND userName LIKE concat('%',#{userName},'%')
        </if>
        <if test="userRole!=null">
            AND userRole=#{userRole}
        </if>
    </trim>
</select>

    prefix：前缀，作用是通过自动识别是否有返回值后，在trim包含的内容上加上前缀，如此处的 where
    suffix：后缀，作用是在trim包含的内容上加上后缀
    prefixOverrides：对于trim包含内容的首部进行指定内容（如此处的"and | or"）的忽略
    suffixOverrides：对用trim包含的内容的首尾进行指定内容的忽略

    2 使用动态SQL实现更新操作

    使用if+set改造更新操作
    set元素主要用于更新操作，在包含的语句前输出一个set，若包含的语句逗号结尾，自动忽略逗号

    <update id="modify" parameterType="User">
    UPDATE USER
     <set>
         <if test="userCode!=null">userCode=#{userCode},</if>
         <if test="userName!=null">userName=#{userName},</if>
         <if test="userPassword!=null">userPassword=#{userPassword},</if>
         <if test="gender!=null">gender=#{gender},</if>
         <if test="phone!=null">phone=#{phone},</if>
         <if test="address!=null">address=#{address},</if>
         <if test="userRole!=null">userRole=#{userRole},</if>
         <if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
         <if test="modifyDate!=null">modifyDate=#{modifyDate},</if>
         <if test="birthday!=null">birthday=#{birthday},</if>    
         </set>
    WHERE id=#{id}
</update>

    使用if+trim改造修改操作

    <update id="modify" parameterType="User">
    UPDATE USER
     <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
         <if test="userCode!=null">userCode=#{userCode},</if>
         <if test="userName!=null">userName=#{userName},</if>
         <if test="userPassword!=null">userPassword=#{userPassword},</if>
         <if test="gender!=null">gender=#{gender},</if>
         <if test="phone!=null">phone=#{phone},</if>
         <if test="address!=null">address=#{address},</if>
         <if test="userRole!=null">userRole=#{userRole},</if>
         <if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
         <if test="modifyDate!=null">modifyDate=#{modifyDate},</if>
         <if test="birthday!=null">birthday=#{birthday},</if>    
         </trim>
</update>

    3 使用foreach完成复杂查询

    foreach主要用在构建in条件中，在sql语句中迭代一个集合。它的主要属性有，item、index、 collection、separator、close、open。

    1.MyBatis入参为数组类型的foreach类型
    编写接口

    List<User> getUserByRoleId_foreach_array(Integer[] roleIds);

    修改UserMapper.xml

    <resultMap id="userMapByRole" type="User">
    <id property="id" column="id"/>
    <result property="userCode" column="userCode"/>
    <result property="userName" column="userName"/>
</resultMap>
<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
    SELECT * FROM USER WHERE userRole IN
    <foreach collection="array" item="roleIds" open="(" separator="," close=")">
        #{roleIds}
    </foreach>
</select>

    编写测试

    SqlSession sqlSession=null;
List<User> userList=new ArrayList<User>(); Integer[] roleIds={2,3}; try{
    sqlSession=MyBatisUtil.createSqlSession();
    
userList=sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleIds); }catch (Exception ex){
    ex.printStackTrace();
}finally {
    MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user:      userList) {
    System.out.println(user.getUserName()+"	"+user.getAddress());
}

    item：表示集合中每一个元素进行迭代时的别名
    index：指定一个名称，用于表示在迭代过程中，每次迭代到的位置
    open：表示该语句以什么开始（in语句以"（"开始）
    separator：表示在每次迭代之间以什么符号做分割符
    close：表示该语句以什么结束
    collection：必须指定，入参为单参类型是List时，collection属性值为list;入参为单参是数组时，为 array；若为多参，需封装Map
    parameterType可以不配置，MyBatis会自动封装为Map传入。

    在接口中添加方法
    List<User> getUserByRoleId_foreach_list(List<Integer> roleList);

    修改UserMapper.xml

    <resultMap id="userMapByRole" type="User">
    <id property="id" column="id"/>
    <result property="userCode" column="userCode"/>
    <result property="userName" column="userName"/>
</resultMap>
<select id="getUserByRoleId_foreach_list" resultMap="userMapByRole">
    SELECT * FROM USER WHERE userRole IN
    <foreach collection="list" item="roleIds" open="(" separator="," close=")">
        #{roleIds}     </foreach>
</select>

    编写测试

    SqlSession sqlSession=null;
List<User> userList=new ArrayList<User>(); List<Integer> nums=new ArrayList<Integer>(); nums.add(1); nums.add(2); try{
    sqlSession=MyBatisUtil.createSqlSession();
    userList=sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_list(nums); }catch (Exception ex){
    ex.printStackTrace();
}finally {
    MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user:
        userList) {
    System.out.println(user.getUserName()+"	"+user.getAddress());
}

    3.MyBatis入参为Map类型的foreach迭代

    处理多个参数入参,编写接口中的方法

    List<User> getUserByConditionMap_foreach_map(Map<String,Object> conditionMap);

    修改UserMapper.xml

    <select id="getUserByConditionMap_foreach_map" resultMap="userMapByRole">
    SELECT * FROM USER WHERE gender=#{gender} AND userRole IN
    <foreach collection="roleIds" item="roleMap" open="(" separator="," close=")">         #{roleMap}
    </foreach>
</select>

    编写测试

    SqlSession sqlSession = null;
List<User> userList = new ArrayList<User>();
Map<String, Object> param = new HashMap<String, Object>(); List<Integer> roleList = new ArrayList<Integer>(); roleList.add(1); roleList.add(2); param.put("gender",2); param.put("roleIds",roleList); try {
    sqlSession = MyBatisUtil.createSqlSession();
    userList = 
sqlSession.getMapper(UserMapper.class).getUserByConditionMap_foreach_map(param); } catch (Exception ex) {
    ex.printStackTrace();
} finally {
    MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user :         userList) {
    System.out.println(user.getUserName() + "	" + user.getAddress());
}

    剖析知识点

    1） MyBatis接受的参数类型：基本类型、对象、List、数组、Map
    2） 无论MyBatis的入参是哪种数据类型，MyBatis都会将参数放在一个Map中，对于单参数入参的情况：

    • 若入参为基本类型：变量名作为key，变量值为value，此时生成的Map只有一个元素若入
    • 若入参为List：默认“list”作为key，该List即为value 若入参为数组：默认“array”作为key，该数组即为value 若入参为Map：键值不变

    4. choose （when、otherwise）

    choose可以选择其中一种情况下的查询结果，流程和switch相同编写接口方法

    List<User> getUserList_choose(@Param("userName")String userName,@Param("userRole")Integer roleId,
                              @Param("userCode")String userCode,@Param("creationDate")Date creationDate);

    修改UserMapper.xml

    <select id="getUserList_choose" resultType="User">
    SELECT * from USER WHERE 1=1
    <choose>
        <when test="userName!=null and userName!=''">
            AND userName=#{userName}
        </when>
        <when test="userCode!=null and userCode!=''">
            AND userCode LIKE concat('%',#{userCode},'%')
        </when>
        <when test="userRole!=null and userRole!=''">
            AND userRole=#{userRole}
        </when>
        <otherwise>
            AND YEAR(creationDate)=YEAR(#{creationDate})         </otherwise>
    </choose>
</select>

    编写测试

    String userName="";
Integer roleId=1;
String userCode="";
Date  creationDate=new SimpleDateFormat("yyyy-MM-dd").parse("2018-2-7");
SqlSession sqlSession = null;
List<User> userList = new ArrayList<User>();
try {
    sqlSession = MyBatisUtil.createSqlSession();
    userList = 
sqlSession.getMapper(UserMapper.class).getUserList_choose(userName,roleId,userCode,null); } catch (Exception ex) {
    ex.printStackTrace();
} finally {
    MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user :         userList) {
    System.out.println(user.getUserName() + "	" + user.getAddress());
}

    4.MyBatis实现分页功能

    1. 使用聚合函数count() 获得总记录数（在之前的示例中已经完成）
      UserMapper.java
      int count();

    2)实现分页，通过limit(起始位置,页面容量) 修改UserMapper.java，增加分页方法

    List<User> getUserList(@Param("userName")String userName,
                       @Param("userRole")Integer roleId,
                       @Param("from")Integer currentPageNo,
                       @Param("pageSize")Integer pageSize);

    编写UserMapper.xml

    <select id="getUserList" resultType="User">
    SELECT u.*,r.roleName FROM USER u,role r WHERE u.userRole=r.id
    <if test="userRole!=null">
        AND u.userRole=#{userRole}
    </if>
    <if test="userName!=null and userName!=''">
        AND u.userName LIKE concat('%',#{userName},'%')
    </if>
    ORDER BY creationDate DESC limit #{from},#{pageSize}
</select>

    编写测试代码

    SqlSession sqlSession = null; 
try {
    sqlSession = MyBatisUtil.createSqlSession();
    List<User> userList = 
sqlSession.getMapper(UserMapper.class).getAddressListByUserId(1);
    for (User user : userList) {
        System.out.println(user.getUserName());         List<Address> addresses = user.getAddressList();         for (Address address :
                addresses) {
            System.out.println("--- " + address.getContact());
        }
    }
} catch (Exception ex) {
    ex.printStackTrace();
} finally {
    MyBatisUtil.closeSqlSession(sqlSession);
}
  • 相关阅读:
    flutter 强制竖屏和横屏
    Error: ADB exited with exit code 1
    UVA-11426
    个人信息表格题
    Kubernetes 机器内核问题排查
    ETCD
    ubuntu16.04在使用vim的时候，中文乱码
    Kubernetes Ingress-Nginx 实现蓝绿、灰度发布
    MySQL高可用架构MHA+ProxySQL实现读写分离和负载均衡
    ElasticSearch
  • 原文地址：https://www.cnblogs.com/Djkang/p/9935320.html
Copyright © 2020-2023  润新知