Menu
I'm working on a single-user desktop database application sort of thing in my spare time, and I'm always unsure about the design choices I'm making. Right now, as it stands, whenever the user wants to interact with the database (which is a local SQLite database, so generally only one user ever sees it at once), the application creates a new connection, does whatever it needs to do, and then closes the connection. Thereforee, over the course of one execution of the application, lots of connections are created and disposed of.
Is this generally the "best" way to go about it, or should the application open the connection at startup and only close it when the application exits? What are the advantages/disadvantages of each method?
468
Absolutely it is safe to do this. This is how client-server applications work. If you are using a three-tier application, the application server will keep a pool of connections open anyway.
If you do not close your database connections, many problems can occur like web pages hanging, slow page loads, and more. Think of it as going through a door to your house. Maybe the door will shut by itself, but maybe it won't. If it doesn't shut, who knows what will happen.
If you don't close it, it leaks, and ties up server resources. @EJP The connection itself might be thread-safe (required by JDBC), but the applications use of the connection is probably not threadsafe. Think of things like different transaction isolation, boundaries (commit/rollback/autocommit) etc.
For the purpose of safe coding, you should always close database connections explicitly to make sure that the code was able to close itself gracefully and to prevent any other objects from reusing the same connection after you are done with it.
I would say it's fine in this case, since there will only ever be one user and the database is hosted on the same machine (more likely in the same memory space, as I think SQLite just loads as a DLL with the main application) as the application. Constantly opening and closing the connection is unnecessary.
One possible exception might be if you need to have multiple threads of your application accessing the database at the same time. Then you could either force them to wait and share a single connection object, OR you could try to create new connections for the different threads. I have never actually tried this in SQLite. This is one situation where closing the main connection and opening/closing multiple connections might be better for a desktop app.
For web applications, or client/server desktop apps, I'd suggest against leaving connections open.
74
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
