MyBatis 批量插入

￡神魔★判官ぃ 2022-10-24 01:53 172阅读 0赞

两种方式：

*  sqlSessionFactory.openSession(ExecutorType.BATCH)
 *  拼接批量 insert SQL 脚本

1、Maven 依赖

<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>
    
        <parent>
            <groupId>constxiong</groupId>
            <artifactId>demo</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </parent>
        <artifactId>mybatis-batchinsert</artifactId>
    
        <dependencies>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.21</version>
            </dependency>
    
            
            <dependency>
    		  <groupId>org.mybatis</groupId>
    		  <artifactId>mybatis</artifactId>
    		  <version>3.5.5</version>
    		</dependency>
    
        </dependencies>
    
        <build>
    
        </build>
    </project>

2、mybatis-config.xml 配置文件

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <settings>
            <setting name="logImpl" value="STDOUT_LOGGING"></setting>
        </settings>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://172.31.32.184:3306/constxiong?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
                    <property name="username" value="root"/>
                    <property name="password" value="constxiong@123"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <mapper resource="constxiong/mapper/UserMapper.xml"/>
        </mappers>
    </configuration>

3、Mapper 接口

package constxiong.mapper;
    
    import constxiong.po.User;
    
    import java.util.List;
    
    /**
     * UserMapper 接口，映射对数据库的操作
     */
    public interface UserMapper {
    
        List<User> selectUsers();
    
        int insertUser(User user);
    
        int deleteUsers();
    
        /**
         * 批量插入
         */
        int insertUserBatch(List<User> userList);
    }

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="constxiong.mapper.UserMapper">
    
        <select id="selectUsers" resultType="constxiong.po.User">
            select * from user
        </select>
    
        <insert id="insertUser" parameterType="constxiong.po.User">
            insert into user values(#{id}, #{name})
        </insert>
    
        <delete id="deleteUsers">
          delete from user
        </delete>
    
        <insert id="insertUserBatch" parameterType="java.util.List">
            insert into user values
            <foreach collection="list" item="item" separator =",">
                (#{item.id}, #{item.name})
            </foreach>
        </insert>
    
    </mapper>

5、User po 模型

package constxiong.po;
    
    /**
     * 用户表模型
     */
    public class User {
        private Integer id;
    
        private String name;
    
        private String mc;
    
        public User(Integer id, String name) {
            this.id = id;
            this.name = name;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getMc() {
            return mc;
        }
    
        public void setMc(String mc) {
            this.mc = mc;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", mc='" + mc + '\'' +
                    '}';
        }
    }

6、测试类

package constxiong;
    
    import constxiong.mapper.UserMapper;
    import constxiong.po.User;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.ExecutorType;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 测试 Mybatis 批量插入
     */
    public class Test {
    
        public static void main(String[] args) throws IOException {
            testBatchInsert();
        }
    
        /**
         * 测试 xml 配置方式分页
         */
        private static void testBatchInsert() throws IOException {
            String resource = "mybatis-config.xml";//xml配置文件路径
            InputStream inputStream = Resources.getResourceAsStream(resource);//读取配置
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//构建 SqlSessionFactory
            SqlSession sqlSession = sqlSessionFactory.openSession(true);//通过 SqlSessionfactory 获取 SqlSession
    
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);//通过 SqlSession 获取 Mapper 接口
    
            System.out.println("------userMapper.deleteUsers()------");
            //删除 user
            userMapper.deleteUsers();
            System.out.println();
    
            System.out.println("------userMapper.insertUser()------");
            //插入 user
            for (int i = 1; i <= 35; i++) {
                userMapper.insertUser(new User(i, "ConstXiong" + i));
            }
            System.out.println();
    
            System.out.println("------insert user batch by session------");
            insertUserBatchBySession(sqlSessionFactory);
            System.out.println();
    
            System.out.println("------insert user batch by sql------");
            List<User> userList = new ArrayList<>();
            for (int i = 46; i <= 55; i++) {
                userList.add(new User(i,"ConstXiong" + i));
            }
            userMapper.insertUserBatch(userList);
            System.out.println();
    
            System.out.println("------userMapper.selectUsers()------");
            //查询所有 user
            List<User> users = userMapper.selectUsers();
            System.out.println(users);
            System.out.println();
    
            //关闭 SqlSession
            sqlSession.close();
        }
    
        /**
         * 批量插入用户
         */
        private static void insertUserBatchBySession(SqlSessionFactory sqlSessionFactory) {
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            for (int i = 36; i <= 45; i++) {
                userMapper.insertUser(new User(i, "ConstXiong" + i));
            }
            sqlSession.commit();
            sqlSession.close();
        }
    
    }

--------------------

### 【Java学习资源】整理推荐 ###

*  [Java 8 Stream API Demo][]
 *  [Druid 使用][Druid]
 *  [借助 libreoffice，word 转 pdf 体验][libreoffice_word _ pdf]
 *  [Java 动态代理][Java]
 *  [openjdk jol 工具打印 Java 对象内存布局][openjdk jol _ Java]
 *  [Spring整合MyBatis][Spring_MyBatis]
 *  [MyBatis 自定义插件][MyBatis]
 *  [MyBatis 延迟加载][MyBatis 1]
 *  [MyBatis 1对1 1对多 关联查询][MyBatis 1_1 1_]
 *  [MyBatis insert 获取自增主键][MyBatis insert]
 *  [MyBatis 批量插入][MyBatis 2]
 *  [分页插件 Mybatis-PageHelper][Mybatis-PageHelper]
 *  [MyBatis 原生使用][MyBatis 3]
 *  [Sharding-JDBC 实现分库分表][Sharding-JDBC]
 *  [JDK1.8 中的日期与时间 API][JDK1.8 _ API]
 *  [Sharding-JDBC 实现读写分离][Sharding-JDBC 1]

--------------------

### 【Java面试题与答案】整理推荐 ###

*  [基础与语法][Link 1]
 *  [集合][Link 2]
 *  [网络编程][Link 3]
 *  [并发编程][Link 4]
 *  [Web][]
 *  [安全][Link 5]
 *  [设计模式][Link 6]
 *  [框架][Link 7]
 *  [算法与数据结构][Link 8]
 *  [异常][Link 9]
 *  [文件解析与生成][Link 10]
 *  [Linux][]
 *  [MySQL][]
 *  [Oracle][]
 *  [Redis][]
 *  [Dubbo][]

[Java 8 Stream API Demo]: https://www.javanav.com/val/5be3f821d00e4cc5b6177c9d0e5dd30f.html
[Druid]: https://www.javanav.com/val/79860880b84442cfb0f6f5e094d53105.html
[libreoffice_word _ pdf]: https://www.javanav.com/val/501ababe5c48493780ed23dbbc7b2cbc.html
[Java]: https://www.javanav.com/val/ab11038d7de64b4b8c0e384d1d4a9ab0.html
[openjdk jol _ Java]: https://www.javanav.com/val/66e564087e244b2f99f85091de029605.html
[Spring_MyBatis]: https://www.javanav.com/val/687c224b31a34d3c9f99fee67e3d5bcc.html
[MyBatis]: https://www.javanav.com/val/a5535343f9b545eda9665f03d62345ba.html
[MyBatis 1]: https://www.javanav.com/val/973ded541e9244aa8b3169b9fb869d60.html
[MyBatis 1_1 1_]: https://www.javanav.com/val/a9fe4555c1614b40b0da07afeabf2a66.html
[MyBatis insert]: https://www.javanav.com/val/3ac331d2674b4c108469cce54ae126f3.html
[MyBatis 2]: https://www.javanav.com/val/2d21b1463f2e4faeaf0def0c49df34a4.html
[Mybatis-PageHelper]: https://www.javanav.com/val/a8a8009f878d47dc9167bf20b9e0ab57.html
[MyBatis 3]: https://www.javanav.com/val/07f60d7946204b3b893abd5dc8bd2fa3.html
[Sharding-JDBC]: https://www.javanav.com/val/eefddd846ae845668daf9d798755c057.html
[JDK1.8 _ API]: https://www.javanav.com/val/fefecf2f089e48b294708e99954c57a1.html
[Sharding-JDBC 1]: https://www.javanav.com/val/f05fe12cb8fd444aaa59fb7f747a3224.html
[Link 1]: https://www.javanav.com/interview/93b0069472fd479393006c0e73043fc4.html
[Link 2]: https://www.javanav.com/interview/4147d95e693945e7a50345fd33ec817e.html
[Link 3]: https://www.javanav.com/interview/68a57ffc78de4133bb890bdd502ac418.html
[Link 4]: https://www.javanav.com/interview/36a24fa3258e45c89873f474ce914912.html
[Web]: https://www.javanav.com/interview/4e27cb63adb440c6825a159d35cf4010.html
[Link 5]: https://www.javanav.com/interview/03affb2ce3f04fda8853f714e5764906.html
[Link 6]: https://www.javanav.com/interview/07a32a9ff17a4006a53550ce658cafed.html
[Link 7]: https://www.javanav.com/interview/5c466456e9b54ab0a749b5948e51f1d3.html
[Link 8]: https://www.javanav.com/interview/943f917041ee4f05add24539f5b861b2.html
[Link 9]: https://www.javanav.com/interview/d3f058cfc05d4c6dadd821b622e84d36.html
[Link 10]: https://www.javanav.com/interview/67a50996c28f418e83b109ea42fc3eff.html
[Linux]: https://www.javanav.com/interview/00cccb81729540d1923bd0a0ef49533a.html
[MySQL]: https://www.javanav.com/interview/d311e78502a845f1a4bb307bd6aec2b3.html
[Oracle]: https://www.javanav.com/interview/5f29e9677edf4a16be3dce30d257c9ab.html
[Redis]: https://www.javanav.com/interview/066c62b3af4d4827b2243a822c3084a9.html
[Dubbo]: https://www.javanav.com/interview/9a3e7a7740324757abaab953f7ebc455.html