Mybatis 框架如何实现 动态 SQL 呢?

HUIYL1 2020-11-10 10:41:05
mybatis SQL 框架 实现 动态


MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。它借助ognl(类似于jsp里面的el表达式)表达式来完成动态sql的拼接使得非常简便。

实习 动态 SQL 的方式

  • if条件判断
  • choose, when, otherwise 选择器使用
  • trim, where, set
  • foreach
  • 使用Ognl表达式

案例实操

if条件判断

动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分。比如:

<!-- 模糊匹配 -->    
<select id="queryUserByUserName" parameterType="string" resultType="user">    
   select id,userName,userPwd from user   where 1=1  
   <if test="userName!=null and userName!=''">      
       and userName like '%#{userName}%'      
   </if>
</select>

使用if标签就是加一个test属性作为判断, 如果有多个条件组合判断的话用and, or连接

实现方法

@Override    
public List<User> queryUserByUserName(String userName) {
   List<User> users=null;      
   SqlSession session=null;    
   try {          
       session=sqlSessionFactory.openSession();      
       Map map=new HashMap();
       //map 参数          
       map.put("userName",userName);
       users=session.selectList("com.xxx.mapper.UserMapper.queryUserByUserName", map);        
   } catch (Exception e) {      
       e.printStackTrace();    
   }finally{            
       if(null!=session){    
           session.close();      
       }            
   }      
   return users;  
}

运行结果, sql自动判断并且拼接上了

choose, when, otherwise 选择器使用

我们不想用到所有的条件语句,而只想从中择其一二。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句

<select id="queryUserByParams" parameterType="map" resultType="user">        
   select id,userPwd      
   <choose>          
       <when test="nation!=null and nation!=''">      
           ,userName      
       </when>        
       <otherwise>    
           ,realName    
       </otherwise>      
   </choose>        
   from user      
   where userName like '%${userName}%'  
   <if test="phone!=null and phone!=''">  
       and phone like '%${phone}%'    
   </if>
</select>

这条语句的意思就是说 如果我传进nation不为空就查userName的值, 否则是realName的值

@Test    
public void test16(){  
   UserDao userDao=new UserDaoImpl(sqlSessionFactory);    
   List<User> list=userDao.queryUserByParams("", null, "xxx");
   for(User u:list){    
       System.out.println(u);  
   }  
}

trim, where, set

前面几个例子已经适宜地解决了一个臭名昭著的动态 SQL 问题, 然后我们再来看第一条的配置

<select id="findUserByUserName" resultMap="RM_User" >  
   select    
    userId, userName, password  
   from  
     user  
   where  
    userName like '%${userName}%'
   <if test="phone != null and phone != ''" >  
       and phone like '%${phone}%'
   </if>
</select>

如果我把 userName like '%${userName}%'这个语句也用if做个判断

<select id="findUserByUserName" resultMap="RM_User" >
   select    
    userId, userName, password  
   from  
    user        
   where   
   <if test="userName != null and userName != ''" >
       userName like '%${userName}%'   
   </if>     
   <if test="phone != null and phone != ''" >
       and phone like '%${phone}%'  
   </if>
</select>

这样的话我们预测一下 打印的sql应该是

select userId, userName, password from user where

很明显这条sql会报错

那为了解决这个问题呢, 我们使用<where></where>标签

<select id="queryUserByParams" parameterType="map" resultType="user">
   select
    id,userPwd,phone
   <choose>  
       <when test="nation!=null and nation!=''">  
           ,userName  
       </when>  
       <otherwise>    
           ,realName  
       </otherwise>  
   </choose>from user<where>  
   <if test="userName !=null and userName !=''">    
       userName like '%${userName}%'  
   </if>  
   <if test="phone!=null and phone!=''">    
       and phone like '%${phone}%'    
   </if>
   </where>
</select>

编写测试类

@Test  
public void test16(){    
   UserDao userDao=new UserDaoImpl(sqlSessionFactory);  
   List<User> list=userDao.queryUserByParams("", "", "");    
   for(User u:list){      
       System.out.println(u);  
   }  
}

where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且,若最后的内容是“AND”或“OR”开头的,where 元素也知道如何将他们去除。就像上面的配置如果我phone有值, userName没值的话 where也知道要将phone 前面的and去掉

但如果 where 元素没有按正常套路出牌,我们还是可以通过自定义 trim 元素来定制我们想要的功能。比如,和 where 元素等价的自定义 trim 元素为:

<select id="queryUserByParams" parameterType="map" resultType="user">    
   select
    id,userPwd,phone      
   <choose>      
       <when test="nation!=null and nation!=''">      
           ,userName    
       </when>      
       <otherwise>          
           ,realName        
       </otherwise>          
   </choose>      
   from user      
   <trim prefix="where" prefixOverrides="and |or" >    
       <if test="userName !=null and userName !=''">  
           userName like '%${userName}%'  
       </if>        
       <if test="phone!=null and phone!=''">    
           and phone like '%${phone}%'        
       </if>                  
   </trim>            
</select>

这样的效果跟<where></where>效果是一样的

prefixOverrides 属性会忽略通过管道分隔的文本序列(注意此例中的空格也是必要的)。它带来的结果就是所有在 prefixOverrides 属性中指定的内容将被移除,并且插入 prefix 属性中指定的内容。

对于update语句, 我们采用<set></set>去设置值

<update id="updateUserById" parameterType="user">    
   update user          
   <set>      
       <if test="userName!=null">  
           userName=#{userName},      
       </if>      
       <if test="userPwd!=null">  
           userPwd=#{userPwd},    
       </if>      
   </set>    
   where id=#{id}
</update>

编写测试方法

@Test  
public void test17(){  
   UserDao userDao=new UserDaoImpl(sqlSessionFactory);
   User user=userDao.queryUserById(6);      
   user.setUserPwd(null);    
   user.setUserName("xxx06");  
   userDao.updateUserById(user);
}

若你对等价的自定义 trim 元素的样子感兴趣,那这就应该是它的真面目:

<update id="updateUserById" parameterType="user">      
   update user      
   <trim prefix="set" suffixOverrides="," > <!-- 此时使用后缀消除, -->      
       <if test="userName!=null">      
           userName=#{userName},      
       </if>    
       <if test="userPwd!=null">    
           userPwd=#{userPwd},    
       </if>        
   </trim>        
   where id=#{id}
</update>

这个效果和set是一致的

foreach

动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历,通常是在构建 IN 条件语句或者是批量插入。比如:

<select id="findUserByUserName" resultMap="RM_User" >
   select    
    userId, userName, password  
   from  
    user  
   <where>  
       <if test="userNameList != null" >
           userName in  
           <foreach item="item" index="index" collection="userNameList"open="(" separator="," close=")">
               #{item}  
           </foreach>  
       </if>  
   </where>
</select>

编写测试方法

@Test
public void testFindUserByUserName() {
   InputStream is = MybatisSecondaryCacheTest.class.getClassLoader().getResourceAsStream("mybatis.xml");  SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);  
   SqlSession session = sessionFactory.openSession();  
   // 创建参数  
   Map<String, Object> params = new HashMap<>();
   // 创建以string数组然后转化成list  
   String[] userName = new String[]{"Tonygogo", "hello", "哈哈哈"};              params.put("userNameList", Arrays.asList(userName));
   // string数组转list, key的名称要与映射文件中的变量名要一直  
   List<User> users = session.selectList("findUserByUserName", params);            System.out.println("查询结果: " + users.toString());
}

使用Ognl表达式

我们在上面的映射中, 如果用if去判断一个值是否为空或者是空字符串时我们是这样做的test="userName != null and userName !='' "这样写起来比较复杂, 为此我们采用Ognl表达式@Ognl@isNotEmpty(userName)去判断。

使用ognl表达式时我们要在根目录的包下面加上Ognl的一个Java类, 这里面会有各种各样的判断比如为空判断@Ognl@isEmpty(userName),不为空判断 @Ognl@isNotEmpty(userName), 是否是空字符串@Ognl@isBlank(userName), 不为空字符串@Ognl@isNotBlank(userName)**等等

我们常用的可能就是这四个,它只是方便我们去做一些操作,实际中也会用到

import java.lang.reflect.Array;
import java.util.Collection;import java.util.Map;  
/**

  • Ognl工具类,主要是为了在ognl表达式访问静态方法时可以减少长长的类名称编写
  • Ognl访问静态方法的表达式为: @class@method(args)
  •  
  • 示例使用:  
  • <pre>
  •  <if test="@Ognl@isNotEmpty(userId)">
  •      and user_id = #{userId}
  •  </if>
  • </pre>

*/
public class Ognl {        
   /**    
   * 可以用于判断String,Map,Collection,Array是否为空    
   * @param o    
   * @return    
   */    
   @SuppressWarnings("rawtypes")    
   public static boolean isEmpty(Object o) throws IllegalArgumentException {        
       if(o == null) return true;        
       if(o instanceof String) {            
           if(((String)o).length() == 0){          
               return true;          
           }      
       } else if(o instanceof Collection) {    
           if(((Collection)o).isEmpty()){    
               return true;      
           }        
       } else if(o.getClass().isArray()) {  
           if(Array.getLength(o) == 0){      
               return true;        
           }      
       } else if(o instanceof Map) {  
           if(((Map)o).isEmpty()){    
               return true;      
           }      
       }else {    
           return false;
           //          throw new IllegalArgumentException("Illegal argument type,must be : Map,Collection,Array,String. but was:"+o.getClass());      
       }      
       return false;  
   }        
   /**    
   * 可以用于判断 Map,Collection,String,Array是否不为空    
   * @param c  
   * @return  
   */    
   public static boolean isNotEmpty(Object o) {    
       return !isEmpty(o);  
   }      
   public static boolean isNotBlank(Object o) {  
       return !isBlank(o);  
   }    
   public static boolean isBlank(Object o) {    
       if(o == null)      
           return true;    
       if(o instanceof String) {    
           String str = (String)o;      
           return isBlank(str);    
       }      
       return false;
   }    
   public static boolean isBlank(String str) {    
       if(str == null || str.length() == 0) {  
           return true;      
       }          
       for (int i = 0; i < str.length(); i++) {    
           if (!Character.isWhitespace(str.charAt(i))) {  
               return false;      
           }    
       }    
       return true;
   }
}

扩展

注解形式动态sql

除了xml 配置能够支持动态 sql 外,MyBatis提供了各种注解如@InsertProvider,@UpdateProvider,@DeleteProvider和@SelectProvider,来帮助构建动态SQL语句,然后让MyBatis执行这些SQL语句。

public interface AccountDao {
•    /**
•     * 添加账户记录  
•     *  添加字符串sql由AccountProvider 类addAccount方法提供
•     *  返回影响行数
•     * @param account
•     * @return
•     */
•    @InsertProvider(method="addAccount",type=AccountProvider.class)
•    public int  addAcccount(Account account);
•    
•    /**
•     * 添加账户记录  
•     *  添加字符串sql由AccountProvider 类addAccount方法提供
•     * 返回主键
•     * @param account
•     * @return
•     */
•    @InsertProvider(method="addAccount",type=AccountProvider.class)
•    @Options(useGeneratedKeys=true,keyColumn="id")
•    public int  addAcccount02(Account account);
•    
•    /**
•     * 根据id查询账户记录  
•     *  查询字符串sql由AccountProvider 类queryAccountById方法提供
•     * @param id
•     * @return
•     */
•    @SelectProvider(method="queryAccountById",type=AccountProvider.class)
•    public Account queryAccountById(@Param("id")int id);
•    
•    /**
•     * 多条件查询账户记录
•     *  查询字符串sql由AccountProvider 类queryAccountByParams方法提供
•     * @param aname
•     * @param type
•     * @param time
•     * @return
•     */
•    @SelectProvider(method="queryAccountByParams",type=AccountProvider.class)
•    public List<Account> queryAccountByParams(@Param("aname")String aname,@Param("type")String type,@Param("time")String time);
•    
•    /**
•     * 更新账户记录
•     *  更新字符串sql由AccountProvider 类updateAccountById方法提供
•     * @param account
•     * @return
•     */
•    @UpdateProvider(method="updateAccount",type=AccountProvider.class)
•    public int updateAccountById(Account account);
•    
•    /**
•     * 根据id删除账户记录
•     *  删除字符串sql由AccountProvider 类deleteAccount方法提供
•     * @param id
•     * @return
•     */
•    @DeleteProvider(method="deleteAccount",type=AccountProvider.class)
•    public int deleteAccountById(@Param("id")int id);
}
public class AccountProvider {
•    /**
•     * 返回添加账户记录sql字符串
•     * @param account
•     * @return
•     */
•    public String addAccount(final Account account){
•        return new SQL(){{
•            INSERT_INTO("account");
•            VALUES("aname","#{aname}");
•            VALUES("type", "#{type}");
•            VALUES("remark","#{remark}");
•            VALUES("money", "#{money}");
•            VALUES("user_id", "#{userId}");
•            VALUES("create_time","#{createTime}");
•            VALUES("update_time", "#{updateTime}");
•        }}.toString();
•    }
•    
•    /**
•     * 返回根据id查询账户记录sql字符串
•     * @param id
•     * @return
•     */
•    public String queryAccountById(@Param("id")int id){
•        return new SQL(){{
•            SELECT("id,aname,type,remark,create_time as createTime,update_time as updateTime,user_id as userId");
•            FROM("account");
•            WHERE(" id=#{id} ");
•        }}.toString();
•    }
•    
•    /**
•     * 返回多条件查询sql字符串
•     * @param aname
•     * @param type
•     * @param time
•     * @return
•     */
•    public String queryAccountByParams(@Param("aname") final String aname,@Param("type")final String type,@Param("time")final String time){
•        String sql= new SQL(){{
•            SELECT("id,aname,type,remark,create_time as createTime,update_time as updateTime,user_id as userId");
•            FROM("account");
•            WHERE(" 1=1 ");
•            if(!StringUtils.isNullOrEmpty(aname)){
•                AND();
•                WHERE(" aname like concat('%',#{aname},'%') ");
•            }
•            if(!StringUtils.isNullOrEmpty(type)){
•                AND();
•                WHERE(" type =#{type}");
•            }
•            if(!StringUtils.isNullOrEmpty(time)){
•                AND();
•                WHERE(" create_time <=#{time}");
•            }
•        }}.toString();
•        return sql;
•    }
•    
•    /**
•     * 返回更新账户记录sql字符串
•     * @param account
•     * @return
•     */
•    public String updateAccount(Account account){
•         return new SQL(){{
•               UPDATE(" account");
•               SET("aname=#{aname}");
•               SET("type=#{type}");
•               WHERE("id=#{id}");
•         }}.toString();
•    }
•    
•    /**
•     * 返回删除账户记录sql字符串
•     * @param id
•     * @return
•     */
•    public String deleteAccount(@Param("id")int id){
•        return new SQL(){{
•            DELETE_FROM("account");
•            WHERE("id=#{id}");
•        }}.toString();
•    }
}
版权声明
本文为[HUIYL1]所创,转载请带上原文链接,感谢
https://segmentfault.com/a/1190000037780792

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云