I want to use pooled connections with Java (because it is costly to create one connection per thread) so I'm using the MysqlConnectionPoolDataSource()
object. I'm persisting my data source across threads. So, I'm only using one datasource throughout the application like this:
startRegistry(); // creates an RMI registry for MySQL
MysqlConnectionPoolDataSource dataSource = new MysqlConnectionPoolDataSource();
dataSource.setUser("username");
dataSource.setPassword("password");
dataSource.setServerName("serverIP");
dataSource.setPort(3306);
dataSource.setDatabaseName("dbname");
InitialContext context = createContext(); // Creates a context
context.rebind("MySQLDS", dataSource);
Now that I have my datasource created, I'm doing the following in each separate thread:
PooledConnection connect = dataSource.getPooledConnection();
Connection sqlConnection = connect.getConnection();
Statement state = sqlConnection.createStatement();
ResultSet result = state.executeQuery("select * from someTable");
// Continue processing results
I guess what I'm confused on is the call to dataSource.getPooledConnection();
Is this really fetching a pooled connection? And is this thread safe?
I noticed that PooledConnection has methods like notify() and wait()... meaning that I don't think it is doing what I think it is doing...
Also, when and how should I release the connection?
I'm wondering if it would be more beneficial to roll my own because then I'd be more familiar with everything, but I don't really want to reinvent the wheel in this case :).
Thanks SO
A simple way to check pool members are re-used: If your JDBC vendor is using the standard toString from Object you should see the same values printed when you print the connection: System. out. println("Connection="+conn);
In Spring Boot, @Autowired a javax. sql. DataSource , and you will know which database connection pool is using in the current running application.
Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread that needs them. Connection pooling can greatly increase the performance of your Java application, while reducing overall resource usage.
Connection pooling is great for scalability - if you have 100 threads/clients/end-users, each of which need to talk to the database, you don't want them all to have a dedicated connection open to the database (connections are expensive resources), but rather to share connections (via pooling).
This is not the right way. The datasource needs to be managed by whatever container you're running the application in. The MysqlConnectionPoolDataSource
is not a connection pool. It is just a concrete implementation of the javax.sql.DataSource
interface. You normally define it in the JNDI context and obtain it from there. Also MySQL itself states it all explicitly in their documentation.
Now, how to use it depends on the purpose of the application. If it is a web application, then you need to refer the JNDI resources documentation of the servletcontainer/appserver in question. If it is for example Tomcat, then you can find it here. If you're running a client application --for which I would highly question the value of a connection pool--, then you need to look for a connection pooling framework which can make use of the MySQL-provided connection pooled datasource, such as C3P0.
The other problem with the code which you posted is that the PooledConnection#getConnection()
will return the underlying connection which is thus not a pooled connection. Calling close on it won't return the connection to the pool, but just really close it. The pool has to create a new connection everytime.
Then the threadsafety story, that depends on the real connection pooling framework in question. C3P0 has proven its robustness in years, you don't worry about it as long as you write JDBC code according the standard idiom, i.e. use only the JDBC interfaces and acquire and close all resources (Connection
, Statement
and ResultSet
) in shortest possible scope.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With