JDBC and Threading

Question

I have a program that must query the database at a given interval and with the records that it calls, performs some actions, and then updates the table again.

I use ExecutorService to start threads, but I wonder if each thread should get its own connection (because it needs to update the database), or can I use the same connection that I used to query the initial results?

Can pooling work, am on Oracle 9i.

Answer 1

You should always use a separate connection for separate threads, since drivers are not thread safe in this way. A connection pool can help you because it allows you to safely use reuse.

You can also make a template for sending a request - if I understand your problem correctly - where one thread is responsible for querying and starting N threads that can update the database, all of which have separate connection objects.

You can also consider batch updating through PreparedStatement so that threads don't bump into each other in terms of locking rows and tables using the following structure:

• 1 stream of requests
• NCPU Processing Threads
• 1 batch update stream

Like a mini db fork-join.

Edit

Examples of how to perform batch updates using Pstmt:

PreparedStatement pstmt = connection.prepareStatement(
    "UPDATE table SET field=? WHERE id=?");
for (int i = 0; i < 100; i++) {
    pstmt.setInt(1, i * i);
    pstmt.setInt(2, i);
    pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();

, :

class WhatToUpdate {
    public int id;
    public int value;
}
Queue<WhatToUpdate> queue = new LinkedBlockingQueue<WhatToUpdate>();

PreparedStatement pstmt = connection.prepareStatement(
    "UPDATE table SET field=? WHERE id=?");

int poisons = THE_NUMBER_OF_PROCESSING_THREADS;
while (true) {
    WhatToUpdate record == queue.take();
    if (record == null) { // poison pill
        if (--poisons <= 0) {
            break;
        }
    }
    pstmt.setInt(1, record.value);
    pstmt.setInt(2, record.id);
    pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();

Answer 2

Oracle jdbc OracleConnection OraclePreparedStatement, , "". Connection , Connection PreparedStatement ( execute) .