Siena JDBC doesn't close connections to the data base

[plunchete]

I have testing the problem with the latest release of siena (siena-1.0.0-b6-SNAPSHOT) and it has the same problem.

Replicating the problem

pool max connections = 10
Thread with TaskExecutor to keep the threads there.

Result: Every thread blocks its connections, so connections never released.

Sample code

public void doTest() {
    int count = 0;
    while(true) {
        count++;
        logger.info("Excuted " + count + " times");
        TestThread tt = new TestThread();
        FutureTask task = new FutureTask<Object>(tt, null);
        Thread thread = new Thread(task);
        thread.start();
        try {
            task.get(1l, TimeUnit.HOURS);
        } catch (Exception e) {
            logger.error(e,e);
        }   
    }
}

class TestThread extends Thread {
    public void run() {
        User.get(150);
    }
}

Console output

INFO  ~ Excuted 2 times
INFO  ~ Excuted 3 times
INFO  ~ Excuted 4 times
INFO  ~ Excuted 5 times
INFO  ~ Excuted 6 times
INFO  ~ Excuted 7 times
INFO  ~ Excuted 8 times
INFO  ~ Excuted 9 times
INFO  ~ Excuted 10 times
INFO  ~ Excuted 11 times

More information about how to manage connections using JDBC and pools
http://stackoverflow.com/questions/2313197/jdbc-mysql-connection-pooling-practices
http://stackoverflow.com/questions/4938517/closing-jdbc-connections-in-pool

[dwursteisen]

Hello !

It isn't to the user the responsibility to close the connection ? For JDBC, Siena close statement and result set, but I think you have to close the connection yourself. I may be mistaken, but Siena can't know when it's the right moment to close the connection.

Please Try something like User.getPersistantManager().closeConnection();
It should work !

[plunchete]

All ORMs that I know manage the connection for you. When you do a connection.close() you just give the connection back to the pool and the pool decides if needs to close the connection or reuse it.

[dwursteisen]

Ok, I think that I understand the issue : after each request (get/delete/...), Siena should close the connection, because, as you said, the pool will close or reuse the connection.

But for non pooled connection, it shouldn't close the connection ? Am I right ? (my knowledge about pooled connection/jdbc is poor, sorry for this noob question ;))

[plunchete]

Not sure about using non pooled connections thought and ORM. I never used an ORM without a pool of connections.

Best practices using JDBC with a noon pooled connections are open the connection, do all your operations, close the connection. Siena and other ORM, are not able to know when you finish using the connection. Some random ideas:

• Always use a transactions with non pooled connections, when you commit the transaction Siena closes the connection.
• Do a kind of time out, after X seconds without using the connection Siena closes it.
• Just close the connection thought Siena when the user is using a pool, if not the user must manage the connection.
• Don't support non pooling connections -> I'm just guessing but I think most of the ORMs in the market use just pooled connections. Here a use case is needed, is there anyone using Siena without a pool? Is that a real use case? Does Siena need support for that?

[mandubian]

Siena has an abstract connection manager which provides DB connections and closes them. Basically Siena doesn't care if there is a pool of connections or anything else. It just asks for a connection and closes it when finished.
Current default implementation of the ConnectionManager uses a ThreadLocal to store the retrieved connection associated to current thread and releases the ThreadLocal when finished.
If anyone needs something different, just override AbstractConnectionManager and define another behavior.