用ResultSet检索整行

Answer 1

下面是一个示例，使用一个查询来计算查询的大小，然后将处理分解到多个线程。

我使用MySQL，所以查询是为此编写的。您需要更改数据库引擎的查询。

public static void main(String[] args) throws Exception {
    final int count;
    try (final Connection conn = DATA_SOURCE.getConnection()) {
        final String countQuery = "SELECT COUNT(*) FROM my_table";
        try (final PreparedStatement ps = conn.prepareStatement(countQuery);
                final ResultSet resultSet = ps.executeQuery()) {
            resultSet.next();
            count = resultSet.getInt(1);
        }
    }
    final int chunksize = 1000;
    final Queue<SqlResult> results = new ConcurrentLinkedQueue<>();
    final ExecutorService es = Executors.newFixedThreadPool(10);
    for (int end = 0; end < count; end += chunksize) {
        es.execute(new ResultReader(count, end, DATA_SOURCE, results));
    }
}

private static class ResultReader implements Runnable {

    private final int start;
    private final int size;
    private final DataSource dataSource;
    private final Queue<SqlResult> results;

    public ResultReader(int start, int size, DataSource dataSource, Queue<SqlResult> results) {
        this.start = start;
        this.size = size;
        this.dataSource = dataSource;
        this.results = results;
    }

    @Override
    public void run() {
        try (final Connection connection = dataSource.getConnection()) {
            final String query = "SELECT id, something, somethingElse FROM my_table LIMIT ?, ?";
            try (final PreparedStatement ps = connection.prepareStatement(query)) {
                ps.setInt(1, start);
                ps.setInt(2, size);
                try (final ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        final SqlResult sqlResult = new SqlResult();
                        sqlResult.setId(rs.getInt("id"));
                        sqlResult.setSomething(rs.getString("something"));
                        sqlResult.setSomethingElse(rs.getString("somethingElse"));
                        results.add(sqlResult);
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

private static class SqlResult {

    private int id;
    private String something;
    private String somethingElse;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getSomething() {
        return something;
    }

    public void setSomething(String something) {
        this.something = something;
    }

    public String getSomethingElse() {
        return somethingElse;
    }

    public void setSomethingElse(String somethingElse) {
        this.somethingElse = somethingElse;
    }
}

这假设您已经使用datasource进行连接池。