mybatis<SQL>(个人记录仅供参考)

1. <?xml version="1.0" encoding="UTF-8"?>    
2. <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">  
3. <mapper namespace="UserInfo">  
4.    <resultMap id="UserInfoResult" type="com.demo.userinfo.model.UserInfo">  
5.       <result column="user_id" jdbcType="VARCHAR" property="userId" />  
6.       <result column="username" jdbcType="VARCHAR" property="username" />  
7.       <result column="password" jdbcType="VARCHAR" property="password" />  
8.    </resultMap>  
9.     
10.    <!-- 语句中不能通过<include refid="xx" />方式引用 commonColumns 和 dynamicWhere-->  
11.    <sql id="commonColumns">  
12.       <![CDATA[ user_id, username, password ]]>  
13.    </sql>  
14.   
15.    <sql id="dynamicWhere">  
16.       <where>  
17.      <if test="@O@isNotEmpty(id)">  
18.          AND ID = #{id}  
19.      </if>  
20.      <if test="@O@isNotEmpty(userName)">  
21.          AND userName = #{userName}  
22.      </if>  
23.      <if test="@O@isNotEmpty(password)">  
24.         AND password = #{password}  
25.      </if>  
26.        </where>  
27.    </sql>  
28.   
29.    <update id="update" parameterType="com.demo.userinfo.model.UserInfo"> UPDATE user_info  
30.     <set>  
31.         <if test="username != null">username = #{username} ,</if>  
32.         <if test="password != null">password = #{password}</if>  
33.     </set>  
34.         WHERE  
35.     user_id = #{userId}  
36.    </update>  
37.   
38.     <!-- 错误***** 只要采用Include方式引用公共的commonColumns和dynamicWhere就报错 ****** -->  
39.     <select id="findById" parameterType="string" resultMap="UserInfoResult">  
40.     select  
41.     <include refid="commonColumns" />  
42.         <![CDATA[ from user_info where user_id = #{userId} ]]>  
43.     </select>  
44.   
45.   
46.     <!-- 正确***** 只要采用Include方式引用公共的commonColumns和dynamicWhere就报错 ****** -->  
47.     <select id="findById" parameterType="string" resultMap="UserInfoResult">  
48.     select  
49.     user_id, username, password  
50.         <![CDATA[ from user_info where user_id = #{userId} ]]>  
51.     </select>  
52.   
53.       
54.     <!-- 错误***** 只要采用Include方式引用公共的commonColumns和dynamicWhere就报错 ****** -->  
55.     <select id="findByConditions" resultMap="UserInfoResult" parameterType="com.demo.page">  
56.     select  
57.         <include refid="commonColumns" />  
58.     from user_info  
59.         where  
60.         <include refid="dynamicWhere" />  
61.     </select>  
62.   
63.     <!-- 正确***** 只要不采用Include方式引用公共的commonColumns和dynamicWhere就正确 ****** -->  
64.     <select id="findByConditions" resultMap="UserInfoResult" parameterType="com.demo.page">  
65.     select  
66.         user_id, username, password  
67.     from user_info  
68.         where 1= 1  
69.       <where>  
70.      <if test="@O@isNotEmpty(id)">  
71.          AND ID = #{id}  
72.      </if>  
73.      <if test="@O@isNotEmpty(userName)">  
74.          AND userName = #{userName}  
75.      </if>  
76.      <if test="@O@isNotEmpty(password)">  
77.         AND password = #{password}  
78.      </if>  
79.        </where>  
80.     </select>  
81. </mapper>