MyBatis學(xué)習(xí) 之三、動態(tài)SQL語句

旭龍 2011-03-20

展開全文

三、動態(tài)SQL語句

有些時候，sql語句where條件中，需要一些安全判斷，例如按性別檢索，如果傳入的參數(shù)是空的，此時查詢出的結(jié)果很可能是空的，也許我們需要參數(shù)為空時，是查出全部的信息。這是我們可以使用動態(tài)sql，增加一個判斷，當(dāng)參數(shù)不符合要求的時候，我們可以不去判斷此查詢條件。
下文均采用mysql語法和函數(shù)（例如字符串鏈接函數(shù)CONCAT）。

源代碼http://limingnihao./admin/blogs/782190頁面最下面；

3.1 if標(biāo)簽

一個很普通的查詢：

<!-- 查詢學(xué)生list，like姓名 -->
<select id="getStudentListLikeName" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</select>

但是此時如果studentName是null或空字符串，此語句很可能報錯或查詢結(jié)果為空。此時我們使用if動態(tài)sql語句先進行判斷，如果值為null或等于空字符串，我們就不進行此條件的判斷。

修改為：

<!-- 查詢學(xué)生list，like姓名 -->
<select id=" getStudentListLikeName " parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
<if test="studentName!=null and studentName!='' ">
WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</if>
</select>

此時，當(dāng)studentName的值為null或’’的時候，我們并不進行where條件的判斷，所以當(dāng)studentName值為null或’’值，不附帶這個條件，所以查詢結(jié)果是全部。

由于參數(shù)是Java的實體類，所以我們可以把所有條件都附加上，使用時比較靈活， new一個這樣的實體類，我們需要限制那個條件，只需要附上相應(yīng)的值就會where這個條件，相反不去賦值就可以不在where中判斷。

代碼中的where標(biāo)簽，請參考3.2.1.

<!-- 查詢學(xué)生list，like姓名，=性別、=生日、=班級，使用where,參數(shù)entity類型 -->
<select id="getStudentListWhereEntity" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
<where>
<if test="studentName!=null and studentName!='' ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</if>
<if test="studentSex!= null and studentSex!= '' ">
AND ST.STUDENT_SEX = #{studentSex}
</if>
<if test="studentBirthday!=null">
AND ST.STUDENT_BIRTHDAY = #{studentBirthday}
</if>
<if test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' ">
AND ST.CLASS_ID = #{classEntity.classID}
</if>
</where>
</select>

查詢，姓名中有‘李’，男，生日在‘1985-05-28’，班級在‘20000002’的學(xué)生。

StudentEntity entity = new StudentEntity();
entity.setStudentName("李");
entity.setStudentSex("男");
entity.setStudentBirthday(StringUtil.parse("1985-05-28"));
entity.setClassEntity(classMapper.getClassByID("20000002"));
List<StudentEntity> studentList = studentMapper.getStudentListWhereEntity(entity);
for( StudentEntity entityTemp : studentList){
System.out.println(entityTemp.toString());
}

3.2 where、set、trim標(biāo)簽

3.2.1 where

當(dāng)if標(biāo)簽較多時，這樣的組合可能會導(dǎo)致錯誤。例如，like姓名，等于指定性別等：

<!-- 查詢學(xué)生list，like姓名，=性別 -->
<select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
WHERE
<if test="studentName!=null and studentName!='' ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</if>
<if test="studentSex!= null and studentSex!= '' ">
AND ST.STUDENT_SEX = #{studentSex}
</if>
</select>

如果上面例子，參數(shù)studentName為null或’’，則或?qū)е麓藄ql組合成“WHERE AND”之類的關(guān)鍵字多余的錯誤SQL。
這時我們可以使用where動態(tài)語句來解決。這個“where”標(biāo)簽會知道如果它包含的標(biāo)簽中有返回值的話，它就插入一個‘where’。此外，如果標(biāo)簽返回的內(nèi)容是以AND 或OR 開頭的，則它會剔除掉。
上面例子修改為：

<!-- 查詢學(xué)生list，like姓名，=性別 -->
<select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
<where>
<if test="studentName!=null and studentName!='' ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</if>
<if test="studentSex!= null and studentSex!= '' ">
AND ST.STUDENT_SEX = #{studentSex}
</if>
</where>
</select>

3.2.2 set

當(dāng)在update語句中使用if標(biāo)簽時，如果前面的if沒有執(zhí)行，則或?qū)е露禾柖嘤噱e誤。使用set標(biāo)簽可以將動態(tài)的配置SET 關(guān)鍵字，和剔除追加到條件末尾的任何不相關(guān)的逗號。
沒有使用if標(biāo)簽時，如果有一個參數(shù)為null，都會導(dǎo)致錯誤，如下示例：

<!-- 更新學(xué)生信息 -->
<update id="updateStudent" parameterType="StudentEntity">
UPDATE STUDENT_TBL
SET STUDENT_TBL.STUDENT_NAME = #{studentName},
STUDENT_TBL.STUDENT_SEX = #{studentSex},
STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},
STUDENT_TBL.CLASS_ID = #{classEntity.classID}
WHERE STUDENT_TBL.STUDENT_ID = #{studentID};
</update>

使用set+if標(biāo)簽修改后，如果某項為null則不進行更新，而是保持數(shù)據(jù)庫原值。如下示例：

<!-- 更新學(xué)生信息 -->
<update id="updateStudent" parameterType="StudentEntity">
UPDATE STUDENT_TBL
<set>
<if test="studentName!=null and studentName!='' ">
STUDENT_TBL.STUDENT_NAME = #{studentName},
</if>
<if test="studentSex!=null and studentSex!='' ">
STUDENT_TBL.STUDENT_SEX = #{studentSex},
</if>
<if test="studentBirthday!=null ">
STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},
</if>
<if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!='' ">
STUDENT_TBL.CLASS_ID = #{classEntity.classID}
</if>
</set>
WHERE STUDENT_TBL.STUDENT_ID = #{studentID};
</update>

3.2.3 trim

trim是更靈活的去處多余關(guān)鍵字的標(biāo)簽，他可以實踐where和set的效果。

where例子的等效trim語句：

<!-- 查詢學(xué)生list，like姓名，=性別 -->
<select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="studentName!=null and studentName!='' ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</if>
<if test="studentSex!= null and studentSex!= '' ">
AND ST.STUDENT_SEX = #{studentSex}
</if>
</trim>
</select>

set例子的等效trim語句：

<!-- 更新學(xué)生信息 -->
<update id="updateStudent" parameterType="StudentEntity">
UPDATE STUDENT_TBL
<trim prefix="SET" suffixOverrides=",">
<if test="studentName!=null and studentName!='' ">
STUDENT_TBL.STUDENT_NAME = #{studentName},
</if>
<if test="studentSex!=null and studentSex!='' ">
STUDENT_TBL.STUDENT_SEX = #{studentSex},
</if>
<if test="studentBirthday!=null ">
STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},
</if>
<if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!='' ">
STUDENT_TBL.CLASS_ID = #{classEntity.classID}
</if>
</trim>
WHERE STUDENT_TBL.STUDENT_ID = #{studentID};
</update>

3.3 choose (when, otherwise)

有時候我們并不想應(yīng)用所有的條件，而只是想從多個選項中選擇一個。MyBatis提供了choose 元素，按順序判斷when中的條件出否成立，如果有一個成立，則choose結(jié)束。當(dāng)choose中所有when的條件都不滿則時，則執(zhí)行otherwise中的sql。類似于Java 的switch 語句，choose為switch，when為case，otherwise則為default。
if是與(and)的關(guān)系，而choose是或（or）的關(guān)系。

例如下面例子，同樣把所有可以限制的條件都寫上，方面使用。選擇條件順序，when標(biāo)簽的從上到下的書寫順序：

<!-- 查詢學(xué)生list，like姓名、或=性別、或=生日、或=班級，使用choose -->
<select id="getStudentListChooseEntity" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
<where>
<choose>
<when test="studentName!=null and studentName!='' ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</when>
<when test="studentSex!= null and studentSex!= '' ">
AND ST.STUDENT_SEX = #{studentSex}
</when>
<when test="studentBirthday!=null">
AND ST.STUDENT_BIRTHDAY = #{studentBirthday}
</when>
<when test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' ">
AND ST.CLASS_ID = #{classEntity.classID}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>

3.4 foreach

對于動態(tài)SQL 非常必須的，主是要迭代一個集合，通常是用于IN 條件。
List 實例將使用“list”做為鍵，數(shù)組實例以“array” 做為鍵。

3.4.1參數(shù)為list實例的寫法：

SQL寫法：

<select id="getStudentListByClassIDs" resultMap="studentResultMap">
SELECT * FROM STUDENT_TBL ST
WHERE ST.CLASS_ID IN
<foreach collection="list" item="classList"  open="(" separator="," close=")">
#{classList}
</foreach>
</select>

接口的方法聲明：

public List<StudentEntity> getStudentListByClassIDs(List<String> classList);

測試代碼，查詢學(xué)生中，在20000002、20000003這兩個班級的學(xué)生：

List<String> classList = new ArrayList<String>();
classList.add("20000002");
classList.add("20000003");
List<StudentEntity> studentList = studentMapper.getStudentListByClassIDs(classList);
for( StudentEntity entityTemp : studentList){
System.out.println(entityTemp.toString());
}

3.4.2參數(shù)為Array實例的寫法：

SQL語句：

<select id="getStudentListByClassIDs" resultMap="studentResultMap">
SELECT * FROM STUDENT_TBL ST
WHERE ST.CLASS_ID IN
<foreach collection="array" item="ids"  open="(" separator="," close=")">
#{ids}
</foreach>
</select>

接口的方法聲明：

public List<StudentEntity> getStudentListByClassIDs(String[] ids);

測試代碼，查詢學(xué)生中，在20000002、20000003這兩個班級的學(xué)生：

String[] ids = new String[2];
ids[0] = "20000002";
ids[1] = "20000003";
List<StudentEntity> studentList = studentMapper.getStudentListByClassIDs(ids);
for( StudentEntity entityTemp : studentList){
System.out.println(entityTemp.toString());
}

附件中是源代碼，其中Resources/sql中是，使用MySql數(shù)據(jù)庫或access數(shù)據(jù)庫的全部的sql語句。