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

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:

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.

• Unless you're retrieving the connection thousands of times per second, you won't notice any performance difference – the communication with the DB will take far more time. Instead, think about testability: how can I swap out the DB connection in an unit test? Your code depends on a DB connection, so how is this dependency injected? – amon May 15 '16 at 6:13
• @amon - Thanks. Could you please elaborate in an answer? – Sipo May 15 '16 at 6:17
• When you say "connection", are you talking about some data like a connection string, or some object that you can call methods on to execute queries, etc.? – Ben Aaronson May 19 '16 at 15:02
• Since database transactions are per connection a static variable is not suitable for multitasking environments (i.e. in a web app) – k3b May 20 '16 at 6:42

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.

• Thanks. I am not sure I understand the answer. Which of the approaches is better? – Sipo May 16 '16 at 4:19
• @Sipo This is a variant of option 2: Instance variables. Except that you don't pass the plain database connection itself, but instead a factory or a container providing ALL of the runtime state defining the behavior of the instance. None of the other two options work for this. – Ext3h May 20 '16 at 16:38

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.

• Good answer. For the record, if you are 100% sure that you will always connect once to just one database, you are wrong. – user949300 May 19 '16 at 16:58
• @user949300: I have worked on some applications in the past where this was a reasonable design, from that experience I cannot agree to you in this generality. All the three scenarios I scetched above can make sense, one has to switch on his/her brain, analyse the specific situations and make an architectural decision. The OP, however, asked for a "general rule, motivated by OOP or performance", and that does IMHO not make sense - there is no such rule. – Doc Brown May 19 '16 at 19:14
• Doc - I worked on 3 projects that assumed a single database, and, as features were added, all three needed major changes to support multiple DBs. – user949300 May 20 '16 at 3:38
• @user949300: this is not a contradiction, quite the opposite. As I wrote, all the cases I described can be a sensible approach for different real world projects. My point is, one should be careful not to overgeneralize from some restricted experience in the past. There are applications where it is clearly better to plan for the future and not to stick to "one database" approach, there are other applications where this would be overengineering. – Doc Brown May 20 '16 at 6:25
• Somewhat agree, but to clarify: If somebody is "pretty sure" that one DB is OK, or at least "good enough for an initial project", that is fine. If, quoting your third bullet, they are "100% sure", they are wrong / deluded. – user949300 May 20 '16 at 19:20

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.

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