2

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.

4
  • 6
    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
    Commented May 15, 2016 at 6:13
  • @amon - Thanks. Could you please elaborate in an answer? Commented May 15, 2016 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.? Commented May 19, 2016 at 15:02
  • 1
    Since database transactions are per connection a static variable is not suitable for multitasking environments (i.e. in a web app)
    – k3b
    Commented May 20, 2016 at 6:42

6 Answers 6

5

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.

2
  • Thanks. I am not sure I understand the answer. Which of the approaches is better? Commented May 16, 2016 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
    Commented May 20, 2016 at 16:38
3

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.

5
  • Good answer. For the record, if you are 100% sure that you will always connect once to just one database, you are wrong.
    – user949300
    Commented May 19, 2016 at 16:58
  • 1
    @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
    Commented May 19, 2016 at 19:14
  • 1
    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
    Commented May 20, 2016 at 3:38
  • 1
    @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
    Commented May 20, 2016 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
    Commented May 20, 2016 at 19:20
1

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.

0

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.

0

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

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.