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.

possible duplicate of [Closing Database Connections in Java](http://stackoverflow.com/questions/2225221/closing-database-connections-in-java) — Ehsan Jelodar, Jul 06 '15 at 14:26

score 8 · Answer 1 · answered Jul 06 '15 at 18:40

8

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.

answered Jul 06 '15 at 18:40

CL.

173,858
17
217
259

"Best practice" is nonsense. This is well illustrated by this Q&A where two Answers are claiming that diametrically opposite approaches are "best practice". See https://www.satisfice.com/blog/archives/5164 – Stephen C Mar 01 '20 at 02:53
Is there a connect with "reconnect" option like with MySQL? The scenario is that a long running application may transiently disconnect because sqlite became temporarily unavailable (service restart, backup or anything else coming from the OS level). So, how do you handle such cases? Maybe it is better to not trust that the connection will be always there, and simply connect when you need it, and disconnect afterwards. – papajony Apr 03 '20 at 09:12
1

@papajony SQLite does not run as a service, and is file based; there is no network connection. – CL. Apr 03 '20 at 10:08
@CL Thanks. Since this is the case, then I believe that the argument to keep the connection open seems very weak. I am talking specifcally about embedded systems where the program runs "for ever". You open the connection only when you need it and close it afterwards. No real advantages to keep the connection open "for ever". – papajony Apr 04 '20 at 07:58

score 3 · Accepted Answer · answered Jul 06 '15 at 14:04

3

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.

answered Jul 06 '15 at 14:04

Yann Thibodeau

1,111
1
12
20

1

If you make a windows app, I guess you don't have to worry to much about memory issues, so it might be best to keep the connection always open. – Yann Thibodeau Jul 07 '15 at 03:01
"Best practice" is nonsense. This is well illustrated by this Q&A where two Answers are claiming that diametrically opposite approaches are "best practice". See https://www.satisfice.com/blog/archives/5164. – Stephen C Mar 01 '20 at 02:53

score 1 · Answer 3 · edited May 23 '17 at 12:13

1

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

edited May 23 '17 at 12:13

Community

1
1

answered Jul 06 '15 at 14:13

Darpan

5,623
3
48
80

Reuse SQLite connection or reconnect every time?

3 Answers3