Mybatis+Oracle批处理

Mybatis+Oracle批处理

1. 批处理 插入
很多时候都涉及到一系列数据的插入，通过mybatis的动态sql语句能够很好的解决这个问题，当然，oracle已经提供了批插入的语句：

insert into students 
   select id,name ,email ,sdate from dual union 
   select id,name ,email ,sdate from dual union 
   select id,name ,email ,sdate from dual 

利用mybatis动态sql语言的写法：

        insert into students 
        
            select #{stus.id},#{stus.name},#{stus.email},#{stus.sdate} from dual
        
    

下面看测试：

/**
     * 批处理： 插入一组数据
     */
    @Test
    public void TestbatchInsertStudents(){
        List stus=Arrays.asList(new Student[]{
                new Student("Rindy_1",9770,"15211433541013",new Date()),
                new Student("Rindy_2",97710,"1521143546392@163.com",new Date()),
                new Student("Rindy_3",97720,"152114743366658",new Date()),
                new Student("Rindy_4",97730,"1527395357437",new Date()),
                new Student("Rindy_5",97740,"132126835435644",new Date()),
                new Student("Rindy_6",97750,"152114524322140",new Date()),
                new Student("Rindy_7",97760,"15873242923860",new Date()),
                new Student("Rindy_8",97770,"15096242043460",new Date())

        });
        int rows=stum.batchInsertStudents( stus );
        System.out.println( rows );
        assertEquals(rows,8);
    }

测试成功


批量删除：

        delete from students where stud_id= #{id}
    

测试成功


批量更新比较麻烦，我们先来回顾一下 oracle的更新：
UPDATE 表名 set 列名 = 新值[, 列名 = 新值[…]] [WHERE condition_expression];
参数多组，批量操作可采用 case when then语句实现

    
        update students 
        
        
            when stud_id=#{stus.id} then #{stus.name}
        
        
            when stud_id=#{stus.id} then #{stus.email}
        
        
            
            
                when stud_id=#{stus.id} then #{stus.sdate}
            
        

        
        
            #{stus.id}
        

    

测试：

/**
     * 批处理： 更新一组数据
     */
    @Test
    public void TestbatchUpdateStudents(){
        List stus=Arrays.asList(new Student[]{
                new Student("Rindy_1_update",9770,"15211423431013_update",new Date()),
                new Student("Rindy_2_update",97710,"15211433446392@163.com",new Date()),
                new Student("Rindy_3_update",97720,"1524321231476658_update",new Date()),
                new Student("Rindy_4_update",97730,"1527395324327437_update",new Date()),
                new Student("Rindy_5_update",97740,"13212268235644_update",new Date()),
                new Student("Rindy_6_update",97750,"152114522432140_update",new Date()),
                new Student("Rindy_7_update",97760,"1587233922433860_update",new Date()),
                new Student("Rindy_8_update",97770,"1502496032443460_update",new Date())

        });
        int rows=stum.batchUpdateStudents02( stus );
        System.out.println( rows );
        assertEquals(rows,8);
    }

测试结果


下面是是本次案例的辅助代码

DROP TABLE STUDENTS;
drop sequence seq_stu_id;

CREATE TABLE STUDENTS  
(  
stud_id integer PRIMARY KEY,  
name varchar2(50) NOT NULL,  
email varchar2(50) ,  
sdate date
);

create sequence seq_stu_id ;

insert into students(name,email,dob) values('Student1','student1@gmail.com', to_date('1983-06-25', 'yyyy-MM-dd');  

insert into students(name,email,dob) values('Student2','student2@gmail.com', to_date('1985-06-25', 'yyyy-MM-dd'); 

2.java entity

package com.rindy.maven.entity;

import java.util.Date;

public class Student {
    private String name;
    private Integer id;
    private String email;
    private Date sdate;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getSdate() {
        return sdate;
    }
    public void setSdate(Date sdate) {
        this.sdate = sdate;
    }
    public Student(String name, Integer id, String email, Date sdate) {
        super();
        this.name = name;
        this.id = id;
        this.email = email;
        this.sdate = sdate;
    }
    public Student() {
        super();
    }

    public Student(String name, String email, Date sdate) {
        super();
        this.name = name;
        this.email = email;
        this.sdate = sdate;
    }
    @Override
    public String toString() {
        return "Student [name=" + name + ", id=" + id + ", email=" + email
                + ", sdate=" + sdate + "] \n";
    }

}

3.MybatisUtil 工具类

package com.rindy.maven.utils;

import java.io.IOException;
import java.io.InputStream;

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.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class MybatisUtil {
    private static Logger log=LoggerFactory.getLogger(MybatisUtil.class);
    private static SqlSessionFactory factory;
    static{
        try {
            log.debug("加载mybatis.xml的配置文件");
            InputStream in =Resources.getResourceAsStream("mybatis.xml");
            log.debug("加载mybatis.xml的配置文件成功");


            log.debug("通过配置文件的数据构建sql session工厂");
            factory=new SqlSessionFactoryBuilder().build(in);
            log.debug("通过配置文件的数据构建sql session工厂 【成功】"  );

            log.debug("生产sqlsession 工厂对象");
        } catch (IOException e) {
            e.printStackTrace();
            log.debug("加载mybatis.xml的配置文件失败",e);
        }

    }

    public static SqlSession getSession(){
        //原来这么些
        //InputStream in=MybatisUtil.class.getClassLoader().getResourceAsStream("mybatis.xml");

        //mybatis这么写
        SqlSession session=null;
        session=factory.openSession();
        log.debug("生产sqlsession 工厂对象 成功");
        return session;
    }

    /**
     * 
     * @param isAutoCommit :true: 自动提交事务， false 手动事务
     * @return
     */
    public static SqlSession getSession(boolean isAutoCommit){
        SqlSession session=null;
        session=factory.openSession( isAutoCommit );
        log.debug("生产sqlsession 工厂对象 成功");
        return session;
    }


}

sql语句注意经常复习。