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 use 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 in conjunction with different databases at the same time, and so on. Thus the "best" solution is the one which matches your requirements best.

Answer 3

For a tiny program that you write just to learn to program, it doesn't matter. In fact, it is probably worth experimenting with all the kinds of variables, while learning, just to get familiar with how they work.

In a larger production program, all of these options will be used, in different parts of the code. Most of the code will use a parameter to supply the connection. A smaller part of the code will be concerned with setting up the connection(s) your software uses, and that code will typically use instance or static fields to hold onto the connection; it will pass that parameter around to all the rest of the code.

The parameters themselves can be complex for a larger piece of software, so just managing the configuration of your program can become a large programming task of its own.

Answer 4

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 5

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".

Answer 6

OOP, performance, and technical implementation are three very different beasts that need their own considerations.

One of the main OOP abuses stems from the OOP structure being altered to account for things that don't relate to OOP. It sounds like you're on that track. Good intentions or not, it seems like you're starting to pave the road to hell.

should the connection be a static field, an instance field, or a local variable?

This decision itself does not impact performance, since you're not discussing when/how this is called, nor how the underlying object is implemented.

Several commonly used database connection contexts use some kind of underlying connection pool that you as a developer have no direct access to. Regardless of how you'd structure the access to these contexts, there'll be a shared connection pool anyway, which mitigates the issue that led to you asking this question.

The question is too broad to be meaningfully answered with further detail. It requires technical implementation details of the database technology that you're simply not providing.

The thing is, I'm not saying you have to provide these details. Instead, I'd suggest that you don't pursue answering this question until you have an actual concrete problem to address. The vagueness of your question sounds to me like you're trying to find a problem to solve rather than trying to solve a problem you're faced with, which is not a productive avenue to walk down (this is also a performance issue, i.e. one of developer effort)