SQLite JDBC PRAGMA setting

Question

I am trying to set PRAGMA foreign_key = ON; in sqlite database. I code some software in java using jdbc driver for sqlite, this one: http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC.

I am also using connection pooling to speed up queries for DB. I am using this library: http://commons.apache.org/dbcp/.

Up to this point, all is good. Now, I need to set PRAGMA setting, concretely PRAGMA foreign_key = ON; before creating tables, because I need to be sure about consistency between some columns in db.

When I create DB, it is automatically set to OFF. So I have to turn it on in order to use it.

But I do not know how to do it, the way I am preparing poolable data source is like this:

    public static DataSource getDataSource(String connectURI) {
GenericObjectPool connectionPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory =
    new DriverManagerConnectionFactory(connectURI, null);
PoolableConnectionFactory poolableConnectionFactory =
    new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);
DataSource dataSource = new PoolingDataSource(connectionPool);
return dataSource;
}

But I do not know how to set that pragma properly, I found that this one is possible:

SQLiteConfig config = new SQLiteConfig();
config.enforceForeignKeys(true);

But I do not know how to use it in connection with that poolable tricky settings ...

Any ideas?

Answer 1

Unfortunately, this is more of a DBCP question than SQLite. I agree, there has to be a place somewhere in DBCP to set/update the config for a given data source. I would expect to see it in the PoolingDataSource class, but of course its not there.

One option to consider is to use a jdbc pooling library that leverages the ConnectionPoolDataSource interface. If so, you can use the SQLiteConnectionPoolDataSource to set up a connection pool like this:

  //Set config
    org.sqlite.SQLiteConfig config = new org.sqlite.SQLiteConfig();
    config.enforceForeignKeys(true);


  //Create JDBC Datasource
    SQLiteConnectionPoolDataSource dataSource = new SQLiteConnectionPoolDataSource();
    dataSource.setUrl("jdbc:sqlite:" + db.toString().replace("\\", "/"));
    dataSource.setConfig(config);

Note that there is an extension to DBCP called DriverAdapterCPDS. It is an implementation of the ConnectionPoolDataSource interface so in theory, you should be able to use the SQLiteConnectionPoolDataSource with DBCP .