MyBatis if tag: conditional judgment

MyBatis if is similar to the if statement in Java. It is the most commonly used judgment statement in MyBatis. Using the if tag can save a lot of work in splicing SQL and focus on the maintenance of XML.

The if statement is simple to use and is often used in combination with the test attribute. The syntax is as follows.

When the judgment condition is true, the included SQL statements will be executed.

Mybatis if   Labels can be used in many types of SQL Statement, we take the query as an example. First, let's look at a very common query:

<!-- Query the user list, like User name -->  
<select id="getUserListLikeName" parameterType="User" resultMap="userResultMap">  
    SELECT * from user u   
WHERE u.username LIKE CONCAT(CONCAT('%', #{username}),'%')  

However, when username or sex is null, this sentence is likely to report an error or the query result is empty. At this time, we use the if dynamic sql statement to judge first. If the value is null or equal to the empty string, we will not judge this condition to increase flexibility.

The parameter is entity class: User. Judge all the attributes in the entity class. If it is not empty, execute the judgment condition.

<!-- add to if(Judgment parameters) - Entity class User Non empty attribute as where condition -->  
<select id="getUserList" resultMap="resultMap_User" parameterType="com.yiibai.pojo.User">  
    SELECT u.username,  
      FROM user u   
    <if test="username !=null ">  
        u.username LIKE CONCAT(CONCAT('%', #{username, jdbcType=VARCHAR}),'%')  
    <if test="sex!= null and sex != '' ">  
        AND = #{Sex, jdbcType=INTEGER}  
    <if test="birthday != null ">  
        AND u.birthday = #{birthday, jdbcType=DATE}  

    <if test="userId != null and userId != '' ">  
        AND id.user_id = #{userId, jdbcType=VARCHAR}  

It is flexible to use. To create a new entity class, we need to limit the condition. We only need to attach the corresponding value to the condition where. On the contrary, we can not judge in where without assigning a value.

public void select_by_if() {  
    User user = new User();  
    List<User> userList = this.dynamicSqlMapper.getUserList_if(user);  
    for (user u : userList) {  

Let's take another look at another example. Let's take a look at the following code:

<select id="dynamicIfTest" parameterType="Blog" resultType="Blog">
        select * from t_blog where 1 = 1
        <if test="title != null">
            and title = #{title}
        <if test="content != null">
            and content = #{content}
        <if test="owner != null">
            and owner = #{owner}

The meaning of this statement is very simple. If you provide the title parameter, you must meet the title=#{title}. Similarly, if you provide Content and Owner, they also need to meet the corresponding conditions, and then return all blogs that meet these conditions. This is a very useful function. In the past, we used other types of frameworks or used them directly JDBC If we want to achieve the same selection effect, we need to spell SQL statements, which is extremely troublesome. Compared with the above dynamic SQL, it is relatively simple.

Keywords: Java MySQL Mybatis SQL

Added by elpaisa on Mon, 27 Sep 2021 22:54:39 +0300