Java complex object mapping with JDBC
When fetching data from a relational DB using JDBC there are two things we need to implement:
- mapping table columns to primitive types using `ResultSet`
- constructing custom value objects or entities from these primitive types.
In this post we will focus on implementing complex mapping logic for the latter. Complex means here that we need to write more than just one test to verify that mapping works. This includes scenarios where we cannot map table columns 1–1 to the fields of our object or where we need to pick the right class to instantiate.
As with any kind of design tests are good way to guide the production code. Testing the `ResultSet` mapping requires use of database. This means we will not be able to write simple focused unit tests. Even if we use an in-memory database¹ we still need to set up the whole database access layer.
Testing complex mapping logic through such tests is quite cumbersome. Therefor we should encapsulate that complexity so that it does not depend on `ResultSet`².
Example
We have an `Order` entity in an e-commerce application. It moves through different states like “pending”, “confirmed”, “funded”. As the behavior of `Order` changes in each state we use different class for each state.
All these different `Order` implementations are persisted in the same table. To instantiate the right kind of `Order` we can implement a factory like this:
In this implementation `PersistedOrder` is stateful. When we need to implement complex transformation it is good idea to split it into multiple smaller methods. Having all data available via object fields is much more convenient than passing it around.
We create `PersistedOrder` using values from `ResultSet` without any conversion (hence the `Timestamp`). We want to implement all conversion logic in `PersistedOrder` and leave only direct `ResultSet` access out. Note that this factory is specific to re-instantiating Orders when retrieving them from database. If we needed to use some kind of factory also for creating new Orders we should implement separate factory.
When we put this all together using a Spring `JdbcTemplate` it will look like this:
Of course there is nothing specific to Spring JDBC here. We can use the same concept with any kind of persistence tools. Key thing is that the mapping needs to be complex enough to warrant creating such a factory.
[1] I have seen teams use in-memory databases like H2 or HSQL but these days it is easy to set up any database server using plain Docker or TestContainers. The problems with slightly different DDL and other small variations in database behavior downplay any potential benefits of using an in-memory DB. In TransferWise there are only a few teams using these for their tests.
[2] I don’t think we should ever mock ResultSet or any other class we don’t own. For more details: https://github.com/testdouble/contributing-tests/wiki/Don't-mock-what-you-don't-own
P.S. Interested to join us? We’re hiring. Check out our open Engineering roles.
