Hibernate: Mapping nativeQuery results to POJO

2 min readJan 22, 2021

TL;DR: If you need a simple class to store the results of an arbitrary query, spring + hibernate will do it for you.

We use spring + hibernate ORM to load entities from a database, which simplifies a lot of CRUD operations on those entities. Recently I needed to run some arbitrary SQL on tables in our database that aren’t represented by entities, and spanned two tables:

SELECT u.userId, 
       a.accountId 
FROM   users u, 
       accounts a, 
       user_accounts ac 
WHERE  u.userId = ac.userId 
       AND a.accountId = ac.accountId 
       AND ac.createdate < 20050101

Even if I have an @Entity for both Users and Accounts, I really just want to get out a list showing me the user-account mapping, without dealing with entities.

I started with

// first attempt; works but risky
@Component
public interface CampaignRepository extends JpaRepository {...  @Query(nativeQuery = true,
            value = "SELECT u.userId, a.accountId FROM users u, account a, user_accounts ac WHERE u.userId = ac.userId AND a.accountId = ac.accountId AND ac.createdate < :createDate")
  List<List<Long>> findOldUserAccountLinks(
  @Param("createDate") @NotNull Long createDate
);
...
}

It worked, did exactly what I needed, and took very few line of code.

However, now my application code was scatter with stuff like

for (List<Long> row : rows) {
  Long userId = row.get(0);
  ...
}

and

Set<Long> allAccountIds = rows.stream()
  .map(r -> r.get(0))
  .collect(Collectors.toSet());

You can probably see the bug in the second block; get(0) should have been get(1).

I tried to create a POJO class to store these two values ( userId and accountID) but now Hibernate was asking for a converter class; it was feeling like a lot of overhead and lines of code to do something “simple”. I tried using SqlResultTransformer and didn’t get far.

Ultimately I found the right Stack Overflow posts and found that the framework will do most of the work for free!

UserAccountTuple is an interface, and on the fly spring+hibernate will dynamically define a class that fulfills that interface, and know how to load the results from the query directly into the List<UserAccountTuple>.

It’s very efficient in terms of lines of code that have to be reviewed, but most importantly the client code is much safer now; we can clearly see that r.getAccountId() does what we want, unlike r.get(0). Hope that helps!

Hibernate: Mapping nativeQuery results to POJO

Written by Mike Kowdley

Responses (2)