Mybatis Dynamic SQL

Choose, when, otherwise

Sometimes we don't want to apply all the conditions, instead we want to choose one of many situations. Switch and statements in Java are similar, MyBatis provides choose elements.

We use the above example, but now we search for only the title condition when the title is provided, only the author condition when the author is provided. If neither is provided, return only featured Blogs (perhaps a list of results chosen by the administrator policy, rather than returning a large list of meaningless random blog results).

<select id= "Findactivebloglike"      resulttype= "blog" >  select * from Blog WHERE state = ' ACTIVE '  < choose>    <when test= "Title! = null" > and      title like #{title}    </when>    <when test= " Author! = null and Author.name! = null "> and      author_name like #{author.name}    </when>    <otherwise > and      featured = 1    </otherwise>  </choose></select>
Choose this combination is a good choice when the condition is not fully entered

Foreach

Another necessary operation that is common to dynamic SQL is to iterate over a collection, usually built in the in condition. Like what:

<select id= "Selectpostin" resulttype= "Domain.blog.Post" >  select * from  Post P  WHERE ID in  < foreach item= "item" index= "index" collection= "list"      open= "(" separator= "," close= ")" >        #{item}  </ Foreach></select>

The foreach element is very powerful and allows you to specify a collection that declares collection items and index variables that can be used within an element body. It also allows you to specify open and closed strings and place delimiters between iterations. This element is very intelligent and does not accidentally append extra separators.

Note that you can pass a List instance or an array as a parameter object to MyBatis. When you do this, MyBatis will automatically wrap it in a Map, using the name as the key. The list instance will be "list" as the key, and the array instance will be "array" as the key.

This section is about the XML configuration file and the XML mapping file. The next section will discuss the Java API in detail, so you can get the most efficient mappings you've created.