你的位置:首页 > Java教程

[Java教程]Java通过Mybatis实现批量插入数据到Oracle中


最近项目中遇到一个问题:导入数据到后台并将数据插入到数据库中,导入的数据量有上万条数据,考虑采用批量插入数据的方式;

结合网上资料,写了个小demo,文章末尾附上demo下载地址

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

      

2、新建数据库表:ACCOUNT_INFO

  

1 CREATE TABLE ACCOUNT_INFO (2   "ID" NUMBER(12) NOT NULL ,3   "USERNAME" VARCHAR2(64 BYTE) NULL ,4   "PASSWORD" VARCHAR2(64 BYTE) NULL ,5   "GENDER" CHAR(1 BYTE) NULL ,6   "EMAIL" VARCHAR2(64 BYTE) NULL ,7   "CREATE_DATE" DATE NULL 8 )

3、创建AccountInfo实体类:

 1 package com.oracle.entity; 2  3 import java.sql.Date; 4  5 public class AccountInfo { 6   private Long id; 7   private String userName; 8   private String password; 9   private String gender;10   private String email;11   private Date createDate;12 13   public Long getId() {14     return id;15   }16 17   public void setId(Long id) {18     this.id = id;19   }20 21   public String getUserName() {22     return userName;23   }24 25   public void setUserName(String userName) {26     this.userName = userName;27   }28 29   public String getPassword() {30     return password;31   }32 33   public void setPassword(String password) {34     this.password = password;35   }36 37   public String getGender() {38     return gender;39   }40 41   public void setGender(String gender) {42     this.gender = gender;43   }44 45   public String getEmail() {46     return email;47   }48 49   public void setEmail(String email) {50     this.email = email;51   }52 53   public Date getCreateDate() {54     return createDate;55   }56 57   public void setCreateDate(Date createDate) {58     this.createDate = createDate;59   }60 61   @Override62   public String toString() {63     return "AccountInfo [id=" + id + ", userName=" + userName64         + ", password=" + password + ", gender=" + gender + ", email="65         + email + ", createDate=" + createDate + "]";66   }67 68 }

4、新建接口映射类:AccountInfoMapper.java

 1 package com.oracle.mapper; 2  3 import java.util.List; 4  5 import com.oracle.entity.AccountInfo; 6  7 public interface AccountInfoMapper { 8   /** 9    * 查询所有的数据10    * @return11   */12   List<AccountInfo> queryAllAccountInfo();13   14   /**15    * 批量插入数据16    * 17    * @param accountInfoList18    * @return19   */20   int batchInsertAccountInfo(List<AccountInfo> accountInfoList);21 }

 

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

 1 <??> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4   <environments default="development"> 5     <environment id="development"> 6       <transactionManager type="JDBC" /> 7       <dataSource type="POOLED"> 8         <property name="driver" value="oracle.jdbc.driver.OracleDriver" /> 9         <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl" />10         <property name="username" value="xxx" />11         <property name="password" value="xxx" />12       </dataSource>13     </environment>14   </environments>15   <mappers>16     <mapper resource="config/AccountInfoMapper. />17   </mappers>18 </configuration>

6、创建接口映射配置文件:AccountInfoMapper.

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)

 

 1 <??> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.oracle.mapper.AccountInfoMapper"><!-- 接口的全类名 --> 4   <!-- type:实体类的全类名 --> 5   <resultMap id="BaseResultMap" type="com.oracle.entity.AccountInfo"> 6     <id column="ID" property="id" jdbcType="DECIMAL" /> 7     <result column="USERNAME" property="userName" jdbcType="VARCHAR" /> 8     <result column="PASSWORD" property="password" jdbcType="VARCHAR" /> 9     <result column="GENDER" property="gender" jdbcType="CHAR" />10     <result column="EMAIL" property="email" jdbcType="VARCHAR" />11     <result column="CREATE_DATE" property="createDate" jdbcType="DATE" />12   </resultMap>13   <!-- id 跟接口中的方法名称保持一致 -->14   <select id="queryAllAccountInfo" resultMap="BaseResultMap">15     select ID,16     USERNAME,PASSWORD,17     GENDER, EMAIL, CREATE_DATE from ACCOUNT_INFO18   </select>19   <insert id="batchInsertAccountInfo" parameterType="java.util.List">20     INSERT INTO ACCOUNT_INFO(ID, USERNAME,PASSWORD,GENDER, EMAIL,CREATE_DATE)21     (22     <foreach collection="list" index="" item="accountInfo"23       separator="union all">24       select25       #{accountInfo.id},26       #{accountInfo.userName},27       #{accountInfo.password},28       #{accountInfo.gender},29       #{accountInfo.email},30       #{accountInfo.createDate}31       from dual32     </foreach>33     )34   </insert>35 </mapper>

7、编写测试类:

 1 package com.oracle.test; 2  3 import java.io.InputStream; 4 import java.sql.Date; 5 import java.util.ArrayList; 6 import java.util.List; 7  8 import org.apache.ibatis.io.Resources; 9 import org.apache.ibatis.session.SqlSession;10 import org.apache.ibatis.session.SqlSessionFactory;11 import org.apache.ibatis.session.SqlSessionFactoryBuilder;12 13 import com.oracle.entity.AccountInfo;14 import com.oracle.mapper.AccountInfoMapper;15 16 public class MybatisTest {17   public static void main(String[] args) throws Exception {18     String resource = "config/mybatis-configuration.;19     InputStream inputStream = Resources.getResourceAsStream(resource);20     SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder()21         .build(inputStream);22     SqlSession session = sessionFactory.openSession();23     AccountInfoMapper mapper = session.getMapper(AccountInfoMapper.class);24     List<AccountInfo> accountInfoList = mapper.queryAllAccountInfo();25     if (accountInfoList == null) {26       System.out.println("The result is null.");27     } else {28       for (AccountInfo personInfo : accountInfoList) {29         System.out.println(personInfo);30       }31     }32     mapper.batchInsertAccountInfo(generateData());33     session.commit();34   }35   36   static List<AccountInfo> generateData(){37     List<AccountInfo> result = new ArrayList<AccountInfo>();38     AccountInfo account = new AccountInfo();39     account.setId(3L);40     account.setUserName("zhangsanfeng");41     account.setPassword("123456");42     account.setGender("1");43     account.setEmail("zhangsanfeng@wudang.com");44     account.setCreateDate(new Date(System.currentTimeMillis()));45     result.add(account);46     47     account = new AccountInfo();48     account.setId(4L);49     account.setUserName("zhouzhiruo");50     account.setPassword("zhangwuji");51     account.setGender("0");52     account.setEmail("zhouzhiruo@emei.com");53     account.setCreateDate(new Date(System.currentTimeMillis()));54     result.add(account);55     56     account = new AccountInfo();57     account.setId(5L);58     account.setUserName("zhaomin");59     account.setPassword("zhangwuji");60     account.setGender("0");61     account.setEmail("zhaomin@yuan.com");62     account.setCreateDate(new Date(System.currentTimeMillis()));63     result.add(account);64     return result;65   }66 }

 项目下载地址:http://download.csdn.net/detail/clqyhy/9553854