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

Question

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);
        }
    }

}

Answer 1

Map<String,String> mapColumnsToProperties = new HashMap<>();
//mapping you database to entity here;
mapColumnsToProperties.put("database_column","entity_property");
BeanProcessor beanProcessor = new BeanProcessor(mapColumnsToProperties);
RowProcessor rowProcessor = new BasicRowProcessor( beanProcessor); 
ResultSetHandler<List<Entity>> resultSetHandler = new BeanListHandler<Entity>(Entity.class,rowProcessor);
List<Entity> entityLists = queryRunner.query(findListSQL, resultSetHandler);    

Answer 2

i'm from china,my english is not very well; but this issue ,this is my resolve : because the dbutils is open source, you can modify the source code, and use maven to make a jar only for you ,i just modify the BeanProcessor class,you can add a method named changeColumnName like this :

public  String changeColumnName(String columnName){
        if(columnName == null){
            return null;
        }
        if(columnName.contains("_")){
            char[] cs = columnName.toCharArray();
            int flag = -1;
            for(int index=0;index<columnName.toCharArray().length;index++){
                if(cs[index] == '_'){
                    flag = index;
                    break;
                }
            }
            columnName = columnName.substring(0, flag) + columnName.substring(flag+1,flag+2).toUpperCase() + columnName.substring(flag+2);
            return changeColumnName(columnName);
        }else{
            return columnName;
        }
    }

and in the method

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

    int cols = rsmd.getColumnCount();
    int[] columnToProperty = new int[cols + 1];
    Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);

    for (int col = 1; col <= cols; col++) {
        String columnName = rsmd.getColumnLabel(col);
        if (null == columnName || 0 == columnName.length()) {
          columnName = rsmd.getColumnName(col);
        }
        String propertyName = columnToPropertyOverrides.get(columnName);
        if (propertyName == null) {
            propertyName = changeColumnName(columnName);//add here
        }
        for (int i = 0; i < props.length; i++) {

            if (propertyName.equalsIgnoreCase(props[i].getName())) {
                columnToProperty[col] = i;
                break;
            }
        }
    }

    return columnToProperty;
}

it can solve the problem.welcome to discuss with me .my google email is guomin.bai@gmail.com

Answer 3

I have already answered similar so question here.

You can use GenerousBeanProcessor which is a subclass of BeanProcessor it ignores underscore & case sensitivity from column name. You don't have to implement your own custom BeanProcessor for this particular case.

GenerousBeanProcessor is available since version 1.6 of commons-dbutils.

Usage:

// TODO initialize
QueryRunner queryRunner = null;

ResultSetHandler<List<State>> resultSetHandler =
                new BeanListHandler<State>(State.class, new BasicRowProcessor(new GenerousBeanProcessor()));

// best practice is mentioning only required columns in the query
final List<State> states = queryRunner.query("select * from states a where a.active='Y'", resultSetHandler);

for (State state : states) {
    System.out.println(state.getStateId());
}

Answer 4

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.

Answer 5

This can be easily done using MapListHandler and Jackson without defining a custom bean processor or providing a column mapping.

1. change your POJO to

    public class State implements Serializable {
   
    @JsonProperty("state_id")//db column name
    private int stateId;
   
    @JsonProperty("state_cd")
    private String stateCode;
   
    //and so on ....
   

   }

2. Use MapListHandler with Jackson to fetch and convert the response to POJO.

ObjectMapper objectMapper = new ObjectMapper();

List<State> stateList = new QueryRunner()
.query(connection, query, new MapListHandler())
.stream()
.map(response -> objectMapper.convertValue(response, State.class))
.collect(Collectors.toList());

In this approach, you are first fetching List<Map<String, Object>> which is a list of records and each record is coming in a map of column name and value, which can be mapped to a POJO using Jackson's convertValue method (which will use @JsonProperty annotation to map the column to the POJO field).

This approach will give you better control of mapping a DB column to a POJO field.