Reusing a connection when polling a database in JDBC?
Has a use case where you need to maintain a connection open to a database, open to execute queries periodically.
Whether it is desirable to close the connection after completing the request and then reopen it after a period interval (10 minutes). I wouldn't guess, as opening a database connection is expensive.
Is the connection pooled alternative and keep using the connections?
source to share
You must use a connection pool. Write your application code to request a connection from the pool, use the connection, then push the connection back to the pool. This will keep your code clean. You then rely on the pooling implementation to determine the most efficient way to manage connections (for example, keep them open and close them).
It is generally "expensive" to open a connection, usually due to the overhead of setting up a TCP / IP connection, authentication, etc. However, it can also be costly to keep the connection "too long" because the database (possibly) has resources (eg memory) reserved for the connection to use. Thus, keeping the connection open can bind these resources.
You don't want to pollute your application code by managing these types of performance tradeoffs, so use a connection pool.
source to share
Yes, connection pooling is an alternative. Open the connection every time (as far as your code is) and close it as quickly as possible. The connection pool will handle the physical connection in an appropriately efficient manner (including any required keepalives, occasional "liveness tests", etc.).
I don't know what the state of the art is, but I used c3p0 very successfully for my latest Java project involving JDBC (quite a while ago).
source to share
The answer here really depends on the application. If the same applications are used at the same time between the same database, then pooling is definitely your answer.
If your whole application makes a db request, wait 10 minutes, then request again and then just connect and reconnect. Joining is considered an expensive operation, but all things are relative. It's not expensive if you only do it once every 10 minutes. If the application is simple, don't add unnecessary complexity.
Note: OK, complexity is also relative, so if you are already using something like Spring and already know how to use the join mechanism then apply it for that case. If it isn't, keep it simple.
source to share
Connection pooling will be an option for you. Then you can leave your code, including opening and closing it. The connection pool will take care of the connections. If you close the pool connection, it will not be closed, but will be available again in the pool. If you open a connection after closing it, if a connection is open in the pool, the pool will return it. This way, you can use built-in connection pools in the application server. For simple java applications, most JDBC drivers also contain a pool driver.
source to share
There are many, many trade-offs in opening and closing connections, keeping them open, making sure that connections that have been "saved" are still "valid" when you start using them again, invalid connections that get corrupted, etc. .d. These complex tradeoffs make it difficult (but certainly not impossible) to implement the "best" connection management strategy for your particular case. The "safest" method is to open a connection, use it, and then close it. But, as you already understood, this is not at all the most effective method. If you manage your own connections, the way you do things to make your strategy more efficient, the complexity will grow very quickly (especially in the presence of any less advanced JDBC drivers,of which there are many).
There are many pooling libraries out there that can take care of all of this for you in extremely customizable ways (they almost always come preconfigured ready to use for the most common use cases, and until you realize that you are doing high load activities, you probably are no need to worry about all this custom configuration, but you will be glad if you scale!) As always, the libraries themselves can be of variable quality.
I have used C3P0 and Apache DBCP successfully . If I were to choose again today, I would probably go with DBCP.
source to share