Spring - How to use BeanPropertyRowMapper without matching column names

Question

I work on an application that has been converted from pure JDBC to Spring template with row mapper. The issue that I have is that the column in database doesn't match the property names which prevent me from using BeanPropertyRowMapper easily.

I saw some posts about using aliases in queries. This would work but it makes it impossible to do a SELECT *

Isn't there any annotation that can be used with BeanPropertyRowMapper as @Column from JPA?

Answer 1

I saw Some posts about using aliases in queries

This is actually an approach suggested in JavaDocs:

To facilitate mapping between columns and fields that don't have matching names, try using column aliases in the SQL statement like "select fname as first_name from customer".

From: BeanPropertyRowMapper.

impossible to do a SELECT *

Please do not use SELECT *. This makes you vulnerable to any database schema change, including completely backward compatible ones like adding or rearranging columns.

Isn't there any annotation that can be used with BeanPropertyRowMapper as @Column from JPA?

Yes, it is called jpa, hibernate and maybe ibatis. Seriously, either use aliases or implement your own RowMapper, Spring is not a full-featured orm.

Answer 2

You can override the BeanPropertyRowMapper.underscoreName, and get the name of the Column annotation to mapping the field with @Column(name = "EXAMPLE_KEY") in the PropertyDescriptor(getter/setter binding).

@Slf4j
public class ColumnRowMapper<T> extends BeanPropertyRowMapper<T> {

  private ColumnRowMapper(final Class<T> mappedClass)
  {
    super(mappedClass);
  }

  @Override
  protected String underscoreName(final String name)
  {
    final Column annotation;
    final String columnName;
    Field declaredField = null;

    try
    {
      declaredField = getMappedClass().getDeclaredField(name);
    }
    catch (NoSuchFieldException | SecurityException e)
    {
      log.warn("Ups, field «{}» not found in «{}».", name, getMappedClass());
    }

    if (declaredField == null || (annotation = declaredField.getAnnotation(Column.class)) == null
        || StringUtils.isEmpty(columnName = annotation.name()))
    {
      return super.underscoreName(name);
    }

    return StringUtils.lowerCase(columnName);
  }

  /**
   * New instance.
   *
   * @param <T> the generic type
   * @param mappedClass the mapped class
   * @return the bean property row mapper
   */
  public static <T> BeanPropertyRowMapper<T> newInstance(final Class<T> mappedClass)
  {
    return new ColumnRowMapper<>(mappedClass);
  }
}

Answer 3

A version of above mapper but with early initiation of mapping index, since reflection is way too slow:

import java.lang.reflect.Field;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import javax.persistence.Column;

import lombok.NonNull;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;

@Slf4j
public class ColumnRowMapper<T> extends BeanPropertyRowMapper<T> {

    private Map<String, String> columnIndex;
    
    private ColumnRowMapper(final Class<T> mappedClass)
    {
        super(mappedClass);
    }

    @Override
    protected void initialize(Class<T> mappedClass) {
        columnIndex = new ConcurrentHashMap<>();
        for (Field f: mappedClass.getDeclaredFields()) {
            String fieldName = f.getName();
            Column annotation = f.getAnnotation(Column.class);
            if (annotation == null) {
                continue;
            }
            String columnName = annotation.name();
            if (StringUtils.isEmpty(columnName)) {
                continue;
            }
            columnIndex.put(fieldName, StringUtils.lowerCase(columnName));
        }
        super.initialize(mappedClass);
    }

    @Override
    protected @NonNull String underscoreName(final @NonNull String name)
    {
        if (columnIndex.containsKey(name)) {
            return columnIndex.get(name);
        }
        return super.underscoreName(name);
    }

    /**
     * New instance.
     *
     * @param <T> the generic type
     * @param mappedClass the mapped class
     * @return the bean property row mapper
     */
    public static <T> BeanPropertyRowMapper<T> newInstance(final Class<T> mappedClass)
    {
        return new ColumnRowMapper<>(mappedClass);
    }
}