mybatis 动态sql

mybtais的动态sql,借鉴了OGNL表达式,如果接触过EL表达式,那么恭喜你,这个表达式,将会更加的容易学会,

OGNL表达式

if 判断标签 , 其中有一个 test 属性 我们可以在这里判断我们需要操作的参数

例如:

<select id="selectStudent" resultType="student1">
		select * from student1
                <where>
                        <if test="sId!=null">
				and s_id=#{sId}
			</if>
			<if test="sAge!=null">
				and s_Age = #{sAge}
			</if>
			<if test="sName!=null && sName.trim()!="" ">
				and s_Name like #{sName}
			</if>
			<if test="cClassId!=null and cClassId!=''">
				and c_classId = #{cClassId}
			</if>
		</where>
	</select>

这里我进行了一系列的判断,并且也使用了转义字符,

<if test="sName!=null && sName.trim()!="" ">

在这里还可以对值进行操作,我对字符进行了去除两端的空格

其中 where 代替了 select * from where 中的where,它可以帮我们不使用and 或者 or,并且可以截取掉当前需要

拼接语句中已经使用的and 或者 or,

<if test="cClassId!=null and cClassId!=''">
	and c_classId = #{cClassId}   ---> 我们拼接的语句
</if>

在这里不使用 and 也是成立的,但是因为习惯还是写上了

如果我们换一种奇葩的方式写呢?

<if test="cClassId!=null and cClassId!=''">
	c_classId = #{cClassId} and   ---> 我们拼接的语句
</if>

这时 where 标签并不能将我们的 and 去掉 , 但是我们可以使用 新的一个标签 trim

trim 标签 自定义字符串截取

prefix : 字符串截取

prefixOverrides : 字符串前覆盖

suffix : 字符串截取

suffixOverrides : 字符串后覆盖

<!-- 自定义字符串截取规则 -->
	<select id="selectStudentTrim" resultType="student1">
		select * from student1
		<trim prefix="where" suffixOverrides="and"> <!-- 拼接后的字符串,在前面添加上 where 去掉最后的 and  -->
			<if test="sId!=null">
				s_id=#{sId} and
			</if>
			<if test="sAge!=null">
				s_Age = #{sAge} and
			</if>
			<if test="sName!=null && sName.trim()!="" ">
				s_Name like #{sName} and
			</if>
			<if test="cClassId!=null and cClassId!=''">
				c_ClassId = #{cClassId} and
			</if>
		</trim>
	</select>

除了,if语句,我们还可以使用 choose 标签 ,这种类似于java种的switch case  default语句

<!-- Choose -->
	<select id="selectStudentChoose" resultType="student1">
		select * from student1
		<where>
			<choose>
				<when test="sId!=null">  <!-- 箱单与 case  -->
					s_id=#{sId}
				</when>
				<when test="sAge!=null">
					s_Age = #{sAge}
				</when>
				<when test="sName!=null && sName.trim()!=""">
					s_Name like #{sName}
				</when>
				<when test="cClassId!=null and cClassId!=''">
					c_classId = #{cClassId}
				</when>
				<otherwise>  <!-- 类似于 default -->
					1=1
				</otherwise>
			</choose>
		</where>
	</select>

可以还可以利用这些标签做更多灵活的操作

结合  set  和  if  或者 trim 和 if 做我们的修改

<!-- update  set 和 if 结合更新 -->
	<!-- 错误s_Age = #{sAge}, -->
	<update id="updateStudentSetAndIf">
		<!-- 第一个版本 -->
		<!-- update student1
		<set>
			<if test="sAge!=null">
				s_Age = #{sAge}
			</if>
			<if test="sName!=null && sName.trim()!="" ">
				s_Name = #{sName}
			</if>
			<if test="cClassId!=null and cClassId!=''">
				c_ClassId = #{cClassId}
			</if>
		</set>
		where s_id=#{sId} -->
		update student1
		<!-- 第二个版本 -->
		<trim prefix="set" suffixOverrides=",">
			<if test="sAge!=null">
				s_Age = #{sAge},
			</if>
			<if test="sName!=null && sName.trim()!="" ">
				s_Name = #{sName},
			</if>
			<if test="cClassId!=null and cClassId!=''">
				c_ClassId = #{cClassId}
			</if>
		</trim>
		where s_id=#{sId}
	</update>

OGNL表达式,如果我们需要进行批量新增 , 也是支持的,我们可以使用他自带的 foreach 标签

MySQL 和 Oracle 的批量是不一样的

foreach 中

separator  :  每次循环后以  ,  号拼接起来

collection  :  需要循环的集合,与javaBean中的变量名一致

item  :  自定义的循环变量的名称

open  :  foreach中的语句以什么开头

close  :  foreach中的语句以什么结尾

mysql 支持

<!-- mysql批量新增 -->
	<!-- foreach遍历 mysql支持 values(),(),() -->
	<insert id="insertBacthStudentByMySql1">
		insert into student1(s_id,s_age,s_name,c_classid) values
		<foreach collection="list" item="student" separator=","
				open="(" close=")">
			#{student.sId},#{student.sAge},#{student.sName},#{student.cClassId}
		</foreach>
	</insert>
	
        <!-- jdbc.url = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true -->
	<!-- 这种方式需要 设置数据库连接属性 allowMultiQueries == true
		也可以使用在别的批量操作
	-->
	<insert id="insertBacthStudentByMySql2">
		<foreach collection="list" item="student" separator=";">
			insert into student1(s_id,s_age,s_name,c_classid) values(
				#{student.sId},#{student.sAge},#{student.sName},#{student.cClassId}
			)
		</foreach>
	</insert>

Oracle支持

<!-- oracle批量新增 不支持values(),()不支持 -->
	<!--  oracle 支持这种批量操作 三种方式
		1.种
		begin 
			insert xxx values(xxx);
			insert xxx values(xxx);
		end;
		2.利用中间表:   <!-- 比较麻烦 -->
		insert into student(xxxx,xxxx,xxxx)
			select xxxx,xxxx,xxxx from(
				select 值,值,值 from student;
				union
				select 值,值,值 from student;
				union
				select 值,值,值 from student;
				union
			)
	 -->
	<insert id="insertBacthStudentByOracle1">
			<foreach collection="list" item="student"
				open="begin" close="end;">
				insert into student1(s_id,s_age,s_name,c_classid) values(
				#{student.sId},#{student.sAge},#{student.sName},#{student.cClassId});
			</foreach>
	</insert>

两个内置参数

_Paramter  :  传过来的参数,如果是单个参数那么_Paramter就是单个参数,如果是多个参数那么_Paramter就是Map

_databaseId  :  数据库的名称,如果在mybatis-config.xml文件中设置了 数据库的别名那么 _databaseId也就是数据库的别名

<databaseIdProvider type="DB_VENDOR">
  	<property name="MySQL" value="mysql"/>
  	<property name="Oracle" value="oracle"/>
  	<property name="SQL Server" value="sqlserver"/>
  </databaseIdProvider>

例子

	 <select id="selectParamterAndDataBaseIdProvider" resultType="student1">
	 	<bind name="_sName" value="'%'+sName+'%'"/>
	 	<if test="_databaseId=='mysql'">
	 		select * from student2
	 		<if test="_parameter!=null">
	 			where s_name like #{_sName}
	 		</if>
	 	</if>
	 	<if test="_databaseId=='oracle'">
	 		select * from student1
	 		<if test="_parameter!=null">
	 			where s_name like #{_sName}
	 		</if>
	 	</if>
	 </select>

在这里我们使用了  一个 bind 标签

bind  :  将一个传过来的值,绑定到一个变量上,方便引用

name : 自定义的变量名称

value : 设置的后的值

<select id="selectParamterAndDataBaseIdProvider" resultType="student1">
	 	<bind name="_sName" value="'%'+sName+'%'"/>
	 	<if test="_databaseId=='mysql'">
	 		select * from student2
	 		<if test="_parameter!=null">
	 			where s_name like #{_sName}

当sql需要重用的话,我们也可以抽取出重复的sql

例子

 <!-- 抽取和重用的sql片段 
	 	1.经常查询,或者插入的列名抽取出来
	 	2.include来引用已经抽取的sql
	 	3.include可以自定义有些property
	 -->
	 <sql id="insertColumn">
	 <!-- xxxx 需要操作的列 -->
	 <!-- 也可以动态创建sql -->
	 	<if test="_databaseId=='oracle'">
	 		<!-- xxxx 需要操作的列 ${asas}取值,不能用#{}-->
	 	</if>
	 	<if test="_databaseId=='mysql'">
	 		<!-- xxxx 需要操作的列 -->
	 	</if>
	 </sql>
	 <select id="xxx">
	 	select 
	 		<!-- 引用外部定义的SQL -->
	 		<include refid="insertColumn">
	 			<property name="asas" value="asas"/>
	 		</include>
	 	from
	 		student1
	 </select>