欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-12 21:19:50
最近项目中遇到一个问题:导入数据到后台并将数据插入到数据库中,导入的数据量有上万条数据,考虑采用批量插入数据的方式;  结合网上资料,写了个小demo,文章末尾...

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

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

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

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("zhangsanfeng@wudang.com");
 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("zhouzhiruo@emei.com");
 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("zhaomin@yuan.com");
 account.setcreatedate(new date(system.currenttimemillis()));
 result.add(account);
 return result;
 }
}

源码下载:

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