Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Am I using Java PooledConnections correctly?

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

like image 251
Polaris878 Avatar asked Mar 01 '10 02:03

Polaris878


People also ask

How do I know if connection pooling is working?

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);

How do I know if my connection pool is working in spring boot?

In Spring Boot, @Autowired a javax. sql. DataSource , and you will know which database connection pool is using in the current running application.

What is true about connection pooling in Java?

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.

Should you use connection pooling?

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).


1 Answers

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.

like image 170
BalusC Avatar answered Oct 14 '22 06:10

BalusC