动态SQL:
Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能，
它存在的意义是："为了解决拼接SQL语句字符串时的痛点问题"。
一、If
-
if标签可通过test属性（即传递过来的数据）的表达式进行判断，若表达式的结果为true，则标签中的内容会执行；反之标签中的内容不会执行
-
在where后面添加一个恒成立条件1=1
-
这个恒成立条件并不会影响查询的结果
-
这个1=1可以用来拼接and语句，例如：当empName为null时
-
如果不加上恒成立条件，则SQL语句为select * from t_emp where and age = ? and sex = ? and email = ?，此时where会与and连用，SQL语句会报错
-
如果加上一个恒成立条件，则SQL语句为select * from t_emp where 1= 1 and age = ? and sex = ? and email = ?，此时不报错
public interface DynamicMapper {
/**
* 动态sql，if语句
* @param emp
* @return
*/
List<Emp> getEmpByCondition(Emp emp);
}
<!--List<Emp> getEmpByCondition(Emp emp);-->
<!-- and == && ，所以条件都要满足 -->
<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>
@Test
public void testGetEmpByCondition() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
List<Emp> empByCondition = mapper.getEmpByCondition(new Emp(null,"王五",34,"女","423423"));
System.err.println(empByCondition);
sqlSession.close();
// empName='王五', age=34, sex='女', email='423423'
}
二、where
-
where和if一般结合使用：
-
若where标签中的if条件都不满足，则where标签没有任何功能，即不会添加where关键字
-
若where标签中的if条件满足，则where标签会自动添加where关键字，并将条件最 "前方" 多余的and/or去掉
/**
* 动态sql，where
* @param emp
* @return
*/
List<Emp> getEmpByCondition_where(Emp emp);
<!--List<Emp> getEmpByCondition_where(Emp emp);-->
<select id="getEmpByCondition_where" 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>
@Test //where
public void testGetEmpByCondition_were() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
List<Emp> empByCondition = mapper.getEmpByCondition_where(new Emp(null, "王五", 34, "女", "423423"));
System.err.println(empByCondition);
sqlSession.close();
// empName='王五', age=34, sex='女', email='423423'
}
错误示范：where标签不能去掉 "条件后" 多余的and/or
<!--这种用法是错误的，只能去掉条件前面的and/or，条件后面的不行-->
<if test="empName != null and empName !=''">
emp_name = #{empName} and
</if>
<if test="age != null and age !=''">
age = #{age}
</if>
三、trim
-
trim用于去掉或添加标签中的内容
-
常用属性
-
prefix：在trim标签中的内容的 {"前面添加"} - 某些内容
-
suffix：在trim标签中的内容的{"后面添加"} 某些内容
-
prefixOverrides：在trim标签中的内容的 {"前面去掉某"} 些内容
-
suffixOverrides：在trim标签中的内容的 {"后面去掉某"} 些内容
-
若trim中的标签都不满足条件，则trim标签没有任何效果，也就是只剩下select * from t_emp
/**
* 动态sql，trim
* @param emp
* @return
*/
List<Emp> getEmpByCondition_trim(Emp emp);
<!--List<Emp> getEmpByCondition_trim(Emp emp);-->
<select id="getEmpByCondition_trim" 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} or
</if>
<if test="email != null and email !=''">
email = #{email}
</if>
</trim>
</select>
@Test //trim
public void testGetEmpByCondition_trim() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
List<Emp> empByCondition = mapper.getEmpByCondition_trim(new Emp(null, "王五", 34, "女", "423423"));
System.err.println(empByCondition);
sqlSession.close();
// empName='王五', age=34, sex='女', email='423423'
}
四、choose、when、otherwise
-
"choose、when、otherwise" 相当于 "if...else if..else"
-
when至少要有一个，otherwise至多只有一个
/**
* 动态sql，choose、when、otherwise
* @param emp
* @return
*/
List<Emp> getEmpByCondition_choose(Emp emp);
<!-- List<Emp> getEmpByCondition_choose(Emp emp);-->
<select id="getEmpByCondition_choose" resultType="Emp">
select * from t_emp
<where>
<choose>
<!--当一个when执行了一个，剩下就不会执行。因为他们是if...else if..else-->
<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>
@Test //choose
public void testGetEmpByCondition_choose() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
List<Emp> empByCondition = mapper.getEmpByCondition_choose(new Emp(null, "王五", 34, "女", "423423"));
System.err.println(empByCondition);
sqlSession.close();
// empName='王五', age=34, sex='女', email='423423'
}
解析：
五、foreach
-
属性：
-
collection：设置要循环的数组或集合
-
item：表示集合或数组中的每一个数据
-
separator：设置循环体之间的分隔符，分隔符前后默认有一个空格，如,
-
open：设置foreach标签中的内容的开始符
-
close：设置foreach标签中的内容的结束符
-
5.1-批量删除：
/**
* 动态sql,"foreach-实现批量删除"
* @param eids
* @return
*/
Integer deleteMoreByArray(@Param("eids") Integer[] eids);
// 删除的SQL：DELETE FROM t_emp where eid IN (9,10)
<!--Integer deleteMoreByArray(@Param("eids") Integer[] eids);-->
<!--SQL：DELETE FROM t_emp where eid IN (9,10)-->
<delete id="deleteMoreByArray">
delete from t_emp where eid in
<foreach collection="eids" item="eid" separator="," open="(" close=")">
#{eid}
</foreach>
</delete>
@Test //foreach-批量删除
public void testDeleteMoreByArray() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
Integer integer = mapper.deleteMoreByArray(new Integer[]{7, 8});
System.err.println("受影响的行数：" + integer);
sqlSession.close();
}
/**
* 动态sql,"foreach-实现批量增加"
* @param emps
* @return
*/
Integer insertMoreByList(@Param("emps") List<Emp> emps);
//批量添加的SQL语句：insert into t_emp values (null,"a",23,"男","321412@111",NULL),(null,"a1",23,"男","321412@111",NULL)
<!-- Integer 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>
@Test
public void insertMoreByList() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
Emp emp1 = new Emp(null, "a", 13, "人妖", "2412343@qq.com");
Emp emp2 = new Emp(null, "b", 1, "男", "123@321.com");
Emp emp3 = new Emp(null, "c", 1, "男", "123@321.com");
List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
int result = mapper.insertMoreByList(emps);
System.out.println(result);
}
六、SQL片段：
-
ql片段，可以记录一段公共sql片段，在使用的地方通过include标签进行引入
-
声明sql片段：标签
<sql id="empColumns">eid,emp_name,age,sex,email</sql>
<sql id="empColumns">eid,emp_name,age,sex,email</sql><!--sql片段-->
<!--List<Emp> getEmpByCondition(Emp emp);-->
<!-- and == && ，所以条件都要满足 -->
<select id="getEmpByCondition_if" resultType="Emp">
select <include refid="empColumns"></include> 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>
