Get SQLite read-only C3P0 ComboPooledDataSource

Question

I need to get a SQLite read-only C3P0 ComboPooledDataSource. This code I found here (Set SQLite connection properties in c3p0 connection pool) creates a SQLite read-only DataSource:

//put the imports where they really go, obviously...
import javax.sql.*;
import org.sqlite.*;
import com.mchange.v2.c3p0.*;

// configure SQLite
SQLiteConfig config = new org.sqlite.SQLiteConfig();
config.setReadOnly(true);
config.setPageSize(4096); //in bytes
config.setCacheSize(2000); //number of pages
config.setSynchronous(SQLiteConfig.SynchronousMode.OFF);
config.setJournalMode(SQLiteConfig.JournalMode.OFF);

// get an unpooled SQLite DataSource with the desired configuration
SQLiteDataSource unpooled = new SQLiteDataSource( config );

// get a pooled c3p0 DataSource that wraps the unpooled SQLite DataSource
DataSource pooled = DataSources.pooledDataSource( unpooled );

It works fine. But the method I am trying to adapt returns a ComboPooledDataSource. How can I get one ?

Answer 1

As you mention, C3P0 update readonly after connection created

Occasionally it is useful to override the default values of standard Connection properties such as transactionIsolation, holdability, or readOnly. c3p0 provides a "hook" interface that you can implement, which gives you the opportunity to modify or track Connections just after they are checked out from the database, immediately just prior to being handed to clients on checkout, just prior to being returned to the pool on check-in, and just prior to final destruction by the pool. The Connections handed to ConnectionCustomizers are raw, physical Connections, with all vendor-specific API accessible. See the API docs for ConnectionCustomizer.

I would suggest, do a switch, similar to Spring framework, from C3P0 to HikariCP connection pool

HikariCP allows you to set readOnly property:

readOnly This property controls whether Connections obtained from the pool are in read-only mode by default. Note some databases do not support the concept of read-only mode, while others provide query optimizations when the Connection is set to read-only. Whether you need this property or not will depend largely on your application and database. Default: false