Lookup table pattern

[chichirico]

Does anyone have advice (or know of a pattern) on implementing classes to represent lookup tables? We have a large number of simple id/name tables that I would like to cache at application start time. Typically I would just use the new enum featur in 1.5 but the values could potentially change and we'd like to be able to update them w/o having to recompile.

[wpoitras]

I would suggest iBatis SQLMaps. You could define all the queries for your id/name tables in a single file, with each statement returning a simple class with the id and name in it. iBatis supports caching. Your code might look something like this:

interface LookupDAO {
   List lookup(String enumName); // Returns List of EnumValue objects
}

class EnumValue {
  public String name;
  public int id;
}

class LookupDAOImpl implements LookupDAO {
  private SqlMapTemplate sqlMapTemplate;
  public SqlMapTemplate getSqlMapTemplate() { return sqlMapTemplate; }
  public void setSqlMap(SqlMapTemplate sqlMapTemplate) {
      this.sqlMapTemplate = sqlMapTemplate; 
  }
  public List lookup(String enumName) {
    return sqlMapTemplate.executeQueryForList(enumName, null);
  }
}

or perhaps if all your columns are named the same, something a little simpler (meaning no SQLMap definition)

class LookupDAOJDBCImpl implements LookupDAO {
  private JdbcTemplate jdbcTemplate;
  private Map enumMap = new HashMap();
  public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; }
  public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
    this.jdbcTemplate = jdbcTemplate;
  }
  private RowMapper rowMapper = new RowMapper() {
    public Object mapRow(ResultSet rs, int rowNum) {
      EnumValue result = new EnumValue();
      result.id = rs.getInt(1);
      result.name = rs.getString(2);
      return result;
    }
  };
  public synchronized List lookup(String enumName) {
    List result = (List)enumMap.get(enumName);
    if (result == null) {
      result = jdbcTemplate.queryForList("SELECT id, name from " + enumName, null, rowMapper);
      enumMap.put(enumName, result);
    }
    return result;
  }
}