JDBC unwanted statement close

Question

Hello

I have a Java JDBC application that uses multiple threads to retrieve information from an Oracle data base. Each thread si supposed to periodically execute a statement that executes a select on a specific table (the table is different for each row).

The threads are instances of a class that extends the Thread class. This class has a private variable that stores a connection to the data base. This thread also has a child thread that periodically deletes some info from the table trough a statement.

When i run a single thread (get data from a single table) it works perfectly, but when i run multiple threads (trying to get data from multiple tables) i get an error that says that my statement was closed before I printed the entire resultset generated by that statement.

My questions are:

Why is a different thread closing my statement? Why isn't the child thread closing the parent thread statement? What can i do to prevent this?

I hope someone can help. Constantin

I don't use connection pooling because the connection on eacht thread stays open permanently because i execute a select statement every 20 milliseconds and the cild thread (that has his own connection) executes a delete statement every 10 seconds.

I cant store the data in an array because as sone as i retrieve it i have to send it ot an API for processing.

I can't understand it. Every thread is a different instance (with different paramenter except those used for connecting aka user, password) of the class (witch has nothing static in it) and yet close each other statements.

Each thread has it's own connection object and it's own statement adn result set object. It should be thread safe

Here is a code sample

stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
//some processing
}
stmt.close();

The conn variable is a connection and is created in the thrad's constructor, could this be the problem?

New EDIT

I have wrapped my connection object in a wraper class and extended it into tow different connection wrapper classes, one that is used by the threads doing the select statements and one by the threads doing the delete statements. I dont close my connection after a statement because it would be inefficient, but i do close the satement I dont have any shared objects between the threads because i dont need any. Each thread select data from a different table using a different statement object and a different resultset and passes it on to the API. To use connection pooling would meam rethinking my entire application, but if no other solution apears than i'll have to do it.

Thanks for the help and sorry if i sound stubern and sorry for not expressing myself more clearly from the start

Answer 1

My suggestions are: 1. Use connection pool. 2. synchronize the data-retrieval process. Store data into vector or arraylist (Thread Safe).

Answer 2

If you make sure you do not share state between threads, you won't need to worry about synchronisation.

1. Use a connection pool. Before each database statement is executed, retrieve a connection from the pool. Even if you are acquiring and releasing a connection 50 times a second, the connection to the database will be kept open by the pool and new connections will only be needed when multiple threads require a DB connection at the same time. I recommend you take a look at DBCP which has a good implementation of a robust, thread safe and flexible database connection pool.
2. Use local variables to make sure the scope of the connection means it is only visible to the current thread.
3. Always close connections (or release them back to the pool) when you have completed your database statement execution. Use a finally block around your JDBC code to do this.
4. When creating statements, again, make sure they are local so that their scope is visible only to the current thread.
5. Always close statements when you are done with them in a finally block.

public Data getMyData() {
    Connection conn = null;
    Statement statement = null;
    try {
        conn = ConnectionPool.getConnection();
        statement conn.prepareStatement("select mydata from mytable");
        //execute statement, get results
        //return Data
    }finally{
        if (statement != null) statement.close();        
        if (conn != null) conn.close(); //release the connection back to the pool
    }
}

As long as your connection pool is thread safe, this code should also be thread safe as you are never sharing connections or statements between threads.

Answer 3

If I was really desperate, I'd try the following to debug the problem:

First, I'd create a wrapper class for JDBC connection objects. Each wrapper method compares the current thread pointer with the thread pointer for the last operation. If they are the same, it calls the wrapped connection. Otherwise it logs an error, with a stacktrace to show exactly where the call was made.

Next, I'd find all places where JDBC connections are created, and change them to wrap the connection with an instance of my wrapper class.

Finally, I'd run the application, and set up something to watch the logs.