最近项目中遇到一个问题导入数据到后台并将数据插入到数据库中导入的数据量有上万条数据考虑采用批量插入数据的方式
结合网上资料写了个小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 queryAllAccountInfo();
13
14 /**
15 * 批量插入数据
16 *
17 * param accountInfoList
18 * return
19 */
20 int batchInsertAccountInfo(List accountInfoList);
21
22 /**
23 * 批量插入数据使用Oracle的序列获取唯一键
24 *
25 * param accountInfoList
26 * return
27 */
28 int batchInsertAccountInfoUseSeq(List accountInfoList);
29
30 /**
31 * 插入数据使用Oracle的序列获取唯一键
32 *
33 * param accountInfoList
34 * return
35 */
36 int insertOne(AccountInfo accountInfo);
37 }
5、创建mybatis配置文件mybatis-configuration.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15 select ID,
16 USERNAME,PASSWORD,
17 GENDER, EMAIL, CREATE_DATE from ACCOUNT_INFO
18
19
20 INSERT INTO ACCOUNT_INFO(ID, USERNAME,PASSWORD,GENDER, EMAIL,CREATE_DATE)
21 (
22
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
33 )
34
35
36
37
38 SELECT ACCOUNT_SEQ.NEXTVAL FROM dual
39
40 INSERT INTO ACCOUNT_INFO(ID, USERNAME,PASSWORD,GENDER, EMAIL,CREATE_DATE)
41 SELECT ACCOUNT_SEQ.NEXTVAL, m.* FROM(
42
43 separator"union all">
44 select
45 #{accountInfo.userName},
46 #{accountInfo.password},
47 #{accountInfo.gender},
48 #{accountInfo.email},
49 sysdate
50 from dual
51
52 ) m
53
54
55
56
57 SELECT ACCOUNT_SEQ.NEXTVAL FROM dual
58
59 INSERT INTO ACCOUNT_INFO(ID, USERNAME,PASSWORD,GENDER, EMAIL,CREATE_DATE)
60 values(
61 #{id},
62 #{userName},
63 #{password},
64 #{gender},
65 #{email},
66 sysdate
67 )
68
69
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 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.batchInsertAccountInfoUseSeq(accountList);
33 session.commit();
34 }
35
36 static List generateData(){
37 List result new ArrayList();
38 AccountInfo account new AccountInfo();
39 account.setId(3L);
40 account.setUserName("zhangsanfeng");
41 account.setPassword("123456");
42 account.setGender("1");
43 account.setEmail("zhangsanfengwudang.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("zhouzhiruoemei.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("zhaominyuan.com");
62 account.setCreateDate(new Date(System.currentTimeMillis()));
63 result.add(account);
64 return result;
65 }
66 }