How to combine multiple parameter sources in Spring JDBC?

Kshitiz Sharma picture Kshitiz Sharma · Nov 12, 2012 · Viewed 7.9k times · Source

The database methods in Spring JDBC accept a single parameter source. For example -

int org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(String sql, SqlParameterSource paramSource) throws DataAccessException

Is it possible to combine multiple parameter sources together? For example, suppose I have a bean Order -

class Order {
int id;
float price;
int customerId;
Date date;
//Lots of other fields
}

I want to save this bean with some additional fields like recordModificationTime and accessLevel.

If I use MapSqlParameterSource for these extra fields which exist outside the bean, I cannot use BeanPropertySqlParameterSource because the method accepts only one parameter source. Having to use MapSqlParameterSource for all my data means I have to manually extract all the bean properties, which is a lot of work.

What is the best way to deal with this problem?

Answer

mrembisz picture mrembisz · Nov 12, 2012

You can extend AbstractSqlParameterSource and aggregate both BeanProperty and Map versions:

public class CombinedSqlParameterSource extends AbstractSqlParameterSource {
  private MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
  private BeanPropertySqlParameterSource beanPropertySqlParameterSource;

  public CombinedSqlParameterSource(Object object) {
    this.beanPropertySqlParameterSource = new BeanPropertySqlParameterSource(object);
  }

  public void addValue(String paramName, Object value) {
    mapSqlParameterSource.addValue(paramName, value);
  }

  @Override
  public boolean hasValue(String paramName) {
    return beanPropertySqlParameterSource.hasValue(paramName) || mapSqlParameterSource.hasValue(paramName);
  }

  @Override
  public Object getValue(String paramName) {
    return beanPropertySqlParameterSource.hasValue(paramName) ? beanPropertySqlParameterSource.getValue(paramName) : mapSqlParameterSource.getValue(paramName);
  }

  @Override
  public int getSqlType(String paramName) {
    return beanPropertySqlParameterSource.hasValue(paramName) ? beanPropertySqlParameterSource.getSqlType(paramName) : mapSqlParameterSource.getSqlType(paramName);
  }
}

And now use it like this:

CombinedSqlParameterSource mySource = new CombinedSqlParameterSource(myOrder);
mySource.addValue("recordModificationTime", time);
mySource.addValue("accessLevel", level);

jdbcTemplate.update(sql, mySource);