Mybatis+Oracle批處理，mybatisoracle

Mybatis+Oracle批處理，mybatisoracle

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語言的寫法：

<!-- 集合 有集合類型  collection 可以指定 parameterType="list"  --><!-- 數組沒有 array 的參數類型  collection="array"  parameterType 指定是數組裡面存放的資料類型 --><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>

下面看測試：

/**     * 批處理： 插入一組資料     */    @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);    }

測試成功

大量刪除：

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

測試成功

批次更新比較麻煩，我們先來回顧一下 oracle的更新：
UPDATE 表名 set 列名 = 新值[, 列名 = 新值[…]] [WHERE condition_expression];
參數多組，大量操作可採用 case when then語句實現

<!-- 批處理： 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,">            <!-- 容錯處理，當屬性值為空白的時候，不更新    -->            <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    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,"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語句注意經常複習。

著作權聲明：本文為博主原創文章，謝謝參考！有問題的地方，歡迎糾正，一起進步。