Best way for many classes to reference a database connection class

Question

I'm soon going to be starting the development stage of my coursework and the current design is to have a single class that handles database connections and many classes that call it; the database class is supposed to open the connection and pass queries along blindly, other classes are responsible for the contents of those queries.

What I want to know is what is the best way for these reference to know about the database class? My instinct would be to make the methods in the Database class static and call them as Database.method() but is there a better way?

P.S. was reference the right word for this? If not what is the right word, it would help next time I have a similar quesiton.

Answer 1

Avoid anything static. Prefer "Parameterisation from Above".

So what you want to do is create your database wrapper object near your 'main'. Then pass that as a constructor argument to objects that need it.

Answer 2

The Singleton pattern will help you out for this. See more info about it here.

The pattern (rightly) is disliked a lot around here, because it essentially is making a bunch of methods global, which is to be avoided in the object-oriented world - but for coursework and for what you are trying to achieve it might be easiest.

Answer 3

If you go as you've suggested then you're going to have a hard time unit testing your classes. One way to go might to pass your database information to the classes making the query, e.g.

Query query = new GetPersonQuery(Database database);
query.run(); // or whatever

This will work especially well if you're using the DAO pattern, so:

PersonDao dao = new PersonDao(Database database);
dao.findAll();

The database can then wrap up whatever it is you need, e.g. Connection pooling, caching etc.

Answer 4

Make the connection a singleton or a monostate.

Answer 5

Be cautious.

Singleton will be a bottleneck.

java.sql.Connection is NOT thread safe, so you could run into issues there.

I'd recommend writing your app with service and persistence layers completely separated. Both should be based on interfaces. The service layer is the one that knows about units of work and transactions, using model and persistence objects to fulfill them. The service should be responsible for acquiring the Connection, making it available to the persistence tier, handling transactions, and closing the Connection in the same method scope in which it was created.

Scope and cleanup are key. If you don't do this, you'll exhaust the database connections.

You don't mention connection pools. I'd recommend one.

Have a look at Spring. Its JDBC module handles all this beautifully. If you can't use Spring for your assignment, at least it'll be a good model for how to design your implementation.

Answer 6

If you want to store the connection statically, at least use a thread-local. But as others said here - better to inject the connection, or its wrapper, into the objects that need to use it.

Answer 7

The traditional approach is to have a DAO (Data Access Object) for each data class.

I.e. if you have a data class "Person" you also have a class "PersonDAO" that implements methods like findById(), findAll(), save(Person) etc. Basically the DAO class handles all the DB interaction.

The constructor of the DAO class could simply accept a Connection object and thus externalize the problem of creating connections or it could invoke a factory method somewhere that doled out a Connection object.

In either case you'll likely want to have such a factory method.

public class Database{
   public static Connection getConnection(){
      // Create a new connection or use some connection pooling library 
   }
}

As someone pointed out java.sql.Connection is not thread safe so you should not hand out the same connection each time unless you are sure that multiple threads will not be accessing the method.

Of course if you need to create a new connection for each call you'll also need to close the connections once you're done with them. The simple approach is to add a close() method to the DAOs and have them take care of it. This does impose a burden on the code using the DAO.

Even if you use connection pooling it is still necessary to close the connections (return to the pool) once you are done with them.

Some one suggested using Thread local to have a per thread connection. This works in some instances, but wouldn't be useful for a web application where each request is a new thread (that is never reused, might as well not store a reference).

You could however take advantage of this in a webapp if you've configured it so that after handling each request a call is made to Database.closeConnection() which then takes care of closing a Tread local connection if one exist.