sql語句where條件中,需要一些安全判斷,例如按性別檢索,如果傳入的參數是空的,此時查詢出的結果很可能是空的,也許我們需要參數為空 時,是查出全部的信息。這是我們可以使用動態sql,增加一個判斷,當參數不符合要求的時候,我們可以不去判斷此查詢條件。 下文均采用MySQL語法和函數(例如字符串鏈接函數CONCAT
3.1 if標簽 一個很普通的查詢:
xml代碼
<!-- 查詢學生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或空字符串,此語句很可能報錯或查詢結果為空。此時我們使用if動態sql語句先進行判斷,如果值為null或等于空字符串,我們就不進行此條件的判斷。
修改為:
Xml代碼<!-- 查詢學生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> 此時,當studentName的值為null或’’的時候,我們并不進行where條件的判斷,所以當studentName值為null或’’值,不附帶這個條件,所以查詢結果是全部。由于參數是java的實體類,所以我們可以把所有條件都附加上,使用時比較靈活, new一個這樣的實體類,我們需要限制那個條件,只需要附上相應的值就會where這個條件,相反不去賦值就可以不在where中判斷。
代碼中的where標簽,請參考3.2.1.
Xml代碼<!-- 查詢學生list,like姓名,=性別、=生日、=班級,使用where,參數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’的學生。Java代碼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標簽
3.2.1 where當if標簽較多時,這樣的組合可能會導致錯誤。例如,like姓名,等于指定性別等:
Xml代碼<!-- 查詢學生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> 如果上面例子,參數studentName為null或’’,則或導致此sql組合成“WHERE AND”之類的關鍵字多余的錯誤SQL。 這時我們可以使用where動態語句來解決。這個“where”標簽會知道如果它包含的標簽中有返回值的話,它就插入一個‘where’。此外,如果標簽返回的內容是以AND 或OR 開頭的,則它會剔除掉。 上面例子修改為:Xml代碼<!-- 查詢學生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當在update語句中使用if標簽時,如果前面的if沒有執行,則或導致逗號多余錯誤。使用set標簽可以將動態的配置SET 關鍵字,和剔除追加到條件末尾的任何不相關的逗號。沒有使用if標簽時,如果有一個參數為null,都會導致錯誤,如下示例:
Xml代碼<!-- 更新學生信息 --> <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標簽修改后,如果某項為null則不進行更新,而是保持數據庫原值。如下示例:Xml代碼<!-- 更新學生信息 --> <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是更靈活的去處多余關鍵字的標簽,他可以實踐where和set的效果。
where例子的等效trim語句:
Xml代碼<!-- 查詢學生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語句:Xml代碼<!-- 更新學生信息 --> <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) 有時候我們并不想應用所有的條件,而只是想從多個選項中選擇一個。MyBatis提供了choose 元素,按順序判斷when中的條件出否成立,如果有一個成立,則choose結束。當choose中所有when的條件都不滿則時,則執行 otherwise中的sql。類似于Java 的switch 語句,choose為switch,when為case,otherwise則為default。 if是與(and)的關系,而choose是或(or)的關系。
例如下面例子,同樣把所有可以限制的條件都寫上,方面使用。選擇條件順序,when標簽的從上到下的書寫順序:
Xml代碼<!-- 查詢學生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對于動態SQL 非常必須的,主是要迭代一個集合,通常是用于IN 條件。List 實例將使用“list”做為鍵,數組實例以“array” 做為鍵。
3.4.1參數為list實例的寫法:SQL寫法:
Xml代碼<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); public List<StudentEntity> getStudentListByClassIDs(List<String> classList); 測試代碼,查詢學生中,在20000002、20000003這兩個班級的學生: 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()); } 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參數為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); public List<StudentEntity> getStudentListByClassIDs(String[] ids);測試代碼,查詢學生中,在20000002、20000003這兩個班級的學生: 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()); }
新聞熱點
疑難解答