使用MyBatis实现批量操作

批量操作DML

//Connection - SqlSession操作Mybatis
        //conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //reader->sqlSession

        //可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader,"devOracle");
        SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);

        StudentMapper studentMapper=session.getMapper(StudentMapper.class);//接口

        long start=System.currentTimeMillis();
        for(int i=0;i<100000;i++){
            Student student=new Student((int)(Math.random()*9000)+1000,null,105,"xx");
            studentMapper.addStudent(student);
        }
        long end=System.currentTimeMillis();
        System.out.println(end-start);
        session.commit();//手动提交
        session.close();

 日志

　　1.loj4j-1.2.17.jar

　　2.配置conf.xml

　　<settings>
        <setting name="jdbcTypeForNull" value="NULL"/>
        <setting name="logImpl" value="LOG4J"/>
   </settings>

 　　3.日志配置文件log4j.properties

log4j.rootLogger=DEBUG,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

 

 

BATCH :预编译SQL一次，其余DML只需要设置参数值即可(推荐)

没有BATCH：预编译N次

 

 

 

 不推荐的方式:拼接SQL

　　

Oracle:批量插入

　　a.create table 表 select ... from 旧表

　　b.insert into 表（...） select ... from 表

　　c.begin　..(DML)..end

　　d.数据泵、SQL Loader、外部表

 

 以begin　..(DML)..end为例

begin
　　insert into student1(stuno,stuname) values(1,'zs');
　　insert into student1(stuno,stuname) values(2,'ls');
end;
 /

 

 核心:将SQL拼接成Oracle能够执行的SQL;

collection的参数必须时collection或list

src\org\myy\mapper\studentMapper.xml

    <insert id="addStudentOracle" databaseId="oracle">
        <foreach collection="list" open="begin" close="end;" item="student">
            insert into student1(stuno,stuname) values(#{student.stuNo},#{student.stuName});
        </foreach>
    </insert>

src\org\myy\mapper\StudentMapper.java

    void addStudentOracle(List<Student> students);

src\org\myy\test\Test.java

 //Connection - SqlSession操作Mybatis
        //conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //reader->sqlSession

        //可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader,"devOracle");
        SqlSession session = sessionFactory.openSession();

        StudentMapper studentMapper=session.getMapper(StudentMapper.class);//接口

        List<Student> students=new ArrayList<>();
        students.add(new Student(10,"zs"));
        students.add(new Student(20,"ls"));

        studentMapper.addStudentOracle(students);
        session.commit();//手动提交
        session.close();

 

 MySQL:批量插入　

insert into student1(stuno,stuname) values(100,'zs'),(200,'ls');

 src\org\myy\mapper\studentMapper.xml

    <insert id="addStudentMysql" databaseId="mysql">
            insert into student1(stuno,stuname) values
            <foreach collection="list" item="student" separator=","  close=";">
                (#{student.stuNo},#{student.stuName})
            </foreach>
    </insert>

 

 src\org\myy\mapper\StudentMapper.java

    void addStudentMysql(List<Student> students);

 

 src\org\myy\test\Test.java

        //Connection - SqlSession操作Mybatis
        //conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //reader->sqlSession

        //可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader,"devMysql");
        SqlSession session = sessionFactory.openSession();

        StudentMapper studentMapper=session.getMapper(StudentMapper.class);//接口

        List<Student> students=new ArrayList<>();
        students.add(new Student(101,"zs"));
        students.add(new Student(201,"ls"));

        studentMapper.addStudentMysql(students);
        session.commit();//手动提交
        session.close();

 

这种批量插入方式不推荐:

1.没用用到mybatis对批量插入的支持

2.不适合数据迁移

3.如果大量数据，则会将拼接的Sql语句拉的很长，而部分数据库对SQL语句的长度有限制

 

insert into ... values(...);

insert into ... values(...);

insert into ... values(...);

insert into ... values(...);

src\db.properties

#mysql
mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://127.0.0.1:3306/myy?allowMultiQueries=true　　#允许多条语句
mysql.username=root
mysql.password=root

 

 src\org\myy\mapper\studentMapper.xml

    <insert id="addStudentMysql2" databaseId="mysql">
    <foreach collection="list" item="student">
        insert into student1(stuno,stuname) values(#{student.stuNo},#{student.stuName});
    </foreach>
    </insert>

 

 src\org\myy\mapper\StudentMapper.java

    void addStudentMysql2(List<Student> students);

 

 src\org\myy\test\Test.java

//Connection - SqlSession操作Mybatis
        //conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //reader->sqlSession

        //可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader,"devMysql");
        SqlSession session = sessionFactory.openSession();

        StudentMapper studentMapper=session.getMapper(StudentMapper.class);//接口

        List<Student> students=new ArrayList<>();
        students.add(new Student(301,"zs"));
        students.add(new Student(302,"ls"));

        studentMapper.addStudentMysql2(students);
        session.commit();//手动提交
        session.close();

 

 

其他:调用存储过程、存储函数

.....