java - 我应该如何使用 2 个线程从数据库中收集数据？

Question

我有一个包含 1600 多条记录的数据库，读取所有记录看起来很慢，所以我想到了使用 2 个线程来获取数据。我有以下功能，但感觉不是更快...

private ArrayList<Worker> getWorkersOnMultipleThread() throws InterruptedException {
    ArrayList<Worker> totalWorkers = new ArrayList<>();
    ArrayList<Worker> totalWorkers2 = new ArrayList<>();
    int total = db.getNumberOfWorkers();
    int firstHalf, secondHalf;
    firstHalf = total / 2;
    if (total % 2 == 1) {
        secondHalf = total / 2 + 1;
    } else {
        secondHalf = total / 2;
    }

    Thread t1 = new Thread() {
        @Override
        public void run() {
            ArrayList<Worker> w1 = db.getHalfOfTheWorkers(firstHalf, true);
            totalWorkers.addAll(w1);
        }
    };

    Thread t2 = new Thread() {
        @Override
        public void run() {
            ArrayList<Worker> w2 = db.getHalfOfTheWorkers(secondHalf, false);
            totalWorkers2.addAll(w2);
        }
    };

    t1.start();
    t1.join();
    t2.start();
    t2.join();

    totalWorkers.addAll(totalWorkers2);

    return totalWorkers;
}

@Override
public ArrayList<Worker> getHalfOfTheWorkers(int limit, Boolean firstHalf) {
    String sql;
    ArrayList<Worker> workers = new ArrayList<>();
    if (firstHalf) {
        sql = "SELECT * FROM NAMES ORDER BY ID FETCH NEXT " + limit + " ROWS ONLY";
    } else {
        sql = "SELECT * FROM NAMES ORDER BY ID OFFSET " + limit + " ROWS";
    }
    try {
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            String name = rs.getString("name");
            int id = rs.getInt("id");
            workers.add(new Worker(id, name));
        }
    } catch (Exception ex) {

    }
    return workers;
}

我的想法是在第一个线程上获取前 50% 的记录，在第二个线程上获取第二个 50% 的记录，希望获取所有记录所需的时间减半。它似乎不起作用(我的意思是它不会给出错误和其他东西并且它具有相同的速度......)

主要内容:

@Override
public void initialize(URL url, ResourceBundle rb) {
    ArrayList<Worker> totalWorkers = new ArrayList<>();
    try {
        totalWorkers = getWorkersOnMultipleThread();
        System.out.println(totalWorkers.size());
    } catch (InterruptedException ex) {
        Logger.getLogger(Controller.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Answer 1

发生这种情况是因为您按顺序启动线程，即当 t1 启动时，main 处于等待状态，因为您使用了 t1.join()。 t2 线程在 t1 线程完成执行后才会启动。

t1.start();
t1.join(); //main waiting
t2.start();
t2.join();

因此您需要更改顺序:

t1.start();
t2.start();
t1.join(); //main waiting
t2.join();

这样线程 t1 和 t2 将并行启动，您将指示 main 等待直到 t1 和 >t2 执行完毕。

理想情况下，statement 对象不应在线程之间共享。您应该从线程池中为每个线程获取一个 Connection 对象。

由于您使用的是 Apache derby，开发人员指南 discourages the sharing of connection .

• Avoid sharing Statements (and their ResultSets) among threads. Each time a thread executes a Statement, it should process the results before relinquishing the Connection.
• Each time a thread accesses the Connection, it should consistently commit or not, depending on application protocol.
• Have one thread be the "managing" database Connection thread that should handle the higher-level tasks, such as establishing the
  Connection, committing, rolling back, changing Connection properties
  such as auto-commit, closing the Connection, shutting down the
  database (in an embedded environment), and so on.
• Close ResultSets and Statements that are no longer needed in order to release resources.