Where should I store a DBI connection and when should I close it?

Question

I'm storing it in a public static field

public class DB {

    private static final String url = "jdbc:sqlite:file:target/todo";
    public static final DBI dbi = new DBI(url);


    public static void migrate() {
        Flyway flyway = new Flyway();
        flyway.setDataSource(url, "", "");
        flyway.migrate();
    }
}

And never close it, is there a better option?

Answer 1

This amounts to how you handle getting ahold of any dependency in your application. The best general model, IMHO, is passing it in to the constructor of things that need it. If you want to put some kind of DAO facade around your database access, pass the DBI to the ctor of your DAO. If you are using a DI framework, bind the DBI instance to the framework, and @Inject it.

For your specific question about Connections, the DBI equivalent of a JDBC Connection is the Handle. You should obtain a Handle, use it, and close it as soon as you are done. Typical use of DBI instance is to give it a DataSource which manages actual database connections, by releasing the Handle as soon as you finish with it, you make better use of the connection pool.

In most cases, you would only close the DBI instance if you want to close the Datasource, that is all that closing the DBI instance does. 98% of the time, in a java-for-server world, closing the datasource doesn't make sense, so worrying about closing the DBI (as compared to the Handle) is not a big deal.

When using JDBI, keep in mind:

DBI -> Datasource
Handle -> Connection
Query/SQLStatement -> Statement

This doc elaborates on these.