MyBatis Dynamic SQL queries-make queries more flexible!

Preface

MyBatis, as we all know, the semi-automatic ORM framework, originally called Ibatis, and later seemed to be the 10 Apache software fund hosting it to Goole Code, renamed the MyBatis, the function is more powerful than before. It's a relatively fully automatic, durable framework hibernate, more flexible and lightweight, which I still have a deep understanding of.

One of the powerful features of MyBatis is the dynamic SQL capability, can save us a lot of concatenation to judge the pain of splicing SQL, according to the project, in a certain situation to use, can greatly reduce the code and complexity of the program, but it is not overly too complex to use, so as not conducive to later maintenance and expansion.

Here's a quick introduction.

Introducing foreach Batch Processing

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: You can pass a List instance or 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. Most commonly used in bulk delete and BULK insert functions, as follows:

<!--bulk delete data--><delete id= "Batchdelete" >  delete from Test where ID in  <foreach collection= "list" index= "index" item= "Item" open= "(" separator= "," close= ")" >   #{item}  </foreach></delete>

        <!--BULK INSERT data--><insert id= "Batchinsert" >insert into Test (ID, name, year,birthday) Values<foreach collection= "List" item= "item" index= "index" separator= "," > (#{id}, #{name},#{year},#{birthday,jdbctype=date}) </foreach></insert>

If you are often sentenced to empty use

<!--update data to selectively update data based on incoming conditions, such as NULL for ID, update all data--><update id= "Update" parametertype= " Com.inspur.demo.po.ExampleBean ">    Update Test set Name=#{name},year=to_number (#{year}), Birthday=to_date (#{ Birthday}, ' Yyyy-mm-dd hh24:mi:ss ')    <if test= "Id!=null" >    where id = #{id}    </if></update >

Use of where set

The <where> element knows that if any content is returned by the contained tag, only the "where" is inserted. Also, if you start with "and" or "or", you will skip WHERE not to insert.

<where><if test= "id!= null" >m.id=#{id}</if><if test= "name!= null" >and m.name like '%${name}% ' </if><if test= "year!= null" >and m.year=#{year,jdbctype=integer}</if><if test= "birthday!= null" ><! [Cdata[and to_char (M.birthday, ' Yyyy-mm-dd ') < #{birthday}]]></if></where>

The <set> element can be used to dynamically include updated columns without the need for updates.

        <!--update data to selectively update data based on incoming conditions, such as NULL for ID, update all data--><update id= "Update" parametertype= " Com.inspur.demo.po.ExampleBean ">    update Test    <set><if test=" name!= null ">name=#{name},< /if><if test= "year!= null" >year=#{year,jdbctype=integer},</if><if test= "birthday!= null" > Birthday=#{birthday,jdbctype=date}</if></set><if test= "Id=!null" >where id=#{id}</if>< /update>

Choose when use (relatively less)

<!--some cases can be selected using the Choose,when,otherwise--><select id= "Getbybean" parametertype= " Com.inspur.demo.po.ExampleBean "resulttype=" map ">select m.* from Test m where 1=1<choose><when test=" Judging 1 " >and conditions 1</when><when test= "Judging 2" >and conditions 2</when><otherwise>and conditions 3</otherwise></ Choose></select>

Reprint please indicate-majava my life (Chen Leixing) Source: http://blog.csdn.net/chenleixing/article/details/43818227

MyBatis Dynamic SQL queries-make queries more flexible!