OOP Programming with database access

Question

I am working on a project with database access. Here is the outline of the classes i have implemented

public class Foo {

    private String id;
    private String name;
    private int x;
    private String y;
    private String z;
    ...

    public Foo(String id) throws SQLException {
       this.id=id;
       try (Statement stmt = MyConnectionManager().getConnection().createStatement()) {
          ResultSet rs = stmt.executeQuery("SELECT * FROM " + TABLE_NAME
                + " WHERE(id='" + this.id + "')");
          if (!rs.next()) {
             throw new NoExistException();
          }
          this.name = rs.getString("Name");
          this.x = ...
          ...
       } catch (SQLException ex) {
          throw ex;
       }
    }
    public int getId() {
       return this.id;
    }

    public String getName() {
    .
    .
    .

But when i looked at most oop samples i have found that most people use an additional class for database access. I don't know what im going to say is a blunder. Those codes access database for initializing each member variable. But in my code database is accessed only once to initialize my object. Should i change this approach. If i change will it affect the speed of my application when it wants to initialize more objects together (may be hundreds). I think this might be a duplication question. But i didn't find any satisfactory answer for my particular problem.

Answer 1

It is a design choice, some people like to modularize their program, it is regarded as a good practice since modification and maintenance do not affect other classes.

If you create a new class for your database access (Refactoring), it allows other classes to access this class, as well, if they require database access.

Answer 2

You approach violates the Single Responsibility Principle and Separation of Concerns - basically a class shouldn't be both a domain class and access the database.

An approach where each member variable is independently initialized and that initialization involves a database call is even worse and is not going to work either - as well as violating the previously mentioned Single Responsibility Principle and Separation of Concerns it simply won't perform.

The way this is most commonly handled is through a data access layer where you have class that performs the database access and maps the result sets to new instances of your domain classes.

What this means in your case is you'd end up with DAO interface that might look something like this:

public interface FooDao
{
  public Collection<Foo> getAll() throws DaoException;

  public Foo getByIdentity(String id) throws DaoException;

  public Collection<Foo> getByName(String name) throws DaoException;

  public void save(Collection<Foo> foos) throws DaoException;
}

The interface might have a series of get methods or expose some sort of criteria based mechanism to allow you to construct more elaborate queries (there's some code I wrote a while back that does this here) but the important thing is that it is this interface all your code uses. You'd then implement this interface as you please (using direct JDBC if your object model is simple or Hibernate if you need something more advanced) or mock it in your unit tests.

When you introduce a Bar class you'd repeat the pattern defining a BarDao and implementing as necessary again.