From the perspective of OOP, taking performance into account - should a database connection be a static field, an instance field, or a local variable?

Question

When methods use a database connection, should the connection be a static field, an instance field, or a local variable?

Here are my doubts. I dithered which are correct and which are baseless. I tried to give sources to my assumptions, but most of it stems from things I have read here and there, and probably did not understand properly:

static fields:

Pros:

• DRY - You can use the connection from any class.
• Shorter code (not always a good thing).

Cons:

• This is practically creating a global variable*, which is considered bad in OOP.
  ^{_{*Perhaps you could refer to the connection as global constant which is not concidered bad programming?}}
• Sometimes fields are referred as states since they describe the state of the class. But logically, the connection do not describe the class, the class's methods just use it.

instance fields:

Pros:

• DRY - You can use the connection from any method in the class.
• Shorter code (Again, not always good).

Cons:

• Again, logically, the connection does not describe the object, the object's methods just use it.

local (method) variable:

Pros:

• Methods open the connection only when they need it*.
  ^{_{*Although, even using a field, you could use the using statement to close the connection when needed.}}

Cons:

• DRY - Very repetitive code.

So, as I said, I can see advantages and disadvantages of the three approaches. But what is the most common approach, and more importantly - the most accurate approach from the perspective of OOP? And which approach leads to better performance?

Thanks.

Answer 1

In general, when used database connections are local variables, since they are almost always retrieved from some factory to help abstract away implementation details. Pragmatically, those details matter since you almost always want to pool your database connections, and the factory handles all of that.

The factory in turn usually is an instance variable since you often want the flexibility of using different instances where needed.

Answer 2

First, lets make a clear statement about performance: in 99,99% of all real world cases the performance will be literally the same, so - as usual - performance is not a useful criterion for making such a decision.

The primary questions you should ask yourself here is:

• "can I imagine a situation where I need different database connections for different functions in my class" (or even for the same function within the same object, when called in a multithreading context, for example)?

• "do I have to expect a need for different database connections for different objects", or do I have to assume there is not always a "global context" available where the "global db connection" might be available (for example, in a testing situation)?

• "am I 100% sure my whole program / library will always connect once to just one database, and there will be never a need to use a different connection / database at the same time?"

If the answer to the first question is "yes", it is clear you need the connection as a parameter for your functions. If the answer to the first is "no", but to the second is "yes", you should instance fields. And if the answer to the third question is "yes", you can use a "global variable" (or static field), probably avoiding some boilerplate code.

So this all boils down to "there is not the one and only best solution". It depends on what kind of program you are writing, is it an application using always one db or many, is it a library which might potentially used agains different databases at the same time, and so on. Thus the "best" solution is the one which matches your requirements best.

Answer 3

A config class is probably best as it will be DRY and implement a interface (keeps it testable). The interface must have a method taking in sql strings and returns a object.

Several implementions can be done for different environments.

Answer 4

Short answer: From the perspective of OOP this should be an implementation detail.

You can hide this implementation detail if you have an instance method that is used like local method variable. This way your three options are implementation details that can be easily changed any time with just a few lines of code:

class MyService {
    protected DBConnection getDBConnection() {
        return new DBConnection(...); // version 3: local variable in method
    }
    protected void closeDBConnection(DBConnection con) {
        con.close(); // version 3: local variable in method
    }

    doSomething() {
        protected DBConnection con = getDBConnection();
        ...
        closeDBConnection(con);     
    }
}

Changing the implementation detail to version 1 or 2 only requires to modify getDBConnection() and closeDBConnection() without affecting the servicemethods doSomething1(), doSomething2(), ...

the static version would become

class MyService {
    private static DBConnection sConnection = null;
    protected DBConnection getDBConnection() {
        if (sConnection == null) sConnection = new DBConnection(...);
        return sConnection;
    }
    protected void closeDBConnection(DBConnection con) {
        // do nothing
    }
 }

The design pattern for this is called "Factory Method".