SFM Integration in Jooq

Introduction

SimpleFlatMapper is a library specialising in mapping flat records - ResultSet, csv etc... - to deep Object. It aims to do this with a very low runtime cost and no configuration.

The project is hosted on github and provides a Csv parser/mapper and ResultSet mapper. It also provides integration with jOOQ, QueryDsl, Spring Jdbc and Sql2o. The binaries are available in maven central.

This article will focus on the jOOQ integration, a very popular library to manage your interaction with a RDBMS that allows you to express your sql query in a type safe way.

jOOQ mapping

There are 2 mapping strategies available in jOOQ.

• the generated classes from the db information. You will get a record that will have named getters matching the column names.
• the fetchInto(Class) method that will call the jOOQ mapper or a third party mapper specified in the configuration.

because fetchInto has to go through the Record creation, its use will mainly be when your query does not map to an generated object - ie sub set of field, joins -.

SFM as a RecordMapperProvider

Sfm can be plugged into the fetchInto mapping.

All you need to do is

configuration.set(new SfmRecordMapperProvider()));

when instantiating your DSL. You will then be able use fetchInto with a cost very close to fetching the record - see the performance section - and the flexibility that SFM offers.

SFM on the ResultSet

Because the RecordMapperProvider works on the record that we don't need we are not as performant as working directly with the ResultSet. Fortunately jOOQ allows you to fetch the ResultSet directly avoiding the cost of the transition form ResultSet to Record.

JdbcMapper mapper = JdbcMapperFactory.newInstance().newMapper(MyObject.class);

ResultQuery<MyRecord> query = dsl.select().from(TABLE);
try (ResultSet rs = query.fetchResultSet()) {
    mapper.stream(rs).forEach(System.out::println);
}

That will allow you to get the query generation power of jOOQ and the close to pure Jdbc performance of SimpleFlatMapper.

Benchmark

Here are the results of the number of nano seconds to execute and fetch 1, 10, 100 and 1000 rows from a local mysql with the different mapping strategies in comparaison to a pure Jdbc implementation. The lower the better.

Type	1	10	100	1000
Pure Jdbc	235,070.92	274,056.72	614,591.85	2,353,159.69
Sfm on ResultSet	331,101.07	358,350.59	737,435.33	2,614,086.19
Fetch Record	334,075.47	382,449.42	812,955.04	3,229,483.74
Fetch Into Sfm	336,108.13	384,757.23	816,275.20	3,364,260.93
Fetch Into JooqMapper	340,480.68	399,280.99	873,295.52	3,881,600.20

And here is a graph of the % difference from the pure Jdbc time

The percentage time gain from the record fetch.

1	10	100	1000
1%	6%	9%	20%

The test was executed using jmh 1.5.1 on a i5 Ivy Bridge with 6GB of RAM, Ubuntu, Mysql 5.6, Java 8, SFM 1.4.1, jOOQ 3.4.4.

What about the property mapping

On top of the performance gain SFM offer a very flexible mapping strategy.

Including :

• Constructor injection
• Object in object injection
• array/List mapping
• not boxing for primitives

the following query for example will be mapped without configuration to the following object structure :

select id, firstname, lastname,
       addresses_0_postcode, addresses_0_street, addresses_0_country, 
       addresses_1_postcode

class Person {
    String firstname;
    String lastname;
    Address[] addresses;
}
class Address {
    PostCode postCode;
    String street;
    String country;
}
class PostCode {
    PostCode(String str){
    }
}

To finish

SimpleFlatMapper is a new library but it's goal it to be focus on the mapping of flat structure to object. It aims at making your life easier. The best way it can do that is with your feedbacks so give it a try and don't hesitate to ask question.

Also have a look at the Csv Parser DSL. If you have to parse csv like flat file it might makes your life easier;