Mybatis + Oracle batch processing, mybatisoracle

Mybatis + Oracle batch processing, mybatisoracle

1. Batch insert
In many cases, data insertion is involved. The MySQL dynamic SQL statement can solve this problem well. Of course, oracle has provided the batch insert statement:

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 

Using mybatis dynamic SQL:

<! -- The set has a collection type. You can specify parameterType = "list" --> <! -- The array does not have the array parameter type. collection = "array" parameterType indicates the Data Type stored in the array --> <insert id = "batchInsertStudents" parameterType = "list"> insert into students <foreach collection = "list" separator = "union" item = "stus"> select # {stus. id}, # {stus. name },# {stus. email}, # {stus. sdate} from dual </foreach> </insert>

See the test below:

/*** Batch processing: Insert a group of data */@ Test public void TestbatchInsertStudents () {List <Student> 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 );}

Test successful

Batch delete:

<delete id="deleteStuById" parameterType="int">        delete from students where stud_id= #{id}    </delete>

Test successful

Batch update is troublesome. Let's review oracle updates first:
UPDATE table name set column name = new value [, column name = new value […] [WHERE condition_expression];
Multiple groups of parameters. Batch operations can be performed using the case when then statement.

<! -- Batch processing: update students set xxx where id --> <update id = "batchUpdateStudents02" parameterType = "list"> update students <set> <foreach collection = "list" item = "stus" open = "name = case" close = "end, "> when stud_id = # {stus. id} then # {stus. name} </foreach> <foreach collection = "list" item = "stus" open = "email = case" close = "end,"> when stud_id = # {stus. id} then # {stus. email} </foreach> <foreach collection = "li St "item =" stus "open =" sdate = case "close =" end, "> <! -- Fault tolerance Processing. When the attribute value is null, It is not updated --> <if test = "# {stus. sdate }! = Null "> when stud_id = # {stus. id} then # {stus. sdate} </if> </foreach> </set> <foreach collection = "list" open = "where stud_id in (" separator = ", "item =" stus "close =") ">#{ stus. id} </foreach> </update>

Test:

/*** Batch processing: update a group of data */@ Test public void TestbatchUpdateStudents () {List <Student> 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, "158723392243366_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 );}

Test Results

The following is the auxiliary code of this case.

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 tool class

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; static {try {log. debug ("load mybatis. xml configuration file "); InputStream in = Resources. getResourceAsStream ("mybatis. xml "); log. debug ("load mybatis. xml configuration file succeeded "); log. debug ("build an SQL session factory through the data in the configuration file"); factory = new SqlSessionFactoryBuilder (). build (in); log. debug ("build an SQL session factory [successful] through the data in the configuration file"); log. debug ("production sqlsession factory object");} catch (IOException e) {e. printStackTrace (); log. debug ("load mybatis. xml configuration file failed ", e) ;}} public static SqlSession getSession () {// The Original: // InputStream in = MybatisUtil. class. getClassLoader (). getResourceAsStream ("mybatis. xml "); // write SqlSession session = null like mybatis; session = factory. openSession (); log. debug ("production sqlsession factory object succeeded"); return session;}/***** @ param isAutoCommit: true: automatic transaction commit, false manual transaction * @ return */public static SqlSession getSession (boolean isAutoCommit) {SqlSession session = null; session = factory. openSession (isAutoCommit); log. debug ("sqlsession factory object production succeeded"); return session ;}}

Pay attention to frequent review of SQL statements.

Copyright Disclaimer: this is an original article by the blogger. Thank you for your reference! If you have any problems, please correct them and make progress together.