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

Basile

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
...

Drafty

Thanks very much! I'll try that now, but which connection pool is that using, and, will it pool both for Hibernate and JDBC directly? – Basile

As I look at this, I'm a bit confused, it seems ComboPooledDataSource is a c3p0 class, but I thought you were saying I should pull c3p0 out because it's not supported? – Basile

We don't auto-configure it but you can perfectly use whatever connection pool you want if you configure it yourself. If you don't care about c3p0, use any of the connection pools we're supporting and the spring.datasource namespace. You don't even need to code anything in that case (just configuration tuning) – Drafty

c3p0 _____IS___ a connection pool so I am not sure I understand the question. The whole point of my answer is to make sure that Hibernate and the JdbcTemplate use the same datasource. That's not the case in your description. – Drafty

Thanks Stephane, that is what I'm trying to achieve (Hibernate and JDBC using the same datasource). I did some reading and now understand that the bit of code you offered was so I could use c3p0. I don't care about using c3p0 specifically, so I think I'll remove it and try to use a pool that you support natively. I'll edit my question to add what I came up with, I think i did it right but I'm not sure. – Basile

Thanks, that's good to know. Because the issue I now have is that when I load it with Apache JMeter, I get many abandoned connections. I posted another question here: #35324258 – Basile

Answer 2

(1) UCP Configuration with Spring. Refer to the blog

(2) Oracle JDBC drivers are not present in the public maven repository. So, you will need to download JDBC drivers manually from OTN page Install as shown below.

mvn install:install-file \ -Dfile=/lib/ojdbc7.jar \ -DgroupId=com.oracle \ -DartifactId=ojdbc7 \ -Dversion=12.1.0.1 \ -Dpackaging=jar \ -DgeneratePom=true

Sagacious