• preface

  • What is dynamic SQL?

  • Common labels

  • if

  • Choose, when, or otherwise

  • where

  • foreach

  • set

  • sql

  • include

  • Expand the

  • How to avoid magic numbers in Mybatis?

  • How do I refer to SQL fragments in other XML?

  • conclusion

preface

  • Through the first two articles we understand Mybatis basic CRUD operation, some basic tag attributes and how to map the results, interested can see my first two articles, respectively is Mybatis basic operation and Mybatis result mapping, you shot accurately? If you have any questions, you can leave a message at the bottom of the article and the author will reply.
  • This article will talk about Mybatis dynamic SQL, in the actual development of Mybatis this function is very important, as for what is dynamic SQL? How to implement dynamic SQL? The following article will introduce it in detail.

What is dynamic SQL?

  • Dynamic SQL is one of the powerful features of MyBatis. As the name implies, SQL is moving, that is, the ability to flexibly splice together a complete SQL statement according to certain conditions. This is similar to case when then else then end…. in MySQL This syntax can dynamically concatenate the required SQL based on certain conditions.
  • Mybatis provides a lot of tags, which can be used flexibly in XML files to achieve the purpose of concatenating SQL.

Common labels

  • Mybatis in order to allow developers to write SQL flexibly is also a lot of effort, defined a lot of tags and syntax, will be introduced one by one below.

if

  • If (true); if (true); if (true); if (true);

  • For example, in HIS system, medical staff need to screen patients based on specific conditions, such as hospital number, bed, gender, etc. Of course, these conditions are not required, and the specific functions are shown in the screenshot below:

  • The filter condition in the screenshot above is not mandatory, so we can’t fix it in SQL. It depends on whether the front end passes the value. So how to write the query? As follows:

    select * from patient_info where status=1 and ipt_num=#{iptNum} and bed_num=#{bedNum}

The test attribute in the tag is used to specify the criteria. In the above example, the criteria in test are all one condition. What if there are two or more criteria? SQL > select * from ‘and’;

<if test="bedNum! =null and bedNum! ='' "> and bed_num=#{bedNum} </if>Copy the code

Choose, when, or otherwise

  • Sometimes, we don’t want to use all of the conditions, but just choose one of several conditions to use. For this, MyBatis provides the Choose element, which is a bit like the Switch statement in Java.

  • Change the above example: at this time, only one screening condition can be met. If the front end transmits the hospital number, only the hospital number will be searched; if the front end transmits the bed number, only the bed number will be screened; if nothing is transmitted, all those in the hospital will be screened. The query is as follows:

    select * from patient_info where 1=1 AND ipt_num=#{iptNum} AND bed_num = #{bedNum} AND status=1

MyBatis provides the choose element to judge whether the conditions in WHEN are true or not in order. If one of the conditions is true, choose ends. When all when conditions in Choose are not satisfied, SQL in Otherwise is executed. Similar to the Java switch statement, choose is switch, when is case, and otherwise is default.

where

  • For example, there are three possible SQL statements for the choose tag query if 1=1 is removed from where:

    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? That’s not true. There’s an AND after where. How to solve it? This is where we use the where tag.

  • The WHERE element inserts the WHERE clause only if the child element returns anything. Also, if the clause begins with AND OR OR, the WHERE element removes those as well.

  • The query transformation is as follows:

    select * from patient_info AND ipt_num=#{iptNum} AND bed_num = #{bedNum} AND status=1

foreach

  • Foreach is used to iterate over collections, which is similar to Java. Typically deals with IN statements in SQL.

  • The foreach element is very powerful. It allows you to specify a collection and declare the collection item and index variables that can be used inside the element. It also allows you to specify beginning and ending strings and separators between iterations of collection items. This element also does not mistakenly add extra delimiters

  • You can pass foreach any iterable (List, Set, etc.), Map, or array object as a collection parameter. When using an iterable or array, index is the sequence number of the current iteration and the value of item is the element fetched in the current 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 * FROM patient_info WHERE ID in #{item}

  • The meanings of each attribute in the modified tag are as follows:

set

  • Before we talk about this tag, let’s take a look at this example:

    UPDATE STUDENT SET NAME = #{name}, MAJOR = #{major}, HOBBY = #{hobby} WHERE ID = #{id}; UPDATE STUDENT SET NAME = #{name}, MAJOR = #{major}, HOBBY = #{hobby} WHERE ID = #{id};

  • The example above does not use the if tag and results in an error if one of the arguments is null. When using the if tag in an UPDATE statement, it may result in a comma error if the last if is not executed. Use the set tag to dynamically configure the set keyword and strip out any irrelevant commas appended to the end of a condition.

  • After using the set+if tag, if an item is null, it is not updated and the database value is kept unchanged. The query is as follows:

    UPDATE STUDENT NAME = #{name}, MAJOR = #{major}, HOBBY = #{hobby} WHERE ID = #{id};

sql

  • Encountered in practical developing, many of the same SQL screening based on certain conditions, for example, you can use in many parts of the screen, we can be extracted as a part of the public, such changes also convenient, once appear the wrong, only need to change this place can be everywhere effect, USES the label at this time.

  • When the query fields or query conditions of multiple types of query statements are the same, they can be defined as constants for easy invocation. SQL statements can also be decomposed for clarity of structure. As follows: ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY where 1=1 and id = #{id} and MAJOR = #{major} and BIRTHDAY = #{birthday} and AGE = #{age} and NAME = #{NAME} and HOBBY = #{HOBBY} include For example, reference the constant defined by the SQL tag above, as follows: SELECT FROM student

  • The refid attribute is the id value (unique identifier) in the specified tag.

conclusion

  • So far, Mybatis dynamic SQL common tags have been introduced, this part of the content in the actual work is must be used, unless your company does not use Mybatis.

Expand the

  • In front of the introduction of dynamic SQL some tags and attributes, I believe that after reading should be able to flexible application, but in the actual development of some strange technology nifty, Chen today simply talk about a few.

How to avoid magic numbers in Mybatis

  • Open alibaba development manual probably know that the code is not allowed to appear magic number, what is magic number? Simply put, it is a number, a number that only you know and no one else knows what it means. Usually in Java code we define a constant class that defines these numbers.

  • Type =1 = doctor; type=2 = nurse;

    -… Access to your doctor –…. Obtain the permission of the nurse

  • There’s nothing wrong with that, but if the meaning of type changes, you’ll need to change all the SQL you’re referring to.

  • A constant class is usually defined in development, as follows:

    package com.xxx.core.Constants; Public class CommonConstants{// constant public final static int DOC_TYPE=1;

    Public final static int NUR_TYPE=2;

    }

So how to write the SQL at this point? As follows:

<if test="type! =null and [email protected]@DOC_TYPE"> -- .... </if> <if test="type! =null and [email protected]@NUR_TYPE"> -- .... Permission to obtain nurse </if>Copy the code
  • It’s that simple, it’s at sign + full name + at sign + constant.
  • In addition to calling constant class constants, but also the method in the class, rarely used, no longer introduced, interested in baidu can ask.

How do I refer to SQL fragments in other XML

  • In the actual development, you may encounter a problem, such as this resultMap or this fragment has been defined in another XXxmapper.xml, at this time, the current XML still needs to be used, shall I copy it? Xiao Bai didn’t ask anything to come up and copy, well, the late modification came, every place needs to be modified. Sad not?

  • Mybatis also supports SQL fragment reference from other Mapper files. For example, if you define a SQL fragment in com.xxx.dao.xxmapper’s XML as follows:

    ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY

At this point I am com. XXX. Dao. PatinetMapper need to reference the XML file, as follows:

 <include refid="com.xxx.dao.xxMapper.Base_Column_List"></include>
Copy the code
  • It’s so simple, it’s like a full class name in Java.
  • The resultMap in the tag can also be referenced in the same way as above, without further elaboration.