Trifork Blog

Category ‘From The Trenches’

Spring Data Native Queries and Projections in Kotlin

August 28th, 2018 by

Koltin, Spring Boot and JPA

This blog describes the solution to mapping native queries to objects. This is useful because sometimes you want to use a feature of the underlying database implementation (such as PostgreSQL) that is not part of the JPQL standard. By the end of this blog you should be able to confidently use native queries and use their outcome in a type-safe way.

In creating great applications based on Machine Learning solutions, we often come across uses for frameworks and databases that aren’t exactly standard. We sometimes need to build functionality that is either so new or so specific that it hasn’t been adopted into JPA implementations yet.

Working on a project with Spring Data is usually simple albeit somewhat opaque. Write a repository, annotate methods with @Query annotation and presto! You have mapped your database entities to Kotlin objects. Especially since Spring Framework 5 many of the interoperability issues (such as nullable values that are never null) have been alleviated.

Confucius wrote “Real knowledge is to know the extent of one’s ignorance”. So, to gauge the extent of our ignorance, let’s have a look at what happens when we cannot use the JPA abstraction layer in full and instead need to work with native queries.

Setting up the entity

When you use non-JPA features of the underlying database store, things can become complex.
Let’s say we have the following PostgreSQL table for storing people:

  first_name VARCHAR(20),
  last_name VARCHAR(20)

Given we represent an individual person like this:

import javax.persistence.Entity
import javax.persistence.GeneratedValue
import javax.persistence.Id
import javax.persistence.Table
@Table(name = "person")
class PersonEntity {
  var id: Long? = null
  var firstName: String? = null
  var lastName: String? = null

We can access that using a Repository:

import org.springframework.stereotype.Repository
@Repository interface PersonRepo : JpaRepository<PersonEntity, Long>

We could now implement a custom query on the repository as follows:

@Repository interface PersonRepo : JpaRepository<PersonEntity, Long> {

  @Query("FROM PersonEntity WHERE first_name = :firstName")
  fun findAllByFirstName(@Param("firstName") firstName: String):

So far so good. It uses JPQL syntax to form database-agnostic queries which is nice because we get some validation of these queries when starting the application, plus the added benefit of the syntax being database-type ignorant.

Adding a native query

Sometimes however, we want to use syntax that is specific to the database that we are using. We can do that by adding the boolean nativeQuery attribute to the @Query annotation and using Postgres’ SQL instead of JPQL:

  @Query("SELECT first_name, random() AS luckyNumber FROM person",
    nativeQuery = true)
  fun getPersonsLuckyNumber(): LuckyNumberProjection?

Obviously this example is simple for the sake of this context, more practical applications are in the area of using the extra data types that Postgres offers such as the cube data type for storing matrices.

You may be, as I was at first, tempted to write a class for LuckyNumberProjection.

class LuckyNumberProjection {
  var firstName: String? = null
  var luckyNumber: Float? = null

You will run cause into the following error:

org.springframework.core.convert.ConverterNotFoundException: No converter found
capable of converting from type
to type

The accompanying stack trace points in the direction of converters. This then makes you need to add a converter. However that doesn’t seem like it should be as hard. Good for us it turns out it isn’t!

Turns out that contrary to Entities, Projections, like Repositories, are expected to be interfaces. So let’s do that instead:

interface LuckyNumberProjection {
  val firstName: String?
  val luckyNumber: Float

This should set you straight next time you want to get custom objects mapped out of your JPA queries.

At Trifork Amsterdam, we are currently doing multiple projects using Kotlin using frameworks such as Spring Boot, Axon Framework and Project Reactor on top of Kubernetes clusters using Helm to build small and smart microservices. More and more of those microservices contain our Machine Learning based solutions. These are in a variety of areas ranging from natural language processing (NLP) to time-series analysis and clustering data for recommender systems and predictive monitoring.

Refactoring from Elasticsearch version 1 with Java Transport client to version 6 with High Level REST client

February 27th, 2018 by

Every long running project accrues technical debt. It may be that the requirements today have evolved in a different direction from what was foreseen when the project was designed, or it may be that difficult infrastructure tasks have been put off in favor of new functionality. From time to time, you need to refactor your code to clean up this technical debt. I recently finished such a refactoring task for a customer, so in the category ‘from the trenches’, I would like to share the story here.

Elasticsearch exposes both a REST interface and the internal Java API, via the binary transport client, for connecting with the search engine. Just over a year ago, Elastic announced to the world that it plans to deprecate the transport client in favor of the high level REST client, “as soon as the REST client is feature complete and is mature enough to replace the Java API entirely”. The reasons for this are clearly explained in Luca Cavanna’s blogpost, but the most important disadvantage is that using the transport client, you introduce a tight coupling between your application and the exact major and minor release of your ES cluster. As long as Elasticsearch exposes its internal API, it has to worry about breaking thousands of applications all over the world that depend on it.

The “as soon as…” timetable sounds somewhat vague and long term, but there may be good reasons to migrate your search functionality now, rather than later. In the case of our customer, their reason is wanting to use the AWS Elasticsearch service. The entire codebase is already running in AWS, and for the past few years they have been managing their own Elasticsearch cluster running in EC2 instances. This turns out to be labor intensive when updates have to be applied to these VMs. It would be easier and probably cheaper to let Amazon manage the cluster. As the AWS Elasticsearch service only exposes the REST API, the dependence on the transport protocol will have to be removed.

Action plan

The starting situation was a dependency on Elasticsearch 1.4.5, using the Java API. The goal was the most recent Elasticsearch version available in the Amazon Elasticsearch Service, which at the time was 6.0.2, using the REST API.

In order to reduce the complexity of the refactoring operation, we decided early on, to reindex the data, rather than trying to convert the indices. Every Elasticsearch release comes with a handy list of breaking changes. Looking through this list, we tried to make a list of breaking changes that would likely affect the search implementation of our customer. There are more potential breaking changes than listed here, but these are the ones that an initial investigation suggested might have an impact:

1.x – 2.x:

  • Facets replaced by aggregations
  • Field names can’t contain dots

2.x – 5.x:

5.x – 6.0:

  • Support for indices with multiple mapping types dropped

The plan was first to convert the existing code to work with ES 6, and only then migrate from the transport client to the High Level REST client.


The entire search functionality, originally written by our former colleague Frans Flippo, was exhaustively covered by unit- and integration tests, so the first step was to update the maven dependency to the current version, run the tests, and see what broke. First there were compilation errors that were easily fixed. Some examples:

Replace FilterBuilder with QueryBuilder, RangeFilterBuilder with RangeQueryBuilder, TermsFilterBuilder with TermsQueryBuilder, PercolateRequestBuilder with PercolateQueryBuilder etc, switch to HighlightBuilder for highlighters, replace ‘fields’ with ‘storedFields’. The count API was removed in version 5.5, and its use had to be replaced by executing a search with size 0. Facets had already been replaced by aggregations by our colleague Attila Houtkooper, so we didn’t have to worry about that.

In ES 5, the suggest API was removed, and became part of the search API. This turned out not to have an impact on our project, because the original developer of the search functionality implemented a custom suggestions service based on aggregation queries. It looks like he wanted the suggestions to be ordered by the number of occurrences in a ‘bucket’, which couldn’t be implemented using the suggest API at the time. We decided that refactoring this to use Elasticsearch suggesters would be new functionality, and outside the scope of this upgrade, so we would continue to use aggregations for now.

Some updates were required to the index mappings. The most obvious one was replacing ‘string’ with either ‘text’ or ‘keyword’. Analyzer became search_analyzer, while index_analyzer became analyzer.

Syntax ES 1:

"fields": {
    "analyzed": {
        "type": "string",
        "analyzer" : "dutch",
        "index_analyzer": "default_min_word_length_2"
    "not_analyzed": {
        "type": "string",
        "index": "not_analyzed"

Syntax ES 6:

"fields": {
  "analyzed": {
    "type": "text",
    "search_analyzer": "dutch",
    "analyzer": "default_min_word_length_2"
  "not_analyzed": {
    "type": "keyword",
    "index": true

Document id’s were associated with a path:

"_id": {
    "path": "id"

The _id field is no longer configurable, so in order to have document ids in Elasticsearch match ids in the database, the id has to be set explicitly, or Elasticsearch will generate a random one.

All in all, it was roughly a day of work to get the project to compile and ready to run the unit tests. All of them were red.

Read the rest of this entry »

How to send your Spring Batch Job log messages to a separate file

April 14th, 2017 by

In one of my current projects we’re developing a web application which also has a couple of dozen batch jobs that perform all sort of tasks at particular times. These jobs produce quite a bit of logging output when they’re run, which is important to see what has happened during a job exactly. What we noticed however, is that the batch logging would make it hard to quickly spot the other logging performed by the application while also running a batch job. In addition to that, it wasn’t always clear in the context of what job a log statement was issued.
To address these issues I came up with a simple solution based on Logback Filters, which I’ll describe in this blog.

Logback Appenders

We’re using Logback as a logging framework. Logback defines the concept of appenders: appenders are responsible for handling the actual log messages emitted by the loggers in the application by writing them to the console, to a file, to a socket, etc.
Many applications define one or more appenders and them simply list them all as part of their root logger section in the logback.xml configuration file:

<configuration scan="true">

  <appender name="LOGSTASH" class="net.logstash.logback.appender.LogstashTcpSocketAppender">
    <encoder class="net.logstash.logback.encoder.LogstashEncoder"/>

  <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
      <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %mdc %-5level %logger{36} - %msg%n</pattern>
  <root level="info">
    <appender-ref ref="LOGSTASH"/>
    <appender-ref ref="FILE"/>


This setup will send all log messages to both of the configured appenders. Read the rest of this entry »

Collecting data from a private LoRaWAN sensor network into Elastic

May 20th, 2016 by

Introduction to LoRaWAN and ELK

Why LoRaWAN, and what makes it different from other types of low power consumption, high range wireless protocols like ZigBee, Z-Wave, etc … ?

LoRa is a wireless modulation for long-range, low-power, low-data-rate applications developed by Semtech. The main features of this technology are the big amount of devices that can connect to one network and the relatively big range that can be covered with one LoRa router. One gateway can coordinate around 20’000 nodes in a range of 10–30km. It’s a very flexible protocol and allows the developers build various types of network architectures according to the demand of the client. The general description of the LoRaWAN protocol together with a small tutorial are available in my previous post.

What is the ELK stack, and why use it with LoRaWAN?

In the figure above, you can see a simplified model of what a typical LoRaWAN network looks like.
As you can see, the data from the LoRa endpoints, has to go through several devices before it reaches the back-end application. Nowadays there are a lot of tools that would allow us to gather and manipulate the data. A very good solution is the ELK stack which consists of Elasticsearch, Logstash and Kibana; these three tools allow to gather, store and analyze big amounts of data. More information and details can be found on the official website:

Read the rest of this entry »

From The Trenches: LoRa, LoRaWAN tutorial with the LoRaBee

March 4th, 2016 by

Once in a while you stumble across an interesting technology which is an enabler of new solutions, and new business cases. At Trifork Eindhoven we believe LoRa / LoRaWan might be such a technology. We are currently working on several Internet of Things solutions which could directly benefit from long distance communication, with a low energy footprint.

In this document I will describe the things that I learnt while working with the LoRaBee development kit. I will also provide some basic tutorials on how to set it up, how to start a connection and how to connect it with different platforms.
The hardware that I am using:

  • SODAQ Mbili with Atmel 1284p Microcontroller x 2
  • SODAQ LoRaBee which contains the Microchip RN2483 Module x 2
  • A set of peripherals for data acquisition ( sensors, buttons, LEDs, serial interfaces… )

Quick intro into LoRa

According to Wikipedia:

LoRaWAN (Long Range Wide Area Network) is a low power wireless networking protocol designed for low-cost secure two-way communication in the Internet of Things (IoT). LoRaWANs use of sub-GHz ISM bands also means the network can penetrate the core of large structures and subsurface deployments within a range of 2 km.[1]

The technology utilized in a LoRaWAN network is designed to connect low-cost, battery-operated sensors over long distances in harsh environments that were previously too challenging or cost prohibitive to connect. With its penetration capability, a LoRa gateway deployed on a building or tower can connect to sensors more than 10 miles away or to water meters deployed underground or in basements. The LoRaWAN protocol offers unique and unequaled benefits in terms of bi-directionality, security, mobility and accurate localization that are not addressed by other LPWAN technologies. These benefits will enable the diverse use cases and business models in deployments of LPWAN IoT networks globally.”

In the following picture, you can see the typical LoRaWAN network:

As you can see, all three classes are required for a fully working LoRaWAN network. In the tutorial, we used two LoRa end node devices for a P2P communication; however that is a simple radio transmit/receive between the LoRa modules and not the LoRaWAN protocol.

Read the rest of this entry »

Spring-AMQP and payload validation: some notes from the trenches

February 29th, 2016 by

It’s been a while since I’ve written one of our from-the-trenches blogs: that’s mostly because I’ve been very busy in those trenches developing systems for our customers.

This week I completed a Spring Boot-based microservice which is responsible for interacting with some 3rd party SOAP service: its own clients communicate with it by sending request message over RabbitMQ, and the service then sends back a response to a response queue after handling the SOAP response message.

Of course I used Spring-AMQP to build this service. Spring-AMQP supports a nice annotation-based listener programming model, based on Spring’s generic Message support.
That allows writing listener methods like this:

@RabbitListener(queues = REQUEST_QUEUE) 
public DeclarationResponse submitDeclaration(DeclarationRequest request) { 
  // handle the request and return a response 

The request parameter here is the result of converting the incoming AMQP message using a Spring-AMQP MessageConverter, after which it is considered to be the payload of the message (even when headers are used in the conversion as well).

The request messages that the clients send have some required fields: without those fields, the service can’t make the SOAP calls. While reading the RabbitListener JavaDoc I noticed that Spring-AMQP allows you to apply validation to message payload parameters by annotating it. When using this, you also have to add the @Payload annotation (which is optional without validation if your method doesn’t have any other arguments), so the result looks like this:

@RabbitListener(queues = REQUEST_QUEUE)
public DeclarationResponse submitDeclaration(@Valid @Payload DeclarationRequest request) { … }

By the way, Spring’s own @Validated (even as a meta-annotation) and in fact every annotation whose name starts with “Valid” are supported for this purpose as well.

Now we can add some JSR-303 Bean Validation annotations to the fields in our DeclarationRequest, like @NotNull, to express our validation constraints.

Read the rest of this entry »

City-wide crowd management in Amsterdam

November 10th, 2015 by

As most residents and visitors of Amsterdam know, every year more people are visiting Amsterdam, city wide events like GayPride, Koningsdag and MuseumNacht are getting bigger and more frequent, putting more strain on the city’s infrastructure and all people living in the city center.

That’s why this November 7th, Amsterdam Marketing organized the Museumn8 hackathon to allow developers to come up with creative and innovative solutions for improving improving mobility, navigation and crowd management in the city. Twenty teams eventually participated.

Trifork (Rienk Prinsen, Marleine van Kampen, Marijn van Zelst) and weCity (David Kat, Luc Deliance) teamed up and joined the hackathon to give their take on solving this problem. Their solution:


By transforming the advertisement billboards of JCDecaux into large information screens displaying real-time information, visitors can get informed about activities and interesting places in the vicinity of the billboard. They receive live crowd information, travel times to and queue lengths at museums and even recommendations where to go next. Read the rest of this entry »

Large organizations are just not set up to be agile

September 21st, 2015 by

There is something going wrong in the organization I am currently part of an onsite project, yet I can’t get my head around the specifics.

This very large governmental organization is growing agile initiatives all over the IT department. Development teams are having fun talking to their customers and are trying to create the best solutions to the needs of their stakeholders, yet everything agile about Agile halts when a shippable increment is ready to begin its journey through the rest of the organization.

A few weeks back I received an email from the team that is tasked with the becoming agile of our organization, asking me to fill out a form so I could show how SCRUM we were. I know it was meant to let us see for ourselves how agile we were, but to me it felt as if we were proving our agility through a checklist.

I know I checked every box there was, so according to the person sending it we were 100% Scrum. I just knew we weren’t agile, so there must be a flaw in checking of the Scrum checklist to see if you are agile.

Read the rest of this entry »

A wrinkle in time

June 30th, 2015 by

Leap second

I clearly remember the morning of Sunday July 1, 2012, almost three years ago. I was at church, actually, when I got a call from one of our clients: “The website doesn’t seem to be working.” All I could check at that point was that, indeed, the website was not responding. So I called our sysadmin, who found that even SSH-ing into the machine running the site was taking much longer than usual. Finally, restarting everything solved the problem, but we were still unsure about what had happened.

As we found out later, it was related to the leap second, a phenomenon I had not even heard of until then. Read the rest of this entry »

Axon from the trenches: how to keep your code compatible with legacy events and Sagas

June 8th, 2015 by

Imagine you’re using Axon to run an event sourcing application. Your production event store might contain millions of events, created in various versions of the application. Wouldn’t it be nice to know for sure that the latest version of your application plays nicely with all your production events and Sagas, including those from previous versions? Well, you can check for that, and it is fairly easy. Read the rest of this entry »