The trim details of dynamic SQL in MyBatis

I. BACKGROUND

I used to use <where>, <update>, <if>, <foreach> tags for many, know there is <trim> this tag, but seldom to use, nor to understand it deeply, Until recently encountered a problem. The problem is this:

A SQL has three int query fields A, B, C, and the expression is:A=#{a} and (B=#{b} OR c=#{c}). Where A is required, B and C are not required (it is assumed that an incoming-1 indicates that the field does not participate in the query). Then the expression will have the following patterns:

• A=#{a}
• A=#{a} and B=#{b}
• A=#{a} and C=#{c}
• A=#{a} and (B=#{b} OR c=#{c})

After seeing this requirement, the logic is quite simple, but writing mapper SQL is not so easy (if you have one, please post the comments below). Consider the multi-layer <if>, <choose> and other tags, although it can also achieve this function, but too cumbersome. Is there a simpler way to achieve this? Yes! <trim>!!!

Two. function description and Usage

Online about <trim> not much, by looking at the source of MyBatis, a sentence to describe the function of Trim: clause end-to-end replacement .

Usage:

WHERE a = #{a}<trim prefix= "and (" prefixoverrides= "OR" suffix= ")" ><if test= "B! =-1" >or B = #{b}</if>< If test= "c! =-1" >or C = #{c}</if></trim>

　　

The trim details of dynamic SQL in MyBatis