mybatis+oracle Batch Processing

1. Batch Insert
A lot of times involves inserting a series of data, which can be well solved by MyBatis Dynamic SQL statements, and 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 

Use the MyBatis dynamic SQL language:

<!--collection has collection type collection can specify parametertype= "list" --<!--arrays have no array parameter type collection= "Array" parametertype specifies the type of data 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>

Here's a look at the test:

/** * Batch processing: Inserting a set of data */    @Test     Public void testbatchinsertstudents() {list<student> stus=arrays.aslist (Newstudent[]{NewStudent ("Rindy_1",9770,"15211433541013",NewDate ()),NewStudent ("Rindy_2",97710,"[email protected]",NewDate ()),NewStudent ("Rindy_3",97720,"152114743366658",NewDate ()),NewStudent ("Rindy_4",97730,"1527395357437",NewDate ()),NewStudent ("Rindy_5",97740,"132126835435644",NewDate ()),NewStudent ("Rindy_6",97750,"152114524322140",NewDate ()),NewStudent ("Rindy_7",97760,"15873242923860",NewDate ()),NewStudent ("Rindy_8",97770,"15096242043460",NewDate ())});intRows=stum.batchinsertstudents (Stus);        SYSTEM.OUT.PRINTLN (rows); Assertequals (Rows,8); }

Test success

Bulk Delete:

<delete id="deleteStuById" parameterType="int">        deletefrom#{id}    </delete>

Test success

Batch update is a hassle, let's review the Oracle update first:
UPDATE table Name set column name = new value [, Column name = new value [...]] [WHERE condition_expression];
Multiple sets of parameters, batch operations can be implemented with case and then statements

<!--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="List" item="Stus"open=" Sdate = case " close=" End, ">              <!--fault-tolerant processing, when the property value is empty, does not update--            <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: Updating a set of data */    @Test     Public void testbatchupdatestudents() {list<student> stus=arrays.aslist (Newstudent[]{NewStudent ("Rindy_1_update",9770,"15211423431013_update",NewDate ()),NewStudent ("Rindy_2_update",97710,"[email protected]",NewDate ()),NewStudent ("Rindy_3_update",97720,"1524321231476658_update",NewDate ()),NewStudent ("Rindy_4_update",97730,"1527395324327437_update",NewDate ()),NewStudent ("Rindy_5_update",97740,"13212268235644_update",NewDate ()),NewStudent ("Rindy_6_update",97750,"152114522432140_update",NewDate ()),NewStudent ("Rindy_7_update",97760,"1587233922433860_update",NewDate ()),NewStudent ("Rindy_8_update",97770,"1502496032443460_update",NewDate ())});intROWS=STUM.BATCHUPDATESTUDENTS02 (Stus);        SYSTEM.OUT.PRINTLN (rows); Assertequals (Rows,8); }

Test results

Here is the auxiliary code for this case

1. Database Scripts

 DROP TABLE STUDENTS; drop sequence seq_stu_id; CREATE TABLE STUDENTS (stud_id integer PRIMARY KEY, name Varchar2 (
     
      50) 
       not 
      NULL, email varchar2 (
      ), sdate 
      date);
      Create sequence seq_stu_id; Insert  into students (NAME,EMAIL,DOB) values(' Student1 ',' [email Protected] ', to_date (' 1983-06-25 ', ' yyyy-mm-dd ');   Insert  into students (NAME,EMAIL,DOB) values(' Student2 ',' [email Protected] ', to_date (' 1985-06-25 ', ' yyyy-mm-dd '); 

2.java entity

 Packagecom.rindy.maven.entity;ImportJava.util.Date; Public  class Student {    PrivateString name;PrivateInteger ID;PrivateString email;PrivateDate sdate; PublicStringGetName() {returnName } Public void SetName(String name) { This. name = name; } PublicIntegergetId() {returnId } Public void setId(Integer ID) { This. id = ID; } PublicStringGetemail() {returnEmail } Public void Setemail(String email) { This. email = email; } PublicDategetsdate() {returnSdate; } 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     PublicStringtoString() {return "Student [name="+ name +", id="+ ID +", email="+ Email +", sdate="+ Sdate +"] \ n"; }}

3.MybatisUtil Tool Class

 PackageCom.rindy.maven.utils;ImportJava.io.IOException;ImportJava.io.InputStream;ImportOrg.apache.ibatis.io.Resources;ImportOrg.apache.ibatis.session.SqlSession;ImportOrg.apache.ibatis.session.SqlSessionFactory;ImportOrg.apache.ibatis.session.SqlSessionFactoryBuilder;ImportOrg.slf4j.Logger;ImportOrg.slf4j.LoggerFactory; Public  class mybatisutil {    Private StaticLogger Log=loggerfactory.getlogger (Mybatisutil.class);Private StaticSqlsessionfactory Factory;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 ("Building SQL Session factory with configuration file Data "); factory=NewSqlsessionfactorybuilder (). build (in); Log.debug ("Build SQL Session Factory" through configuration file data "Success "); Log.debug ("Production of sqlsession plant objects"); }Catch(IOException e)            {E.printstacktrace (); Log.debug ("Failed to load mybatis.xml configuration file", e); }    } Public StaticSqlsessiongetsession(){//Original        //inputstream In=mybatisutil.class.getclassloader (). getResourceAsStream ("Mybatis.xml");        //mybatis said so .Sqlsession session=NULL;        Session=factory.opensession (); Log.debug ("Production sqlsession Factory object Success");returnSession }/** * * @param isautocommit:true: Auto COMMIT TRANSACTION, False manual transaction * @return * *     Public StaticSqlsessiongetsession(BooleanIsautocommit) {sqlsession session=NULL;        Session=factory.opensession (Isautocommit); Log.debug ("Production sqlsession Factory object Success");returnSession }}

SQL statements are often reviewed.

Copyright NOTICE: This article for Bo Master original article, thank you for reference! Where there is a problem, you are welcome to correct and progress together.

mybatis+oracle Batch Processing