1185人阅读
oracle(12)
mysql(28)
当我们使用某个表达式作为输出的一列时
我们无法再
条件中直接使用该列作判断条件
例如下面的
select id, (c1 + c2) as s from t1
where s & 100
SQL Server
select id, (c1 + c2) as s from t1
where (c1 + c2) & 100
就没问题了
可是当表达式复杂时就很繁琐了
有没有可以在
中使用这样的列名的办法
或者有什么其他办法可以解决这类问题呢
解决方法:
select t2.*
from (select id, (c1 + c2) as c from t1) t2
where c & 100
select t2.*
from (select id, c = c1+c2 from t1) t2
where c & 100
语句不能直接使用列别名,因此我们需要将
语句给包装一下
子句中引用列别名会出错,这是为什么呢?
语句的执行顺序
3. start with
4. connect by
6. group by
等集合演算演算
11. order by
&&相关文章推荐
参考知识库
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
访问:218235次
积分:5450
积分:5450
排名:第4367名
原创:313篇
转载:119篇
评论:18条
(2)(2)(6)(5)(10)(14)(8)(5)(9)(19)(3)(1)(3)(4)(16)(9)(11)(8)(9)(5)(10)(25)(25)(35)(18)(2)(1)(6)(17)(43)(13)(1)(11)(1)(5)(33)(30)(8)(1)(1)> mybatis动态sql设置有些时候,sql语句where条件中,需要一些安全判断,例如按性别检索
mybatis动态sql设置有些时候,sql语句where条件中,需要一些安全判断,例如按性别检索
发布时间: & &
浏览:146 & &
回复:0 & &
悬赏:0.0希赛币
mybatis动态sql设置
有些时候,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& &!-- 查询学生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& &!-- 查询学生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& &!-- 查询学生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&查询,姓名中有‘李’,男,生日在‘’,班级在‘’的学生。
Java代码 StudentEntity entity = new StudentEntity(); entity.setStudentName("李"); entity.setStudentSex("男"); entity.setStudentBirthday(StringUtil.parse("")); entity.setClassEntity(classMapper.getClassByID("")); List&StudentEntity& studentList = studentMapper.getStudentListWhereEntity(entity); for( StudentEntity entityTemp : studentList){
System.out.println(entityTemp.toString()); } StudentEntity entity = new StudentEntity();entity.setStudentName("李");entity.setStudentSex("男");entity.setStudentBirthday(StringUtil.parse(""));entity.setClassEntity(classMapper.getClassByID(""));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& &!-- 查询学生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& &!-- 查询学生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& &!-- 更新学生信息 --&&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& &!-- 更新学生信息 --&&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& &!-- 查询学生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& &!-- 更新学生信息 --&&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& &!-- 查询学生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& &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&接口的方法声明:
Java代码 public List&StudentEntity& getStudentListByClassIDs(List&String& classList); public List&StudentEntity& getStudentListByClassIDs(List&String& classList); 测试代码,查询学生中,在000003这两个班级的学生:
Java代码 List&String& classList = new ArrayList&String&(); classList.add(""); classList.add(""); List&StudentEntity& studentList = studentMapper.getStudentListByClassIDs(classList); for( StudentEntity entityTemp : studentList){
System.out.println(entityTemp.toString()); } List&String& classList = new ArrayList&String&();classList.add("");classList.add("");
List&StudentEntity& studentList = studentMapper.getStudentListByClassIDs(classList);for( StudentEntity entityTemp : studentList){ System.out.println(entityTemp.toString());}
3.4.2参数为Array实例的写法:SQL语句:
Xml代码 &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& &select id="getStudentListByClassIDs" resultMap="studentResultMap"& SELECT * FROM STUDENT_TBL ST
WHERE ST.CLASS_ID IN
&foreach collection="array" item="ids"
open="(" separator="," close=")"&
&/foreach&&/select&
接口的方法声明:
Java代码 public List&StudentEntity& getStudentListByClassIDs(String[] ids); public List&StudentEntity& getStudentListByClassIDs(String[] ids);测试代码,查询学生中,在000003这两个班级的学生:
Java代码 String[] ids = new String[2]; ids[0] = ""; ids[1] = ""; List&StudentEntity& studentList = studentMapper.getStudentListByClassIDs(ids); for( StudentEntity entityTemp : studentList){
System.out.println(entityTemp.toString()); }
本问题标题:
本问题地址:
温馨提示:本问题已经关闭,不能解答。
暂无合适的专家
&&&&&&&&&&&&&&&
希赛网 版权所有 & &&本帖子已过去太久远了,不再提供回复功能。}