Shiro Hibernate Tomcat - how to unify datasources using JNDI from Hibernate

[Tudor Raneti]

Questions are marked bold below. This is the context of the question:

Shiro wants a JNDI datasource:

jndiSaltedJdbcRealm.jndiDataSourceName = java:comp/env/jdbc/mysqlds

so I configured it one in Tomcat:

<Context>
	<Resource auth="Container" description="MySQL Connection"
		driverClass="com.mysql.jdbc.Driver" maxPoolSize="40" minPoolSize="2"
		acquireIncrement="2" name="jdbc/mysqlds" user="root" password=""
		factory="org.apache.naming.factory.BeanFactory" type="com.mchange.v2.c3p0.ComboPooledDataSource"
		jdbcUrl="jdbc:mysql://localhost:3306/mydb" />
</Context>

however, I'm using another setup from hibernate for my application:

<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mydb</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password"></property>
...
<property name="hibernate.connection.datasource">java:comp/env/jdbc/mysqlds</property>
...
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
		<property name="c3p0.min_size">6</property>
		<property name="hibernate.c3p0.max_size">40</property>
		<property name="hibernate.c3p0.acquire_increment">2</property>
		<property name="hibernate.c3p0.idle_test_period">600</property>
		<property name="hibernate.c3p0.max_statements">50</property>
		<property name="hibernate.c3p0.timeout">1800</property>

This is also necessary in web.xml for the line in hibernate.cfg.xml:

<resource-ref>
		<description>MySQL Connection</description>
		<res-ref-name>jdbc/mysqlds</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>

I think I'm using two separate datasources, aren't I?Is that bad? Because I can't tell wether I'm using one PooledDataSource or two at the moment... Everything looks fine, except I think I will run into connection problems or the datasource registered in JNDI because it's not properly set with c3p0... also need to confirm this is a proper setup for this scenario.

What I want, is to register the hibernate datasource in JNDI to use it with Shiro, in common with the rest of my application. How do I do that? Is there any chance the configuration I'm using treats both datasources with the same c3p0 configuration, or maybe it sees the two datasources as one? Hopeful thinking...

Logs indicate there's two separate data sourcepools registering:
One at start, immediately after "INFO: Initializing Shiro environment", probably the tomcat configured one:

2012-07-03 14:37:52,854 DEBUG [com.mchange.v2.c3p0.management.DynamicPooledDataSourceManagerMBean]: MBean: com.mchange.v2.c3p0:type=PooledDataSource[1br95m48ol9gia01xosxah|4cd580] registered.

and the other later(s!?) when application calls on it, probably the hibernate one:

2012-07-03 14:38:41,539 DEBUG [com.mchange.v2.c3p0.management.DynamicPooledDataSourceManagerMBean]: MBean: com.mchange.v2.c3p0:type=PooledDataSource[1br95m48ol9gia01xosxah|1364ee5] registered.
2012-07-03 14:38:41,539 DEBUG [com.mchange.v2.c3p0.management.DynamicPooledDataSourceManagerMBean]: MBean: com.mchange.v2.c3p0:type=PooledDataSource[1br95m48ol9gia01xosxah|1364ee5] unregistered, in order to be reregistered after update.
2012-07-03 14:38:41,539 DEBUG [com.mchange.v2.c3p0.management.DynamicPooledDataSourceManagerMBean]: MBean: com.mchange.v2.c3p0:type=PooledDataSource[1br95m48ol9gia01xosxah|1364ee5] registered.

but afterwards there's a lot of pools initializing:

This is from the datasource configured in tomcat

Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@31380c0b [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@3653f943 [ acquireIncrement -> 2

this seems related to the initial settings of c3p0 in hibernate.cfg.xml

BasicResourcePool]: com.mchange.v2.resourcepool.BasicResourcePool@1b2591c config: [start -> 6

then on a short distance this appears:

GooGooStatementCache]: cxnStmtMgr.statementSet( com.mysql.jdbc.JDBC4Connection@992725 ).size(): 1
...
BasicResourcePool]: com.mchange.v2.resourcepool.BasicResourcePool@1bb3e9a config: [start -> 3

... probably a separate thread and connection/settings for caching statements? hmm...

then another initialization

AbstractPoolBackedDataSource]: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 2

and afterwards, when there's idle checks, only one triggers according to settings:

BasicResourcePool]: trace com.mchange.v2.resourcepool.BasicResourcePool@1b2591c [managed: 6, unused: 6

meaning the other one will run into problems after the 8 hours set by mysql wait_timeout of 28800 seconds (in fact that's exactly what happened if I think about it, because authorization/authentication failed ...can't tell if the application crapped out too because everything was behind Shiro, anyway, enabling c3p0 for the hibernate connection will not enable it for Shiro datasource too...)

EDIT: In the meantime I discovered this, for Tomcat 7 (lucky me if it works):
http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html

EDIT2: EDIT doesn't work, there's some bug going on that was patched supposedly but can't get it to work.
Anyway, that's for a JDBC pooled datasource. For a com.mchange.v2.c3p0.ComboPooledDataSource there's
http://www.mchange.com/projects/c3p0/

still, it would be nice if I could register the hibernate datasource in JNDI...

[Tudor Raneti]

There's hibernate.jndi property to be set but I haven't the time to get into that now... dunno if that's the answer.
For some reason the other people that asked the same question didn't make use of this option.