One of the great features of MyBatis is its dynamic SQL. If you have experience with JDBC or other similar frameworks, you know how painful it can be to concatenate SQL statements based on different conditions. When concatenating, make sure you don’t forget the necessary Spaces and omit the comma at the end of the list of column names. You can take advantage of dynamic SQL to get rid of this pain. It uses OGNL (similar to EL expressions in JSP) expressions to do dynamic SQL concatenation to make it very easy.

Practice the way of dynamic SQL

  • If conditional judgment

  • Choose, when, and otherwise selectors are used

  • trim, where, set

  • foreach

  • Use Ognl expressions

A case in field

If conditional judgment

What dynamic SQL typically does is conditionally include part of a WHERE clause. Such as:

<! -- Fuzzy matching -->    
<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>
Copy the code

Using the if tag adds a test attribute, and concatenating and, or concatenating multiple conditions

Implementation method

@Override    
public List<User> queryUserByUserName(String userName) { 
    List<User> users=null;       
    SqlSession session=null;    
    try {           
        session=sqlSessionFactory.openSession();      
        Map map=new HashMap();
        / / map parameters
        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;   
}
Copy the code

Run the result, SQL automatically judge and concatenate

Choose, when, and otherwise selectors are used

We don’t want to use all the conditional statements, just one or two of them. For this, MyBatis provides the Choose element, which is a bit like the Switch statement in Java

<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>
Copy the code

If the nation is not empty then check the value of userName, otherwise check the value of realName

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

trim, where, set

Now that the previous examples have nicely solved a notorious dynamic SQL problem, let’s look at the first configuration

<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>
Copy the code

UserName like ‘%${userName}%

<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>
Copy the code

In this case, we predict that the printed SQL will be

select userId, userName, password from user where 
Copy the code

Obviously this SQL will report an error

To solve this problem, we use the <where></where> tag

<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>
Copy the code

Writing test classes

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

The WHERE element knows to insert the “where” clause only if more than one if condition has a value. Also, if the final content begins with “AND” OR “, the WHERE element knows how to remove them. If my phone has a value and my userName doesn’t have a value, then WHERE also knows to remove the “and” from phone

However, if the WHERE element doesn’t behave the way it should, we can still customize the functionality we want by customizing the trim element. For example, the custom trim element equivalent to the WHERE element is:

<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>
Copy the code

This has the same effect as <where></where>

The prefixOverrides property ignores the sequence of text delimited by pipes (note that whitespace is also necessary in this example). The result is that all the content specified in the prefixOverrides attribute is removed and the content specified in the prefix attribute is inserted.

For the UPDATE statement, we use <set></set> to set the value

<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>
Copy the code

Writing test methods

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

If you’re interested in what the equivalent custom trim element looks like, this is what it looks like:

<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> 
Copy the code

This effect is identical to set

foreach

Another common requirement for dynamic SQL is the need to iterate over a collection, usually when building IN conditions or bulk inserts. Such as:

<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>
Copy the code

Writing test methods

@Test
public void testFindUserByUserName(a) {	
    InputStream is = MybatisSecondaryCacheTest.class.getClassLoader().getResourceAsStream("mybatis.xml");		SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);			
    SqlSession session = sessionFactory.openSession();		
    // Create a parameter
    Map<String, Object> params = new HashMap<>();	
    // Create an array of strings and convert it to a list
    String[] userName = new String[]{"Tonygogo"."hello"."Ha ha ha."};				    						params.put("userNameList", Arrays.asList(userName)); 
    // String array to list, the name of the key must be consistent with the name of the variable in the mapping file
    List<User> users = session.selectList("findUserByUserName", params); 					 					System.out.println("Query result:" + users.toString());	
}
Copy the code

Use Ognl expressions

In the mapping above, if we use if to determine whether a value is empty or an empty string we do test=”userName! = null and userName ! *@Ognl@isNotEmpty(userName)* Ognl = @Ognl@isNotEmpty(userName)*

When using an OGNL expression, we add a Java class from ogNL to the root package, which has all kinds of judgments like null *@Ognl@isEmpty(userName), @Ognl@isNotEmpty(userName)** is an empty string @Ognl@isBlank(userName), @Ognl@isNot**Blank(userName)*, etc

We may be used to these four, it is just convenient for us to do some operations, will also be used in practice

import java.lang.reflect.Array;
import java.util.Collection;import java.util.Map;  
/** * the Ognl utility class is designed to reduce the long class name when Ognl expressions access static methods.@class@method(args) * * Example use: * <pre> * <if test="@Ognl@isNotEmpty(userId)"> 
*      and user_id = #{userId} 
*  </if> 
* </pre>
* 
*/
public class Ognl {        
    / * * * can be used to determine the String, Map, the Collection, whether the Array is empty@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;   
    }        
    / * * * can be used to determine the Map, a Collection, String, Array is not null@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; }}Copy the code

extension

Annotated form dynamic SQL

In addition to XML configuration to support dynamic SQL, MyBatis provides various annotations such as @insertProvider, @updateProvider, @deleteProvider and @selectProvider to help build dynamic SQL statements. Then let MyBatis execute these SQL statements.

public interface AccountDao {

​    /** * addAccount record ** add string SQL provided by the AccountProvider class addAccount method ** returns the number of affected lines **@param account

​     \* @return* /

​    @InsertProvider(method="addAccount",type=AccountProvider.class)

​    public int  addAcccount(Account account);

​    

​    /** * addAccount record ** add string SQL provided by the AccountProvider class addAccount method ** returns primary key *@param account

​     \* @return* /

​    @InsertProvider(method="addAccount",type=AccountProvider.class)

​    @Options(useGeneratedKeys=true,keyColumn="id")

​    public int  addAcccount02(Account account);

​    

​    /** * query account records by ID. * query string SQL provided by the queryAccountById method of the AccountProvider class@param id

​     \* @return* /

​    @SelectProvider(method="queryAccountById",type=AccountProvider.class)

​    public Account queryAccountById(@Param("id")int id);

​    

​    /** * multiple conditional query account records ** Query string SQL provided by the queryAccountByParams method of the AccountProvider class@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);

​    

​    /** * update account records ** Update string SQL provided by the updateAccountById method of the AccountProvider class@param account

​     \* @return* /

​    @UpdateProvider(method="updateAccount",type=AccountProvider.class)

​    public int updateAccountById(Account account);

​    

​    /** * deleteAccount records by id. * delete string SQL provided by the AccountProvider class deleteAccount method@param id

​     \* @return* /

​    @DeleteProvider(method="deleteAccount",type=AccountProvider.class)

​    public int deleteAccountById(@Param("id")int id);

}

 

 

public class AccountProvider {

​    /** * returns add account record SQL string \*@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();

​    }

​    

​    /** * returns the SQL string \* to query account records by ID@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();

​    }

​    

​    /** * return multiple conditional query SQL string \*@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;

​    }

​    

​    /** * returns the update account record SQL string \*@param account

​     \* @return* /

​    public String updateAccount(Account account){

​         return new SQL(){{

​               UPDATE(" account");

​               SET("aname=#{aname}");

​               SET("type=#{type}");

​               WHERE("id=#{id}");

​         }}.toString(); 

​    }

​    

​    /** * returns the SQL string \* for deleting account records@param id

​     \* @return* /

​    public String deleteAccount(@Param("id")int id){

​        return new SQL(){{

​            DELETE_FROM("account");

​            WHERE("id=#{id}"); }}.toString(); }}Copy the code