动态SQL
- MyBatis的强大特性之一是它的动态SQL。使用JDBC或其它类似框架，根据不同条件拼接SQL语句是痛苦的。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号等着用的诸多语法细节。利用动态SQL这一特性可以彻底摆脱这种痛苦，让我们更加关注sql本身的逻辑语义。虽然在以前使用动态SQL并非一件易事，但正是MyBatis 提供了可以被用在任意SQL映射语句中的强大的动态SQL语言得以改进这种情形。动态SQL元素和JSTL或基于类似XML的文本处理器相似。在 MyBatis 之前的版本中，有很多元素
需要花时间了解。MyBatis3大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis采用功能强大的基于OGNL的表达式来淘汰其它大部分元素。
一、if语句
1.1 新增语句
- 新增语句中对入参进行判断。这样当插入的属性没有赋值的话，就会使用默认值，默认是空的，数据库中不会有记录
<!--if动态标签-->
<insert id="addPeopleIf" parameterType="com.intellif.mozping.entity.People">
insert into tb_people(
<if test="id != null">
id,
</if>
<if test="name != null">
name,
</if>
<if test="age != null">
age,
</if>
<if test="address != null">
address,
</if>
<if test="edu != null">
edu
</if>
)
values(
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="age != null">
#{age,jdbcType=INTEGER},
</if>
<if test="address != null">
#{address,jdbcType=VARCHAR},
</if>
<if test="edu != null">
#{edu,jdbcType=VARCHAR}
</if>
)
</insert>
1.2 查询语句
- 查询语句中对入参进行判断,这里原本有2个条件，经过判断之后可以输入任意一个条件或者都为null。
- 映射文件
<!--动态if标签查询-->
<select id="findByNameAndAddressIf" resultType="com.intellif.mozping.entity.People">
select *
from tb_people p
<where>
<if test="name != null and name != ''">
and p.name = #{name}
</if>
<if test="address != address and address != ''">
and p.address = #{address}
</if>
</where>
</select>
@Test
public void query() {
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressIf("Ma yun", null);
for (People p : byNameAndAddressIf) {
System.out.println(p);
}
}
二、choose + when + otherwise
- 如果不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis 提供了choose 元素，它有点像 Java 中的 switch语句
- 映射文件
<!--choose + when + otherwise-->
<select id="findByNameAndAddressChooseWhenOtherwise" resultType="com.intellif.mozping.entity.People">
select *
from tb_people p where
<choose>
<when test="name != null and name != ''">
p.name = #{name}
</when>
<when test="address != null and address != ''">
p.address = #{address}
</when>
<otherwise>
1 = 1
</otherwise>
</choose>
</select>
@Test
public void queryChooseWhenOtherwise() {
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
//List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressChooseWhenOtherwise("Ma yun", "tianjing");
//List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressChooseWhenOtherwise(null, "hangzhou");
List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressChooseWhenOtherwise(null, null);
for (People p : byNameAndAddressIf) {
System.out.println(p);
}
}
- 测试效果：我们在测试程序里面的3个查询，第一个传了name和address，但是会查出name是"Ma yun"的全部记录，和address无关，第二个语句当name为null的时候，会查出address为"hangzhou"的全部记录，如果2个参数都是null，那么会查出全部数据库记录，这就是这三个标签的作用，他可以做一定逻辑上的优先级。
三、where
- 当我们做where条件的动态拼接时，比如按照之前的使用if，如果我们按照第一种写法，在条件缺失的时候会有问题，第二种则不会有问题。
第一种的where是写死的，如果if全部为null，那么就没有条件，语法上就是错的，有时候会写1=1，但是那样是可能造成sql注入的，并且也不
是很好的处理，使用where标签的话，即使条件全部都没有，mybatis也能够正确的处理
- 映射文件
<!--第一种：-->
<select id="findByNameAndAddressIf" resultType="com.intellif.mozping.entity.People">
select *
from tb_people p
where
<if test="name != null and name != ''">
and p.name = #{name}
</if>
<if test="address != address and address != ''">
and p.address = #{address}
</if>
</select>
<!--条件都为null时，打印出来的预编译语句是：select * from tb_people p where ，语法会报错-->
<!--第二种：-->
<select id="findByNameAndAddressIf" resultType="com.intellif.mozping.entity.People">
select *
from tb_people p
<where>
<if test="name != null and name != ''">
and p.name = #{name}
</if>
<if test="address != address and address != ''">
and p.address = #{address}
</if>
</where>
</select>
<!--条件都为null时，打印出来的预编译语句是：select * from tb_people p -->
四、set
<!--使用set更新-->
<update id="updateWithSet" parameterType="com.intellif.mozping.entity.People">
update tb_people
<set>
<if test="name!=null"> name=#{name},</if>
<if test="age!=null"> age=#{age},</if>
<if test="address!=null"> address=#{address},</if>
<if test="edu!=null"> edu=#{edu},</if>
</set>
where id=#{id};
</update>
//测试set标签
@Test
public void updateWithSet() {
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
People people = new People();
people.setId(11);
people.setAge(54);
people.setName("Ma hua teng");
people.setAddress("shenzhen");
people.setEdu("Bachelor");
int rowAffected = peopleMapper.updateWithSet(people);
System.out.println("rowAffected: "+rowAffected);
//显示提交事务
sqlSession.commit();
sqlSession.close();
}
打印：
15:25:31.027 [main] DEBUG c.i.m.dao.PeopleMapper.updateWithSet - ==> Preparing: update tb_people SET name=?, age=?, address=?, edu=? where id=?;
15:25:31.071 [main] DEBUG c.i.m.dao.PeopleMapper.updateWithSet - ==> Parameters: Ma hua teng(String), 54(Integer), shenzhen(String), Bachelor(String), 11(Integer)
15:25:31.073 [main] DEBUG c.i.m.dao.PeopleMapper.updateWithSet - <== Updates: 1
rowAffected: 1
五、trim
- trim标记是一个格式化的标记，可以完成set或者是where标记的功能，主要包含前缀/后缀的处理，去掉第一个指定内容或者去掉最后一个指定内容
| 属性 |
功能 |
| prefix |
前缀 |
| prefixOverrides |
去掉第一个指定内容 |
| suffix |
后缀 |
| suffixoverride |
去掉最后一个指定内容 |
<!--trim标签，-->
<select id="findByNameAndAddressTrim" resultType="com.intellif.mozping.entity.People">
select *
from tb_people p
<trim prefix="where" prefixOverrides="AND|OR">
<if test="name != null and name != ''">
and p.name = #{name}
</if>
<if test="address != address and address != ''">
and p.address = #{address}
</if>
</trim>
</select>
@Test
public void queryTrim() {
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
List<People> byNameAndAddressIf = peopleMapper.findByNameAndAddressTrim("Parker", "tianjing");
for (People p : byNameAndAddressIf) {
System.out.println(p);
}
}
打印：
15:41:58.869 [main] DEBUG c.i.m.d.P.findByNameAndAddressTrim - ==> Preparing: select * from tb_people p where p.name = ?
15:41:58.889 [main] DEBUG c.i.m.d.P.findByNameAndAddressTrim - ==> Parameters: Parker(String)
15:41:58.906 [main] DEBUG c.i.m.d.P.findByNameAndAddressTrim - <== Total: 1
People(id=2, name=Parker, age=20, address=tianjing, edu=Bachelor)
注意：
prefix="where" 会自动给我们加上where前缀
prefixOverrides="AND|OR"的作用是去除第一个And或者OR，为什么呢，在这个测试中传了2个条件，因此没有这个的话，是where and name = "Parker" and address = "tianjing" ，sql语法是错误的。
假如这里连续多个条件，那么每一个条件都有可能不传，不传的那个会被忽略，因此其实并不知道哪一个if里面的and是第一个and，所以无法确定哪一个里面的and不写，所以只能都写了，让框架去去除
第一个and。
- 在set赋值更新语句中也是一样，需要将最后面的逗号给去掉，原理和前缀类似
<trim suffixOverrides=",">
<if test="username!=null">
name = #{username},
</if>
<if test="age != 0">
age = #{age},
</if>
</trim>
六、foreach
- 用于遍历数组或者集合,比如一个集合中包含很多主键id，要查询这些id对应的数据。
| 属性 |
说明 |
| collection |
collection属性的值有三个分别是list、array、map三种 |
| open |
前缀 |
| close |
后缀 |
| separator |
分隔符，表示迭代时每个元素之间以什么分隔 |
| item |
表示在迭代过程中每一个元素的别名 |
| index |
用一个变量名表示当前循环的索引位置 |
6.1 查询
<!--forEach-->
<select id="findByIdForEach" resultType="com.intellif.mozping.entity.People">
select *
from tb_people where id in
<foreach collection="ids" open="(" close=")" separator="," item="id" >
#{id}
</foreach>
</select>
//Java接口：
List<People> findByIdForEach(@Param("ids")List<Integer> ids);
//测试：
@Test
public void queryForEach() {
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
ArrayList<Integer> ids = new ArrayList<>();
for (int i = 1; i < 10; i++) {
ids.add(i);
}
List<People> peopleList = peopleMapper.findByIdForEach(ids);
for (People p : peopleList) {
System.out.println(p);
}
}
6.2 插入
- 插入操作也类似，比如插入一个多个对象，插入一个集合
<insert id="insertPeopleList">
insert into t_people(name,age) values
<foreach collection="users" item="user" separator=",">
(#{user.name},#{user.age})
</foreach>
</insert>
七、bind
- bind 元素可以从OGNL表达式中创建一个变量并将其绑定到上下文。说起来比较抽象，看示例。
- 映射文件
<!--forEach-->
<select id="findByBind" resultType="com.intellif.mozping.entity.People">
<!-- 声明了一个参数queryId，在后面就可以使用了 -->
<bind name="queryId" value="1"/>
select * from tb_people where id = ${queryId}
</select>
//测试bind
@Test
public void queryByBind() {
SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactoryInstaceByConfig(CONFIG_FILE_PATH).openSession();
PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
List<People> peopleList = peopleMapper.findByBind();
for (People p : peopleList) {
System.out.println(p);
}
}
//这里不知道为什么，我用字符串，按照name查找就报错了，不太清楚OGNL
八、sql
<!--sql-->
<sql id="baseSql">
name, age
</sql>
<select id="findBySql" resultType="com.intellif.mozping.entity.People">
select
<include refid="baseSql"/>
from tb_people
</select>
- 相当于定义一个可以重复使用的语句块，减少重复工作。
九、参考
