mybatis内容解析之动态sql - part2

科技 08-15 来源：小柴学Java

package com.example.mapper;import com.example.pojo.User;import java.util.List;/** * 数据访问层的接口，定义对数据库完成的CRUD的操作 */public interface UsersMapper {    //循环查询    List getByIds(Integer []id_array);    //批量删除    int deleteByIds(Integer []id_array);    //批量插入    int insertBatch(List users);        //批量更新    int updateBatch(List users);}

UsersMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>                        delete from                   users        where            id        in                    #{id}                                    insert into            users(username, birthday, sex, address)        values                            (#{user.userName}, #{user.birthday}, #{user.sex}, #{user.address})                                                    update                users                                                username=#{user.userName},                                                    birthday=#{user.birthday},                                                    sex=#{user.sex},                                                    address=#{user.address},                                        where                id=#{user.id}

映射文件分析

当入参是数组时，parameterType可以不写，

其实只有当入参类型是实体类时，才必须指明其类型，其他类型的数据皆可不写

< foreach >标签的属性说明

collection：指明待遍历的数据容器的类型，可选的有三个：array，list，map
item：给遍历出的每个元素指定一个名称，便于在标签内使用
open 和 close：原先sql语句，in后面待遍历的数据放在括号中，这里通过标签属性的形式来实现可以将sql语句和标签的书写分隔开，使得代码更加直观注意：这是循环外层的括号，用来容纳所有的元素，循环内部的元素自身的括号不能用这对标签，要手动加上
是否要使用 open 和 close标签的说明：

<foreach collection="array" item="id" separator="," open="(" close=")"> #{id} foreach> <foreach collection="list" item="user" separator=","> (#{user.userName}, #{user.birthday}, #{user.sex}, #{user.address}) foreach>

separator：遍历出的元素之间用什么符号分隔，mybatis框架会确保间隔符号的正确使用正确使用是指：在指定需要的间隔符后，间隔符号的个数以及出现在元素之间的位置，由mybatis自动正确放置(如果有多个元素的话)

进行批量更新操作时

本质：由底层解析出的sql语句可知，本质执行的是多条独立的update语句这也决定了< update >标签内应该是一个 < foreach >标签，分隔符应该是";"，用来间隔多条相互独立的update语句
注意：这与执行一条update语句，影响多行记录是不同的，必须在jdbc.properties文件中的url的值后新增配置：allowMultiQueries=true

jdbc.driverClassName=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://ip:3306/ssm?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true jdbc.username=XXXX jdbc.password=YYYY

不修改jdbc.properties时报错

各sql标签在底层分别被解析为

//批量查询==>  Preparing: select id, username, birthday, sex, address from users where id in ( ? , ? , ? )

//批量删除==>  Preparing: delete from users where id in ( ? , ? )

//批量插入==>  Preparing: insert into users(username, birthday, sex, address) values (?, ?, ?, ?) , (?, ?, ?, ?) , (?, ?, ?, ?)

//批量更新==>  Preparing: update users SET username=?, birthday=?, sex=?, address=? where id=? ;update users SET username=?, birthday=?, sex=?, address=? where id=? ; update users SET username=?, birthday=?, sex=?, address=? where id=?

测试代码

package com.example.mapper;import com.example.pojo.User;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.After;import org.junit.Before;import org.junit.Test;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Array;import java.util.List;public class TestUsersMapper {    //时间刷    SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");    //SqlSession对象    SqlSession sqlSession;    //mybatis动态代理对象    UsersMapper usersMapper;    //获取SqlSession    @Before    public void getSqlSession() throws IOException {        //读取核心配置文件        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");        //创建SqlSessionFactory对象        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);        //获取SqlSession        sqlSession = factory.openSession();        //获取mybatis动态代理对象        usersMapper = sqlSession.getMapper(UsersMapper.class);    }    //归还SqlSession    @After    public void closeSession(){        sqlSession.close();    }    //循环查询测试    @Test    public void testGetByIds(){        Integer []id_array = {1, 3, 5};        List users = usersMapper.getByIds(id_array);        users.forEach(System.out::println);    }    //批量删除测试    @Test    public void testDeleteByIds(){        Integer []id_array = {3, 29};        int num = usersMapper.deleteByIds(id_array);        if(num == 2){            System.out.println("批量删除成功!");            sqlSession.commit();        }else{            System.out.println("批量删除失败!");        }    }        //批量插入测试    @Test    public void testInsertBatch() throws ParseException {        User u1 = new User("西决", date.parse("2001-01-01"), "男", "北京");        User u2 = new User("南音", date.parse("2002-02-02"), "女", "北京");        User u3 = new User("北北", date.parse("2003-03-03"), "男", "北京");        List users = new ArrayList<>();        users.add(u1);        users.add(u2);        users.add(u3);        int num = usersMapper.insertBatch(users);        if(num == 3){            System.out.println("批量插入成功!");            sqlSession.commit();        }else{            System.out.println("批量插入失败!");        }    }        //批量更新测试    @Test    public void testUpdateBatch() throws ParseException {        User u1 = new User(31,"西决2", date.parse("2001-01-01"), "男", "北京");        User u2 = new User(32,"南音2", date.parse("2002-02-02"), "女", "北京");        User u3 = new User(33,"北北2", date.parse("2003-03-03"), "男", "北京");        List users = new ArrayList<>();        users.add(u1);        users.add(u2);        users.add(u3);        int num = usersMapper.updateBatch(users);        if(num == 1){            System.out.println("批量更新成功!");            sqlSession.commit();        }else{            System.out.println("批量更新失败!");        }    }}

测试代码分析(着重分析一下批量更新)

在执行批量更新操作时，为什么数据表的记录明明修改了3条，输出结果中更新结果的返回值却是1呢？
更新后的数据表，修改了3条记录

批量更新后的返回结果

<==    Updates: 1批量更新成功!

原因：

首先要区别< update >标签和update语句：在 < update >标签中有一个< foreach >标签，他循环了3条相互独立的update语句
注意：每条update语句恰巧都只是修改了一条记录，所以< update >标签返回3次1后结束，因为循环3次后循环标签结束了
对于如下测试代码，num其实被赋值3次，num的值是最后一次赋值的结果，本次测试恰好是：1其实笔者不太确定num是否被赋值了3，但是根据底层输出的结果可知，起作用的一定是最后一条update语句影响的记录条数不是循环的3条update语句影响条数的和，因为输出结果：是1，而不是3

int num = usersMapper.updateBatch(users);

输出结果

//批量查询结果Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]Opening JDBC ConnectionCreated connection 16148478.Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@f667fe]==>  Preparing: select id, username, birthday, sex, address from users where id in ( ? , ? , ? )==> Parameters: 1(Integer), 3(Integer), 5(Integer)<==    Columns: id, username, birthday, sex, address<==        Row: 1, 荷包蛋, 2002-08-23, 女, 黑河市<==        Row: 3, 小张, 1999-02-22, 1, 长沙<==        Row: 5, 段, 2001-03-10, 1, 太原<==      Total: 3Users{id=1, userName='荷包蛋', birthday=Fri Aug 23 00:00:00 CST 2002, sex='女', address='黑河市'}Users{id=3, userName='小张', birthday=Mon Feb 22 00:00:00 CST 1999, sex='1', address='长沙'}Users{id=5, userName='段', birthday=Sat Mar 10 00:00:00 CST 2001, sex='1', address='太原'}Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@f667fe]Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@f667fe]Returned connection 16148478 to pool.Process finished with exit code 0

//批量删除结果Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]Opening JDBC ConnectionCreated connection 544966217.Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]==>  Preparing: delete from users where id in ( ? , ? )==> Parameters: 3(Integer), 29(Integer)<==    Updates: 2批量删除成功!Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]Returned connection 544966217 to pool.Process finished with exit code 0

//批量增加结果Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]Opening JDBC ConnectionCreated connection 749604930.Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2cae1042]==>  Preparing: insert into users(username, birthday, sex, address) values (?, ?, ?, ?) , (?, ?, ?, ?) , (?, ?, ?, ?)==> Parameters: 西决(String), 2001-01-01 00:00:00.0(Timestamp), 男(String), 北京(String), 南音(String), 2002-02-02 00:00:00.0(Timestamp), 女(String), 北京(String), 北北(String), 2003-03-03 00:00:00.0(Timestamp), 男(String), 北京(String)<==    Updates: 3批量插入成功!Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2cae1042]Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2cae1042]Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2cae1042]Returned connection 749604930 to pool.Process finished with exit code 0

//批量更新结果Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]Opening JDBC ConnectionCreated connection 1718322084.Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@666b83a4]==>  Preparing: update users SET username=?, birthday=?, sex=?, address=? where id=? ; update users SET username=?, birthday=?, sex=?, address=? where id=? ; update users SET username=?, birthday=?, sex=?, address=? where id=?==> Parameters: 西决2(String), 2001-01-01 00:00:00.0(Timestamp), 男(String), 北京(String), 31(Integer), 南音2(String), 2002-02-02 00:00:00.0(Timestamp), 女(String), 北京(String), 32(Integer), 北北2(String), 2003-03-03 00:00:00.0(Timestamp), 男(String), 北京(String), 33(Integer)<==    Updates: 1批量更新成功!Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@666b83a4]Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@666b83a4]Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@666b83a4]Returned connection 1718322084 to pool.Process finished with exit code 0