【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,使代码变得更加灵活。