Java Data Hierarchy and Row Mapping

Question

I have an application that needs to model some existing DB tables (I cannot change them) with a number of duplicated rows. I'm trying to figure out a good design to reuse as much of the mapping code as possible so I don't have to duplicate a bunch of set calls.

Here is an example of two of the tables.

TIME_AND_EXPENSE_STAGING

Name               Null     Type           
------------------ -------- -------------- 
SEQ_NUM            NOT NULL NUMBER(31)     
DATETIME_STAMP              TIMESTAMP(6)   
DEPT_ID            NOT NULL VARCHAR2(10)   
EMPL_ID            NOT NULL VARCHAR2(11)   
JOB_CODE           NOT NULL VARCHAR2(6)    
REGULAR_HRS        NOT NULL NUMBER(4,2)    
OVERTIME_HRS       NOT NULL NUMBER(4,2)    
MILES              NOT NULL NUMBER(8,2)    
COMMENTS           VARCHAR2(4000) 

TIME_AND_EXPENSE_APPROVED

Name               Null     Type           
------------------ -------- -------------- 
SEQ_NUM            NOT NULL NUMBER(31)     
DATETIME_STAMP              TIMESTAMP(6)   
DEPT_ID            NOT NULL VARCHAR2(10)   
EMPL_ID            NOT NULL VARCHAR2(11)   
JOB_CODE           NOT NULL VARCHAR2(6)    
REGULAR_HRS        NOT NULL NUMBER(4,2)    
OVERTIME_HRS       NOT NULL NUMBER(4,2)    
MILES              NOT NULL NUMBER(8,2)    
COMMENTS           VARCHAR2(4000)     
APPROVE_TIME       NOT NULL TIMESTAMP(6)
APPROVER_ID        NOT NULL VARCHAR2(11)

I am using the Spring framework and the Spring RowMapper to populate the data objects. I feel like there should be some graceful way to use inheritance or polymorphism or some design pattern that will prevent me from having to copy and paste a bunch of set statements.

Please advise.

Answer 1

I personally tend to like to use immutable objects when dealing with this sort of data. Imagine the following class:

public class TimeAndExpense {
    private final String jobCode;
    // and a bunch of other attributes such as department...

    // Then the additional data (approval data)
    private final Optional<String> approverId;
    private final Optional<Timestamp> approveTime;

    public TimeAndExpense(final String jobCode) {
        this(jobCode, null, null);
    }

    public TimeAndExpense(
                final String jobCode, 
                final Timestamp approveTime, 
                final String approverId) {

        this.jobCode = jobCode;
        // And the rest of the attributes...

        this.approveTime = Optional.ofNullable(approveTime);
        this.approverId = Optional.ofNullable(approverId);
    }

    public Optional<Timestamp> getApproveTime() {
        return approveTime;
    }

    public Optional<String> getApproverId() {
        return approverId;
    }

    public String getJobCode() {
        return jobCode;
    }

    public boolean isApproved() {
        return approverId.isPresent();
    }

    // Creates a new object with the approval data (immutable)
    public TimeAndExpense withApprovalData(final Timestamp approveTime, final String approverId) {
        return new TimeAndExpense(this.getJobCode(), approveTime, approverId);
    }
}

The method withApprovalData can be used to create a TimeAndExpense-object with the additional approval data. When used this way you can setup your RowMappers like this:

// The RowMapper for the table TIME_AND_EXPENSE_STAGING
// More columns than JOB_CODE exists but are not part of the example
private final RowMapper<TimeAndExpense> timeAndExpenseRowMapper = 
        (rs, rowNum) -> new TimeAndExpense(rs.getString("JOB_CODE"));

// The RowMapper for the table TIME_AND_EXPENSE_APPROVED
// Reuse the timeAndExpenseRowMapper's mapping, then add additional approval data
private final RowMapper<TimeAndExpense> timeAndExpenseApprovedRowMapper =
        (rs, rowNum) -> timeAndExpenseRowMapper
                .mapRow(rs, rowNum) // use the timeAndExpenseRowMapper
                .withApprovalData(  // and add the additional approval data
                        rs.getTimestamp("APPROVE_TIME"),
                        rs.getString("APPROVER_ID"));

This delegation (one RowMapper uses another RowMapper) works well when the tables are very similar and enables a great deal of code reuse.

Inheritance is something that I personally try to use as little as possible in favour of composition. But, for the domain model it might make sense to have some kind of base object (e.g. Identifiable) that contains e.g. an id (since e.g. the Department, Employee and TimeAndExpense objects all seem to contain an id an possibly even a timestamp).