12

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

DigitalZebra
  • 39,494
  • 39
  • 114
  • 146

1 Answers1

15

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.

Paul Vargas
  • 41,222
  • 15
  • 102
  • 148
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • Its a RESTlet stand-alone application... so no app server or JNDI – DigitalZebra Mar 01 '10 at 02:39
  • C3P0 is pretty big for something that can fit in one class file if you use PooledConnection for the task. – Hakanai Sep 10 '10 at 06:07
  • Can you provide an example for WAS 7? – Paul Vargas Jun 14 '12 at 17:28
  • @Paul: sorry no WAS7 here. I stopped using it after WAS5. But if I recall correctly, you can just configure it in the admin console on port 4848 or so. Look somewhere in "JNDI resources" or "JDBC resources" sections. – BalusC Jun 14 '12 at 17:47
  • 2
    Weird answer. "The datasource needs to be managed by whatever container" - no, no datasource _needs_ to be managed by any container. It can, but doesn't need to. It's unreasonable to introduce a container just to have a connection pool. To manually add pooling I'd suggest to user Apache Commons DBCP (as Tomcat itself did), or try Tomcat's JDBC Pool (the faster one they implemented in version 7 as a replacement for Commons DBCP). – mvmn Aug 06 '12 at 13:41
  • 1
    @mvmn: "whatever container **you're running the application in**". In layman's terms, that's the servlet container / webserver / application server (Tomcat, Glassfish, JBoss AS, WebSphere AS, etc). – BalusC Aug 06 '12 at 13:51
  • 3
    @BalusC: Changes nothing about what I said - there is no _need_ to have a container to make a DataSource pooled. No _need_, ok? It doesn't matter what container actually - be it Servlet Container or something else, there is no need to, say, convert the standalone app to webapp, or add something like Spring, just to have pooled DataSource. "Use C3P0" is better answer than the actual "answer". IMO. – mvmn Aug 06 '12 at 14:31
  • @mvmn: I completely fail to understand you, sorry. – BalusC Aug 06 '12 at 14:33
  • 1
    @BalusC What he is trying to say is, "you can have a pooled datasource in a standalone java application, a java application that is not running in a container". I think he has a point and it would be better if you updated your answer. – Koray Tugay Jul 30 '16 at 10:20
  • 1
    Stumbled upon this and I really find @BalusC's answer, well.. at least - misleading. First of all, I would agree with mvmn - it's really a huge overhead to incorporate any Java EE complaint (or anything alike) server just for having Pooled Connections. Secondly, BalusC is *stressing*, that `MysqlConnectionPoolDataSource` is *just a concrete implementation of DataSource* and it has nothing to do with Connection Pool.. but that's wrong imo. Why would authors call it **`MysqlConnectionPoolDataSource`** if it's just another implementation?.. there are other implementations, like `XADataSource`. – Giorgi Tsiklauri Nov 27 '21 at 22:04
  • Besides, Oracle docs [state](https://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html), that: "*When the DataSource interface is implemented to work with a ConnectionPoolDataSource implementation, all of the connections produced by instances of that DataSource class will automatically be pooled connections*" - mentioning **nothing** about any EE server.. or container. It just says (see "Advantages of DataSource Objects"), that Connections instantiated by XConnectionPoolDataSource will be pooled ones and ones gotten from XXADDataSource - distributed-transactionals (X = DBMS). – Giorgi Tsiklauri Nov 27 '21 at 22:09