Summarize some of the basic common Mybatis problems, convenient for themselves, convenient for readers to learn, not much content

1, greater than the number, less than the conversion of the sign in the sql statement

When using mybatis, the sql statement is written in an xml file, and if there are special characters in the sql, such as symbols such as .lt; There are two ways to resolve this: escape characters and mark CDATA blocks.

Mode 1, escape characters

1 <select id="searchByPrice" parameterType="Map" resultType="Product">




2     <!-- 方式1、转义字符 -->
3     select * from Product where price &gt;= #{minPrice} and price &lt;= #{maxPrice}
4 </select>

Mode 2, mark CDATA

1 <select id="searchByPrice" parameterType="Map" resultType="Product">
2   <!-- 方式2、CDATA -->
3   <![CDATA[select * from Product where price >= #{minPrice} and price <= #{maxPrice} ]]>
4 </select>

Escape character tables

2, resultType and resultMap in MyBatis

There is a lot of summary online, in short, resultType is used to return a type with only one field, and resultMap is used to return a type with multiple fields. As for whether the result is List or one, define whether the return value is List or a single in Mapper.

Using resultType:

1 <select id="count" resultType="java.lang.Integer">  
2         SELECT count(*) FROM USER  
3 </select>  

Using resultMap:

 1 <resultMap type="com.liulanghan.Blog" id="BlogResult">    
 2     <id column="id" property="id"/>    
 3     <result column="title" property="title"/>    
 4     <result column="content" property="content"/>    
 5     <result column="owner" property="owner"/>    
 6 </resultMap>   
 7    
 8 <select id="selectBlog" parameterType="int" resultMap="BlogResult">    
 9       select * from t_blog where id = #{id}    
10 </select>  

3, parameters

The parameter name needs to be @Param in Mapper ("queryDate"), the parameter is used in sql, and the string does not need quotation marks.

Parameter judgment and use of if:

1 <if test="queryDate != null">
2     and queryDate >= #{queryDate}
3 </if>

When otherwise is if else

1 <choose>
2      <when test="isDelete != null and isDelete == 0">
3           isDelete=0
4       </when>
5       <otherwise>
6           isDelete=1
7        </otherwise>
8 </choose>

If you want to judge a string, you need to quote it

1 <when test="gender != null and gender == 'MALE'">
2     gender='MALE'
3 </when>

4. The incoming parameter parameter is 0 query condition invalid

4.1 Scene reproduction

The scenario is this, you need to do a statement query, you can query by the amount range, the page parameters are written after the conditions, input 0 test countless times are invalid.

4.2 Reason resolution

When the page parameter is 0 and is passed into the xml of mybatis, if it is not a string, specify the data type or it will be mistaken for null

<if test="data.tatalAmount != null and data.totalAmount !='' ">
and total_Amount=#{data.totalAmount}
</if>

• 1
• 2
• 3

In this case, if totalAmount is 0, it will be mistaken for null, and the conditions inside will not be executed. The correct posture is as follows

1. Add 0 judgment

<if test="data.tatalAmount != null and data.totalAmount !='' or tatalAmount==0 ">
and total_Amount=#{data.totalAmount}
</if>

• 1
• 2
• 3

2. Specify the type of incoming parameters

<if test="data.tatalAmount != null and data.totalAmount !='' ">
and total_Amount=#{data.totalAmount,jdbc.Type=DECIMAL}
</if>