Closing Datasource connection when calling Base.close

[imtigeroo]

I'm currently using HikariCP for connection pooling, since Base.open needs to be called or attached to every thread that access the DB I have attached it to my Channel Handlers in my Netty Socket implementation (NIO / NIO-EventLoopGroup thread). So when a user connects it opens a connection per user (we can have hundreds connected at a time) and when a user goes inactive, I close the connection via Base.close.

However, the connection pool never closes so after 30 connections, i'm not able to connect to the DB using Base.open as it reports that too many connections are open and it gives up trying to connect to the DB after 3 attempts.

Is there a way to have one single instance of Base.open and have be shared throughout my Netty NIO threads? I'm not sure how to avoid this error where it fails to connect due to too many connections even when calling Base.close.

Thank you.

[ipolevoy]

looks like you are starving your app of database connections. Having hundreds of connections to DB open at any given time is a bad idea, unless you are actually using them all. In any case, I suggest to source connections from the pool not when your user connects but when that user actually needs a connection. If you have a good architecture, you probably have such places. When you need a connection, do not open a new one, but rather request an instance of DataSource from the pol and pass that to Base.open(dataSource).
Here is a simple example working with pools manually: https://github.com/javalite/javalite/blob/master/activejdbc/src/test/java/org/javalite/activejdbc/C3P0PoolTest.java

[imtigeroo]

Thanks for the link, this seems to work perfectly!

This might be a little off-topic but, just wondering would it be okay to open/close the connection from/to the pool when doing constant action several times? For example if a user would do x action 100+ times it would update the data on the spot (get connection from pool, update, then close conn) all within a couple of seconds/minutes?

I just recently ported from hibernate -> activejdbc so I apologize for these questions!

Otherwise, thanks again, I appreciate it!

[ipolevoy]

@imtigeroo no problem asking questions! generally speaking, if you want to use a connection from a pool for a very short time, even for running many requests in seconds, it is fine. However, holding on to a connection when you do not need it (minutes) is a very bad idea, and will not scale. This has nothing to do with ActiveJDBC, just common logic. If you pick a connection from a pool, than this connection is not available for other threads, so you may get exceptions from a pool telling that pool is exhausted, or your other threads will be blocked on the pool waiting for connections to be returned by other threads.

Congrats on porting from Hibernate, how was your experience and what is your perception of ActiveJDBC so far?