Getting started with mybatis-dynamic SQL

Dynamic SQL for dynamic SQL judgment

MyBatis Core is the flexible operation of SQL statements, through the expression of judgment, the SQL is flexible splicing, assembly.

The existing requirements are as follows: You need to query the user, enter the user class, if the user's gender class is not empty, then the gender as one of the query criteria, if the user's name is not empty, then the user name as one of the query criteria. If the user has two properties that are empty, all users are queried.

We know that in mapper, we have only one parameter passed in, and multiple parameters can only be implemented by packet-to-class, now how to solve this problem? The answer is to add a judgment in the XML file to make the SQL statement dynamically generated. The corresponding Mapper.xml file code for the requirements is as follows:

　　　　

SQL fragment

Extract the dynamic SQL judgment blocks implemented above to form a SQL fragment. SQL fragments can be referenced in other statement. Easy for programmers to develop.

Define a SQL Fragment

　　　　

Referencing a SQL fragment

　　　　

Foreach

In our SQL statements, there are times when this happens:

SELECT * from USER WHERE id=1 or id=10 or id=16

SELECT * FROM USER WHERE ID in (1,10,16)

We will use foreach to solve this problem, the code is as follows:

　　　　

1 <SQLID= "Query_user_where">2         <ifTest= "Usercustom!=null">3             <ifTest= "Ids!=null">4             <!--using a foreach traversal for incoming IDs5 collection: Specifying the collection properties in the input object6 item: Each traversal of the build object7 Open: Start the concatenation of the string8 Close: End-of-time concatenation of strings9 Separator: A string that needs to be spliced in the two objects traversedTen               - One              <!--use the SQL stitching below to implement: A and (id=1 or id=10 or id=16) -                - -             <foreachCollection= "IDs"Item= "user_id"Open= "and ("Close=")"Separator= "or"> the                 <!--each traversal requires a concatenation of the string - - id=#{user_id} -             </foreach> -              +             <!--implement "and ID in (1,10,16)" Stitching - -             <!--<foreach collection= "IDs" item= "user_id" open= "and ID in (" close= ")" separator= "," > + each traversal requires a concatenation of the string A #{user_id} at </foreach> - -              -             </if> -         </if> -     </SQL>

View Code

Getting started with mybatis-dynamic SQL