一、
1.前提:为什么会出现动态SQL?
首先我们来看一下分别在JDBC、Hibernate和MyBatis中使用SQL语句的情况注意:此例子满足经典数据库列名字A_COLUMN到java对象属性名aColumn的映射,省略了在mybatis-config.xml文件中自动映射的配置<setting name="mapUnderscoreToCamelCase" value="true"/>,只有当设置为true且满足驼峰命名规则时,才可以实现从数据库到POJO对象的自动映射,否则需要自定义映射规则,这里不做介绍,请看mybatis的其他总结!
(1)设计一张学生表如图:
(2)定义一个Student类:
package com.cyn.po;
/*
属性的命名符合驼峰命名规范
*/
public class Student {
private int Sno;
private String Sname;
private String Ssex;
private int Sage;
private String Sdept;
public int getSno() {
return Sno;
}
public void setSno(int sno) {
Sno = sno;
}
public String getSname() {
return Sname;
}
public void setSname(String sname) {
Sname = sname;
}
public String getSsex() {
return Ssex;
}
public void setSsex(String ssex) {
Ssex = ssex;
}
public int getSage() {
return Sage;
}
public void setSage(int sage) {
Sage = sage;
}
public String getSdept() {
return Sdept;
}
public void setSdept(String sdept) {
Sdept = sdept;
}
}
(3)定义两个变量
String Sname = "lisi";
String Sage = 16
情景:
通过Sname和Sage在T_STUDENT表中查询该学生的所有信息,比较在JDBC、Hibernate(HQL)和MyBatis中使用SQL语句的不同
(1)JDBC:
"select * from T_STUDENT where S_NAME = '"+Sname+"' and S_AGE = '"+Sage+"'"
(2)Hibernate:
"from Student where S_NAME = '"+Sname+"' and S_AGE = '"+Sage+"'"
(3)MyBatis:
"select * from T_STUDENT where S_NAME = #{Sname} and S_AGE = #{Sage}"
2.结论:
(1)JDBC:SQL的拼接要特别注意引号和空格的使用,后期维护是个灾难
(2)Hibernate:SQL被进行了过度封装,而HQL不够灵活,特殊场景下不适用
(3)MyBatis:可以根据参数信息灵活多变,在下面的使用中可以深切体会它的强大之处-针对同一个表而言,多次不同条件的查询只需写一次select,多次不同字段的更新只需写一次update等
二、动态SQL的使用:
1.动态SQL-if语句:
(1)首先不使用动态SQL
<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
<!--student是com.cyn.po.Student的别名,我们省略了在mybatis-config.xml中的配置,以下情况中均使用student来代替com.cyn.po.Student-->
select * from T_STUDENT where S_NAME = #{Sname} and S_AGE = #{Sage}
</select>
注意:这里和普通的sql查询语句差不多,对于只有一个参数,后面的#{Sname}表示占位符,里面不一定要写Sname,写啥都可以,但是不要空着,如果有多个参数则必须写pojo类里面的属性
- ·id:和接口方法名保持一致
parameterType:和接口方法中的参数类型保持一致
resultType:和接口方法中的返回值类型保持一致
- ·#{**}和$(**)的作用?
从参数域中取出参数名为**的值,值为什么类型,默认#{}就是什么类型
例如:String username = "cyn"
#{username} = "cyn" = 'cyn'
- ·为什么使用#{**}而不使用$(**)?
使用#{**}可以防止SQL注入
(2)接上面的查询语句,我们发现如果#{Sname}为空,那么查询结果也为空,如何解决呢?
<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
select * from T_STUDENT where
<if test="Sname != null and Sname != '' ">
S_NAME = #{Sname}
</if>
<if test="Sage != null and Sage != '' ">
and S_AGE = #{Sage}
</if>
</select>
注意:这样写我们可以看到,如果Sage等于null那么查询语句为 select * from T_STUDENT where S_NAME = #{Sname},但是如果Sname为空呢?那么查询语句为 select * from T_STUDENT where and Sage = #{Sage},这是错误的SQL语句,如何解决呢?请看下面的where语句
*2.动态SQL-if+where语句
<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
select * from T_STUDENT
<where>
<if test="Sname != null and Sname != '' ">
and S_NAME = #{Sname}
</if>
<if test="Sage != null and Sage != '' ">
and S_AGE = #{Sage}
</if>
</where>
</select>
可能看到很多人只有在第一个判断执行语句中省略了and,为了防止记性混乱,我们统一方便都在判断执行语句前面添加and!你只需知道这个“where”标签会知道如果它包含的标签中有返回值的话,它就插入一个‘where’。此外,如果标签返回的内容是以AND 或OR 开头的,则它会自动剔除掉以此保证sql语句格式的正确性。
注意:当上述查询条件Sname和Sage都为空的时候,默认查询语句为:select * from T_STUDENT,符合某些当不输入查询条件时返回全部查询的需求,更为灵活!
3.动态SQL:if+set语句
(1)当不使用set标签而使用原生sql语句中的set:
<update id="updateStudentBySnameAndSage" parameterType="com.cyn.po.Student">
update T_STUDENT set
<if test="Sname != null and Sname != '' ">
S_NAME = #{Sname},
</if>
<if test="Sage != null and Sage != '' ">
S_AGE = #{Sage},
</if>
<if test="Sdept != null and Sdept != '' ">
S_DEPT = #{Sdept}
</if>
where S_NO = #{Sno}
</update>
注意:当更新全部字段时候成立,但是当Sdept等于null,此时我们只想更新Sname和Sage字段时sql语句则变成了:update T_STUDENT set S_NAME = #{Sname},S_AGE = #{Sage}, where S_NO = #{S_no}。我们发现多了一个逗号,如何解决呢?
*(2)当在update更新语句中引入set标签时:
<update id="updateStudentBySnameAndSage" parameterType="com.cyn.po.Student">
update T_STUDENT
<set>
<if test="Sname != null and Sname != '' ">
S_NAME = #{Sname},
</if>
<if test="Sage != null and Sage != '' ">
S_AGE = #{Sage},
</if>
<if test="Sdept != null and Sdept != '' ">
S_DEPT = #{Sdept},
</if>
</set>
where S_NO = #{Sno}
</update>
在这种情况下,当我们只想更新Sname和Sage字段时sql语句格式正确:update T_STUDENT set S_NAME = #{Sname},S_AGE = #{Sage} where S_NO = #{S_no}
注意:可能看到很多人只有在最后一个判断执行语句中省略了逗号,为了防止记性混乱,我们统一方便都在判断执行语句后面添加逗号即可!你只需知道在动态生成的sql语句中,set标签会默认将sql语句中最后一个逗号去掉,即可满足所有的更新情况!
*4.动态SQL:choose(when,otherwise)语句
有时候,我们不想用到所有的查询条件,只想选着其中的一个,查询条件有一个满足即可,类似于java中的switch case default选择语句。在MyBatis中,我们使用choose标签可以解决此类问题
<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
select * from T_STUDENT
<where>
<choose>
<when test="Sno != null and Sno != ''">
and S_NO = #{Sno}
</when>
<when test="Sname != null and Sname != ''">
and S_NAME = #{Sname}
</when>
<otherwise>
and S_AGE = #{Sage}
</otherwise>
</choose>
</where>
</select>
也就是说,这里我们列出的三个查询条件:Sno、Sname、Sage,我们只能选择一个作为查询条件
(1)如果Sno不为空,则SQL语句为:select * select * from T_STUDENT where S_NO = Sno
(2)如果Sno为空,那么接下来看Sname是否为空,如果不为空,则SQL语句为:select * select * from T_STUDENT where S_NAME = Sname
(3)如果Sname为空,那么查询语句为:select * select * from T_STUDENT where S_AGE = Sage
5.动态SQL:trim语句
trim标记是一个格式化的标记,可以完成set或者是where标记的功能
(1)用trim改写上面第二点的if+where语句
<!-- <select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
select * from T_STUDENT
<where>
<if test="Sname != null and Sname != '' ">
and S_NAME = #{Sname}
</if>
<if test="Sage != null and Sage != '' ">
and S_AGE = #{Sage}
</if>
</where>
</select> -->
<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
select * from T_STUDENT
<trim prefix="where" prefixOverrides="and || or">
<if test="Sname != null and Sname != '' ">
and S_NAME = #{Sname}
</if>
<if test="Sage != null and Sage != '' ">
and S_AGE = #{Sage}
</if>
</trim>
</select>
注意:
prefix:前缀加一个where
prefixoverride:去掉where后紧接着的第一个and或者是or
弊端:当Sname和Sage都为空的时候,使用trim就成了select * from T_STUDENT where,所有一般不建议使用,在特殊情况下才使用该标签
(2)用 trim 改写上面第三点的 if+set 语句
<!-- <update id="updateStudentBySnameAndSage" parameterType="com.cyn.po.Student">
update T_STUDENT set
<if test="Sname != null and Sname != '' ">
S_NAME = #{Sname},
</if>
<if test="Sage != null and Sage != '' ">
S_AGE = #{Sage},
</if>
<if test="Sdept != null and Sdept != '' ">
S_DEPT = #{Sdept}
</if>
where S_NO = #{S_no}
</update> -->
<update id="updateStudentBySnameAndSage" parameterType="com.cyn.po.Student">
update T_STUDENT
<trim prefix="set" suffixOverrides=",">
<if test="Sname != null and Sname != '' ">
S_NAME = #{Sname},
</if>
<if test="Sage != null and Sage != '' ">
S_AGE = #{Sage},
</if>
<if test="Sdept != null and Sdept != '' ">
S_DEPT = #{Sdept},
</if>
</trim>
where S_NO = #{Sno}
</update>
注意:
prefix:前缀加一个set
suffixoverride:去掉最后一个逗号(也可以是其他的标记,就像是上面前缀中的and一样)
6.动态SQL:foreach语句
当我们需要查询表中学号为:95001、92002、95003的学生信息,
SQL语句:select * from T_STUDENT where S_NO in (95001,95002,95003)
(1)设置传递的参数为List类型的snoList:
List<Integer> snoList = new ArrayList<>();
snoList.add(95001);
snoList.add(95002);
snoList.add(95003);
(2)使用foreach来改写select * from T_STUDENT where S_NO in (95001,95002,95003)
<select id="selectStudentBySnameAndSage" parameterType="List" resultType="com.cyn.po.Student">
select * from T_STUDENT
<where>
<foreach collection="list" item="Sno" open="and S_NO in (" close=")" separator=",">
#{Sno}
</foreach>
</where>
</select>
注意: foreach的作用是循环遍历,通常用来遍历集合比如数组、List和Set等。
collection:指定输入对象中的集合属性,当遍历数组时候,只能写array!!!,参数类型为List!!!
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
7.动态SQL:include语句
有时候可能某个sql语句我们用的特别多,为了增加代码的重用性,简化代码,我们需要将这些代码抽取出来,然后使用时直接调用。
比如:假如我们需要经常根据学生姓名和性别来进行联合查询,那么我们就把这个代码抽取出来,如下:
(1)首先定义一个复用的SQL片段
<sql id="selectStudentBySnameAndSageSQL">
<if test="Sname != null and Sname != '' ">
and S_NAME = #{Sname}
</if>
<if test="Sage != null and Sage != '' ">
and S_AGE = #{Sage}
</if>
</sql>
(2)引用SQL片段
<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
select * from T_STUDENT
<where>
<!-- 引用sql片段,如果refid 指定的不在本文件中,那么需要在前面加上 namespace -->
<include refid="selectStudentBySnameAndSageSQL"></include>
<!-- 在这里还可以引用其他的 sql 片段 -->
</where>
</select>
注意:最好基于单表来定义sql片段,提高片段的可重用性;在sql片段中不要包括where
8.动态SQL:bind语句
作用是通过OGNL表达式去自定义一个上下文变量,多次使用一次定义,方便开发效率
!
<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
<bind name="pattern_name" value=" '%' + Sname + '%'"/>
<bind name="pattern_age" value=" '%' + Sage + '%'"/>
select * from T_STUDENT where S_NAME like #{pattern_name} and S_AGE like #{pattern_age}
</select>
注意:这里的Sname和Sage就是传递进来的参数,它和通配符连接后,赋值给了pattern_name和pattern_age,然后我们就可以在select语句中使用这个变量进行模糊查询,这个变量在上下文中均可使用。
三、心得
为了防止SQL语句拼接出错,我们应该先写出原生SQL语句->然后再根据MyBatis的基本语法生成动态SQL,所以我们最好自己写一个MyBatis通用增删改查的动态SQL语法框架,能够让你很快去对照着改出来,这个通用格式可以根据上面的总结出来)
GO