How does the Mybatis framework implement dynamic SQL?

How does the Mybatis framework implement dynamic SQL?

One of the powerful features of MySQL is its dynamics. If you have experience using JDBC or other similar frameworks, you can realize how painful it is to splice sql statements according to different conditions. When splicing, make sure you don't forget the necessary spaces, and pay attention to omitting the comma at the end of the column name list. Using the feature of dynamic sql can completely get rid of this pain. It completes the splicing of dynamic sql with the help of ognl (similar to el expression in jsp), which makes it very simple.

If you need more information, please click the picture below ⬇ (scan code to add friends → remark 66, refuse to add without remark)

How to practice dynamic SQL

  • if condition judgment

  • choose, when, otherwise

  • trim, where, set

  • foreach

  • Using Ognl expressions

Case practice

if condition judgment

What dynamic SQL usually does is conditionally include part of the where clause. For example:

<!-- 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>

Using the if tag is to add a test attribute as a judgment. If there are multiple combinations of conditions, use and, or to connect

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;   
}

[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-wjnqjjas-1600915888331)( https://imgkr2.cn-bj.ufileos.com/6c5fe387-492c-4721-89aa-c8560f6a528f.png?UCloudPublicKey=TOKEN_8d8b72be-579a-4e83-bfd0-5f6ce1546f13&Signature=bC8cFRwCZAEavYH%2FBpq18iDwJ1Y%3D&Expires=1596378864)]

As a result, sql automatically judges and splices the results

choose, when, otherwise

We don't want to use all the conditional statements, but just choose one or two of them. In this case, 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>

The meaning of this statement is to check the value of userName if the nation passed in is not empty, otherwise it is the value of 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

The previous examples have appropriately solved a notorious dynamic SQL problem, and then let's look at the configuration of the first article

<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>

if I use the statement userName like '%${userName}%' to make a judgment

<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>

In this case, let's predict that the printed sql should be

select userId, userName, password from user where 

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>

Write test class

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

The where element knows that the "where" clause is inserted only when more than one if condition has a value. Moreover, if final content starts with the "and" OR ", where element also knows how to remove them. Just like the above configuration, if my phone has a value and userName has no value, where knows to remove the and in front of the phone

However, if the where element does not play according to the normal routine, we can still customize the functions we want by customizing the trim element. For example, the user-defined 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>

This effect is the same as that of < where > < / where >

The prefixOverrides property ignores text sequences separated by pipes (note that spaces in this example are also necessary). The result is that all the contents specified in the prefix overrides attribute will be removed and the contents specified in the prefix attribute will be 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>

Write test method

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

If you are interested in the appearance of the equivalent custom trim element, this should be its true face:

<update id="updateUserById" parameterType="user">       
    update user       
    <trim prefix="set" suffixOverrides="," > <!-- Use suffix elimination at this time, -->       
        <if test="userName!=null">      
            userName=#{userName},       
        </if>     
        <if test="userPwd!=null">    
            userPwd=#{userPwd},     
        </if>         
    </trim>        
    where id=#{id}
</update> 

This effect is consistent with set

foreach

Another common and necessary operation of dynamic SQL is to traverse a set, usually IN the construction of IN conditional statements or batch insertion. For example:

<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>

Write test method

@Test
public void testFindUserByUserName() {	
    InputStream is = MybatisSecondaryCacheTest.class.getClassLoader().getResourceAsStream("mybatis.xml");		SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);			
    SqlSession session = sessionFactory.openSession();		
    // Create parameters		
    Map<String, Object> params = new HashMap<>();	
    // Create a string array 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, and the name of key should be consistent with the variable name in the mapping file		
    List<User> users = session.selectList("findUserByUserName", params); 					 					System.out.println("Query results: " + users.toString());	
}

Using Ognl expressions

In the above mapping, if we use if to judge whether a value is empty or an empty string, we do this. Test = "username! = null and username! = ''" it is more complex to write. Therefore, we use ognl expression@ Ognl@isNotEmpty(userName) to judge.

When using ognl expression, we need to add a Java class of ognl under the package in the root directory. There will be various judgments, such as null judgment@ Ognl@isEmpty(userName), not null@ Ognl@isNotEmpty(userName) * *, is it an empty string@ Ognl@isBlank(userName) * *, not an empty string@ Ognl@isNotBlank(userName) * * etc

These four may be commonly used. They are just convenient for us to do some operations and will also be used in practice

import java.lang.reflect.Array;
import java.util.Collection;import java.util.Map;  
/** 
* Ognl Tool class is mainly used to reduce the long class name when ognl expression accesses static methods 
* Ognl The expression to access the static method is:@ class@method(args) 
*  
* Example use:  
* <pre> 
*  <if test="@Ognl@isNotEmpty(userId)"> 
*      and user_id = #{userId} 
*  </if> 
* </pre>
* 
*/
public class Ognl {        
    /**     
    * It can be used to judge whether string, map, collection and array are 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;   
    }        
    /**    
    * It can be used to judge whether map, collection, string and array are not empty    
    * @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;
    } 
} 

extend

Annotation form dynamic sql

In addition to xml configuration supporting dynamic SQL, MyBatis provides various annotations such as @ InsertProvider,@UpdateProvider,@DeleteProvider and @ SelectProvider to help build dynamic SQL statements, and then let MyBatis execute these SQL statements.

public interface AccountDao {

​    /**

​     \* Add account record  

​     \*  Add string sql is provided by the addAccount method of AccountProvider class

​     \*  Return the number of affected rows

​     \* @param account

​     \* @return

​     */

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

​    public int  addAcccount(Account account);

​    

​    /**

​     \* Add account record  

​     \*  Add string sql is provided by the addAccount method of AccountProvider class

​     \* Return 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  

​     \*  The query string sql is 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);

​    

​    /**

​     \* Multi criteria query account records

​     \*  The query string sql is 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

​     \*  The update string sql is provided by the updateAccountById method of the AccountProvider class

​     \* @param account

​     \* @return

​     */

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

​    public int updateAccountById(Account account);

​    

​    /**

​     \* Delete account record according to id

​     \*  Delete string sql is provided by the deleteAccount method of AccountProvider class

​     \* @param id

​     \* @return

​     */

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

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

}

 

 

public class AccountProvider {

​    /**

​     \* Return sql string of adding account record

​     \* @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 for querying account records based on 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 sql string of multi condition query

​     \* @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;

​    }

​    

​    /**

​     \* Return sql string for updating account record

​     \* @param account

​     \* @return

​     */

​    public String updateAccount(Account account){

​         return new SQL(){{

​               UPDATE(" account");

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

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

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

​         }}.toString(); 

​    }

​    

​    /**

​     \* Return sql string for deleting account record

​     \* @param id

​     \* @return

​     */

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

​        return new SQL(){{

​            DELETE_FROM("account");

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

​        }}.toString();

​    }

}

;

​ }

​ /**

* return the sql string of the updated account record

​ * @param account

​ * @return

​ */

​ public String updateAccount(Account account){

​ return new SQL(){{

​ UPDATE(" account");

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

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

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

​ }}.toString();

​ }

​ /**

* return the sql string of deleted account record

​ * @param id

​ * @return

​ */

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

​ return new SQL(){{

​ DELETE_FROM("account");

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

​ }}.toString();

​ }

}

Tags: Java architecture

Posted by kovudalion on Sun, 15 May 2022 17:41:32 +0300