Enabling connection pooling with HSQLDB and Spring embedded database

Question

Recently, I have been trying to implement an in-memory database based on HSQLDB for one of our applications which uses Oracle DB in the production. The application uses spring framework. However, I have to implement the data-source bean programmatically as we are using the existing SQL DDL statements(Oracle queries) and so have to programmatically remove constructs like namespaces before they can run on HSQLDB.

I initialize the database using EmbeddedDatabaseBuilder(ResourceLoader).

Now my issue is that I now want to add connection pooling using say c3p0 to this. Normally I would be using

<bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="HSQLDB driver path" /> (this is just for representation)
    <property name="jdbcUrl" value="${xxx.jdbcUrl}" />
    <property name="user" value="${xxx.username}" />
    <property name="password" value="${xxx.password}" />
    <property name="minPoolSize" value="1" />
    <property name="maxPoolSize" value="3" />
    <property name="maxIdleTime" value="20" />
</bean>

However, I am confused as to how I can define this while using the Spring embedded database.

Disclaimer: I am really new to spring.

Answer 1

Following this link:

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
    public static ComboPooledDataSource newDefaultDS() {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setMinPoolSize(1);
        dataSource.setMaxPoolSize(1);
        dataSource.setMaxIdleTime(20);

        return dataSource;
    }
}

import java.beans.PropertyVetoException;
import java.sql.Driver;

import org.springframework.jdbc.datasource.embedded.ConnectionProperties;
import org.springframework.jdbc.datasource.embedded.DataSourceFactory;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class ComboPoolDataSourceFactory implements DataSourceFactory,
        ConnectionProperties {
    private final ComboPooledDataSource dataSource;

    public ComboPoolDataSourceFactory() {
        this(C3P0Utils.newDefaultDS());
    }

    public ComboPoolDataSourceFactory(ComboPooledDataSource dataSource) {
        assert dataSource != null;
        this.dataSource = dataSource;
    }

    public ConnectionProperties getConnectionProperties() {
        return this;
    }

    public ComboPooledDataSource getDataSource() {
        return dataSource;
    }

    public void setUsername(String username) {
        dataSource.setUser(username);
    }

    public void setPassword(String password) {
        dataSource.setPassword(password);
    }

    public void setUrl(String url) {
        dataSource.setJdbcUrl(url);
    }

    public void setDriverClass(Class<? extends Driver> driverClass) {
        try {
            dataSource.setDriverClass(driverClass.getName());
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
    }
}

import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;

public class EmbeddedC3P0DatabaseBuilder extends EmbeddedDatabaseBuilder {
    public EmbeddedC3P0DatabaseBuilder() {
        setDataSourceFactory(new ComboPoolDataSourceFactory());
    }
}

And a short usage example:

EmbeddedC3P0DatabaseBuilder builder = new EmbeddedC3P0DatabaseBuilder();
        EmbeddedDatabase db = builder
                .setType(EmbeddedDatabaseType.H2)
                .addScript("setup-tables.sql")
                .build();

        JdbcTemplate template = new JdbcTemplate(db);
                ....
        db.shutdown();