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

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 jaExbxDBMPva.util.List;

import com.oracle.entity.AccountInfo;

public interface AccountInfoMapper {

/**

* 查询所有的数据

* @return

*/

List queryAllAccountInfo();

/**

* 批量插入数据

*

* @param accountInfoList

* @return

*/

int batchInsertAccountInfo(List accountInfoList);

}

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

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)

select ID,

USERNAME,PASSWORD,

GENDER, EMAIL, CREATE_DATE from ACCOUNT_INFO

INSERT INTO ACCOUNT_INFO(ID, USERNAME,PASSWORD,GENDER, EMAIL,CREATE_DATE)

(

separator="union all">

select

#{accountInfo.id},

#{accountInfo.userName},

#{accountInfo.password},

#{accountInfo.gender},

#{accountInfo.email},

#{accountInfo.createDate}

from dual

separator="union all">

select

#{accountInfo.id},

#{accountInfo.userName},

#{accountInfo.password},

#{accountInfo.gender},

#{accountInfo.email},

#{accountInfo.createDate}

from dual

)

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 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 generateData(){

List result = new ArrayList();

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;

}

}

源码下载：http://xiazai.jb51.net/201606/yuanma/java-oracle(jb51.net).rar

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。