非Spring应用中JdbcTemplate能否并行处理大表且避免内存溢出？

Great question! Dealing with large datasets while avoiding OOM errors and keeping processing fast is a common pain point, and yes—there are solid ways to combine RowCallbackHandler's memory efficiency with parallel processing in your non-Spring app. Let's break down the best approaches:

1. RowCallbackHandler + ExecutorService (Simplest, Most Direct Approach) #

RowCallbackHandler already solves the OOM problem by processing rows one at a time (no full dataset loaded into memory). Pairing it with a thread pool lets you offload the notification-sending work to background threads, so your main query thread can keep fetching rows without waiting for each notification to complete.

Implementation Steps: #

First, set up a thread pool tailored to your server's resources (adjust core threads/queue size based on how many concurrent notifications your system can handle):

// Configure thread pool - tweak numbers based on your CPU/network capacity
ExecutorService notificationExecutor = Executors.newFixedThreadPool(8);

Next, create your custom RowCallbackHandler that submits notification tasks to the pool:

RowCallbackHandler idProcessingHandler = rs -> {
    String targetId = rs.getString("id"); // Adjust type to match your table's id column
    
    // Submit notification task to background thread
    notificationExecutor.submit(() -> {
        try {
            // Replace with your actual mobile notification logic
            sendMobileNotification(targetId);
        } catch (Exception e) {
            // Handle failures (log, queue for retry, etc.)
            System.err.printf("Failed to send notification for ID %s: %s%n", targetId, e.getMessage());
        }
    });
};

Run your query with the handler:

jdbcTemplate.query("SELECT id FROM your_large_table", idProcessingHandler);

Finally, clean up the thread pool once processing is done:

notificationExecutor.shutdown();
try {
    // Wait for all pending tasks to finish before exiting
    if (!notificationExecutor.awaitTermination(30, TimeUnit.MINUTES)) {
        notificationExecutor.shutdownNow();
    }
} catch (InterruptedException e) {
    notificationExecutor.shutdownNow();
}

Key Notes: #

• Ensure your sendMobileNotification method is thread-safe (no shared mutable state without proper synchronization).
• Adjust the thread pool size carefully—too many threads can overwhelm your database connection pool or mobile notification API.
• Add retry logic for failed notifications if needed (e.g., using a retry library or a persistent queue).

2. Partitioned Queries + Parallel Processing (More Controlled) #

If you want more control over database load (e.g., avoiding a single long-running query), split the dataset into smaller chunks and process each chunk in parallel. Each chunk still uses RowCallbackHandler to avoid OOM.

Implementation Example: #

First, get the range of IDs to split your table:

Long minId = jdbcTemplate.queryForObject("SELECT MIN(id) FROM your_large_table", Long.class);
Long maxId = jdbcTemplate.queryForObject("SELECT MAX(id) FROM your_large_table", Long.class);
int chunkSize = 15000; // Adjust based on your table's row density

Then, submit chunked queries to the thread pool:

ExecutorService chunkExecutor = Executors.newFixedThreadPool(5);

for (Long start = minId; start <= maxId; start += chunkSize) {
    Long end = Math.min(start + chunkSize - 1, maxId);
    // Capture variables effectively for lambda
    final Long chunkStart = start;
    final Long chunkEnd = end;
    
    chunkExecutor.submit(() -> {
        jdbcTemplate.query(
            "SELECT id FROM your_large_table WHERE id BETWEEN ? AND ?",
            new Object[]{chunkStart, chunkEnd},
            rs -> {
                String targetId = rs.getString("id");
                sendMobileNotification(targetId);
            }
        );
    });
}

// Shutdown logic same as above
chunkExecutor.shutdown();

Key Notes: #

• Works best if your id column is numeric and sequential. For non-sequential IDs, use LIMIT/OFFSET (but be aware of performance hits with large offsets) or partition by another column (e.g., creation date).
• Reduces the risk of a single long-running query holding a database connection for too long.

3. Spring Batch (For Long-Term Big Data Processing Needs) #

If you anticipate more large-scale data tasks in the future, consider adding Spring Batch to your non-Spring app (you don't need a full Spring context—just the core Batch dependencies).

Spring Batch natively supports:

• Cursor-based reading (like RowCallbackHandler, no full dataset in memory)
• Parallel processing via task executors
• Built-in retry, error handling, and job tracking

You'd configure a JdbcCursorItemReader to fetch IDs, a TaskExecutor for parallel processing, and an ItemWriter to send notifications. It's more setup upfront but gives you robust tooling for big data workflows.

内容的提问来源于stack exchange，提问作者Daniel Kaplan