java批处理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语句注意常常复习。