ActiveRecord Naming Conventions with Spring JDBC

One of the best choices we made at my current job was splitting the web development from the heavy message processing system, allowing the former to be written in Ruby on Rails and the latter to be written in Java. However, for ease of development, we allowed the two code bases share a single database. What then to do about database naming conventions? Those familiar with ActiveRecord will know that it is very particular about its database naming conventions so it was up to the Java code to play nicely. The first solution to present itself required hardcoding database names in each Java object but a more elegant solution seemed possible and, after the discovery of one small but important 3rd party project, it revealed itself.

Inflection

In Rails, the class responsible for converting object names to database names is called Inflections. For those unfamiliar with the term (as I was), inflection is the modification of a word to express different grammatical categories. Armed with this new knowledge I set about googling and was relieved to find that Tom White had already implemented a Java Inflector. However, the Java Inflector only implements the pluralization aspect of the Rails Inflections; for this to work I was going to need some additional string manipulation tools. Luckily the old Apache Commons StringUtils was there to lend a helping hand. Finally, confident in my string manipulation tools, the next step was to look into the JDBC side of things.

ORMs

When dealing with mapping objects to and from a database Hibernate is always the name that pops up. I’ve worked with it in the past and, while incredibly powerful, I always felt like I was wielding a hatchet when what I wanted was a surgical knife. I’ve heard its configuration has been simplified with JPA but its still a beast of a project that relies on a bit more magic than I’m comfortable with. In a more recent project I played with iBatis (now myBatis) and enjoyed the simplicity but felt overwhelmed by the xml configuration. Version 3.0 supports annotation configuration which greatly simplifies this but I was looking for something based on naming conventions so I wouldn’t have to supply the mapping myself. I don’t mind getting down and dirty with some SQL so I opted for Spring JDBC. Spring provides a super thin layer on top of raw JDBC which takes what is a fairly developer hostile API and makes it a breeze to use, all while adding some wonderful utilities to speed development.

Putting It All Together

AbstractDbObj

The first step was to create an AbstractDbObj to allow all other database objects to inherit from. This is where the logic for creating db names using the Inflector and StringUtils lives along with some other shared code.

import static com.google.common.collect.Collections2.filter;
import static com.google.common.collect.Collections2.transform;
import static org.apache.commons.lang.StringUtils.join;
import static org.apache.commons.lang.StringUtils.lowerCase;
import static org.apache.commons.lang.StringUtils.splitByCharacterTypeCamelCase;
import static org.jvnet.inflector.Noun.pluralOf;
import java.util.Collection;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;
import org.apache.commons.beanutils.BeanMap;
import org.apache.commons.lang.builder.EqualsBuilder;
import org.apache.commons.lang.builder.HashCodeBuilder;
import org.apache.commons.lang.builder.ReflectionToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import com.google.common.base.Function;
import com.google.common.base.Predicate;
import com.google.common.collect.ImmutableSet;

public abstract class AbstractDbObj  implements DbObj {
  private static final String[] EXCLUDED_FIELD_NAMES = new String[] { "columnNames", "map", "fieldNames", "tableName" };
  private static final Predicate<String> IS_COLUMN = new Predicate<String>() {
    private final ImmutableSet<String> notColumns = ImmutableSet.of( "class", "tableName", "columnNames", "fieldNames" );
    public boolean apply( final String propName ) {
      return !notColumns.contains( propName );
    }
  };

  private static final ConcurrentMap<Class<? extends LocaDbObj>, String> TABLE_NAME_LOOKUP = new ConcurrentHashMap<Class<? extends LocaDbObj>, String>();

  private static final Function<String, String> TO_COLUMN = new Function<String, String>() {
    public String apply( final String fieldName ) {
      return underscore( fieldName );
    }
  };

  private static String tableName( final Class<? extends LocaDbObj> clazz ) {
    return pluralOf( underscore( clazz.getSimpleName() ) );
  }

  protected static String underscore( final String camelCasedWord ) {
    return lowerCase( join( splitByCharacterTypeCamelCase( camelCasedWord ), '_' ) );
  }

  private Integer id;
  private final Collection<String> fieldNames;
  private final String tableName;
  private final Map<String, Object> map;
  private final Collection<String> columnNames;

  @SuppressWarnings( "unchecked" )
  protected AbstractLocaDbObj() {
    super();
    final Class<? extends LocaDbObj> clazz = getClass();
    String tblName = TABLE_NAME_LOOKUP.get( clazz );
    if ( tblName == null ) {
      tblName = tableName( clazz );
      TABLE_NAME_LOOKUP.put( clazz, tblName );
    }
    tableName = tblName;
    final BeanMap beanMap = new BeanMap( this );
    fieldNames = filter( beanMap.keySet(), IS_COLUMN );
    columnNames = transform( fieldNames, TO_COLUMN );
    map = beanMap;
}

  @SuppressWarnings( "unchecked" )
  protected AbstractLocaDbObj( final String tableName ) {
    super();
    this.tableName = tableName;
    final BeanMap beanMap = new BeanMap( this );
    fieldNames = filter( beanMap.keySet(), IS_COLUMN );
    columnNames = transform( fieldNames, TO_COLUMN );
    map = beanMap;
  }

  @Override
  public boolean equals( final Object obj ) {
    return EqualsBuilder.reflectionEquals( this, obj, EXCLUDED_FIELD_NAMES );
  }

  public String getColumnName( final String fieldName ) {
    return underscore( fieldName );
  }

  public Collection<String> getColumnNames() {
    return columnNames;
  }

  public Collection<String> getFieldNames() {
    return fieldNames;
  }

  public Integer getId() {
    return id;
  }

  public String getTableName() {
    return tableName;
  }

  @Override
  public int hashCode() {
    return HashCodeBuilder.reflectionHashCode( this, EXCLUDED_FIELD_NAMES );
  }

  public void setId( final Integer id ) {
    this.id = id;
  }

  public Map<String, Object> toMap() {
    return map;
  }

  @Override
  public String toString() {
    return new ReflectionToStringBuilder( this ).setExcludeFieldNames(EXCLUDED_FIELD_NAMES ).toString();
  }
}

A few things to point out. First, I’m making use of the Google Collections Function and Predicate. For more info on that check out the developer.com article. Second, I’m using Apache BeanUtils BeanMap which uses reflection to easily create a property to value map of a JavaBean. Finally, I’m storing the table names in a lookup since the pluralization a very expensive operation. The rest should hopefully be somewhat straight forward.

SimpleJdbcInsert

Using Spring’s SimpleJdbcInsert, combined with the heavy lifting in the abstract class, insertions are practically free as you can see in the following code.

// Get Datasource from Spring Context
SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert( dataSource ).withTableName( dbObj.getTableName() ).usingGeneratedKeyColumns( "id" );
final int id = jdbcInsert.executeAndReturnKey( new BeanPropertySqlParameterSource( dbObj ) ).intValue();
dbObj.setId( id );

Note: you can reuse a SimpleJdbcInsert since it is thread safe, I created a new one in the above code just to show how it is done.

SimpleJdbcTemplate

After inserts, the other two pieces of required functionality are query and update, both of which are supplied by Spring’s SimpleJdbcTemplate. The trick here is to make generous use of Spring’s BeanPropertySqlParameterSource and BeanPropertyRowManager which effortlessly map between JavaBean properties and database columns.

Lets start with the simple case of deleting an object.

simpleJdbcTemplate.update( "DELETE FROM " + dbObj.getTableName() + " WHERE id = :id", new BeanPropertySqlParameterSource( dbObj ) );

You’ll notice the string “id = :id” in the query. This allows Spring to do value substitution, using the BeanPropertySqlParameterSource to replace :id with the actual value of .getId() from the dbObj. With this in mind we can step up to the more complicated update case.

final Collection<String> parts = buildUpdateParts( dbObj );
if ( parts.isEmpty() ) { return; }
final String query = "UPDATE " + dbObj.getTableName() + " SET " + join( parts, ", " ) + " WHERE id = :id";
simpleJdbcTemplate.update( query, new BeanPropertySqlParameterSource( dbObj ) );

private static final Collection<String> buildUpdateParts( final LocaDbObj dbObj ) {
  return transform( filter( dbObj.getFieldNames(), not( IS_ID ) ), new BuildPart( dbObj ) );
}

private static final class BuildPart implements Function<String, String> {
  private final LocaDbObj dbObj;
  public BuildPart( final LocaDbObj dbObj ) {
    this.dbObj = dbObj;
  }
  public String apply( final String propName ) {
    return dbObj.getColumnName( propName ) + " = :" + propName;
  }
}

This code again makes use of google collections but the point is that it takes the field names and the column names from the DbObj and creates snips of sql setting the column name to the field name identifier (i.e. the field name prefixed with ‘:’) just like was done in the deletion case with “id = :id”. Continuing to build on the previous examples we finally come to the query case. This one is a bit difference since we are interested in reading results from the database as well as building a query so we will have to deal with the BeanPropertyRowMapper.

final Collection<String> parts = buildQueryParts( dbObj );
if ( parts.isEmpty() ) { return ImmutableList.of(); }
final String query = "SELECT " + columnNames + " FROM " + dbObj.getTableName() + " WHERE " + join( parts, " AND " );
return result = simpleJdbcTemplate.query( query, new BeanPropertyRowMapper<DbObj>( dbObj.getClass() ), new BeanPropertySqlParameterSource( dbObj ) );

private static final Collection<String> buildQueryParts( final LocaDbObj dbObj ) {
  return transform( filter( dbObj.getFieldNames(), new HasValue( dbObj.toMap() ) ), new BuildPart( dbObj ) );
}

private static final class HasValue implements Predicate<String> {
  private final Map<String, Object> map;
  public HasValue( final Map<String, Object> map ) {
    this.map = map;
  }
  public boolean apply( final String propName ) {
    return map.get( propName ) != null;
  }
}

Hopefully by this point the google collections syntax is becoming familiar. We want to include only object fields that are non null so we use the HasValue Predicate to check each property by name. From there on the rest is quite similar to the update method where we build parts setting each column name equal to its field name identifier. The only other difference is we use the BeanPropertyRowMapper so Spring can return us a list of the correct type of DbObj.

While certainly a bit heavy on the code I hope this will be useful to those trying to create a simple, straight forward database layer with Spring and ActiveRecord naming conventions. As a bit of a tease, the full code base that this is pulled from contains an genericized DAO object that contains basic annotation driven caching as well as the ability to execute CRUD commands via example objects, ids, sql snippets (i.e. where clauses), or full raw SQL all while keeping things nice and type safe as well as sql injection free using PreparedStatements and value substitution.