Announcement Announcement Module
No announcement yet.
Lookup table pattern Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Lookup table pattern

    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.

  • #2
    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();
 = rs.getInt(1);
 = 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;