Generic Dao in Java

I'm trying to develop a general Dao in Java I tried the following This is

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;
        }

    }

}

I now 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;

    }
}

Solution

If you can use spring, I would suggest the following improvements:

>Let spring handle exceptions. > Use the jdbctemplate instead of creating your own prepared statements

Independent of using spring, I would recommend the following:

>Do not send the table name as a parameter This should be done during the initialization phase. > Use string on the ID parameter because it is more general. > Consider returning generic objects instead of collections, because collections should always contain only one object

An improved abstractdao and 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 can see, there is no exception handling or hacking using the original SQL class This template closes the resultset for you. I can't see it in your code

And 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;
        }
    }
}

to update:

When you know that ID and ID correspond to a single row in the database, you should consider returning generic objects rather than collections

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

This makes your service code more readable because you don't need to retrieve users from the list, just:

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