提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
mybatis学习记录
09. MyBatis 动态SQL(常用 1,2,5)
提示:以下是本篇文章正文内容,下面案例可供参考
动态SQL(常用 1,2,5)
1,2,3,6点的测试方法和mapper接口都是一样的
测试方法:(DynamicSQLMapperTest)
@Test
public void testGetEmpByCondition(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Emp> list = mapper.getEmpByCondition(new Emp(null,"吉安",null,"男",""));
System.out.println(list);
}
DynamicSQLMapper接口:
public interface DynamicSQLMapper {
List<Emp> getEmpByCondition(Emp emp);
}
1.if
i f \color{red}{if} if :根据标签中test属性所对应的表达式决定标签中的内容是否需要拼接到SQL语句中
映射文件:(DynamicSQLMapper.xml)
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp where 1=1
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="email != null and email != ''">
and email = #{email}
</if>
</select>
2.where
w
h
e
r
e
\color{red}{where}
where :当where标签中有内容时,会生成where关键字,并且将内容前多余的and或or去掉;
当where标签中没有内容时,不会生成where关键字
注意:不能将内容后面多余的and或or去掉
映射文件:
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<where>
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="email != null and email != ''">
and email = #{email}
</if>
</where>
</select>
3.trim
trim:用于去掉或添加标签中的内容
常用属性:(标签内有内容时生效,没有内容时trim标签也不生效)
prefix:在trim标签中的内容的前面添加某些内容
prefixOverrides:在trim标签中的内容的前面去掉某些内容
suffix:在trim标签中的内容的后面添加某些内容
suffixOverrides:在trim标签中的内容的后面去掉某些内容
映射文件:
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<trim prefix="where" suffixOverrides="and|or">
<if test="empName != null and empName != ''">
emp_name = #{empName} and
</if>
<if test="age != null and age != ''">
age = #{age} and
</if>
<if test="sex != null and sex != ''">
sex = #{sex} and
</if>
<if test="email != null and email != ''">
email = #{email}
</if>
</trim>
</select>
4.choose、when、otherwise
相当于if…else if…else;when至少有一个,otherwise最多只能有一个
测试方法:
@Test
public void testGetEmpByChoose(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Emp> list = mapper.getEmpByChoose(new Emp(null,"吉安 ",null,"男",""));
System.out.println(list);
}
mapper接口:
List<Emp> getEmpByChoose(Emp emp);
映射文件:
<!--List<Emp> getEmpByChoose(Emp emp);-->
<select id="getEmpByChoose" resultType="Emp">
select * from t_emp
<where>
<choose>
<when test="empName != null and empName != ''">
emp_name = #{empName}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
<when test="sex != null and sex != ''">
sex = #{sex}
</when>
<when test="email != null and email != ''">
email = #{email}
</when>
<otherwise>
did = 1
</otherwise>
</choose>
</where>
</select>
5.foreach
f
o
r
e
a
c
h
\color{red}{foreach}
foreach :可实现批量删除或添加
属性:
collection:设置要循环的数组或集合
item:表示集合或数组中的每一个数据
separator:设置循环体之间的分隔符
open:设置foreach标签中的内容的开始符
close:设置foreach标签中的内容的结束符
- a>通过数组实现批量删除
测试方法:
@Test
public void testDeleteMoreByArray(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
int result = mapper.deleteMoreByArray(new Integer[]{7, 8, 9});
System.out.println(result);
}
mapper接口:
int deleteMoreByArray(@Param("eids") Integer[] eids);
映射文件:
第一种方式:where eid in ( ? , ? , ? )
<!--int deleteMoreByArray(@Param("eids") Integer[] eids);-->
<delete id="deleteMoreByArray">
delete from t_emp where eid in
<foreach collection="eids" item="eid" separator="," open="(" close=")">
#{eid}
</foreach>
</delete>
第二种方式:where eid = ? or eid = ? or eid = ?
<!--int deleteMoreByArray(@Param("eids") Integer[] eids);-->
<delete id="deleteMoreByArray">
delete from t_emp where
<foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
</delete>
- b>通过list集合实现批量添加
测试方法:
@Test
public void testInsertMoreByList(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp1 = new Emp(null,"a",23,"男","22555633@qq.com");
Emp emp2 = new Emp(null, "c", 15, "女", "1546789@qq.com");
Emp emp3 = new Emp(null, "f", 20, "女", "5891239@qq.com");
List<Emp> emps = Arrays.asList(emp1,emp2,emp3);
int result = mapper.insertMoreByList(emps);
System.out.println(result);
}
mapper接口:
int insertMoreByList(@Param("emps") List<Emp> emps);
映射文件:
<!--int insertMoreByList(@Param("emps") List<Emp> emps);-->
<insert id="insertMoreByList">
insert into t_emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
</foreach>
</insert>
6.sql标签
定义:<sql id="empColumns">eid,emp_name,age,sex,email</sql>
引用:<include refid="empColumns"></include>
(测试方法和mapper接口同1)
映射文件:
<!--sql标签定义-->
<sql id="empColumns">eid,emp_name,age,sex,email</sql>
<!--sql标签使用,以where为例-->
<select id="getEmpByConditionTwo" resultType="Emp">
select <include refid="empColumns"></include> from t_emp
<where>
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="email != null and email != ''">
and email = #{email}
</if>
</where>
</select>