Java实现mybatis批量插入数据到Oracle

最近项目中遇到一个问题：导入数据到后台并将数据插入到数据库中，导入的数据量有上万条数据，考虑采用批量插入数据的方式；
 结合网上资料，写了个小demo，文章末尾附上demo下载地址 

1、新建项目：项目目录结构如下图所示，添加相应的jar包

2、新建数据库表：account_info

create table account_info (
 "id" number(12) not null ,
 "username" varchar2(64 byte) null ,
 "password" varchar2(64 byte) null ,
 "gender" char(1 byte) null ,
 "email" varchar2(64 byte) null ,
 "create_date" date null 
)

3、创建accountinfo实体类：

package com.oracle.entity;

import java.sql.date;

public class accountinfo {
 private long id;
 private string username;
 private string password;
 private string gender;
 private string email;
 private date createdate;

 public long getid() {
 return id;
 }

 public void setid(long id) {
 this.id = id;
 }

 public string getusername() {
 return username;
 }

 public void setusername(string username) {
 this.username = username;
 }

 public string getpassword() {
 return password;
 }

 public void setpassword(string password) {
 this.password = password;
 }

 public string getgender() {
 return gender;
 }

 public void setgender(string gender) {
 this.gender = gender;
 }

 public string getemail() {
 return email;
 }

 public void setemail(string email) {
 this.email = email;
 }

 public date getcreatedate() {
 return createdate;
 }

 public void setcreatedate(date createdate) {
 this.createdate = createdate;
 }

 @override
 public string tostring() {
 return "accountinfo [id=" + id + ", username=" + username
 + ", password=" + password + ", gender=" + gender + ", email="
 + email + ", createdate=" + createdate + "]";
 }

}

4、新建接口映射类：accountinfomapper.java

package com.oracle.mapper;

import java.util.list;

import com.oracle.entity.accountinfo;

public interface accountinfomapper {
 /**
 * 查询所有的数据
 * @return
 */
 list<accountinfo> queryallaccountinfo();
 
 /**
 * 批量插入数据
 * 
 * @param accountinfolist
 * @return
 */
 int batchinsertaccountinfo(list<accountinfo> accountinfolist);
}

5、创建mybatis配置文件：mybatis-configuration.xml 

<?xml version="1.0" encoding="utf-8"?>
<!doctype configuration public "-//mybatis.org//dtd config 3.0//en" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
 <environments default="development">
 <environment id="development">
 <transactionmanager type="jdbc" />
 <datasource type="pooled">
 <property name="driver" value="oracle.jdbc.driver.oracledriver" />
 <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl" />
 <property name="username" value="xxx" />
 <property name="password" value="xxx" />
 </datasource>
 </environment>
 </environments>
 <mappers>
 <mapper resource="config/accountinfomapper.xml" />
 </mappers>
</configuration>

6、创建接口映射配置文件：accountinfomapper.xml
 oracle的批量插入数据库跟mysql不一样，
 mysql：
 

 insert into account_info(id, username,password,gender, email,create_date)values(,,,,,,)(,,,,,,,)

oracle：
 

insert into account_info(id, username,password,gender, email,create_date) (select 1,,,,,, from dual union all select 1,,,,,, from dual)

 

<?xml version="1.0" encoding="utf-8" ?>
<!doctype mapper public "-//mybatis.org//dtd mapper 3.0//en" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.oracle.mapper.accountinfomapper"><!-- 接口的全类名 -->
 <!-- type:实体类的全类名 -->
 <resultmap id="baseresultmap" type="com.oracle.entity.accountinfo">
 <id column="id" property="id" jdbctype="decimal" />
 <result column="username" property="username" jdbctype="varchar" />
 <result column="password" property="password" jdbctype="varchar" />
 <result column="gender" property="gender" jdbctype="char" />
 <result column="email" property="email" jdbctype="varchar" />
 <result column="create_date" property="createdate" jdbctype="date" />
 </resultmap>
 <!-- id 跟接口中的方法名称保持一致 -->
 <select id="queryallaccountinfo" resultmap="baseresultmap">
 select id,
 username,password,
 gender, email, create_date from account_info
 </select>
 <insert id="batchinsertaccountinfo" parametertype="java.util.list">
 insert into account_info(id, username,password,gender, email,create_date)
 (
 <foreach collection="list" index="" item="accountinfo"
 separator="union all">
 select
 #{accountinfo.id},
 #{accountinfo.username},
 #{accountinfo.password},
 #{accountinfo.gender},
 #{accountinfo.email},
 #{accountinfo.createdate}
 from dual
 </foreach>
 )
 </insert>
</mapper>

7、编写测试类：

package com.oracle.test;

import java.io.inputstream;
import java.sql.date;
import java.util.arraylist;
import java.util.list;

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 com.oracle.entity.accountinfo;
import com.oracle.mapper.accountinfomapper;

public class mybatistest {
 public static void main(string[] args) throws exception {
 string resource = "config/mybatis-configuration.xml";
 inputstream inputstream = resources.getresourceasstream(resource);
 sqlsessionfactory sessionfactory = new sqlsessionfactorybuilder()
 .build(inputstream);
 sqlsession session = sessionfactory.opensession();
 accountinfomapper mapper = session.getmapper(accountinfomapper.class);
 list<accountinfo> accountinfolist = mapper.queryallaccountinfo();
 if (accountinfolist == null) {
 system.out.println("the result is null.");
 } else {
 for (accountinfo personinfo : accountinfolist) {
 system.out.println(personinfo);
 }
 }
 mapper.batchinsertaccountinfo(generatedata());
 session.commit();
 }
 
 static list<accountinfo> generatedata(){
 list<accountinfo> result = new arraylist<accountinfo>();
 accountinfo account = new accountinfo();
 account.setid(3l);
 account.setusername("zhangsanfeng");
 account.setpassword("123456");
 account.setgender("1");
 account.setemail("[e-mail]");
 account.setcreatedate(new date(system.currenttimemillis()));
 result.add(account);
 
 account = new accountinfo();
 account.setid(4l);
 account.setusername("zhouzhiruo");
 account.setpassword("zhangwuji");
 account.setgender("0");
 account.setemail("[e-mail]");
 account.setcreatedate(new date(system.currenttimemillis()));
 result.add(account);
 
 account = new accountinfo();
 account.setid(5l);
 account.setusername("zhaomin");
 account.setpassword("zhangwuji");
 account.setgender("0");
 account.setemail("[e-mail]");
 account.setcreatedate(new date(system.currenttimemillis()));
 result.add(account);
 return result;
 }
}

源码下载：

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。