Design question, c3p0 and more..

[kantorn]

Hi.

I'm planning an enterprise application, based on Tomcat, Spring and Hibernate whos core functionality would be this:

A main database (with Spring-Hibernate access layer) store "snapshots" of other databases. Access to these other databases is handled via Spring JDBC setup, and urls, password and so on are stored in a table in the main database. Performance isn't prime concern in querying the external db:s, because querying will take place weekly or so.

Now, the question I have is this: in order to be able to query all these databases for "snapshots" I have to be able to aquire a temporary connection with correct data for the DriverManager. This connection which will be closed after statemants is executed.

Say I choose to use a connection pool like c3p0 for the temporary connection. The "main databse" shouldn't cause any troble, I've used Hibernate w. c3p0 before. But there will have to be a second instance declared, one that supports runtime setup/closure of connections.
First of all, how do you declare this c3p0-pool in a Spring xml?
Secondly; does this c3p0 pool support so to speak random connections, which are acuired at runtime?
Third question: are there any known problems of declaring multiple pools/datasources in one Spring app?

Thank you!

[Juergen Hoeller]

You can easily define C3P0 ComboPooledDataSource instances as beans in a Spring context. Just give them different names and pass each instance to all your application beans that need it via bean references. This is analogous to how our sample apps use Commons DBCP's BasicDataSource or Spring's own DriverManagerDataSource.

If you use C3P0 for Hibernate, you can either specify this in Hibernate configuration (which I assume is what you do now) or set up a ComboPooledDataSource as Spring-managed bean and pass it to LocalSessionFactoryBean's "dataSource" property (analogous to how our Petclinic sample app does this with a DriverManagerDataSource).

Juergen

[kantorn]

OK.

I'll be right on it.

Thanks!

[kantorn]

Nope...

I didn't quite get it anyway.

I get this exception when using ComboPooledDataSource. Seems it is not compatible in som way, I guess a Setter is missing.

15:08:29,625 ERROR DispatcherServlet:208 - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in resource
[/WEB-INF/applicationContext-jdbc.xml] of ServletContext: Error setting property values; nested exception is org.springf
ramework.beans.NotWritablePropertyException: Invalid property 'driverClassName' of bean class [com.mchange.v2.c3p0.Combo
PooledDataSource]: Property 'driverClassName' is not writable
org.springframework.beans.NotWritablePropertyException: Invalid property 'driverClassName' of bean class [com.mchange.v2
.c3p0.ComboPooledDataSource]: Property 'driverClassName' is not writable
        at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:666)
        at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:588)
        at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:720)

I want to use the JDBC API from Spring. This is an excerpt from file
applicationContext-jdbc.xml:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClassName"><value>$&#123;jdbc.driverClassName&#125;</value></property>
		<property name="url"><value>$&#123;jdbc.url&#125;</value></property>
		<property name="username"><value>$&#123;jdbc.username&#125;</value></property>
		<property name="password"><value>$&#123;jdbc.password&#125;</value></property>
	</bean>

whick I suspect cause this exception. What am I missing here?

[kantorn]

OK. I missed that c3p0 has slightly different property names. You should use ''driverClass' , 'jdbcUrl', 'user' , 'password' and nothing else.