Data transformation functions

Data transformation functions

In certain situations could be necessary to apply a transformation to the data read from the DB. For example, you may want to write a custom string if a null value is received or more simply you may want to map a data type to another. To address these situations, MemPOI v1.5 introduces data transformation functions, that rely on 4 principles:

• bound to a specific column
• supplied by the user
• receives one of the Apache POI supported data types (String, Double, Boolean, Date) and returns the same type or another one of them
• executed for each value of the selected column, just before the write to the Apache POI workbook operation

MemPOI provides 4 types of data transformation functions, which reflect the data types supported by Apache POI:

• StringDataTransformationFunction receives a String (data read from the DB and cast to String)
• DoubleDataTransformationFunction receives a number (data read from the DB cast to Double)
• BooleanDataTransformationFunction receives a Boolean (data read from the DB cast to Boolean)
• DateDataTransformationFunction receives a Date (data read from the DB and cast to Date)

A data transformation function can return whatever you want but in the set of data types supported by Apache POI, so again String, Double, Boolean or Date.

In the following example, we are returning "NO NAME" if the value read by the "name" column is null. Please note that in order to receive null values within data transformation functions you must set nullValuesOverPrimitiveDetaultOnes to true.

MempoiColumnConfig mempoiColumnConfig = MempoiColumnConfigBuilder.aMempoiColumnConfig()
        .withColumnName("name")
        .withDataTransformationFunction(new StringDataTransformationFunction<String>() {
            @Override
            public String transform(final ResultSet rs, String value) throws MempoiException {
                return null == value
                    ? "NO NAME"
                    : value;
            }
        })
        .build();

MempoiSheet mempoiSheet = MempoiSheetBuilder.aMempoiSheet()
        .withPrepStmt(prepStmt)
        .addMempoiColumnConfig(mempoiColumnConfig)
        .build();

In the following example we are changing data type to Integer:

MempoiColumnConfig mempoiColumnConfig = MempoiColumnConfigBuilder.aMempoiColumnConfig()
        .withColumnName("name")
        .withDataTransformationFunction(new StringDataTransformationFunction<Integer>() {
            @Override
            public Integer transform(final ResultSet rs, String value) throws MempoiException {
                return 999;
            }
        })
        .build();

In some particular cases, you need to transform the data based on the current SQL statement values, for this reason, the current ResultSet is provided among the transformation params.

MempoiColumnConfig mempoiColumnConfig = MempoiColumnConfigBuilder.aMempoiColumnConfig()
    .withColumnName("name")
    .withDataTransformationFunction(new StringDataTransformationFunction<String>() {
        @Override
        public String transform(final ResultSet rs, String value) throws MempoiException {
            try {
                if (rs.getBoolean("valid")){
                    return value + " validated";
                } else{
                    return value;
                }
            } catch (SQLException e) {
              throw new MempoiException(e);
            }
        }
    })
    .build(); 

!!! BE AWARE !!! THIS IS AN ADVANCED FEATURE: EVERY ACTION MADE ON THE RESULTSET MAY INVALIDATE RESULTING DATA