Mybatis批量增删改查

倚-天-照-海

已于 2023-12-03 22:05:13 修改

阅读量144

点赞数 1

分类专栏： mybatis 文章标签： mybatis java 开发语言

于 2023-12-03 21:50:00 首次发布

本文链接：https://blog.csdn.net/cxbtdd/article/details/134770270

版权

mybatis 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

mybatis批量增删改查

1、批量增删改查简单使用

目录结构：

1、pom.xml文件引入依赖：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>mybatis-learn</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.8.2</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

2、创建实体类

package com.learn.entity;

public class User {

    private Integer id;

    private String username;

    private Integer age;
    // 省略get、set方法
}

3、创建Mapper接口

package com.learn.mapper;

import com.learn.entity.User;

import java.util.List;

/**
 * @Author: 倚天照海
 */
public interface UserMapper {
    List<User> batchQueryByIds(List<Integer> ids);

    void batchCreateUser(List<User> users);

    void batchUpdateUser(List<User> users);

    void batchDeleteUser(List<Integer> userIds);
}

4、创建Mapper.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.learn.mapper.UserMapper">
    <!--namespace的值必须是mapper接口的全限定名-->

    <resultMap id="baseResultMap" type="com.learn.entity.User">
        <result property="userId" column="user_id" jdbcType="INTEGER"/>
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="age" column="age" jdbcType="INTEGER"/>
    </resultMap>

    <sql id="columnInfo">
        user_id, username, age
    </sql>
    <!-- batchQueryByIds方法的返回值类型虽然是List<User>，但是resultType或resultMap对应的是List中元素的类型，即User -->
    <select id="batchQueryByIds" resultMap="baseResultMap">
        select
        <include refid="columnInfo"/>
        from t_user
        <where>
            user_id in
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </where>
    </select>

    <insert id="batchCreateUser" parameterType="user">
        insert into t_user(user_id, username, age) values
        <foreach collection="list" item="item" separator=",">
            (#{item.userId}, #{item.username}, #{item.age})
        </foreach>
    </insert>

    <update id="batchUpdateUser" parameterType="user">
        update t_user
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="username = case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.username != null">
                        when user_id = #{item.userId} then #{item.username}
                    </if>
                </foreach>
            </trim>
            <trim prefix="age = case" suffix="end">
                <foreach collection="list" item="item">
                    <if test="item.age != null">
                        when user_id = #{item.userId} then #{item.age}
                    </if>
                </foreach>
            </trim>
        </trim>
        <where>
            user_id in
            <foreach collection="list" item="item" separator="," open="(" close=")">
                #{item.userId}
            </foreach>
        </where>
        <!--<where>
            <foreach collection="list" item="item" separator="or" open="(" close=")">
                user_id = #{item.userId}
            </foreach>
        </where>-->
    </update>

    <delete id="batchDeleteUser">
        delete from t_user
        <where>
            user_id in
            <foreach collection="list" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </where>
    </delete>

</mapper>

5、创建mybatis配置文件

<?xml version="1.0" encoding="UTF-8" ?>  <!--xml文件的一个声明-->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd"><!--xml文件的约束,来规定当前配置文件中都能使用哪些标签-->
<configuration>
    <!-- 设置别名，在xxMapper.xml文件中使用实体类时可以不用指定类的完全限定名，直接使用首字母小写的类名即可 -->
    <typeAliases>
        <package name="com.learn.entity"/>
    </typeAliases>
    <!--配置环境-->
    <environments default="mysql">
        <!--配置Mysql的环境-->
        <environment id="mysql">
            <!--配置事务的类型-->
            <transactionManager type="JDBC"></transactionManager>
            <!--配置数据源(连接池)-->
            <dataSource type="POOLED">
                <!--配置连接数据库的四个基本信息-->
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mytest" />
                <property name="username" value="root" />
                <property name="password" value="root123" />
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/UserMapper.xml" />
        <!--<mapper resource="mapper/*Mapper.xml" />-->
    </mappers>

</configuration>

6、创建测试类进行增删改查

import com.learn.entity.User;
import com.learn.mapper.UserMapper;
import org.apache.commons.lang3.StringUtils;
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.Assert;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author 倚天照海
 */
public class MybatisTest {
    @Test
    public void test() {
        //mybatis配置文件
        String resource = "MybatisConfig.xml";
        InputStream inputStream;
        SqlSessionFactory sqlSessionFactory;
        SqlSession sqlSession = null;
        try {
            //根据配置文件获取输入流
            inputStream = Resources.getResourceAsStream(resource);
            //根据配置文件输入流创建会话工厂
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            //根据会话工厂得到sqlsession
            sqlSession = sqlSessionFactory.openSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
            // 测试批量增删改查
            testBatchCreateUpdateDelete(sqlSession, userMapper);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //关闭会话
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }

    private User buildUser(Integer id, String name, Integer age) {
        User user = new User();
        user.setUserId(id);
        user.setUsername(name);
        user.setAge(age);
        return user;
    }

    private void testBatchCreateUpdateDelete(SqlSession sqlSession, UserMapper userMapper) {
        batchCreateUser(sqlSession, userMapper);
        batchUpdateUser(sqlSession, userMapper);
        batchDeleteUser(sqlSession, userMapper);
    }

    public void batchCreateUser(SqlSession sqlSession, UserMapper userMapper) {
        User user1 = buildUser(11, "11", 11);
        User user2 = buildUser(12, "12", 12);
        User user3 = buildUser(13, "13", 13);
        List<User> users = new ArrayList<>();
        users.add(user1);
        users.add(user2);
        users.add(user3);
        userMapper.batchCreateUser(users);
        //提交事务，增删改需要commit，查询无需commit
        sqlSession.commit();
        List<User> userList = userMapper.batchQueryByIds(Arrays.asList(11, 12, 13));
        List<Integer> ids = userList.stream().map(User::getUserId).collect(Collectors.toList());
        Assert.assertEquals(3, userList.size());
        Assert.assertTrue(ids.contains(11));
        Assert.assertTrue(ids.contains(12));
        Assert.assertTrue(ids.contains(13));
    }

    /**
     * update t_user set username = case when user_id = ? then ?
     *                                   when user_id = ? then ?
     *                                   when user_id = ? then ? end
     * WHERE (user_id = ? or user_id = ? or user_id = ?)
     */
    public void batchUpdateUser(SqlSession sqlSession, UserMapper userMapper) {
        User user1 = buildUser(11, "111", 111);
        User user2 = buildUser(12, "112", 112);
        User user3 = buildUser(13, "113", 113);
        List<User> users = new ArrayList<>();
        users.add(user1);
        users.add(user2);
        users.add(user3);
        userMapper.batchUpdateUser(users);
        //提交事务，增删改需要commit，查询无需commit
        sqlSession.commit();
        List<User> userList = userMapper.batchQueryByIds(Arrays.asList(11, 12, 13));
        List<String> names = userList.stream().map(User::getUsername).collect(Collectors.toList());
        Assert.assertEquals(3, userList.size());
        Assert.assertTrue(names.contains("111"));
        Assert.assertTrue(names.contains("112"));
        Assert.assertTrue(names.contains("113"));
    }

    public void batchDeleteUser(SqlSession sqlSession, UserMapper userMapper) {
        List<Integer> ids = Arrays.asList(11, 12, 13);
        userMapper.batchDeleteUser(ids);
        //提交事务，增删改需要commit，查询无需commit
        sqlSession.commit();
        List<User> users = userMapper.batchQueryByIds(ids);
        Assert.assertEquals(0, users.size());
    }
}

测试结果：

2、批量新增

UserMapper.java接口：

void batchCreateUser(List<User> users);

UserMapper.xml文件：

<insert id="batchCreateUser" parameterType="user">
    insert into t_user(user_id, username, age) values
    <foreach collection="list" item="item" separator=",">
        (#{item.userId}, #{item.username}, #{item.age})
    </foreach>
</insert>

在写批量插入/新增的SQL语句时，重点是<foreach>标签，有collection、item、index、separator、open、close属性。

1. collection的取值通常是list或array，可以通过mapper接口中方法的参数类型来判断collection的取值。如果参数是list类型，就写成list，如果是数组类型，就写成array。由于此处UserMapper接口中batchCreateUser(List<User> users)方法的参数类型是List类型，所以collection的值写成list。

2.item的取值是指循环体中的具体对象，item的值可以随意写，通常item的值写成item，上例中item的取值表示User对象。

3.index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，通常index的值写成index。在List和数组中，index是元素的序号，在map中，index是元素的key，该参数可选。

4.separator表示在每次进行迭代之间以什么符号作为分隔符。

5. open和close分别表示<foreach>标签中的内容以什么符号开始和结束，通常是open="("和close=")"。

注意：在批量新增的SQL语句中， <foreach>标签中的内容要用小括号包起来，不能写open="("和close=")"属性，否则会报错：java.sql.SQLException: Column count doesn't match value count at row 1。因为如果使用open="("和close=")"，会变成如下SQL语句，将插入的三条数据放到一个括号中。

SQL: insert into t_user(user_id, username, age) values (?, ?, ?, ?, ?, ?, ?, ?, ?)

上面是在mysql中批量新增的写法，在Oracle中略有不同，下面是在Oracle中批量新增的写法。

<!-- oracle中的批量新增 -->
<insert id="batchCreateUser" parameterType="user">
    insert into t_user(user_id, username, age) (
    <foreach collection="list" item="item" index="index" separator=" UNION ALL ">
        SELECT #{item.userId},#{item.username},#{item.age} FROM DUAL
    </foreach>
    )
</insert>

在Oracle的版本中，有几点需要注意的：

1.SQL中没有VALUES；

2.<foreach>标签中的(selece ..... from dual)；

3.<foreach>标签中的separator的属性为"UNION ALL"，将查询合并结果集。

3、主键自增的批量新增

假设user_id是t_user表的主键。

(1)Mybatis+MySQL主键自增的单条新增：

<!-- Mybatis+MySQL 主键自增的单条新增 -->
<insert id="batchCreateUser" parameterType="user" useGeneratedKeys="true" keyProperty="userId" keyColumn="user_id">
    insert into t_user(username, age) values(#{item.username}, #{item.age})
</insert>

(2)Mybatis+MySQL主键自增的批量新增：

<!-- Mybatis+MySQL 主键自增的批量新增 -->
<insert id="batchCreateUser" parameterType="user" useGeneratedKeys="true" keyProperty="userId" keyColumn="user_id">
    insert into t_user(username, age) values
    <foreach collection="list" item="item" separator=",">
        (#{item.username}, #{item.age})
    </foreach>
</insert>

(3)Mybatis+Oracle主键自增的单条新增：

1.首先需要创建自增序列

--创建自增序列T_USER_SEQUENCE
CREATE SEQUENCE T_USER_SEQUENCE
START WITH 1
INCREMENT BY 1
MAXVALUE 99999999
NOMINVALUE
NOCACHE
NOCYCLE;

2.编写SQL语句

<insert id="createUser" parameterType="user">
   <selectKey resultType="java.lang.Integer" keyProperty="userId" order="BEFORE">
      SELECT T_USER_SEQUENCE.Nextval from sys.dual
   </selectKey>
   INSERT INTO T_USER(USER_ID, USERNAME,AGE)
   VALUES(
   #{userId,jdbcType=NUMERIC},
   #{userName,jdbcType=VARCHAR},
   #{age,jdbcType=NUMERIC})
</insert>

(4)Mybatis+Oracle主键自增的批量新增：

1.首先需要创建自增序列

--创建自增序列T_USER_SEQUENCE
CREATE SEQUENCE T_USER_SEQUENCE
START WITH 1
INCREMENT BY 1
MAXVALUE 99999999
NOMINVALUE
NOCACHE
NOCYCLE;

2.编写SQL语句

<insert id="batchCreateUser" parameterType="user">
   INSERT INTO T_USER(USER_ID, USERNAME,AGE)
   SELECT T_USER_SEQUENCE.Nextval, A.* FROM(
   <foreach collection="list" item="item" index="index" separator="UNION ALL">
      SELECT
      #{item.userName,jdbcType=VARCHAR},
      #{item.age,jdbcType=NUMERIC} 
      FROM DUAL
   </foreach>
   ) A
</insert>

在上面这条SQL语句中，T_USER_SEQUENCE.Nextval表示从自增序列中获取下一个值用于赋值给user_id字段。其中的A表示临时表，存储了item.userName和item.age两个字段的值。

4、批量删除

1.接口参数是数组

UserMapper.java接口：

void batchDeleteUser(Integer[] userIds);

UserMapper.xml文件：

<delete id="batchDeleteUser">
    delete from t_user
    <where>
        user_id in
        <foreach collection="array" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </where>
</delete>

2.接口参数是列表

UserMapper.java接口：

void batchDeleteUser(List<Integer> userIds);

UserMapper.xml文件：

<delete id="batchDeleteUser">
    delete from t_user
    <where>
        user_id in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </where>
</delete>

3.根据userId和username批量删除

UserMapper.java接口：

void batchDeleteUser(List<User> users);

UserMapper.xml文件：

<delete id="batchDeleteUser2">
    delete from t_user
    <where>
        user_id in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.userId}
        </foreach>
        and username in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.username}
        </foreach>
    </where>
</delete>

5、批量修改

UserMapper.java接口：

void batchUpdateUser(List<User> users);

UserMapper.xml文件：

<update id="batchUpdateUser" parameterType="user">
    update t_user
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="username = case" suffix="end,">
            <foreach collection="list" item="item">
                <if test="item.username != null">
                    when user_id = #{item.userId} then #{item.username}
                </if>
            </foreach>
        </trim>
        <trim prefix="age = case" suffix="end">
            <foreach collection="list" item="item">
                <if test="item.age != null">
                    when user_id = #{item.userId} then #{item.age}
                </if>
            </foreach>
        </trim>
    </trim>
    <where>
	<!-- in的方式 -->
        user_id in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.userId}
        </foreach>
    </where>
    <!-- or的方式 -->
    <!--<where>
        <foreach collection="list" item="item" separator="or" open="(" close=")">
            user_id = #{item.userId}
        </foreach>
    </where>-->
</update>

映射文件中parameterType参数的值是mapper接口方法batchUpdateUser(List<User> users)中User类的全限定名，如果开启了别名映射，则使用首字母小写的类名。

SQL语句：UPDATE 表名 SET 字段1 = CASE WHEN 参考字段 = 参考值1 THEN 字段1的新值1 WHEN 参考字段 = 参考值2 THEN 字段1的新值2 END, 字段2 = CASE WHEN 参考字段 = 参考值1 THEN 字段2的新值1 WHEN 参考字段 = 参考值2 THEN 字段2的新值2 END, ......,字段n = CASE WHEN 参考字段 = 参考值1 THEN 字段n的新值1 WHEN 参考字段 = 参考值2 THEN 字段n的新值2 END WHERE ( 参考字段 = 参考值1 OR 参考字段 = 参考值2 )

参考字段一般都是主键，根据主键进行修改其他字段的值。

注意：<trim prefix="set" suffixOverrides=",">标签中的suffixOverrides=","表示最后一个end如果带有逗号，则将逗号去掉。如果不加suffixOverrides=","，生成的SQL语句中最后一个end和where之间可能会有一个逗号，则会导致SQL语法错误。

6、批量查询

1.接口参数是数组

UserMapper.java接口：

List<User> batchQueryByIds(Integer[] userIds);

UserMapper.xml文件：

<!-- batchQueryByIds方法的返回值类型虽然是List<User>，但是resultType或resultMap对应的是List中元素的类型，即User -->
<select id="batchQueryByIds" resultMap="baseResultMap">
    select user_id, username, age
    from t_user
    <where>
        user_id in
        <foreach collection="array" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </where>
</select>

<resultMap id="baseResultMap" type="com.learn.entity.User">
    <result property="userId" column="user_id" jdbcType="INTEGER"/>
    <result property="username" column="username" jdbcType="VARCHAR"/>
    <result property="age" column="age" jdbcType="INTEGER"/>
</resultMap>

2.接口参数是列表

UserMapper.java接口：

List<User> batchQueryByIds(List<Integer> userIds);

UserMapper.xml文件：

<!-- batchQueryByIds方法的返回值类型虽然是List<User>，但是resultType或resultMap对应的是List中元素的类型，即User -->
<select id="batchQueryByIds" resultMap="baseResultMap">
    select user_id, username, age
    from t_user
    <where>
        user_id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </where>
</select>

3.根据userId和username批量查询

UserMapper.java接口：

List<User> batchQueryByIdAndName(List<User> users);

UserMapper.xml文件：

<select id="batchQueryByIdAndName" resultMap="baseResultMap">
    select user_id, username, age
    from t_user
    <where>
        user_id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.userId}
        </foreach>
        and username in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.username}
        </foreach>
    </where>
</select>

4.有无@Param的区别

进行批量查询时，如果在mapper接口方法中的参数上没有使用@Param注解，参数若是数组的形式，在mapper映射文件中<foreach>标签中的collection属性的值默认是array，参数若是链表的形式，collection的值默认是list，参数若是Set集合的形式，collection的值默认是set。如果在mapper接口方法中的参数上使用了@Param注解，此时collection的值必须与@Param注解的值相同。@Param注解的值可以任意取，可以与参数的名字不一样。例如List<User> batchQueryByIds(@Param("ids") List<Integer> userIds);，那么collection="ids"。

UserMapper.java接口：

List<User> batchQueryByIds(@Param("ids") List<Integer> userIds);

UserMapper.xml文件：

<select id="batchQueryByIds" resultMap="baseResultMap">
    select user_id, username, age
    from t_user
    <where>
        user_id in
        <foreach collection="ids" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </where>
</select>

7、模糊查询

1.通过bind标签实现模糊查询

UserMapper.java接口：

/**
 * 通过名称模糊查询
 *
 * @param username 用户名
 * @return 用户列表
 */
List<User> queryUserByNameContains(@Param("name") String username);

UserMapper.xml文件：

<select id="queryUserByNameContains" parameterType="java.lang.String" resultMap="baseResultMap">
    <bind name="pattern" value="'%' + name + '%'" />
    select user_id, username, age
    from t_user
    <where>
        username like #{pattern}
    </where>
</select>

通过bind标签实现模糊查询时，Mapper接口方法中的参数必须要加上@Param注解，否则会报下面的错误：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'username' in 'class java.lang.String'。

另外，@Param注解中的值必须与mapper.xml映射文件中的<bind>标签中的value的值相同(除去两个%)，比如@Param("name")，映射文件中是<bind name="pattern" value="'%' + username + '%'" />，则会报下面的错误：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'username' not found. Available parameters are [name, param1]。

2.通过concat函数实现模糊查询

使用这种方式进行模糊查询时，参数上可以不用使用@Param注解。如果使用了@Param注解，使用concat拼接参数时必须与@Param注解中的参数保持一致。

UserMapper.java接口：

/**
 * 通过名称模糊查询
 *
 * @param username 用户名
 * @return 用户列表
 */
List<User> queryUserByNameContains(@Param("name") String username);

UserMapper.xml文件：

<select id="queryUserByNameContains" parameterType="java.lang.String" resultMap="baseResultMap">
    select user_id, username, age
    from t_user
    <where>
        username like concat(concat('%', #{name}), '%')
    </where>
</select>