Usage of Mybatis Fuzzy Query and Dynamic sql Statement

Mybatis Fuzzy Queries and Dynamic sql Statements

Fuzzy query

The most commonly used operation on the database is query, but how to use Mybatis for fuzzy query? Let's look at a simple fuzzy query first

  <select id="select01" resultMap="BasicResultMap">
   SELECT 
   * 
  FROM
   oa_employee 
  WHERE emp_name LIKE #{asd} 
  </select>

This is a pseudo-fuzzy query because the true fuzzy "%" is not implemented. Parameter is a string, so the contents in # {} are not restricted. But how should I insert the% character? The first thing that comes to mind is to insert% into the string "Zhang%" when passing string parameters, but this method is slightly cumbersome. The strategy for using the sql method is provided below

 <select id="select01" resultMap="BasicResultMap">
   SELECT 
   * 
  FROM
   oa_employee 
  WHERE emp_name LIKE CONCAT( #{asd} ,'%')
  </select>

Another way to write it is not recommended for everyone

<select id="select01" resultMap="BasicResultMap">
   SELECT 
   * 
  FROM
   oa_employee 
  WHERE emp_name LIKE '${emp_name}%'
  </select>

The # {} is precompiled, PerpareStatement in JDBC, which prevents sql injection, but the ${} is not precompiled, and the parameters are written as attributes in the class or key values of map or parameter names annotated in the interface.

mybatis provides the bind tag. Here's an example

 <select id="select01" resultMap="BasicResultMap">
  <bind name="emp_name" value="'%'+ _parameter.getEmp_name() +'%'"/>
   SELECT 
   * 
  FROM
   oa_employee 
  WHERE emp_name LIKE #{emp_name}
  </select>

He automatically fills in the value value in the # {} expression, and it is worth noting that " _parameter.getEmp_name() "The method called is an get method that is a property in the object as a query parameter

Multi-condition query

The key point of multi-condition query is to judge whether the query condition is empty and splice sql statement. if tags and where tags are provided in mybatis. The following is an introduction to the usage of the two labels.

if Tag

<select id="select01" resultMap="BasicResultMap"> 
SELECT 
* 
FROM 
oa_employee 
WHERE 1=1 
<if test="emp_name != null and emp_name != ''"> 
and emp_name = #{emp_name } 
</if> 
<if test="emp_sex != null and emp_sex != ''"> 
and sex = #{emp_sex} 
</if> 
</select> 

The if tag in mybatis is somewhat similar to the use of EL expressions, and test can directly write attributes in classes or key values.

where Tag

<select id="select01" resultMap="BasicResultMap">
   SELECT 
   * 
   FROM
   oa_employee 
   <where>
   <if test="emp_name != null and emp_name != ''">
    and emp_name = #{emp_name }
   </if>
   <if test="emp_sex != null and emp_sex != ''">
    and sex = #{emp_sex}
   </if>
   </where>
  </select>

The where tag here replaces the where 1=1 in the previous code. The where tag in mybatis will judge whether there is content in the tag. If there is content, it will automatically generate where and remove the first and +1 space and or +1 space after where.

choose, when and otherwise labels

<select id="select01" resultMap="BasicResultMap">
   SELECT 
   * 
   FROM
   oa_employee 
   <where>
   <choose>
    <when test="emp_name != null and emp_name != ''">
       and emp_name = #{emp_name }
    </when>
     <when test="emp_sex != null and emp_sex != ''">
       and sex = #{emp_sex}
    </when>
    <otherwise>
      emp_id = 50
    </otherwise>
   </choose>
   </where>
  </select>

When all conditions are not met, the contents of the otherwise tag are executed.

trim Tag

<select id="select01" resultMap="BasicResultMap">
   SELECT 
   * 
   FROM
   oa_employee 
    <trim prefix="where" prefixOverrides="and |or ">
    <if test="emp_name != null and emp_name != ''">
      and emp_name = #{emp_name }
    </if>
    <if test="emp_sex != null and emp_sex != ''">
       and sex = #{emp_sex}
    </if>
  </trim>

Attributes of trim tags and their meanings

-prefix: There is content added at the front between tags -prefixOverrides: Check whether the front of the content matches, and delete the match -suffix: There is content added at the back between tags -suffixOverrides: Check for a match at the back of the content and delete the match

set Tag

set tags are commonly used for update operations and automatically erase irrelevant,

 <update id="update01" >
  UPDATE 
   oa_employee 
  <set>
    <if test="emp_name != null and emp_name != ''">
       emp_name = #{emp_name}
    </if>
    <if test="emp_sex != null and emp_sex != ''">
       , sex = #{emp_sex}
    </if>
  </set>
  WHERE emp_id = 50 
  </update>

foreach Tag

foreach is used to process arrays or list collections. Here is an example of batch addition

 <insert id="insert01">
  INSERT INTO 
  oa_employee 
  ( emp_name, sex, fk_dept_id) 
  VALUES
  <foreach collection="list" item="employee" separator=","> 
   (#{employee.emp_name},#{employee.emp_sex},#{employee.fk_dept_id})
  </foreach>
  </insert>

Where collection can only be "array" if the parameters are arrays, collection can only be "list" if the parameters are List collections, and item is similar to var in JSTL, referring to every 1 object in the container. separator = "," means that each piece of data is divided by. Unspecified attributes are open and close, which mean that their contents are added at the beginning and end of the traversal, respectively.

Summarize