In Spring Boot, using c3p0 simultaneously with jdbcTemplate and Hibernate

Question

I have a SpringBoot app (1.3.2.RELEASE on Java 8) and it's using both Hibernate 4.3.11.Final and SQL calls via JDBC against Oracle JDBC driver 12.1.0.1. It's also using hibernate-c3p0 4.3.11.Final. JDBC calls are made against an autowired JdbcTemplate instance.

In my pom, I also have dependencies for Oracle UCP and ONS. Here are the relevant pom entries:

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-c3p0</artifactId>
        <version>4.3.11.Final</version>
    </dependency>
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc7</artifactId>
        <version>12.1.0.1</version>
    </dependency>

    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ucp</artifactId>
        <version>12.1.0.2</version>
    </dependency>
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ons</artifactId>
        <version>12.1.0.2</version>
    </dependency>

However, I can find no configuration for the Oracle UCP. Also, all the configuration for c3p0 appears to just apply to Hibernate.

Here are the relevant application.properties file entries (there are no other properties files):

# Properties for Hibernate and Oracle
spring.datasource.driverClassName=oracle.jdbc.driver.OracleDriver
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.jpa.properties.hibernate.connection.driver_class = oracle.jdbc.driver.OracleDriver

spring.datasource.url=jdbc:oracle:thin:@my-db-server:1523:me
spring.datasource.username=myuser
spring.datasource.password=mypass


# Configure the C3P0 database connection pooling module
spring.jpa.properties.hibernate.c3p0.max_size = 15
spring.jpa.properties.hibernate.c3p0.min_size = 6
spring.jpa.properties.hibernate.c3p0.timeout = 2500
spring.jpa.properties.hibernate.c3p0.max_statements_per_connection = 10
spring.jpa.properties.hibernate.c3p0.idle_test_period = 3000
spring.jpa.properties.hibernate.c3p0.acquire_increment = 3
spring.jpa.properties.hibernate.c3p0.validate = false
spring.jpa.properties.hibernate.c3p0.numHelperThreads = 15

spring.jpa.properties.hibernate.connection.provider_class = org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

spring.jpa.properties.hibernate.connection.url=jdbc:oracle:thin:@sea-db-server:1523:me
spring.jpa.properties.hibernate.connection.username=myuser
spring.jpa.properties.hibernate.connection.password=mypass

What I am trying to figure out is whether or not the JDBC calls made against the spring autowired JdbcTemplate are using the c3p0 connection pool or not, and also if the Oracle UCP is doing anything at all since it appears to have no configuration.

I really need connection pooling for the JDBC calls. Right now, I'm running into an issue where the connections to Oracle get closed. We're not using Oracle RAC, so I don't need UCP and therefore could just use c3p0.

If someone could help me understand what's happening now, or tell me what to check for, I would appreciate it. Also, assuming I'm right and the JDBC calls are not using a pool, what's the best way to fix that?

UPDATE

Based on the answer and comments below, I decided to remove c3p0 and use a pool that is natively supported in Spring. So I'll pull c3p0 out of the pom (along with oracle ucp and ons) and have what is below in the application.properties file.

I am trying to make sure that (1) I have a connection pool that will manage reconnection to the database should a connection be lost and (2) that JDBC and Hibernate are using the same datasource.

Did I get this right?

spring.datasource.url=jdbc:oracle:thin:@db-server:1523:mysvc
spring.datasource.username=myuser
spring.datasource.password=mypass

spring.datasource.max-active=50
spring.datasource.initial-size=5
spring.datasource.max-idle=10
spring.datasource.min-idle=5
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1 FROM DUAL
spring.datasource.time-between-eviction-runs-millis=5000
spring.datasource.min-evictable-idle-time-millis=60000

Answer 1

It looks like you're trying to create your connection pool via Hibernate. Spring Boot auto-create a DataSource that is bound to Hibernate but that configuration of yours (spring.jpa.properties.hibernate. and spring.jpa.properties.hibernate.connection.url) is creating another DataSource!

If you rely on an auto-configured JdbcTemplate you shouldn't ask hibernate to create the DataSource (there's no reason we should reuse that). I don't know that particular feature but I'd do the following:

1. Remove all spring.jpa.properties.hibernate.c3p0 and the spring.jpa.properties.hibernate.connection.url bits
2. Rely on Spring Boot to create the DataSource. We don't support c3p0 (maybe we should?). If you want to use that, you can create your own
3. Remove all the dialect stuff, we auto-detect that for you anyway

Here is a simple way to create a DataSource and bound it to the environment

@Bean
@ConfigurationProperties("yourapp.datasource")
public ComboPooledDataSource dataSource() {
    return new ComboPooledDataSource();
}

Then in your configuration you can add something like

yourapp.datasource.driver-class=oracle.jdbc.driver.OracleDriver
yourapp.datasource.jdbc-url=jdbc:oracle:thin:@sea-db-server:1523:me
...
yourapp.datasource.min-pool-size=6
yourapp.datasource.max-pool-size=15
...