Combining Spring Boot and JDBI

I recently found myself architecting yet another microservices REST backend. Within the past few years I’ve written from-scratch backends in Spring, Dropwizard and even in Python using web.py but each has left me wanting more. In this article I’ll show how and why I settled on Spring Boot and JDBI as a combination of frameworks which allows me to easily develop REST services backed by a relational datastore.

Key Takeaways

• Spring Boot and JDBI can be integrated to create REST services backed by a relational datastore, providing the best of both Spring Boot’s dependency injection and JDBI’s simple ORM layer.
• The Hibernate ORM layer, which is typically used with Spring Boot, can be cumbersome and generate unnecessary queries. JDBI is an alternative that allows developers more flexibility and can make their life easier.
• The integration of Spring Boot and JDBI is done through various steps, including removing Hibernate libraries, adding necessary dependencies, setting JVM timezone default to UTC, and creating a Spring Boot configuration that will expose JDBI and map date/time objects.
• Once integrated, a DBI object can be autowired and used to execute everything that JDBI allows, offering a simple and convenient way to query data.

REST Frameworks

Let’s compare and contrast some of the modern REST frameworks that can be used today in order to build REST services. There are some obvious ones missing (e.g. Ruby on Rails, Flask, Django, NodeJS) but I’m going to focus on the frameworks that I’ve personally used to build significant production-grade service tiers.

Spring

The grandaddy of modern microservices backends, Spring has great dependency injection (DI) and a large extensive library of addons, but it tends to have long startup time and feels quite heavy and enterprisey when compared to some of the newer microservices frameworks. Spring.io itself recently posted this good article drawing out some of the concerns they’ve heard from developers regarding the Spring framework.

It also features out of the box support for Hibernate as an ORM layer, which (feel free to flame me in the comments) in my experience gets in the way as often as it doesn’t. It’s great for simple relational structures and is quick to bootstrap solutions but as soon as you’re dealing with complex data structures or want specific types of queries (say for efficiency) it becomes quite cumbersome.

Dropwizard

Dropwizard is essentially a REST tier built on a light-weight Jersey container which provides great support for JDBI as an ORM-light solution. I’ve found JDBI to be a simple way to interact with a DB. Yes, you have to bind your ResultSets to your model objects manually but I’ve found it a small price to pay for the great flexibility it provides by allowing me to generate the exact queries I want without having to rely on (and trust) SQL auto-generated by an ORM. In my opinion it’s a really nice solution that sits somewhere between JDBC and a full-blown ORM.

What Dropwizard doesn’t do well is DI. There is support for Guice via extensions like dropwizard-guice but I have found these to be clunky at best. My biggest issue with all of these solutions is that you end up with separate DI graphs for each module. This isn’t a problem for simple applications but I’ve experienced in complex applications that dropwizard-guice becomes quite unwieldy and the separate DI graphs end up having the same class being injected with a different instance/state which can start producing unintended results.

I really just want out of the box DI that is easy to use and gets out of the way. This should be plumbing, not an application feature.

web.py

Well, clearly one of these things is not like the other. I included web.py because I have written a full-featured rest tier in it and if your religion is Python then it’s a good choice. If you’re actually contemplating a Java-based framework then this obviously isn’t your cup of tea.

Spring Boot

Spring Boot is a lightweight version of Spring that is focussed on creating Spring-like applications that “just run”. If you’re new to Spring Boot there are some excellent getting started guides in the Spring Boot reference guide.

Whereas Spring can feel quite enterprisey, Spring Boot both feels and performs like a true first-class microservices framework due to it’s quick learning curve and snappy startup. It has the same great support for DI as Spring and also features an extensive library of spring-boot-starter-* maven dependencies that you can use to add support for a great many things.

It still, however, uses Hibernate as it’s “native” ORM tier. Now if that’s fine for you then go for it, Spring Boot out of the box is a great solution but for me I just find Hibernate too cumbersome.

Hibernate vs JDBI

I’ve stated that Hibernate “gets in the way” and that JDBI is “a really nice solution”, which bears further discussion to draw out a few points. Firstly, there are many Hibernate applications which operate well at scale (and I have developed many), however, there is very much a trend to move away from heavy ORM solutions for some of the following reasons:

• Hibernate can generate large numbers of unnecessary queries, even due to minor misconfigurations (e.g. here and here, just to illustrate a few). When properly configured it still often performs worse than simple SQL queries.
• Hibernate is opinionated about your data structure. When you have a data structure that doesn’t folow it’s notion of keys and relationships the Object-relational Impedance Mismatch is going to hit you even harder. Hybrid-ORM frameworks, such as JDBI, allow more flexibility when mapping to objects and thus provide more runway before you have to start coding significant work arounds for this issue.
• You don’t see the performance problems until you scale! When your application is small and simple everything seems to go along smoothly, then you scale and all of a sudden hibernate queries are the root of your performance problems. I’ve myself hit this situation many times and it’s always taken significant rework to fix the issues.

This quora post is an excellent discussion that reinforces some of the points I make above and also touches on others. Note that I am not trying to hate on Hibernate here. It’s a good tool and when configured correctly can have great performance in a number of situations (I have personally written high scale solutions using Hibernate as the ORM).

So this certainly isn’t a right-or-wrong discussion and I do not want to suggest that JBDI is the cure to all evils. But it is an alternative to a “heavy” ORM like Hibernate and can make your life easier as a developer. I have personally encountered many tech companies which are happily using JDBI inside Dropwizard and would not move back to using Hibernate.

So what’s a savvy software engineer to do?

Integrating Spring Boot + JDBI

I wanted the best of both Spring Boot DI as well as a nice simple and consice ORM layer using JDBI so I created spring-boot-jdbi-seed. This project on GitHub features a full rest tier (note: no security, that’s for another blog post) as well as integration tests, Checkstyle, FindBugs, etc and you can use it as a scaffold for your next project if you want to. Here, I want to focus on specifically what I had to do to get Spring Boot and JDBI integrated.

build.gradle

Firstly I had to get rid of those hibernate libs, so I excluded them in my build.gradle file:

configurations {
    all*.exclude group: "org.hibernate", module: "hibernate-entitymanager"
    all*.exclude group: "org.apache.tomcat", module: "tomcat-jdbc"
}

In exchange I added the dependencies I need:

dependencies {
    compile(
            ...
            "org.springframework.boot:spring-boot-starter-jdbc",

            "org.springframework.boot:spring-boot-starter-data-jpa",
            "org.jdbi:jdbi:2.71",
            "joda-time:joda-time:2.8.1",
            ...
    )

    runtime(
            "mysql:mysql-connector-java:5.1.38",
            "com.zaxxer:HikariCP:2.4.3"
    )
}

I also added joda-time to allow for DateTime objects to be serialized to/from the DB. Personally, I prefer it over Java 8 Date and Time because Interval, PeriodFormatter, or PeriodType are not available in Java-8.

application.properties

Next I added the data access properties configuration to application.properties. Spring Boot nicely allows us to use the generic spring.datasource.* properties rather than any jdbi-specific properties.

spring.datasource.url=jdbc:mysql://localhost/services?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Note that I used MySQL as the DB but that can be swapped out with whichever driver you want to use of course.

ServiceApplication

Next I set the JVM timezone default to UTC so that all DateTime objects will be set to UTC. This helps greatly serializing to/from the DB and not accidentally getting timezone conversions being applied when they shouldn’t be. Secondly, we’re registering JodaModule so that we can actually serialize the instances.

@PostConstruct
public void postConstruct() {
    // set the JVM timezone to UTC
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}

@Bean
public Module jodaModule() {
    return new JodaModule();
}

PersistenceConfiguration

As a final step I created the Spring Boot configuration that will actually expose JDBI as well as map date/time objects. The full code can be found in PersistenceConfiguration.java but I’ll draw out the key code here:

@Configuration
public class PersistenceConfiguration {

    @Autowired
    DataSource dataSource;

    @Bean
    public DBI dbiBean() {
        DBI dbi = new DBI(dataSource);
        dbi.registerArgumentFactory(new DateTimeArgumentFactory());
        dbi.registerArgumentFactory(new LocalDateArgumentFactory());
        dbi.registerColumnMapper(new JodaDateTimeMapper());
        return dbi;
    }

    ...

    /**
     * DBI argument factory for converting joda DateTime to sql timestamp 
     */
    public static class DateTimeArgumentFactory 
            implements ArgumentFactory<DateTime> {

        @Override
        public boolean accepts(Class<?> expectedType, 
                               Object value, 
                               StatementContext ctx) {
            return value != null 
                    && DateTime.class.isAssignableFrom(value.getClass());
        }

        @Override
        public Argument build(Class<?> expectedType, 
                              final DateTime value, 
                              StatementContext ctx) {
            return new Argument() {
                    @Override
                    public void apply(int position, 
                                      PreparedStatement statement, 
                                      StatementContext ctx) throws SQLException {
                        long millis = value.withZone(DateTimeZone.UTC).getMillis();
                        statement.setTimestamp(
                                position, new Timestamp(millis), getUtcCalendar());
                }
            };
        }
    }

    /**
     * A {@link ResultColumnMapper} to map Joda {@link DateTime} objects.
     */
    public static class JodaDateTimeMapper 
            implements ResultColumnMapper<DateTime> {

        @Override
        public DateTime mapColumn(ResultSet rs, 
                                  int columnNumber, 
                                  StatementContext ctx) throws SQLException {
            final Timestamp timestamp = 
                    rs.getTimestamp(columnNumber, getUtcCalendar());
            if (timestamp == null) {
                return null;
            }
            return new DateTime(timestamp.getTime());
        }

        @Override
        public DateTime mapColumn(ResultSet rs, 
                                  String columnLabel, 
                                  StatementContext ctx) throws SQLException {
            final Timestamp timestamp = 
                    rs.getTimestamp(columnLabel, getUtcCalendar());
            if (timestamp == null) {
                return null;
            }
            return new DateTime(timestamp.getTime());
        }
    }

}

Autowire and Use JDBI

That’s it, now you can autowire and use a DBI object to do everything that JDBI allows you to do.

@RestController
@RequestMapping("/test")
@Slf4j
public class TestResource {

    @Autowired
    DBI dbi;

    @RequestMapping(method = RequestMethod.GET)
    public String get() {
        String name;
        Handle handle = dbi.open();
        try {
            name = handle.createQuery("select description from my_test")
                    .map(StringMapper.FIRST)
                    .first();
        } finally {
            handle.close();
        }
        return name;
    }
}

I personally tend to use the JDBI SQL Object API which I find a simple and convinient way to query data but you can read the docs and use what’s right for you.

Running the Code

Make sure you have Java 8, Git and MySQL installed on your machine. The project includes a gradle wrapper so you do not need to install Gradle.

Execute the following on the command line:

# pull the code from git
git clone https://github.com/dhagge/spring-boot-jdbi-seed.git
# build the project, create the database schema and seed some test data
./gradlew build
./gradlew dbUpdate
./gradlew dbSeed

Now you can run the Spring Boot service and call the test REST service:

./gradlew bootRun

# now curl the service (from a new command prompt)
curl localhost:9090/test

You’ll see the response Hello there, this is a test! which is actually data obtained from the my_test table in the database via the TestResource class.

Full source code can be found at spring-boot-jdbi-seed, please fork and use at your leisure!

Summary

Spring Boot and Dropwizard are both great microservices frameworks but, in my opinion, each leaves something to be desired. Specifically, Dropwizard lacks first-class dependency injection (DI) support and Spring Boot comes with Hibernate built in, which may not be the best choice for your needs (at least it usually isn’t for mine). I’ve shown above how we can combine some of the best of both worlds by integrating JDBI with Spring Boot. I’ve been quite happy using this combination of technologies for some significant projects and I hope you will be too!

Frequently Asked Questions (FAQs) about Combining Spring Boot and JDBI