generic DAO in java

Question

I am trying to develop generic DAO in java. I have tried the following. Is this a good way to implement generic DAO? I don't want to use hibernate. I am trying to make it as generic as possible so that I don't have to repeat the same code over and over again.

public abstract class  AbstractDAO<T> {

    protected ResultSet findbyId(String tablename, Integer id){
        ResultSet rs= null;
        try {
           // the following lines are not working
            pStmt = cn.prepareStatement("SELECT * FROM "+ tablename+ "WHERE id = ?");
            pStmt.setInt(1, id);
            rs = pStmt.executeQuery();


        } catch (SQLException ex) {
            System.out.println("ERROR in findbyid " +ex.getMessage() +ex.getCause());
            ex.printStackTrace();
        }finally{
            return rs;
        }

    }

}

Now I have:

public class UserDAO extends AbstractDAO<User>{

  public List<User> findbyid(int id){
   Resultset rs =findbyid("USERS",id) // "USERS" is table name in DB
   List<Users> users = convertToList(rs);
   return users; 
}


 private List<User> convertToList(ResultSet rs)  {
        List<User> userList= new ArrayList();
        User user= new User();;
        try {
            while (rs.next()) {
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setFname(rs.getString("fname"));
                user.setLname(rs.getString("lname"));
                user.setUsertype(rs.getInt("usertype"));
                user.setPasswd(rs.getString("passwd"));
                userList.add(user);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return userList;

    }
}

Pyrogallate

plz see the line saying // the following lins in not working; – Pyrogallate

Why don't you want to use Hibernate (or other ORM)? – Heptane

I can understand not wanting to use Hibernate :-) – Gylys

Beware that you have to call close() on JDBC objects such as ResultSet when you are done with them. Where are you going to call close() on your ResultSet? – Fountainhead

Answer 1

My advice:

• Don't write a generic DAO; Generic classes come back to bite you when you realise they don't quite do what you need in a specific situation and often end up growing in complexity to cover the ever-increasing array of use-cases. Better to code application specific DAOs and then attempt to generify any common behaviour later on.
• Consider using Spring JDBC to write app-specific DAOs but in a much more compact and less error-prone fashion than JDBC. Also, unlike Hibernate, Spring JDBC only acts a thin wrapper around raw JDBC giving you finer grained control and more visibility.

Example

// Create or inject underlying DataSource.
DataSource ds = ...
// Initialise Spring template, which we'll use for querying.
SimpleJdbcTemplate tmpl = new SimpleJdbcTemplate(ds);     

// Create collection of "Role"s: The business object we're interested in.
Set<Role> roles = new HashSet<Role>();

// Query database for roles, use row mapper to extract and create
// business objects and add to collection.  If an error occurs Spring
// will translate the checked SQLException into an unchecked Spring
// DataAccessException and also close any open resources (ResultSet, Connection).
roles.addAll(tmpl.query("select * from Role", new ParameterizedRowMapper<Role>() {
  public Role mapRow(ResultSet resultSet, int i) throws SQLException {
    return new Role(resultSet.getString("RoleName"));
  }
}));

Gylys

Another vote for Spring JDBC. It handles a lot of the boilerplate connection stuff (to avoid resource leaks). In addition, it provides a much nicer way of mapping columns to your objects. One other comment, I wouldn't hard-code the primary key column name. As soon as you do that, someone comes along a creates a table with a column named something else, or uses a multi-column primary key. – Perceptive

Answer 2

If you can live with Spring, I will suggest the following improvements:

• Let Spring do the exception handling.
• Use JdbcTemplate instead of creating prepared statements yourself.

Independent of using Spring, I will recommend the following:

• Don't send the table name as parameter. That should be done in the initialization phase.
• Use a String on the id parameter, since that's much more generic.
• Consider returning a generic object instead of a collection, since the collection should always contain only one object.

An improved AbstractDao with Spring:

import java.util.Collection;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public abstract class AbstractDao<T> {

    protected final RowMapper<T> rowMapper;

    protected final String findByIdSql;

    protected final JdbcTemplate jdbcTemplate;

    protected AbstractDao(RowMapper<T> rowMapper, String tableName,
            JdbcTemplate jdbcTemplate) {
        this.rowMapper = rowMapper;
        this.findByIdSql = "SELECT * FROM " + tableName + "WHERE id = ?";
        this.jdbcTemplate = jdbcTemplate;
    }

    public  Collection<T> findById(final String id) {
        Object[] params = {id};
        return jdbcTemplate.query(findByIdSql, params, rowMapper);
    }
}

As you see, no exception handling or hacking with the primitive SQL classes. This templates closes the ResultSet for you, which I can't see in your code.

And the UserDao:

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class UserDao extends AbstractDao<User> {

    private final static String TABLE_NAME = "USERS";

    public UserDao(JdbcTemplate jdbcTemplate) {
        super(new UserRowMapper(), TABLE_NAME, jdbcTemplate);
    }

    private static class UserRowMapper implements RowMapper<User> {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setUserName(rs.getString("username"));
            user.setFirstName(rs.getString("fname"));
            user.setLastName(rs.getString("lname"));

            return user;
        }
    }
}

Updated:

When you know the id and the id corresponds to a single row in the database, you should consider returning a generic object instead of a collection.

public T findUniqueObjectById(final String id) {
    Object[] params = {id};
    return jdbcTemplate.queryForObject(findByIdSql, params, rowMapper);
}

This makes your service code more readable, since you don't need to retrieve the user from a list, but only:

User user = userDao.findUniqueObjectById("22");

Fauteuil

I am trying to implement your approach and everything seems OK except deciding where and how to initialize userDao object. Before implementing your approach I was using userDao with autowired annotation in userService class. But now in your example there isn't any empty constructor and cannot use dao as autowired object. I thought there can be empty constructor of dao class which calls other constructor with autowired jdbctemplate. Is that useful? – Glade

Answer 3

It is okay but change the method

private List<User> convertToList(ResultSet rs)  { 
        List<User> userList= new ArrayList(); 
        User user= new User();; 
        try { 
            while (rs.next()) { 
                user.setId(rs.getInt("id")); 
                user.setUsername(rs.getString("username")); 
                user.setFname(rs.getString("fname")); 
                user.setLname(rs.getString("lname")); 
                user.setUsertype(rs.getInt("usertype")); 
                user.setPasswd(rs.getString("passwd")); 
                userList.add(user); 
            } 
        } catch (SQLException ex) { 
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex); 
        } 

        return userList; 

    } 

to

private List<User> convertToList(ResultSet rs)  { 
        List<User> userList= new ArrayList<User>(); 
        try { 
            while (rs.next()) { 
                User user= new User();
                user.setId(rs.getInt("id")); 
                user.setUsername(rs.getString("username")); 
                user.setFname(rs.getString("fname")); 
                user.setLname(rs.getString("lname")); 
                user.setUsertype(rs.getInt("usertype")); 
                user.setPasswd(rs.getString("passwd")); 
                userList.add(user); 
            } 
        } catch (SQLException ex) { 
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex); 
        } 

        return userList; 

    } 

User object should be created inside while loop.

Adelaadelaida

This code will leak resources if an SQLException is thrown. You need to ensure the ResultSet, Statement and Connection are closed. Easiest way is to use a framework like Spring JDBC rather than raw JDBC. – Gylys

Is that because of this? (missing space before WHERE) Change the below one "SELECT * FROM "+ tablename+ "WHERE id = ?" to "SELECT * FROM "+ tablename+ " WHERE id = ?" – Adelaadelaida

Answer 4

Do not reinvent the wheel, you can already find good projects doing this, example generic-dao project on google.

EDIT: answered too quickly probably, the google project is JPA based but nevertheless you can use some of the concepts inside it.

Fluorescent

Answer 5

You need to add a space before your "WHERE" clause see below:

pStmt = cn.prepareStatement("SELECT * FROM "+ tablename+ "WHERE id = ?");

to

 pStmt = cn.prepareStatement("SELECT * FROM "+ tablename+ " WHERE id = ?");

Coper

Answer 6

If I've correctly understood the problem statement, you are trying to implement kind of an isolation layer between your services and a plain database exposed through a JDBC interface. The isolation layer would serve as a data mapper of your POJO domain objects to SQL data sets. That is precisely the task of iBATIS library, which I recommend you to ponder over instead of implementing the homebrew GenericDAO class.

Lakia

Answer 7

Even though everyone suggests Spring and its API here, it uses metadata and it's a bad combination of code. So don't use generic DAO's or Spring at all.

Generic code is heavy and does multiply your load on.

Canzonet

It sounds like you had a bad experience with generic code. Metadata is not bad if done properly--the more info you move into metadata from code, the better your remaining code should be (If done correctly). Generic code is really the only way to go in many cases (DRY trumps most other guidelines/smells and is the root of most good code and coding practices). – Laughton