PostGrepSql 线程池多线程数据插入

最新推荐文章于 2024-06-27 16:58:50 发布

BoomLee

最新推荐文章于 2024-06-27 16:58:50 发布

阅读量1k

点赞数

分类专栏：个人总结

本文链接：https://blog.csdn.net/boomlee/article/details/106399920

版权

个人总结专栏收录该内容

1 篇文章 0 订阅

订阅专栏

需求

数据库表复制，数据量大时，用单纯的客户端工具只能单线程导入，时间耗时长。本文主要采用 JDBC 和 线程池 解决这个问题。

JDBC

我用的 PostGrepSql 数据库，根据自己的数据库添加pom.xml

 <dependency>
        <groupId>postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.1-901-1.jdbc4</version>
 </dependency>

工具类

static String url = "*";
static String user = "*";
static String password = "*";

public static Connection getConnect() {
        Connection con = null;
        try {
            Class.forName("org.postgresql.Driver");
            con = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return con;
    }

需要执行的SQL 添加到一个集合，我用的是

INSERT INTO TableA (select * from TableB)

定义线程池

public static void main(String[] args) {

        List<String> recordList = DbUtils.getSqlList();

        int threadCount = 20;

        if (recordList.size() > 0) {

            System.out.println(new Date() + "准备执行的 SQL Total --- > " + recordList.size());

            final BlockingQueue<String> queue = new ArrayBlockingQueue<String>(recordList.size());

            for (String record : recordList) {
                queue.offer(record);
            }

            ExecutorService executorService = Executors.newFixedThreadPool(threadCount);
            final CountDownLatch countDownLatch = new CountDownLatch(recordList.size());

            for (int i = 0; i < threadCount; i++) {
                executorService.submit(new Runnable() {
                    @Override
                    public void run() {
                        while (true) {
                            if (queue.isEmpty()) {
                                break;
                            }
                            String string = null;
                            try {
                                string = queue.take();

                            } catch (InterruptedException e1) {
                                System.out.println(new Date() + " " + e1);
                            }
                            try {
                            	//单线程执行SQL
                                singleInsert(string);
                                System.out.println(new Date()+"  "+string);
                            } catch (Exception e) {
                                System.out.println(e.getMessage());
                            } finally {
                                countDownLatch.countDown();
                            }
                        }
                    }
                });
            }
            while (countDownLatch.getCount() != 0) {
                try {
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
            executorService.shutdownNow();
            System.out.println(new Date() + "多线程导入结束");
        }
    }