Convert methods to a generic method in DAO layer

Question

Currently I'm having some methods in my DAO layer with multiple select queries. What I was thinking is to have a generic method for all these three methods so that it can be used for further also. Here are my methods.

public List<Customer> findAll(){

    String sql = "SELECT * FROM CUSTOMER";

    List<Customer> customers  = getJdbcTemplate().query(sql,
        new BeanPropertyRowMapper(Customer.class));

    return customers;
} 

For finding phone numbers of a customer.

public List<Phone> findPhoneNumbers(int custId){

    String sql = "SELECT * FROM PHONE WHERE CUST_ID="+custId;

    List<Phone> phoneNumbers  = getJdbcTemplate().query(sql,
        new BeanPropertyRowMapper(Phone.class));

    return phoneNumbers;
} 

and so on.

Can these methods can be converted in a single generic method, so that it can be called from my service layer. Any suggestions or ideas would be greatly appreciated.

Answer 1

public <T> List<T> findAll(String sql, Class<T> clazz) {
   return getJdbcTemplate().query(sql, new BeanPropertyRowMapper(clazz));
}

Then you call

String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = findAll(sql, Customer.class);

Or

String sql = "SELECT * FROM PHONE WHERE CUST_ID="+custId;
List<Phone> phoneNumbers = findAll(sql, Phone.class);

The problem is that you have to construct sql statement before calling the method.

Maybe you want an android sqldatabase-like interface and construct sql statement in the method.

public <T> List<T> findAll(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy,  Class<T> clazz) {
    String sql = // construct sql statement
    return getJdbcTemplate().query(sql, new BeanPropertyRowMapper(clazz));
}

I'm not familiar with sql statement, you may find something in the Android SDK SQLiteQueryBuilder.buildQueryString(...).

If you really want to return a single item not a list with one item, you need another method.

public <T> T findOne(String sql, Class<T> clazz) {
    // your sql statement should contains something like "limit 1"
    List<T> result = findAll(sql, clazz);
    return result.isEmpty() ? null : result.get(0);
}

If you need one method but you require to return List<T> for multiple data and T for single data. Try this:

public <T> Object findAll(String sql, Class<T> clazz, bool one) {
    List<T> all = getJdbcTemplate().query(sql, new BeanPropertyRowMapper(clazz));
    return one ? all.get(0) : all;
}

But I don't really suggest this approach. I don't think it is necessary to return a single item rather than a list with a single item.