软件开发|编程技术|编程代码|编程入门先学什么—程序设计语言

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 */ ListAccountInfo queryAllAccountInfo();  /** * 批量插入数据 *  * @param accountInfoList * @return */ int batchInsertAccountInfo(ListAccountInfo 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); ListAccountInfo 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 ListAccountInfo generateData(){ ListAccountInfo result = new ArrayListAccountInfo(); 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

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