mybatis系列四:动态SQL

       动态SQL是MyBatis的—个强大的特性。在使用JDBC操作数据时，如果查询条件特别多，将条件串联成SQL字符串是一项痛苦的事情，通常的解决方法是写很多的if-else条件语句和字符串进行拼接，并确保不能忘了空格或在字段的最后省略逗号。MyBatis使用一种强大的动态SQL语言来改进这种情形，动态SQL基于OGNL的表达式，可使我们方便地在SQL语句中实现某些逻辑。

一  常用标签

案例1   使用动态sql进行查询

sql语句：

<select id="selectByCon" 
	  parameterType="com.obtk.entitys.ConditionEntity" 
	        resultType="StudentEntity">
		select * from student  where  1=1
	
			<if test="gender!=null">
			   and gender=#{gender}
			</if>
			<if test="stuName!=null">
			   and stuName like CONCAT('%',#{theName},'%')
			</if>
			<if test="minAge!=null">
			   and age>=#{minAge}
			</if>
			<if test="maxAge!=null">
			   and age<![CDATA[<=]]>#{maxAge}
			</if>
			<if test="address!=null">
			   and address=#{address}
			</if>
	
	</select>

代码：

package com.obtk.test2;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.ConditionEntity;
import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestConQuery {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4.得到session
			session=MybatisUtil.getSession();
			ConditionEntity con=new ConditionEntity();
			con.setMinAge(20);
			con.setMaxAge(44);
			con.setGender("男");
			//5.执行语句
			List<StudentEntity> stuList=session.
			         selectList("stu.selectByCon",con);
			for(StudentEntity stu : stuList){
				System.out.println(stu.getStuName()+","+stu.getGender()
						+","+stu.getAge());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

或者使用where标签，如下：

<select id="selectByCon" 
	  parameterType="com.obtk.entitys.ConditionEntity" 
	        resultType="StudentEntity">
		select * from student
		<where>
			<if test="gender!=null">
			   and gender=#{gender}
			</if>
			<if test="stuName!=null">
			   and stuName like CONCAT('%',#{theName},'%')
			</if>
			<if test="minAge!=null">
			   and age>=#{minAge}
			</if>
			<if test="maxAge!=null">
			   and age<![CDATA[<=]]>#{maxAge}
			</if>
			<if test="address!=null">
			   and address=#{address}
			</if>
		</where>
	</select>

代码同上。

      where元素的作用是在写入where元素的地方输出一个where。另外，MyBatis会智能处理输出条件，如果所有条件都不满足，那么MyBatis会查出所有的记录；如果输出后是以and开头的，MyBatis会把第一个and忽略。因此不用写1=1这样的条件了。

案例2    使用动态sql进行更新操作

sql语句

<update id="updateByCon" parameterType="StudentEntity" statementType="PREPARED">
		update student 
		<set>
			<if test="stuName!=null">stuName=#{stuName},</if>
			<if test="gender!=null">gender=#{gender},</if>
			<if test="age!=null">age=#{age},</if>
			<if test="address!=null">address=#{address},</if>
			<if test="deptIdd!=null">deptIdd=#{deptIdd},</if>
		</set>
		where stuId=#{stuId}
	</update>

代码：

package com.obtk.test2;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestUpdateByCon {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4.得到session
			session=MybatisUtil.getSession();
			StudentEntity theStu=new StudentEntity("小绿花", "女", 23, "火星");
			theStu.setStuId(129);
			//5.执行语句,参数比较多，就用实体类的对象传参
			int theId=session.update("stu.updateByCon", theStu);
			session.commit();
			System.out.println("修改成功!"+theId);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

二  trim标记的使用

trim标记是一个格式化的标记，可以完成set或者是where标记的功能，如下代码：
示例1. 

<select id="queryByCon" parameterType="stuEntity" resultMap="stuMap">
		select * from student
		<trim prefix="where" prefixOverrides="and|or">
			<if test="studentName!=null">
				and stuName like concat('%',#{studentName},'%')
			</if>
			<if test="sex!=null">
				and gender=#{sex}
			</if>
			<if test="age!=null">
				and age>=#{age}
			</if>
		</trim>
</select>

上面两个属性的意思如下：
　　prefix：前缀　　　　　　
　　prefixoverride：去掉第一个and或者是or

示例2.

<update id="updateByCon" parameterType="stuEntity" >
		update student 
		<trim prefix="set" suffixOverrides="," suffix="where stuId=#{stuId}">
			<if test="studentName!=null">
				stuName=#{studentName},
			</if>
			<if test="sex!=null">
				gender=#{sex},
			</if>
			<if test="age!=null">
				age=#{age},
			</if>
			<if test="address!=null">
				address=#{address},
			</if>
			<if test="deptIdd!=null">
				deptIdd=#{deptIdd},
			</if>
	   </trim>
	</update>

 上面三个属性的意义如下，其中prefix意义：自动在前面加上set标签
　　suffixoverride：去掉最后一个逗号（也可以是其他的标记，
                    就像是上面前缀中的and一样）
　　suffix：后缀,这里是自动在sql语句的后面加上where stuId=#{stuId}

 

三  mybatis动态sql中foreach标签的使用

foreach标签主要用于构建in条件，他可以在sql中对集合进行迭代。如下：

<delete id="deleteBatch"> 
　　　　delete from user where id in
　　　　<foreach collection="array" item="id"  open="(" close=")" separator=",">
　　　　　　#{id}
　　　　</foreach>
　　</delete>

我们假如说参数为----  int[] ids = {1,2,3,4,5}  ----那么打印之后的SQL如下：
　　delete form user where id in (1,2,3,4,5)
　　释义：
　　　　collection ：collection属性的值有三个分别是list、array、map三种，分别对应的参数类型为：List、数组、map集合，我在上面传的参数为数组，所以值为array
　　　　item ： 表示在迭代过程中每一个元素的别名
　　　　index ：表示在迭代过程中每次迭代到的位置（下标）
　　　　open ：前缀
　　　　close ：后缀
　　　　separator ：分隔符，表示迭代时每个元素之间以什么分隔
我们通常可以将之用到批量删除、添加和 in 查询字句 等操作中。

具体案例：

<select id="queryForEach1" resultMap="stuMap">
		select * from student where stuId in 
		<foreach collection="array" item="theId" open="(" separator="," close=")">
			#{theId}
		</foreach>
	</select>
	<select id="queryForEach2" resultMap="stuMap">
		select * from student where stuId in 
		<foreach collection="list" item="theId" open="(" separator="," close=")">
			#{theId}
		</foreach>
	</select>

代码

package com.obtk.test;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class EachQuery {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			session=MybatisUtil.getSession();
			//int[] ids=new int[]{100,105,109,110};
			ArrayList idList=new ArrayList();
			idList.add(100);
			idList.add(105);
			idList.add(109);
			idList.add(110);
			//List<StudentEntity> stuList=session.selectList("stu.queryForEach1",ids);
			List<StudentEntity> stuList=session.selectList("stu.queryForEach2",idList);
			for(StudentEntity stu : stuList){
				System.out.println(stu.getStuId()+"\t"+stu.getStudentName()+"\t"+stu.getSex());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}