Connection Pooling - c3p0 - Tomcat.

[Chris Smith]

From what I understand, just a matter of having the c3p0.jar in the
effective classpath; adding the following code and getting connections
from the DataSource:

import com.mchange.v2.c3p0.*;
...
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbcostgresql://localhost/testdb" );
cpds.setUser("dbuser");
cpds.setPassword("dbpassword");

That would work, but it's not ideal.

Coz there's this part:
"You can easily configure Apache's Tomcat web application server to use
c3p0 pooled DataSources. Below is a sample config to get you started.
It's a fragment of Tomcat's conf/server.xml file, which should be
modified to suit and placed inside a <Context> element."

That is ideal. The idea behind a data source is that your code doesn't
care where the connections are coming from. If you add the above code
to your project, then you have to recompile your code to connect a
database other than PostgreSQL on localhost; not the best idea in the
world. By placing the configuration in server.xml, you can change the
database configuration by editing a config file. You'll still aquire
the data source by the same JNDI name, and your code will be identical
when you connect to a database somewhere else.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Rico]

As the docs say, c3p0 was designed to be very simple to use, and it is.

From what I understand, just a matter of having the c3p0.jar in the
effective classpath; adding the following code and getting connections
from the DataSource:

import com.mchange.v2.c3p0.*;
....
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbcostgresql://localhost/testdb" );
cpds.setUser("dbuser");
cpds.setPassword("dbpassword");


So I'm thinking for our web-application I can just add the above code to
my classes in WEB-INF/classes. Is that enough?

Coz there's this part:
"You can easily configure Apache's Tomcat web application server to use
c3p0 pooled DataSources. Below is a sample config to get you started.
It's a fragment of Tomcat's conf/server.xml file, which should be
modified to suit and placed inside a <Context> element."

Do I need to do this? What would it gain us?

Thanks.

Rico.

 

[Rico]

That is ideal. The idea behind a data source is that your code doesn't
care where the connections are coming from. If you add the above code
to your project, then you have to recompile your code to connect a
database other than PostgreSQL on localhost; not the best idea in the
world. By placing the configuration in server.xml, you can change the
database configuration by editing a config file. You'll still aquire
the data source by the same JNDI name, and your code will be identical
when you connect to a database somewhere else.

Thanks for the input Chris. Until you outlined the purpose of all this
stuff, I was at a loss even as to what JNDI is and why I would want it.
I saw Naming Directory and that reminded me of LDAP which seemed not even
remotely related to what I wanted because I've never used it.

So, hardcoding the ComboPooledDataSource into the JavaBean saw intermittent
occurrences of some ResourceClosedException or something, it seems to have
stopped after making use of our good old container's services.

I'm wondering, creating a new DataSource each time we need a connection is
the right way, isn't it? If so, what could make it so cheap?
How come the DataSource is new and cheap, yet the Connection likely isn't?

Thanks.

Rico.

 

[Sudsy]

Rico wrote:

I'm wondering, creating a new DataSource each time we need a connection is
the right way, isn't it? If so, what could make it so cheap?
How come the DataSource is new and cheap, yet the Connection likely isn't?

The whole point is that you /don't/ create a new pooled DataSource
every time. All you do is obtain a reference to the DataSource and
invoke getConnection on the object. The DataSource manages a pool
of connections and simply returns one which is currently unused.
It's cheap because you don't create a new connection; you obtain
one which has been created previously. Same when you invoke
Connection#close; you're actually returning the connection to the
pool, making it available for reallocation at some time in the
future.

 

[Rico]

The whole point is that you /don't/ create a new pooled DataSource
every time. All you do is obtain a reference to the DataSource and
invoke getConnection on the object.

Precisely. I was thinking that something from the library needs to remain
'alive'. I would understand that what Chris described as the 'ideal' way
of making use of the container's context to lookup the DataSource wouldn't
create a new pooled DataSource every time.

But it seems to me that in the 1st, non-ideal, case of "new
ComboPooledDataSource( )" hardcoded in the
WEB-INF/classes/misc/Util.class, we're creating a new pooled DataSource
every time. It even goes out of scope upon returning from the method call,
doesn't it? Which might explain the ResourceClosedException that was
thrown every now and then in this non-ideal case...

I've found an article that says that even the lookup is expensive and
recommends performing the lookup in HttpServlet's init() and keeping the
reference to DataSource around somehow. Any thoughts on the expensiveness
mentioned?

http://www.javaworld.com/javaworld/jw-06-2004/jw-0628-performance.html

The DataSource manages a pool of connections and simply returns one
which is currently unused. It's cheap because you don't create a new
connection

This much is clear. What happens in the case of using "new
ComboPooledDataSource( )" explicitly? Isn't that a new DataSource created?

Thanks.
Rico.

 

[Sudsy]

Rico wrote:

I've found an article that says that even the lookup is expensive and
recommends performing the lookup in HttpServlet's init() and keeping the
reference to DataSource around somehow. Any thoughts on the expensiveness
mentioned?

We're getting some cross-over with another thread discussing connection
pools. In fact I just responded to that thread with a quote from the
java.sql.PooledConnection javadocs.
Your information is quite correct: JNDI lookups are expensive and you
should make every effort to perform them as rarely as practicable.
In the case of Struts applications, I store the Home references in
application scope. You just have to implement a recovery mechanism
so that naming system outages don't permanently (until restart) dis-
able your application.

This much is clear. What happens in the case of using "new
ComboPooledDataSource( )" explicitly? Isn't that a new DataSource created?

You're creating a new object instance but the behaviour "under the
covers" might be to return a Facade. You could, in fact, be accessing
a Singleton when you invoke methods on the object. It's impossible to
tell without seeing the source code.
While I don't have access to my source at this moment, I seem to recall
performing the initial lookup on the ConnectionPoolDataSource and just
keeping it around.

 

[simplyjava]

Hi,
While going through the forum , i came across this post. Can someone help me in configuring datasource using c3p0 in tomcatv5.5.25.

<Resource name="jdbc/mydatasource" auth="Container"
type="javax.sql.DataSource" driverClassName="com.mchange.v2.c3p0.ComboPooledDataSource"
url="jdbcracle:thinurl:servicename"
username="user" password="password"/>


---------------------------------------------------------------------------
public static Connection getConnection() {
Connection conn = null;
if (datasource == null) {
datasource = locateDataSource();

}
if (datasource != null) {
try {
conn = datasource.getConnection();
} catch (SQLException e) {
LOGGER.error("Cannot get connection:" + e.getMessage()+e);
}
}

return conn;
}
/**
* Method to locate data source.
* @return
*/
private static DataSource locateDataSource() {
Context initialContext = null;
String DATASOURCE_CONTEXT = "java:comp/env/jdbc/mydatasource";
try {
initialContext = new InitialContext();
datasource = (ComboPooledDataSource)initialContext.lookup(DATASOURCE_CONTEXT);

} catch (NamingException e) {
LOGGER.error("Cannot get connection:" + e.getMessage() +e);
}
return datasource;

}
-----------------------------------------------------------------------
i take connection like this for my data access layer
connection = DBUtil.getConnection();

i am getting class cast exception. Would some one will help me in configuring c3p0 for my application.i have c3p0.jar in my tomcat common/lib.