MyBatis的动态SQL操作

MyBatis

动态SQL操作

if语句

修改配置文件deptMapper.xml，添加

<!-- 动态IF条件 -->
	<select id="selectListUseIf" parameterType="Dept"  resultMap="deptResultMap">
		select * from dept where 1=1 
		<if test="deptId!=null">
			and dept_id=#{deptId} 
		</if>
		<if test="deptName!=null">
			and dept_name=#{deptName} 
		</if>
		<if test="deptAddress!=null">
			and dept_address=#{deptAddress}

		</if>

修改DeptDaoImpl.java，添加selectListUseIf方法：

//根据参数使用配置文件的IF语句自动填充查询的过滤条件
	public List<Dept> selectListUseIf(Dept dept){
		List<Dept> depts=null;
		try {
			session=MyBatisUtil.getSession();
			depts=session.selectList("cn.itcast.entity.DeptMapper.selectListUseIf",dept);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSession();
		}
		return depts;
        }

where语句

修改配置文件deptMapper.xml，添加

<!-- 动态Where条件 ,一般也需要与if结合使用，与纯if比较，省略了where 1=1-->
	<select id="selectListUseWhere" parameterType="Dept"  resultMap="deptResultMap">
		select * from dept
		<where>
			<if test="deptId!=null">
				and dept_id=#{deptId}
			</if>
			<if test="deptName!=null">
				and dept_name=#{deptName}
			</if>
			<if test="deptAddress!=null">
				and dept_address=#{deptAddress}
			</if>
		</where>
        </select>

choose(when , otherwise)语句

修改配置文件deptMapper.xml，添加

	<select id="selectListUseChoose" parameterType="Dept" resultMap="deptResultMap">
		select * from dept where 1=1
		<choose>
			<when test="deptId!=null">and dept_id=#{deptId}</when>
			<when test="deptName!=null">and dept_name=#{deptName}</when>
			<when test="deptAddress!=null">and dept_address=#{deptAddress}</when>
			<otherwise>and !1 = 1</otherwise>
		</choose>
        </select>

set语句

修改配置文件deptMapper.xml,添加：

	<!--动态set语句可以用来更新数据 -->
	<update id="updateUseSet" parameterType="Dept">
		update dept
		<set>
			<if test="deptName!=null">dept_name=#{deptName},</if>
			<if test="deptAddress!=null">dept_address=#{deptAddress},</if>
		</set>
		where dept_id=#{deptId}
        </update>

foreach语句

修改配置文件deptMapper.xml,添加：

<!-- 定义根据多个部门ID查询部门相关部门信息的SQL语句 ,resultMap的值是指集合里元素的类型,parameterType不用指定 -->
<select id="selectListUseForeach"  resultMap="deptResultMap">
		select * from dept where dept_id in
		<!-- collection＝"array或list",array用来对应参数为数组，list对应参数为 集合 -->
		<foreach collection="array" item="deptId" open="(" separator="," close=")">
			#{deptId}
		</foreach>
</select>

include语句

修改配置文件deptMapper.xml,添加：

<!-- 使用include语句动态插入表的字段及对应的值 -->
	<sql id="key">
		<!--suffixOverrides="," 可以忽略最后“，”号 -->
		<trim suffixOverrides=",">

			<if test="deptName!=null">
				dept_name,
			</if>
			<if test="deptAddress!=null">
				dept_address,
			</if>
		</trim>
	</sql>
	<sql id="value">
		<trim suffixOverrides="," >
				<if test="deptName!=null">
				#{deptName},
			</if>
			<if test="deptAddress!=null">
				#{deptAddress},
			</if>
		</trim>
	</sql>
	
	<insert id="insertUseInclude" parameterType="Dept">
		insert into dept(
		<include refid="key" />
		) values(
		<include refid="value"/>
		)
</insert>