Mybatis dynamic SQL, do you really know it? Interview killer

In order to enable developers to write SQL flexibly, Mybatis also spent a lot of effort, defining a lot of tags and syntax, which will be introduced one by one below.

Although the English is not very good, I don't know if it means in such a simple way. It also exists in Java syntax. Only when the judgment condition is true will the SQL statement be executed.

Take chestnuts for example: TCM nurses in HIS system need to screen patients according to specific conditions, such as hospitalization number, bed, gender, etc. Of course, these conditions are not required. The specific function screenshots are as follows:

The condition filtering in the above screenshot is not required, so we can't fix it in SQL. We should judge whether this condition needs to be added according to whether the front end passes a value. How to write the query statement at this time? As follows:

<select id ='selectPats' resultType='com.xxx.domain.PatientInfo'>

  select * from patient_info 

  where status=1

  <!--The inpatient number from the front end is not null，Indicates that it needs to be filtered according to the hospitalization number. At this time Where Statement needs to add this condition-->

  <if test="iptNum!=null">

      and ipt_num=#{iptNum}

  </if>

  

  <!--Bed number screening-->

  <if test="bedNum!=null">

      and bed_num=#{bedNum}

  </if>

</select>

The attribute test in the tag is used to specify the judgment conditions. Here's the problem. The judgment conditions in test in the above example are all one condition. What if it becomes two or more conditions? Similar to the syntax of SQL, and can be connected as follows:

 <if test="bedNum!=null and bedNum!='' ">

      and bed_num=#{bedNum}

  </if>

choose,when,otherwise

Sometimes, we don't want to use all the conditions, but just want to choose one from multiple conditions. In this case, MyBatis provides a choose element, which is a bit like a switch statement in Java.

Let's change the above example: at this time, only one screening condition can be met. If the front-end sends an inpatient number, it will only be searched according to the inpatient number. If the front-end sends a bed number, it will only be screened according to the bed number. If nothing is sent, it will screen all the patients in the hospital. The query is as follows:

 <select id="selectPats"

     resultType="com.xxx.domain.PatientInfo">

  select * from patient_info where 1=1

  <choose>

    <!--Hospitalization number is not null Search by hospitalization number-->

    <when test="iptNum != null">

      AND ipt_num=#{iptNum}

    </when>

    <!--Bed number is not NUll-->

    <when test="bedNum != null">

      AND bed_num = #{bedNum}

    </when>

    <otherwise>

      AND status=1

    </otherwise>

  </choose>

</select>

MyBatis provides a choose element to judge whether the conditions in when are true in order. If one is true, choose ends. When all the when conditions in choose are not satisfied, execute sql in otherwise. Similar to the switch statement in Java, choose is switch, when is case, and otherwise is default.

where

For example, for the query in the example of choose tag, if 1 = 1 after where is removed, what will the SQL statement look like? There are three possible SQL statements, as follows:

select * from patient_info where AND ipt_num=#{iptNum};



select * from patient_info where AND bed_num = #{bedNum};



select * from patient_info where AND status=1;

What happened? Are the above three SQL statements correct? Obviously, it is wrong. Obviously, there is an AND after where. How to solve it? The where tag will be used at this time.

The WHERE element inserts the WHERE clause only if the child element returns anything. Also, if clause starts with the AND OR, WHERE element removes them.

The query transformation at this time is as follows:

<select id="selectPats"

     resultType="com.xxx.domain.PatientInfo">

  select * from patient_info

    <where>

        <choose>

          <!--Hospitalization number is not null Search by hospitalization number-->

          <when test="iptNum != null">

            AND ipt_num=#{iptNum}

          </when>

          <!--Bed number is not NUll-->

          <when test="bedNum != null">

            AND bed_num = #{bedNum}

          </when>

          <otherwise>

            AND status=1

          </otherwise>

        </choose>

   </where>

</select>

foreach

foreach is used to traverse collections, which is very similar to the function in Java. Usually handle in statements in SQL.

The foreach element is very powerful. It allows you to specify a collection and declare collection items and index variables that can be used in the element body. It also allows you to specify the separator between the beginning and end strings and the iteration of collection items. This element will not add extra separators by mistake

You can pass any iteratable object (such as List, Set, etc.), Map object or array object to foreach as a Set parameter. When using an iteratable object or array, index is the sequence number of the current iteration, and the value of item is the element obtained in this iteration. When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.

Examples are as follows:

<select id="selectPats" resultType="com.xxx.domain.PatientInfo">

  SELECT *

  FROM patient_info 

  WHERE ID in

  <foreach item="item" index="index" collection="list"

      open="(" separator="," close=")">

        #{item}

  </foreach>

</select>

I also made some conclusions in this interview. There is really a lot to learn. Relevant interview questions have also been sorted out and can be shared with you to understand the real interview questions. Small partners who want to enter a large factory or want to change jobs may wish to make good use of their time to learn. The pace of learning must not stop!

Friends who need this information can download it here for free , the contents sorted out are as follows: