wood burning stoves 2.0*
The moose likes JBoss/WildFly and the fly likes JBoss with C3P0 Connection Pool Config/Usage Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Products » JBoss/WildFly
Bookmark "JBoss with C3P0 Connection Pool Config/Usage" Watch "JBoss with C3P0 Connection Pool Config/Usage" New topic

JBoss with C3P0 Connection Pool Config/Usage

Rusty Enisin
Ranch Hand

Joined: May 26, 2005
Posts: 107
Server configuration is not my specialty. I have an app I created that uses ejp3 over mysql. It was working fine except the database would disconnect after a period of inactivity. I decided to put in a connection pool that would manage this. C3P0 looked good and would be able to solve this problem.

Per the instruction on this site (http://www.mchange.com/projects/c3p0/#jboss-specific) I put the C3P0 jar in the jboss/server/default/lib dir. I created c3p0-service.xml per the site's instructions and then put it in the jboss/server/default/deploy dir. Restarting at this point causes no ill effect. I have C3P0 configured with AutomaticTestTable to ping periodically to keep the connection alive. I can see in my database that the table has been created. That tells me jboss found C3P0 and started it up.

Now this is where I get lost. Am I done? It seems to me that I now need to tell my old datasource (MySqlDS) to use C3P0 as the connection pool. Or do I now use the connection pool directly in the application instead of the old datasource (like in persistence.xml)?

The squeaky wheel gets the grease. Well, that or replaced...
Javid Jamae
Ranch Hand

Joined: May 14, 2008
Posts: 198
No, AFAIK, your datasource won't use the connection pool. You now have two, or perhaps three, datasources defined, which are accessible from different JNDI names.

1) Out of the box, there is a datasource called java:DefaultDS that is configured in deploy/hsqldb-ds.xml.
2) The config file you specified defines a java:PooledDS, which points to your database directly, hence the JDBC URL you defined.
3) You said that you had a java:MySqlDS defined as well.

Did you remove the default datasource (hsqldb-ds.xml)? If so, did you point all the existing services to point to your MySqlDS datasource? How did you configure your MySqlDS?

I'd suggest removing the MySqlDS (however you configured it) and the deploy/hsqldb-ds.xml. Then you can change the JNDI name on your c3p0 configuration to java:DefaultDS. Then you'll have to make all your code just call java:DefaultDS (or better yet you can use the ENC to create a local namespace for your app).

Side note: Those of you who have JBoss in Action, we talk about how to configure the default datasource in section 15.5.

Author: JBoss in Action, javidjamae.com, @javidjamae on Twitter
Rusty Enisin
Ranch Hand

Joined: May 26, 2005
Posts: 107
Hey. Thanks for the reply. You confirmed what I feared; I have three independent datasources, none of which are accomplishing what I need.

I made the changes you suggested as best I understood. I now have three files related to the database connections:

1. hsqldb-ds.xml
2. c3p0-service.xml
3. jms/mysql-jdbc2-service.xml

Here is how each is configured.




The server started without errors and the connection works in the application. The database was disconnecting after a period of no use. Now I will wait a while to see if it disconnects.
Rusty Enisin
Ranch Hand

Joined: May 26, 2005
Posts: 107
I am still not confident that the DefaultDS is using c3p0 for the connection pool. Any ideas on how to test it? I am still getting this error after about 8 hours of inactivity:

MESSAGE: Broken pipe


java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2637)

It is entirely possible that c3p0 is working but is just not the proper solution for the problem. If so maybe I should create a new forum thread.
Jaikiran Pai

Joined: Jul 20, 2005
Posts: 10069

after about 8 hours of inactivity

See if configuring the datasource with appropriate timeout values helps. More details here. By the way,

in your -ds.xml, looks incorrect to me. It should be set to x minutes, where x is the amount of time the an idle connection can be let to stay in the pool.

[My Blog] [JavaRanch Journal]
Javid Jamae
Ranch Hand

Joined: May 14, 2008
Posts: 198
I'm not sure you can configure two different datasources to point to DefaultDS the way you did.

Your hsqldb-ds.xml file and your c3po-service.xml files are both trying to configure datasources using the same JNDI name (javaefaultDS). I would imagine that you would have an error somewhere in your logs complaining about binding to a JNDI name that already exists. So, the problem might be that c3p0 is not even binding to the JNDI name because its loading the hsqldb-ds.xml file first.

I would try deleting the hsqldb-ds.xml file altogether, thereby letting the c3p0-service.xml file bind the connection pool to that JNDI name.
I agree. Here's the link: http://aspose.com/file-tools
subject: JBoss with C3P0 Connection Pool Config/Usage