spring boot配置oracle 批量插入

Posted on by yangxing

ps:win下创建oracle数据库，系统应用搜索datebase 。。。 什么鬼的，开始创建。

环境：Oracle 11g
spring boot：1.5.15
jdk8

废话不多说，直接上代码：
前期工作
安装ojdbc6-11.2.0.1.0到本地仓库（与本地oracle版本一致，其他版本未测试）
pom文件：
ojdbc6 jar包的引用见文章：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.1.0</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

application.yml文件：

1
2
3
4
5
6
spring:
  datasource:
    driver-class-name: oracle.jdbc.driver.OracleDriver
    url: jdbc:oracle:thin:@192.168.0.118:1521:databasename
    username: aaaaa
    password: 11111

实体类文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.hachuizi.entity;

import lombok.Data;

import java.io.Serializable;

/**
 * Created by wosha on 2018/11/15.
 */
@Data
public class UserData implements Serializable {

    private final static long  serialVersionUID= 1L;
    private String id;
    private String name;
    private String age;

    public UserData(String id, String name, String age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }
}

mapper文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?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.hachuizi.dao.UserData_OracleDao">

    <insert id="saveList" parameterType="UserData">
        INSERT INTO T_USER ( ID, NAME, AGE )
        (
        <foreach collection="list" item="item" index="index" separator="union all">
            SELECT
            #{item.id},
            #{item.name},
            #{item.age}
            FROM dual
        </foreach>
        )
    </insert>

</mapper>

controller文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.hachuizi.controller;

import com.hachuizi.Service.TestData_Oracle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * Created by wosha on 2018/11/15.
 */
@RequestMapping("oracledata")
@RestController
public class OracleController {

    @Autowired
    private TestData_Oracle oracle;

    @GetMapping("save")
    public String saveListOracle() {
        return oracle.saveListOracle();
    }
}

service文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
package com.hachuizi.Service;

import com.hachuizi.dao.UserData_OracleDao;
import com.hachuizi.entity.UserData;
import com.owinfo.utils.basetools.OwinfoStringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

import static com.hachuizi.utils.DataHandle.PagerBYXX;

/**
 * Created by wosha on 2018/11/15.
 */
@Component
public class TestData_Oracle {

    @Autowired
    private UserData_OracleDao oracleDao;

    public String saveListOracle() {
        long start = System.currentTimeMillis();
        int all = 1000000;
        int pageSize = 1000;
        List<UserData> lists = new ArrayList<>();
        for (int i = 0; i<all; i++){
            UserData userData = new UserData(OwinfoStringUtil.createUUID(), "姓名" + i, "年龄" + i);
            lists.add(userData);
        }
        long end1 = System.currentTimeMillis();
        System.out.println("遍历消耗时长：" + (end1 - start));
        for (int j = 1; j<=(all/pageSize+1); j++){
            List<UserData> small = PagerBYXX(lists,j,pageSize);
            if(small.size()>0){
                thread(small);
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("入库消耗时长：" + (end - start));
        return "消耗时长：" + (end - start);
    }

    private void thread(List<UserData> small) {
        oracleDao.saveList(small);

        /*ExecutorService executor = Executors.newFixedThreadPool(1);
        executor.execute(new Runnable() {
            @Override
            public void run() {
                oracleDao.saveList(small);
            }
        });*/
    }


}

dao文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
package com.hachuizi.dao;

import com.hachuizi.entity.UserData;

import java.util.List;

/**
 * Created by wosha on 2018/11/15.
 */
public interface UserData_OracleDao {

    int saveList(List<UserData> lists);

}

启动类文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.hachuizi;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication(scanBasePackages= {"com.hachuizi"})
@MapperScan("com.hachuizi.dao")
@EnableTransactionManagement
@EnableAsync
@EnableCaching
@ServletComponentScan
@EnableScheduling
public class MongodbDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(MongodbDemoApplication.class, args);
    }
}

工具类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com.hachuizi.utils;

import java.util.List;

/**
 * Created by wosha on 2018/11/15.
 */
public class DataHandle {

    /*手动分页*/
    public static <T extends Object> List<T> PagerBYXX(List<T> lists, int page, int pageSize){
        List<T> listRes;
        if(null == lists || lists.size() == 0){
            return null;
        }
        page = page<=0 ? 1:page;
        pageSize = pageSize<=0 ? 10:pageSize;
        int fromIndex = (page-1)*pageSize;
        int toIndex = (page-1)*pageSize+pageSize;
        if(page == 1 && lists.size() <= page*pageSize){
            return lists;
        }else if(page*pageSize > lists.size()){
            int remain = lists.size()%pageSize;
            listRes = lists.subList(lists.size()-remain, lists.size());
            return listRes;
        }else {
            listRes = lists.subList(fromIndex, toIndex);
            return listRes;
        }
    }
}
ˆ Back To Top