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

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

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

如需要跟多资料请点击下方图片⬇（扫码加好友→备注66，不备注拒绝添加哦）

实习 动态 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;   
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wjnQjJAS-1600915888331)(https://imgkr2.cn-bj.ufileos.com/6c5fe387-492c-4721-89aa-c8560f6a528f.png?UCloudPublicKey=TOKEN_8d8b72be-579a-4e83-bfd0-5f6ce1546f13&Signature=bC8cFRwCZAEavYH%252FBpq18iDwJ1Y%253D&Expires=1596378864)]

运行结果， 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();

​    }

}

;

​ }

​

​ /**

​ * 返回更新账户记录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();

​ }

}