Reuse SQLite connection or reconnect every time?

Question

I'm making a Java app that uses an SQLite database to store product information. To get the info I made a class with static methods, and a static variable private static Connection c so that it stays active until the program ends.

public static void Init() {
    try {
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:test.db");
        crearTablas();
    } catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.exit(0);
    }
    System.out.println("Opened database successfully");
}

Multiple queries are performed. Should I close the database connection at the end of each query, or keep it active and reuse (to close when I stop using the program with a static method)?

The app is for a Windows system (stock control for a computer store), not for phone.

Answer 1

The best practice for SQLite is to keep a single connection for the lifetime of your program. Closing and opening the connection will not save much memory because SQLite does not keep much state (the only significant memory usage is the page cache, whose size you can configure), and the time for those operations (such as re-reading the database structure) is much worse than the memory that is going to be used.

As for concurrency, there would be no problem with having multiple open connections (from a single process or from multiple processes), because the database gets locked only while a transaction is active.

Answer 2

The best practice for SQLite is to close your connection after every function and open it once for a function. By closing and opening the connection you will save on memory and the time for those operations is less significant than the memory that is going to be used.

You can refer to this article for the best practice on SQLite.

Answer 3

Or use Content provider, and never worry about opening and closing databases, it will take care of it by its own. - Reference

(Despite popular belief that content providers should only be used if you want to share data across apps, it is quite beneficial if you use it to access local data only.)

Check this discussion here