用于筛选条件的查询
例如找工作,首先筛选地区,再筛选工作种类,再筛选薪资......等等
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.mapper.EmployeeMapper">
<!-- 如果传入属性就判断相等,如果不传入属性就不加对应条件
where
if 判断传入参数,最终决定是否添加语句
test属性,内部做比较运算,最终true将标签内的sql语句进行拼接
false就不拼接标签内部语句
判断语句“key 比较符号 值 and | or key 比较符号 值"
大于号和小于号不推荐直接写符号 实体符号 > == > < == <
问题:
假如第一个不满足,第二个满足就会多出一个 AND sql语句会报错
假如都不满足 会 多出一个 where sql语句会报错================把where换成wehre标签
where标签 自动添加where,标签内任意if满足就会添加where关键字,不满足则去掉
自动去掉多余的 AND Or 关键字
where和if标签是嵌套使用的
-->
<select id="query" resultType="employee">
select * from t_emp
<where>
<if test="name != null">emp_name = #{name}</if>
<if test=" salary != null and salary > 100">and emp_salary = #{salary}</if>
</where>
</select>
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.mapper.EmployeeMapper">
<!--
根据员工的id更新员工数据,要求只有在name和salary不为空时才更新
问题: 如果只有第一个满足就多了个 ,
都不满足多个了 set
set标签: 1.自动去掉多余的 ,
2.自动添加set关键字
-->
<select id="query" resultType="employee">
select * from t_emp
<where>
<if test="name != null">emp_name = #{name}</if>
<if test=" salary != null and salary > 100">and emp_salary = #{salary}</if>
</where>
</select>
<update id="update">
update t_emp
<set>
<if test="empName != null">
emp_name = #{empName} ,
</if>
<if test="empSalary != null">
emp_salary = #{empSalary},
</if>
</set>
where emp_id = #{empId}
</update>
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.mapper.EmployeeMapper">
<select id="queryTrim" resultType="employee">
select * from t_emp
<trim prefix="where" prefixOverrides="and|or">
<if test="name != null">emp_name = #{name}</if>
<if test=" salary != null and salary > 100">and emp_salary = #{salary}</if>
</trim>
</select>
<update id="updateTrim">
update t_emp
<trim prefix="set" suffixOverrides=",">
<if test="empName != null">
emp_name = #{empName} ,
</if>
<if test="empSalary != null">
emp_salary = #{empSalary},
</if>
</trim>
where emp_id = #{empId}
</update>
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.mapper.EmployeeMapper">
<!-- //根据两个条件查询,如果姓名不为null使用姓名查询,如果姓名为空但是薪水不为空就使用薪水查询,都为空,查询全部-->
<!-- List<Employee> queryChoose(@Param("name") String name, @Param("salary")Double salray);-->
<select id="queryChoose" resultType="employee">
select * from t_emp
<choose>
<when test="name != null">
emp_name = #{name}
</when>
<when test="salary != null">
and emp_salary = #{salary}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</select>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.mapper.EmployeeMapper">
<select id="queryBatch" resultType="employee">
select * from t_emp
where emp_id in
<!--遍历的数据
collection="ids" 集合名称
open="" 遍历之前要追加的字符串
close="" 遍历结束要加的字符串
separator="" 每次遍历中间的分割,最后一次不会追加
item="" 获取每个遍历项
-->
<foreach collection="ids" open="(" separator="," close=")" item="id">
<!-- 遍历的内容 #{遍历item指定遍历的key,只能在标签内部引用}-->
#{id}
</foreach>
</select>
<delete id="deleteBatch">
delete from t_emp
where emp_id in
<foreach collection="ids" open="(" separator="," close=")" item="id">
#{id}
</foreach>
</delete>
<insert id="insert">
insert into t_emp (emp_name,emp_salary)
values
<foreach collection="list" separator="," item="employee">
(#{employee.empName},#{employee.empSalary})
</foreach>
</insert>
<!-- 如果一个标签设计多个语句,需要设置允许多个语句-->
<update id="updateBatch">
<foreach collection="list" item="employee">
update t_emp emp_name = #{empName} , emp_salary = #{empSalary}
where emp_id = #{empId}
</foreach>
</update>
</mapper>
相当于复制粘贴
<sql id="selectedSql">
select * from t_emp
</sql>
<include refid="selectedSql"></include>
推荐阅读: