preface

We know that before the project development, we used database query, which was based on JDBC for connection query, and then advanced jdbcTemplate for query, but we found that it was still not very convenient, there were a lot of repeated SQL statements, and the code was coupled with low efficiency, so the ORM framework was derived. Such as Mybatis,Hibernate, and SpringBoot, Spring Data JPA

Conditions of the query

Mybatis mapper (mybatis mapper, mybatis Mapper, mybatis Mapper, mybatis Mapper

Detailed content reference

Common conditional query operations are

We achieve dynamic splicing SQL statements through the unique label provided by Mybatis for conditional judgment

If label WHERE label Choose when otherwise label ForeACH label

Quick start

If the label

Grammar:

<if test="xxx ! = null and xxx ! = "" >Copy the code

Test write the judgment condition parameter directly paramN or alias multiple conditions connect using and or OR

Concatenate as long as the condition is true. In Sql statements, if everything is true, concatenate everything

Note that the where clause adds 1=1 to avoid the risk of and

Here’s an example:

<select id="selg" resultType="log">
    select * from log where 1=1
    <if test="param1! =null and param1! = "">
    and outno=#{param1}
    </if>
    <if test="param2! =null and param2! = "">
    and inno=#{param2}
    </if>
</select>
Copy the code

Where the label

The Sql statement automatically adds the WHERE keyword and removes the first AND

SQL > alter table SQL

<select id="selg" resultType="log">
      select * from log 
  <where>
       <if test="param1! =null and param1! = "">
      and outno=#{param1}
      </if>
      <if test="param2! =null and param2! = "">
      and inno=#{param2}
      </if>
  </where>
     
</select>
Copy the code

Choose when Otherwise tag

Similar to case switch statement judgment in Java syntax

As long as one of the conditions is true, the others will not be judged. If there are no valid conditions, the content in Otherwise is executed by default

SQL > alter table SQL

<select id="selg" resultType="log">
      select * from log 
  <where>
    <choose>
       <when test="param1! =null and param1! = "">
      and outno=#{param1}
      </when>
      <when test="param2! =null and param2! = "">
      and inno=#{param2}
      </when>
      <otherwise>
        and 1=1
      </otherwise>
    </choose>
  </where>
     
</select>
Copy the code

The foreach tag

Grammar:

  <foreach collection="idList" item="id" open="(" separator="," close=")">
</foreach>
Copy the code
  1. Collection: The collection object to iterate over
  2. Item: Records the result of each iteration
  3. Open: Add content to the left of the result
  4. Separator: The content between the result and the result
  5. Close: The last thing added

Commonly used for in query, and batch insert operations as follows:

<select id="selF" parameterType="list" resultType="account">
    select * from account where ano in
    <foreach collection="list" item="item" open="(" separator="," close=")">
    #{item}
    </foreach>
   </select>

Copy the code

<insert id="insertBatch">
        INSERT INTO t_user
        (id, name, password)
        VALUES
        <foreach collection ="userList" item="user" separator =",">
            (#{user.id}, #{user.name}, #{user.password})
        </foreach >
    </insert>
Copy the code

Other labels use reference click to enter ·

Case scenario

  1. This is used when we need to perform complex dynamic conditional queries on the associated data of multiple tablesifLabels are judged as follows

According to the user’s mobile phone number, name, age, gender, etc., for dynamic condition retrieval, at this time we need to dynamically adjust to splicing SQL when the condition meets the SQL statement plus the corresponding condition difference

<select id="findUsersByUser" resultType="cn.soboys.kmall.sys.entity.User">
        select tu.USER_ID,tu.USERNAME,tu.SSEX,td.DEPT_NAME,tu.MOBILE,tu.EMAIL,tu.STATUS,tu.CREATE_TIME,
        td.DEPT_ID
        from t_user tu left join t_dept td on tu.DEPT_ID = td.DEPT_ID
        where tu.ADMIN_TYPE_ID  &gt;= 0 AND tu.ADMIN_TYPE_ID  &lt;= #{userParams.adminType}
        <if test="userParams.roleId ! = null and userParams.roleId ! = "">
           and (select group_concat(ur.ROLE_ID)
            from t_user u
            right join t_user_role ur on ur.USER_ID = u.USER_ID,
            t_role r
            where r.ROLE_ID = ur.ROLE_ID
            and u.USER_ID = tu.USER_ID and r.ROLE_ID=#{userParams.roleId})
        </if>


        <if test="userParams.mobile ! = null and userParams.mobile ! = "">
            AND tu.MOBILE =#{userParams.mobile}
        </if>
        <if test="userParams.username ! = null and userParams.username ! = "">
            AND tu.USERNAME   like CONCAT('%',#{userParams.username},'%')
        </if>
        <if test="userParams.ssex ! = null and userParams.ssex ! = "">
            AND tu.SSEX  =#{userParams.ssex}
        </if>
        <if test="userParams.status ! = null and userParams.status ! = "">
            AND tu.STATUS =#{userParams.status}
        </if>
        <if test="userParams.deptId ! = null and userParams.deptId ! = "">
            AND td.DEPT_ID =#{userParams.deptId}
        </if>
        <if test="userParams.createTime ! = null and userParams.createTime ! = "">
            AND DATE_FORMAT(tu.CREATE_TIME,'%Y%m%d') BETWEEN substring_index(#{userParams.createTime},'#',1) and substring_index(#{userParams.createTime},'#',-1)
        </if>
    </select>
Copy the code

Corresponding to mapper corresponding method

<T> IPage<User> findUsersByUser(Page<T> page, @Param("userParams") SearchUserParams userParams);
Copy the code

Corresponding parameter entity object

@Data
public class SearchUserParams {
    private String username;
    private String mobile;
    private String status;
    private String ssex;
    private Long deptId;
    private String createTime;
    private long adminType;
    private String roleId;
}
Copy the code

The if tag is used to determine whether the conditions are met, and then the corresponding SQL is spliced

Note that the first conditional concatenation we mentioned above is where concatenation, not AND. Avoid and risks and ensure that the SQL syntax is correct as follows

<select id="findSearchCouponsPage" parameterType="cn.soboys.kmall.bean.web.params.SearchCouponParams" resultType="coupon">
        select *
        from coupon c
        left join user_coupon uc on c.coupon_id = uc.coupon_id
        WHERE 1 = 1
        <if test="couponParams.userId ! = null and couponParams.userId ! = "">
           and uc.user_id =#{couponParams.userId}
        </if>
        <if test="couponParams.status ! = null and couponParams.status ! = "">
            and c.status =#{couponParams.status}
        </if>
        <if test="couponParams.couponId ! = null and couponParams.couponId ! = "">
            and c.coupon_id =#{couponParams.couponId}
        </if>
        <if test="couponParams.couponType ! = null and couponParams.couponType ! = "">
            and c.type =#{couponParams.couponType}
        </if>
    </select>
Copy the code

We can solve this by assuming that we give it a default condition WHERE 1 = 1, or by nesting WHERE tags