Should I Call The Database Connection Setup In Each Method Or Just Once In Constructor

I'm working on a small project in Java and I have got DatabaseController class which contains many methods to insert/select/delete from my database. my question is, related to sett

Solution 1:

DataSource

Firstly, it is best to put access to your database behind a DataSource interface. This gives you the power to change your strategy for getting a database connection without changing all the many places in your code where you use those database connections. See Oracle Tutorial.

Your JDBC driver should provide an implementation of DataSource. For example, consider the Postgres JDBC driver from https://jdbc.postgresql.org. That driver supplies two implementations: One for direct connections to the database, and the other for pooling connections. By using the DataSource as a façade, you could switch between either of these, or even use a 3rd party connection pooling solution, in one place in your code, without changing the many places you call DataSource::getConnection().

A third kind of implementation you might want to substitute in would be for distributed transactions.

Another example of swapping the concrete class behind the interface: Perhaps you decide to switch JDBC drivers. Perhaps for your Postgres database, you decide to switch from the jdbc.postgresql.org driver to the pgjdbc-ng driver. You want to be able to make this configuration change in one place, without breaking all your existing app code.

Here is an example of using a concrete implementation behind the DataSource interface.

public javax.sql.DataSource obtainDataSource() {
    org.postgresql.ds.PGSimpleDataSource dataSource = new PGSimpleDataSource() ;
    dataSource("AcmeInvoices database data source") ;
    source.setServerName( "localhost" ) ;
    source.setDatabaseName( "invoicing" ) ;
    source.setUser( "Scott" ) ;
    source.setPassword( "tiger" ) ;
    return dataSource ;  // Returning a reference to the object of this concrete class `PGSimpleDataSource` as an object of the interface `DataSource`. 
}

The methods you see being called in that code snippet above will vary dramatically by the capabilities of your database and of your JDBC driver. For example, if you are using encrypted connections to the database, you will have additional methods to call for configuring the encryption keys.

You set up this DataSource object early on, likely as part of your app launch. Keep a reference someplace. Or keep several: you may have different connection types (different user name & passwords) for different parts of your app where different security or different user roles are necessary.

Pass the DataSource object around your codebase to the many methods that insert/select/delete with your database. In each method, they call myDataSource.getConnection(), then go on to execute their SQL. When that method finishes its work, it should close the connection by calling Connection::close. Even better, use try-with-resources to automatically close the connection.

If the connection came from a connection pool, the connection is not really closed. Rather, the connection is returned to the pool. This is another example of the power of using an interface, here Connection. The concrete class behind the interface can choose how to respond to invoking the close method — and that concrete class can be swapped out later without breaking the calling code.

Open connections

Should I establish the database connection in DatabaseController constructor so it's opened once and left open

No, it is not generally a good idea to keep a connection open with no further work to be done.

Basically you would be creating your own connection pool. And connection pooling is not a simple matter. There are various issues to consider in pooling, and you are unlikely to think of them much less be competent to solving them. All the connection pooling tools I have seen have each had their share of bugs, issues, and limitations. So do not take on this task yourself. If you do choose to go with pooling, find a robust well-worn existing implementation. And study its documentation thoroughly to be sure you understand how to configure and use the pool properly.

Frankly, I stopped using connection pooling myself. The various risks and complications are not worth it in my opinion for systems with a lower number of less frequent number of users/connections. For example, look at the default time-out set in the various pooling solutions where the pooled connections are closed after some period of no use. If your activity in the pool is mostly lower than that time-out, then you effectively won't be benefiting from the pool. Also, I have found that most of the claims about opening a connection to the database as expensive are exaggerated, especially for a local database on the same machine.

Examples

Should I place a method to connect to the database before my insert/delete/select methods

Here are some example scenarios showing how you should get-and-close the database connection close to where you use the connection for some specific database task.

In your login screen where you authenticate the user by looking up users and groups stored in your database, when the user clicks the Login button, your code should retrieve a reference to the DataSource object, call getConnection, do the user-and-groups lookups, then immediately close the connection.

When the user goes on to lookup overdue invoices, do the same. Retrieve the DataSource, call getConnection, run your SQL to find any overdue invoices, load that invoice data into memory as a cache, call Connection::close to close the connection to the database, and proceed to build your GUI to display the cached invoice data.

When the user clicks a button to take some action on one of those invoices, do the same process. Retrieve the DataSource, call getConnection, run your SQL to fetch the particular invoice row for the desired primary key value, retrieve additional invoice fields and related rows such as invoice-items, call Connection::close to close the connection to the database, and proceed with your action such as generating an email containing the cached invoice details and its invoice-item rows. Notice how you might use the connection multiple times within this action method, first to hit the invoice table, then again to hit the invoice-item table. When finally all the immediate work at hand is complete, close the connection.

Remember, in each of these several times where you got a connection and closed a connection, if pooled:

• You are getting a connection that may or may not be new.
• “Closing” returns the connection to the pool rather than actually closing it.

The pool may close the connection at some point, and at some point perhaps replace it with a fresh connection. But that is transparent to all your user-authentication code and invoice lookup code.