Trifork Blog

Category ‘Java’

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.

Integrating the AWS Parameter Store with Spring Cloud

July 20th, 2018 by

I’ll tell you all my secrets (but I lie about my past)
— Tom Waits – Tango till they’re sore


We’ve integrated the AWS Parameter Store with Spring Cloud so that it can be used as a secure configuration backend for services deployed to EC2, including ECS. This code has recently been merged in Spring Cloud AWS and is available in its 2.0 release.


At the moment I’m working on a project where we’re developing a microservices-based system based on Spring Cloud for the Dutch Lotteries. The services are deployed on Amazon Web Services using Amazon’s current Docker support (ECS).

When we started late last year, we decided to use Consul, both as a service registry and as a key-value store for configuration. Spring Cloud has excellent built-in integration with Consul, both for service discovery as well as for using it as a shared configuration backend.

However, we quickly found out that we needed an internal load balancer to allow ECS to perform health checks on the services, so we might as well use that for server-side load balancing. This eliminated the need for client-side routing and service discovery. Furthermore, we weren’t too happy with the options to easily restrict access to secrets stored as config in Consul and were looking for a configuration service provided by AWS (rather than e.g. Vault) so that we’d no longer need to operate our own Consul cluster or other middleware.

AWS Parameter Store

When we looked for alternative solutions we soon found the AWS Parameter Store: it’s an option provided by EC2 to store all sorts of configuration parameters, including secrets that are encrypted at rest. Using IAM roles you can restrict access to parameters, which can have nested paths that can be used to define ACL-like access constraints. It also integrates with ECS quite nicely, by allowing containers to retrieve credentials to access the store, and provides versioning of parameter values.

This screenshot provides an impression of the corresponding console:

However, when looking for integration with Spring Cloud I just found some open tickets, so I decided to try to develop some integration myself. This blog post describes the result of that effort.

Read the rest of this entry »

Using Axon with PostgreSQL without TOAST

October 9th, 2017 by

The client I work for at this time is leveraging Axon 3. The events are stored in a PostgreSQL database. PostgreSQL uses a thing called TOAST (The Oversized-Attribute Storage Technique) to store large values.

From the PostgreSQL documentation:

“PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows”

As it happens, in our setup using JPA (Hibernate) to store events, the DomainEventEntry entity has a @Lob annotation on the payload and the metaData fields (via extension of the AbstractEventEntry class):

For PostgreSQL this will result in events that are not easily readable:

SELECT payload FROM domainevententry;

| payload |
| 24153   |

The data type of the payload column of the domainevententry table is OID.

The PostgreSQL JDBC driver obviously knows how to deal with this. The real content is deTOASTed lazily. Using PL/pgSQL it is possible to store a value in a file. But this needs to be done value by value. But when you are debugging your application and want a quick look at the events of your application, this is not a fun route to take.

So we wanted to change the data type in our database to something more human readable. BYTEA for example. Able to store store large values in, yet still readable. As it turned out, a couple changes are needed to get it working.

It took me a while to get all the pieces I needed. Although the solution I present here works for us, perhaps this could not be the most elegant of even the best solution for everyone.
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 »

Writing less code

November 23rd, 2016 by

Have you had that feeling that you have to write too much code to build simple functionality? Some things just feel repetitive, they feel you should be not have to write them yourself, instead a framework should make your life easier.

Recently I’ve been building a project in Java/Spring, and after some time I started wondering about alternatives and how to build the same functionality with less code.

There is lots of alternative frameworks and multiple ways of building rest endpoints in Java/Spring.

  • Building the controller/service/dao layers manually in Spring ;
  • Using spring-data-rest to export your spring-data repositories ;
  • Groovy/grails RestfulController ;
  • Python/django django-rest-framework ;
  • etc


Below some abbreviated examples of how a simple rest endpoint looks for each approach. To actually run the examples, you’ll need check out the tutorials mentioned earlier. My goal here is a quick comparison of how you do things in each framework.

Read the rest of this entry »

Personalised city trip itinerary using integer linear programming

February 1st, 2016 by


As a research project I have developed an itinerary service. The idea started when I was doing a hackathon with colleagues for the city of Amsterdam (see earlier post). I wanted to recommend an itinerary to a tourist visiting the city of Amsterdam. Furthermore, I wanted to make the itinerary based on the user’s interests to recommend interesting places and activities for him in the city. If the user is interested in modern art for example, the recommendation scores for modern art museums will increase for that user.

The tool divides the duration of the tourist’s stay into separate time slots. For example, a single day could be divided in 3 time slots: morning, afternoon, evening. POIs get a different recommendation score for each time slot they can be visited. The Vondelpark for example could be less recommended on Monday morning because of expected rain or because of expected crowds. On Tuesday morning the Vondelpark could be recommended because of an interesting event or nice weather.

The itinerary tool will try to limit travel time between the recommended POIs (point of interests). In this way the tourist will not waste time on travailing. The user can also set a budget for the entire itinerary.

By taking all these considerations into account the tool should be able to aid the user in making good decisions about which places to visit and when to visit them.

Here is a screenshot of the user interface of the itinerary tool that was created as part of the research project:


Read the rest of this entry »

Controlling Java with the Leap Motion

November 17th, 2015 by

Leap Motion Controller

The Leap Motion Controller is a device that uses two cameras to track the hands and fingers. This makes it possible to use gestures for controlling the computer or applications. It is possible to buy or download applications through the Leap Motion app store, but there is also an SDK for different languages available to integrate the controller in your own application.

With this article I aim to give an insight in the usability of the Leap Motion Controller in combination with Java. For this I describe the controller and Java API itself and have written an example application which uses the controller. The application is written in Java and is available on github.

Functionality of the controller

The basic functionality for the controller and API is working without problems. This makes it possible to make the interaction with devices and computers more intuitive. In the next screenshot an example is shown from the supplied Visualizer application with the detected hands and fingers.
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 »

Recognizing commercials using the Alphonso API

September 21st, 2015 by

Liberty Global organized the Hack & Play Appathon in Ziggo dome on September 15th and 16th. More than 20 teams of hackers, designers and programmers were invited to create an app or a game for the Liberty Global product Horizon set-top box. Team Trifork joined with Dennis de Goede (Design & Frontend), Tony Abidi (Devops) and myself (Front & Backend).

Alphonso added another challenge to the appathon: Create the best integration with the Alphonso platform. Integration challenge? Sounds like a Trifork challenge to me.



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 »