最近項目中遇到一個問題：導入數據到後台並將數據插入到數據庫中，導入的數據量有上萬條數據，考慮采用批量插入數據的方式；
結合網上資料，寫了個小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 @Override
62 public String toString() {
63 return "AccountInfo [id=" + id + ", userName=" + userName
64 + ", 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 * @return
11 */
12 List<AccountInfo> queryAllAccountInfo();
13
14 /**
15 * 批量插入數據
16 *
17 * @param accountInfoList
18 * @return
19 */
20 int batchInsertAccountInfo(List<AccountInfo> accountInfoList);
21 }
5、創建mybatis配置文件：mybatis-configuration.xml
1 <?xml version="1.0" encoding="UTF-8"?> 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.xml" /> 17 </mappers> 18 </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)
1 <?xml version="1.0" encoding="UTF-8" ?>
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_INFO
18 </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 select
25 #{accountInfo.id},
26 #{accountInfo.userName},
27 #{accountInfo.password},
28 #{accountInfo.gender},
29 #{accountInfo.email},
30 #{accountInfo.createDate}
31 from dual
32 </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.xml";
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("[email protected]");
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("[email protected]");
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("[email protected]");
62 account.setCreateDate(new Date(System.currentTimeMillis()));
63 result.add(account);
64 return result;
65 }
66 }
項目下載地址：http://download.csdn.net/detail/clqyhy/9553854
