Java Multithreading – Using Database Connection on Multiple Threads

javamultithreading

let's say I have a database connection in the class Database which offers the following public methods

public synchronized void open() {}
public synchronized void close() {}
public synchronized void addData(Object data) {}

My main program looks as follows:

Database databaseConnector = new Database();

for (Feed feed : feedList) {
    Timer timer = new Timer();
    FeedTimerTask timerTask = new FeedTimerTask(feed, databaseConnector, stopWords);
    // Run the task every five minutes.
    timer.scheduleAtFixedRate(timerTask, 0, 1000 * 60 * 5);
}

It is easy to see, that an unknown number of threads is repeated every 5 minutes. These threads itself read data from a feed, and try to save it to a database afterwards.

Since they are all using the same Database connection, it seems not clever to me to open() and close() the connection for every feed. Especially since one reader can close the connection while another is still trying to write something to this feed.

Instead I thought that I want something like this timeline:

(open) (write1) (write2) ... (writeN) (close) ... (wait 5 mins) (open) ... (close)

The idea is, that every 5 minutes exactly one connection is opened and after all the threads have written their data, is closes again. So far, I have the following solution.

I added private int openConnections; to the Database and modified the code as follows:

public synchronized void open() {
    if(openConnections++ == 0) {
        // Connect to the database, because we were not connected before
    }
}

public synchronized void(close) {
    if(--openConnections == 0) {
        // Close to the connections because this was the last one
    }
}

Now, in every Thread, I do the following:

db.open();
db.addData(...);
db.close();

With this strategy, it is always checked, if there is still a need for an open connection and in that case, the connection is not closed. And I don't plan to have more threads than MAX_INT. My question is: Is this a good idea? Are there better ways to achieve the same behavior? It works completely fine but I am wondering if there is another solution which I should prefer.

Best Answer

Use database connection pooling. I don't recommend writing your own. Here's an explanation on StackOverflow. You can use c3p0 or read up more on your own by searching for "connection pooling."

Related Topic