第3章 动态sql（4课时）

第1节
今天课程目标：掌握动态sql写法与用途

动态sql

学习以下动态sql语句

  1. if(多条件动态拼接)
    2)where(解决where and/or情况)
    3)set(解决update语句多逗号情况 与update一起用)
    4)trim(**可以前后追加，同时还能用指定字符分隔语句，还可以去除语句前后的指定字符)
    5)foreach（拼装in语句）
    6)sql片段（提取出公用的sql）
    7)choose（选择很多情况下的一种，和 Java 中的 switch语句相似

if(多条件动态拼接)

1）页面让用户输入查询条件
用户名 身份证号 邮箱
后台动态判断他输入了什么
如果输入了用户名我就把用户名做为选择条件
如果输入了身份证号我就把身份证号做为选择条件
如果输入了邮箱我就把邮箱做为选择条件

select * from t_user
where (如果输入用户名 username=#{username})
(如果输入身份证号 and/or idcard=#{useridcard})
(如果输入邮箱 and/or email=#{useremail})

样例代码
接口

public interface UserSqlMapper {
  public User getUser(User user);
}

xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.neuedu.day02.mapper.UserSqlMapper">
<select id="getUser" parameterType="User" resultType="user">
select * from t_user where 
<if test="user_name!=null and user_name!=''">
 user_name=#{user_name}
</if>
<if test="user_email!=null and user_email!=''">
 and user_email=#{user_email}
</if>
<if test="user_idCard!=null and user_idCard!=''">
 and user_idCard=#{user_idCard}
</if>
</select>
</mapper>

测试类以下方法调用usersqlmapper下的

   @Test
   public void testFind() throws IOException{
       IUserDao userDao=new UserDaoImpl(factory);
       User user=new User();
       user.setUser_name("chenhao");
       user.setUser_email("chenhao.newedu.com");
       user.setUser_idCard("210101200006061234");
       User dbuser=userDao.getUser(user);
       System.out.println(dbuser.getUser_name());
   }

如果上述查询不输入用户名，会出现where直接加and/or的情况，导致查询不到结果

where(解决if多条件拼装时，如果第一个条件不存在，where+and/or的问题。)

把上面的statement稍微改写一个。改写成用where子句把if包起来。即下面这样：

<select id="getUser" parameterType="User" resultType="user">
select * from t_user 
<where> 
<if test="user_name!=null and user_name!=''">
 user_name=#{user_name}
</if>
<if test="user_email!=null and user_email!=''">
 and user_email=#{user_email}
</if>
<if test="user_idCard!=null and user_idCard!=''">
 and user_idCard=#{user_idCard}
</if>
</where>
</select>

set(解决update语句多逗号情况 与update一起用)

关键代码
UserSqlMapper.xml

<update id="updateUser" parameterType="user">
update t_user set
<if test="user_name!=null and user_name!=''">
user_name=#{user_name},
</if>
<if test="user_email!=null and user_email!=''">
user_email=#{user_email},
</if>
<if test="user_idCard!=null and user_idCard!=''">
user_idcard=#{user_idCard},
</if>
<if test="user_pwd!=null and user_pwd!=''">
user_pwd=#{user_pwd}
</if>
where  user_id=#{user_id}
</update>

测试类：

@Test
   public void testUpdate() throws IOException{
       IUserDao userDao=new UserDaoImpl(factory);
       User user=new User();
       //主键必须带
       user.setUser_id(8);
       //user.setUser_name("chenhao");
       user.setUser_pwd("654321");
       user.setUser_email("chenhao@newedu.com");
       user.setUser_idCard("210101200006061233");
       int count=userDao.updateUserSql(user);
       System.out.println(count);
   }

上面的语句如果最后一个条件没有输入，会有多余的逗号，导致语句出错，把set写成动态的，也就是用<set> </set>把IF语句把起来，能够解决上述问题。
上面的语句改造成下面的形式

<update id="updateUser" parameterType="user">
update t_user 
<set>
<if test="user_name!=null and user_name!=''">
user_name=#{user_name},
</if>
<if test="user_email!=null and user_email!=''">
user_email=#{user_email},
</if>
<if test="user_idCard!=null and user_idCard!=''">
user_idcard=#{user_idCard},
</if>
<if test="user_pwd!=null and user_pwd!=''">
user_pwd=#{user_pwd}
</if>
</set>
where  user_id=#{user_id}
</update>

第二节

trim(可以前后追加，同时还能用指定字符分隔语句，还可以去除语句前后的指定字符)

格式：
<trim prefix="" suffix="" prefixOverrides="" suffixOverrides=""></trim>
其中：prefix：前面加上什么
suffix：后面加上什么
prefixOverrides：前面去掉什么
suffixOverrides：后面去掉什么
他可以替代前面的set where
举例如果想动态拼装inset语句：
insert into t_user (key1,key2)
values (value1,value2)
我们可以通过更改上下两个括号的部分来实现
来试一下

*注意如果字段是int类型，user_power=0时，user_power!=''为true
所以，
关键代码：
动态的insert的xml为

<insert id="insertUserSql" parameterType="User">
insert into t_user 
<trim prefix="(" suffix=")" suffixOverrides=",">
user_id,
<if test="user_name!=null and user_name!=''">
user_name,
</if>
<if test="user_pwd!=null and user_pwd!=''">
user_pwd,
</if>
<if test="user_email!=null and user_email!=''">
user_email,
</if>
<if test="user_idcard!=null and user_idcard!=''">
user_idcard,
</if>
<if test="user_power!=null">
user_power,
</if>
</trim>
 values 
<trim prefix="(" suffix=")" suffixOverrides=",">
null,
<if test="user_name!=null and user_name!=''">
#{user_name},
</if>
<if test="user_pwd!=null and user_pwd!=''">
#{user_pwd},
</if>
<if test="user_email!=null and user_email!=''">
#{user_email},
</if>
<if test="user_idcard!=null and user_idcard!=''">
#{user_idcard},
</if>
<if test="user_power!=null">
#{user_power},
</if>
</trim>

对应的测试类为：

 @Test
   public void testInsert() {
       IUserDao userDao=new UserDaoImpl(factory);
       User user=new User();
       user.setUser_name("gujunn");
       user.setUser_pwd("123456");
       user.setUser_power(0);
       //user.setUser_email("chenhao.newedu.com");
       //user.setUser_idCard("210101200006061234");
       int count=userDao.insertUserSql(user);
       System.out.println(count);
   }

第三节

foreach（拼装in语句）

注意：你可以传递一个 List 实例或者数组作为参数对象传给 MyBatis。当你这么做的时
候，MyBatis 会自动将它包装在一个 Map 中，用名称在作为键。List 实例将会以“list”
作为键，而数组实例将会以“array”作为键。
语法：
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>

代码示例:用户批量删除功能
UserSqlMapper.xml

<delete id="delUserList">
<!-- 分析批量删除语句delete from t_user where user_id in (1,2,3,4,5) 
上述语句需要动态拼(1,2,3,4,5)
foreach可以实现循环拼接
如果传递参数为list,collection="list"（固定写法）
前面加(  open="("
后面加)  close=")"
中间加,  separator=","
-->
delete from t_user where user_id in 
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item}
</foreach>

接口里对应方法UserSqlMapper.java

public int delUserList(List ids);

测试用例：UserSqlServiceTest.java

@Test
   public void testDeleteBatch() throws IOException{
       SqlSession session=factory.openSession();
       //执行查询
       UserSqlMapper mapper=session.getMapper(UserSqlMapper.class);
       List<Integer> ids=new ArrayList();
       ids.add(10);
       ids.add(8);
       int count=mapper.delUserList(ids);
       System.out.println(count);
       //关闭连接
       session.close();
   }

学生练习：

批量删除传入参数为Array

关键代码

UserSqlMapper.java

public int delUserArray(Integer[] ids);

UserSqlMapper.xml

<delete id="delUserArray">
<!-- 分析批量删除语句delete from t_user where user_id in (1,2,3,4,5) 
上述语句需要动态拼(1,2,3,4,5)
foreach可以实现循环拼接
如果传递参数为数组,collection="array"（固定写法）
前面加(  open="("
后面加)  close=")"
中间加,  separator=","
-->
delete from t_user where user_id in 
<foreach collection="array" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>

  @Test
   public void testDeleteBatchArray() throws IOException{
       SqlSession session=factory.openSession();
       //执行查询
       UserSqlMapper mapper=session.getMapper(UserSqlMapper.class);
       Integer[] ids=new Integer[]{9,10};
       int count=mapper.delUserArray(ids);
       System.out.println(count);
       //关闭连接
       session.commit;
       session.close();
   }

问题：前面两个方法的sql语句中有重复的部分 delete from t_user where user_id in 为了减少代码冗余，我们引入sql片段

sql片段（提取出公用的sql）

<sql id="delusersql">
delete from t_user where user_id in 
</sql>

再将上面的statement改造成如下形式

<delete id="delUserArray">
<!--refid与sql的id对应-->
<include refid="delusersql"/>
<foreach collection="array" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>

choose（选择很多情况下的一种，和 Java 中的 switch语句相似） （手册41页）##

作业：
练习上面的代码，完成用户动态添加、修改、批量删除、动态拼接查询语句

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • MarkDown
    1. 简介 1.1 什么是 MyBatis ？ MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的...
    笨鸟慢飞阅读 11,076评论 0赞 4
  • 摘录的一些知识点
    ORACLE自学教程 --create tabletestone ( id number, --序号usernam...
    落叶寂聊阅读 4,811评论 0赞 0
  • Java面试
    一. Java基础部分.................................................
    wy_sure阅读 9,228评论 0赞 11
  • 什么是sql注入？
    姓名：于川皓 学号：16140210089 转载自：https://baike.baidu.com/item/sq...
    道无涯_cc76阅读 5,955评论 0赞 2
  • 2018-06-22
    6.22日 周五 晴 庆妈妈第297篇 今天下午，展开我们每月一次的家庭交流会，董老师的到来，给所有来到的家...
    史响庆阅读 741评论 0赞 0