DAO design pattern: where should the database access be implemented?

I am building a Java application that uses a database and I’m using a DAO design pattern: in my code, all objects classes have an associated DAO class that implements an interface with get, save and update methods. For instance, for a User object, I will have the following class (ConnectionDB implements the connection to the database):

public class UserDAO implements Dao<User, String> {

    private final static String TABLE_NAME = "users";
    private final static UserDAO instance = new UserDAO();
    public static UserDAO getInstance() {
        return instance;
    }

    private UserDAO() {

    }

    @Override
    public User get(String username) throws SQLException {
        String query = "SELECT * FROM " + TABLE_NAME + " WHERE username = ?";

        PreparedStatement stmt = ConnectionDB.getInstance().prepareStatement(query);
        stmt.setString(1, username);
        ResultSet result = stmt.executeQuery();

        if (!result.next())
            return null;

        User user = new User(
            result.getInt("id"),
            username,
        );

        stmt.close();
        result.close();

        return user;
    }

    /* same thing for save and update */
}

Here is the Dao interface for reference:

public interface Dao<T, S> {
    T get(S id) throws SQLException;

    ArrayList<T> getAll() throws SQLException;

    void save(T t) throws SQLException;

    void update(T t) throws SQLException;
}

This way works pretty fine but as I have more and more classes in my application, and a DAO class for each one of them, I have a lot of repetitive code. For instance, the only difference between the get implementation on different objects is the name and type of the primary key and the call to the constructor.

In order to make the code more generic, I tried to implement a fetchItem method in the ConnectionDB class that would be able to query an item from the database:

public <T> HashMap<String, Object> fetchItem(String table_name, String pk, T id) throws SQLException {
    String query = "SELECT * FROM " + table_name + " WHERE " + pk + " = ?";

    PreparedStatement stmt = prepareStatement(query);
    stmt.setObject(1, id);
    ResultSet result = stmt.executeQuery();

    if (!result.next())
        return null;

    HashMap<String, Object> values = buildObject(result);

    stmt.close();
    result.close();

    return values;
}

public HashMap<String, Object> buildObject(ResultSet result) throws SQLException {
    ResultSetMetaData metadata = result.getMetaData();
    int columnCount = metadata.getColumnCount();
    HashMap<String, Object> values = new HashMap<>();
    for (int i = 1; i <= columnCount; i++) {
        values.put(metadata.getColumnName(i), result.getObject(i));
    }
    return values;
}

With this implementation, I can now replace my first get method in the UserDAO class by the following simplified code:

public User get(String username) throws SQLException {
    HashMap<String, Object> values = ConnectionDB.getInstance()
            .fetchItem(TABLE_NAME, "username", username);

    if (values == null || values.isEmpty())
        return null;


    return new User(
        id,
        (String) values.get("String")
    );
}

While this new implementation is simpler and allows the get methods to only do what they’re supposed to do (here, create a User object with the right parameters from the DB), I find it a bit dangerous as I’ll have to make a lot of casts; as I have a lot of Object variables in my code I’m not sure whether it’ll be easy to debug the code if something fails in any of these function calls.

So here’s my question: which implementation is better, easier to maintain and safer?

Answer

Connection DB is a very bad place to define such implementation. It is just a link with a specific database thats all. You violate single responsibility rule. Better to implement base generic class for all DAO’s and place common logic there.
Also if you will use Hibernate framework, you will not need to work with query strings and Object variables casts.