Java – JDBC and threads

I have a program that needs to query the database within a given time interval, perform some operations through the records it calls, and then update the table again

I'm using executorservice to run threads, but I wonder if each thread has its own connection (because it needs to update the database), or can I use the same connection I used to query the initial results?

It can work in connection pool. I work on Oracle 9i

Solution

You should always use separate connections for separate threads because drivers are not thread safe in this way Connection pooling can help you because it allows you to reuse connections in a secure way

You can also execute query - scheduling mode - if I understand your problem correctly - one thread is responsible for querying and starting n threads that may update the database - all of which have separate connection objects

You can also consider batch updating through Preparedstatement, so threads will not happen to each other in row and table locks. Use the following structure:

>1 query thread > ncpu processing thread > 1 batch update thread

Like a mini DB fork join

edit

Examples of how to use pstmt for batch updates:

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();

Alternatively, you can query the queue where the update request arrives from the processing thread in the loop:

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();