How to map database column names to Java class fields using apache-commons-dbutils

I would like to populate a POJO (State.java) from DB using Apache DBUtils library. However, since the names of the Bean properties do not match exactly with DB column names some of the properties are left unfilled.

Now, I did some research on this by googling and found that this can be achieved by:

1. Column aliasing while writing an SQL query (which I do not prefer as I have multiple joins in some of larger tables and hence would require lot of aliasing)
2. Using BeanProcessor (could not find a really good example anywhere)

Can anybody provide a good example on how to use BeanProcessor to map column names to properties? Tweaking the example provided me would be even better.

DB Table

CREATE TABLE public.states (
  state_id INTEGER DEFAULT nextval('states_seq'::regclass) NOT NULL,
  state_cd VARCHAR(2) NOT NULL,
  name VARCHAR(100) NOT NULL,
  tax_pct NUMERIC(10,2) DEFAULT 0.00 NOT NULL,
  active CHAR(1) DEFAULT 'Y'::bpchar NOT NULL,
) 

State.java

  public class State implements Serializable {

    private int stateId;
    private String stateCode;
    private String name;
    private BigDecimal taxPct = new BigDecimal(0);
    private Date expiryDate;
    private String createdBy;
    private Date createdOn;
    private String active;

    //getters and setters here
}

Main.java

    public class Main {

    public static void main(String[] args) {
        String url = "jdbc:postgresql://gsi-547576.gsiccorp.net:5432/istore-db";
        String driver = "org.postgresql.Driver";
        String user = "postgres";
        String pwd = "postgres";
        Connection conn = null;
        List<State> states = null;

        try {
            DbUtils.loadDriver(driver);
            conn = DriverManager.getConnection(url, user, pwd);

            states = (List<State>) new QueryRunner().query(conn, "select * from states a where a.active='Y'", new BeanListHandler(State.class);

            System.out.println("states::  " + states);

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

}

If you take a look at the Java docs for BeanProcessor :

protected int[] mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props) throws SQLException

The positions in the returned array represent column numbers. The values stored at each position represent the index in the PropertyDescriptor[] for the bean property that matches the column name. If no bean property was found for a column, the position is set to PROPERTY_NOT_FOUND. Parameters: rsmd - The ResultSetMetaData containing column information. props - The bean property descriptors. Returns: An int[] with column index to property index mappings. The 0th element is meaningless because JDBC column indexing starts at 1.

Looks like you will need to create a class that extends from BeanProcessor and overrides the mapColumnsToProperties method as follows :

public class StateBeanProcessor extends BeanProcessor {

    @Override
    protected  int[] mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props) throws SQLException {
          int[] mapping = super.mapColumnsToProperties(rsmd, props);
          /*Map database columns to fields in the order in which they appear
            1st column in the DB will be mapped to 1st field in the Java
            class and so on.. */
          for(int i=0;i<mapping.length;++i) {
             mapping[i]=i;
          }
      }
  }

You can then plugin the above StateBeanProcessor into your code as follows :

states = (List<State>) new QueryRunner().query(conn, "select * from states", new BeanListHandler(State.class,new BasicRowProcessor(new StateBeanProcessor())));

Disclaimer : I have not tested out this code. It aims to show you the bits and pieces that you can put together to have custom field mappings. If you find an issue with it, you can let me know so I can look into it.