【Mybatis】动态SQL

查询条件不确定，需要根据情况产生SQL语法，这种情况叫动态SQL

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.qblank.entity.Student">
	<!-- 当表名和字段名不一样的时候可以设置resultMap进行映射 -->
	<resultMap type="student" id="studentMap">
		<id property="id" column="stu_id"/>
		<result property="name" column="stu_name"/>
		<result property="sal" column="stu_sal"/>
	</resultMap>
	
	<!-- 批量添加学生 -->
	<select id="addAll" parameterType="student" resultMap="studentMap">
		insert into t_students(stu_id,stu_name,stu_sal) values(#{id},#{name},#{sal})
	</select>
	
	<!-- 动态查询学生 -->
	<!-- mybatis会将查询出来的表记录和studentMap这个id所对应的映射结果相互匹配 -->
	<select id="dynafindAll"  parameterType="map" resultMap="studentMap">
		select stu_id,stu_name,stu_sal from t_students
		<where>
			<if test="pid!=null">
				and stu_id = #{pid}
			</if>
			<if test="pname!=null">
				and stu_name = #{pname}
			</if>
			<if test="psal!=null">
				and stu_sal = #{psal}
			</if>
		</where>
	</select>
	
	<!-- 动态更新 -->
	<update id="dynaUpdate" parameterType="map">
		update t_students
		<set>
			<if test="uname!=null">
				stu_name = #{uname},
			</if>
			<if test="usal!=null">
				stu_sal = #{usal},
			</if>
		</set>
		where stu_id = #{uid}
	</update>
	
	<!-- 动态删除(数组) -->
	<delete id="dynaDeleteArray" parameterType="int">
		delete from t_students where stu_id in
		<!-- foreach用于迭代数组元素
			 collection:表示容器
			 open表示开始符号
			 close表示结束符合
			 separator表示元素间的分隔符
			 item表示迭代的数组，属性值可以任意，但提倡与方法的数组名相同
			 #{ids}表示数组中的每个元素值
		 -->
		 <foreach collection="array" open="(" close=")" item="ids" separator=",">
		 	#{ids}
		 </foreach>
	</delete>
	<!-- 动态删除(集合) -->
	<delete id="dynaDeleteList" parameterType="int">
		delete from t_students where stu_id in
		<foreach collection="list" open="(" close=")" item="ids" separator=",">
			#{ids}
		</foreach>
	</delete>
	
	<!-- 动态添加 -->
	<insert id="dynaAdd" parameterType="student">
		insert into t_students(<include refid="key"/>) values(<include refid="value"/>)
	</insert>
	<!-- sql片段对应字段名,id属性值任意 -->
	<sql id="key">
		<!-- 去掉最后一个, -->
		<trim suffixOverrides=",">
			<if test="id!=null">
				stu_id,
			</if>
			<if test="name!=null">
				stu_name,
			</if>
			<if test="sal!=null">
				stu_sal,
			</if>
		</trim>
	</sql>
	<!-- sql片段对应字段名,id属性值任意 -->
	<sql id="value">
		<!-- 去掉最后一个, -->
		<trim suffixOverrides=",">
			<if test="id!=null">
				#{id},
			</if>
			<if test="name!=null">
				#{name},
			</if>
			<if test="sal!=null">
				#{sal},
			</if>
		</trim>
	</sql>
</mapper>

然后在dao层StudentDao封装相应的方法

/**
 * 动态添加
 * @param student
 */
public void dynaAdd(Student student){
	SqlSession sqlSession = null;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		int rows = sqlSession.insert(Student.class.getName() +".dynaAdd",student);
		System.out.println("更新了" + rows + "行");
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		sqlSession.rollback();
		throw e;
	}finally{
		MybatisUtil.closeSqlSession();
	}
}

/**
 * 动态更新
 * @param id
 * @param name
 * @param sal
 */
public void dynaUpdate(Integer id,String name,Double sal){
	SqlSession sqlSession = null;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		Map<String, Object> map = new LinkedHashMap<>();
		map.put("uid", id);
		map.put("uname", name);
		map.put("usal", sal);
		int rows = sqlSession.update(Student.class.getName() +".dynaUpdate",map);
		System.out.println("更新了" + rows + "行");
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		sqlSession.rollback();
		throw e;
	}finally{
		MybatisUtil.closeSqlSession();
	}
}

/**
 * 动态查询
 * @return
 */
public List<Student> dynafindAll(Integer id,String name,Double sal){
	SqlSession sqlSession = null;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		Map<String, Object> map = new LinkedHashMap<>();
		map.put("pid", id);
		map.put("pname", name);
		map.put("psal", sal);
		return sqlSession.selectList(Student.class.getName() +".dynafindAll",map);
	} catch (Exception e) {
		e.printStackTrace();
		throw e;
	}finally{
		MybatisUtil.closeSqlSession();
	}
}

/**
 * 动态删除(根据ID批量删除学生(数组版本))
 * @param id
 * @param name
 * @param sal
 */
public void dynaDeleteArray(int...ids){
	SqlSession sqlSession = null;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		int rows = sqlSession.update(Student.class.getName() +".dynaDeleteArray",ids);
		System.out.println("更新了" + rows + "行");
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		sqlSession.rollback();
		throw e;
	}finally{
		MybatisUtil.closeSqlSession();
	}
}
/**
 * 动态批量删除(集合)
 * @param ids
 */
public void dynaDeleteList(List<Integer> ids){
	SqlSession sqlSession = null;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		int rows = sqlSession.update(Student.class.getName() +".dynaDeleteList",ids);
		System.out.println("更新了" + rows + "行");
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		sqlSession.rollback();
		throw e;
	}finally{
		MybatisUtil.closeSqlSession();
	}
}

这样就实现了mybatis的动态SQL,使代码变得更加灵活。