Hibernate Tips: Fluent JPA Native Query Builder

 Introduction

 Hibernate offers a convenient abstraction layer over most of the common CRUD SQL operations. Combining it further with Spring Data JPA makes us feel that we have this huge and important part of our development covered by simple Interfaces and APIs. Sooner or later however we will be faced with a requirement that will force us to dig deeper into the SQL handbook and come up with a Query that is not supported by JPA/Hibernate. We then will be thrown into the not so pleasant world of calling Native SQL.


Convenient until it's not

JPA's support for calling Native Queries is not friendly at all. You are required to abandon all of your Java abstraction layers and naming conventions, and simply provide a Native SQL Query in a String. Anyone who needed to code anything using Strings knows how fun it is, even with IDE's support.

Let's set a realistic example to work with. Suppose we have an online book store and we keep our book entries using the following simple Entities:

@Entity
@Table(name = "BOOKS")
public class Book {

    @Id
    @Column(name = "BOOK_ID")
    @GeneratedValue
    private Long id;

    @Column(name = "NAME", unique = true)
    private String name;

    @Column(name = "DESC", nullable = true, columnDefinition = "CLOB")
    private String desc;

    @Column(name = "PRICE")
    private BigDecimal price;

    @Column(name = "IN_STOCK")
    private Boolean inStock;

    @ManyToOne
    @JoinColumn(name = "CAT_ID", nullable = false)
    private Category category;

@Entity
@Table(name = "CATEGORIES")
public class Category {

    @Id
    @Column(name = "CAT_ID")
    @GeneratedValue
    private Long id;

    @Column(name = "NAME", unique = true)
    private String name;


    @Column(name = "DESC", nullable = true, columnDefinition = "CLOB")
    private String desc;

 

Getting a List of all Category Names with an accompanying Count of Books for each Category, divided by 'In Stock' and 'Out of Stock' Books seems easy enough using Criteria API:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Book> book = query.from(Book.class);
Join<Book, Category> category = book.join("category", JoinType.INNER);
query
	.multiselect(category.get("name"), book.get("inStock"), cb.count(book.get("id")))
	.groupBy(category.get("name"), book.get("inStock"));
List<Tuple> result = entityManager.createQuery(query).getResultList();

or JPQL:

List<Tuple> result = entityManager
		.createQuery("select c.name, b.inStock, count(b.id) " +
				"from Book b " +
				"join b.category c " +
				"group by c.name, b.inStock", Tuple.class)
		.getResultList();

 

But what if the Result Set of such query is so big, that we need to paginate it on the UI side? We don't want to display a long grid with hundreds of rows, right? OK, so let's add pagination to our code! Adding limit and offset to our Query won't be an issue. However for pagination to work properly, we need to count the Result Set, because the UI has to compute the number of Pages. And here's where we stumble on a serious issue. We can't do that using JPA API. Trying to perform such Count operation:

Long result = entityManager
		.createQuery("select count(*) from (" +
				"select c.name " +
				"from Book b " +
				"join b.category c " +
				"group by c.name, b.inStock)", Long.class)
		.getFirstResult();

Will result with Hibernate throwing an error:

java.lang.IllegalArgumentException: 
org.hibernate.hql.internal.ast.QuerySyntaxException: 
unexpected token: ( near line 1, column 22 [select count(*) from (select c.name from pl.exsio.querydsl.entityql.groovy.jpa.entity.it.Book b join b.category c group by c.name, c.desc)]

 

Hibernate doesn't support nested 'select' Statements in the 'from' Clause. In such situation the only thing left to do is to write a Native SQL Query and call it a day:

Long result = entityManager
		.createNativeQuery("select count(*) from (" +
				"select c.name " +
				"from books b " +
				"inner join categories c on c.cat_id = b.cat_id " +
				"group by c.name, b.in_stock)", Long.class)
		.getFirstResult();

 

This solution is far from ideal. We need to use completely different naming convention, because Native Query works only with Database Object names. Not to mention the need of using entirely different API. 

And what if our Query gets more complex? What if it will be dynamic, with parts added of removed from it depending on the incoming Request? What if we will need to use Database-specific stuff (like native functions - for example JSON_QUERY from Oracle) that is not supported by our in-memory H2 test database?

We will have on our hands a String concatenation nightmare that will sooner or later cripple our productivity and make maintenance more expensive!

 

A cure for insanity

This is the exact dilemma I've been facing some time ago when using Hibernate.  I was already having issues with using ugly, anachronistic Criteria API and writing JPQL in Strings, but  having to work with Native SQL in such a repulsing way was a deal breaker for me. I needed to come up with something better for the sake of my own sanity.

Implementing fully fledged SQL Builder from scratch seemed to be an overkill. Besides, I wanted to re-use my pre-existent JPA Entity mappings. And so I've decided that the best solution would be to create an adapter between JPA Mappings and some other, already battle-tested SQL library. After a short research I've picked QueryDSL, and so a brand new way of writing JPA Queries was born! Meet...

 

EntityQL to the rescue!

Well, I had to pick a name, right? So I picked one that in my opinion best describes its capabilities. It allows us to write Queries using Entities. QueryDSL has its own, proprietary metadata that is used for building Native SQL. All I had to do is to provide a translation layer that would read the metadata from JPA Entities and pass it to QueryDSL. The results are fabulous. All it takes for you to start using EntityQL is to get it into your project by adding Jitpack to your repositories:

<repositories>
	<repository>
		<id>jitpack.io</id>
		<url>https://jitpack.io</url>
	</repository>
</repositories>

and then by adding the dependency itself:

<dependency>
	<groupId>com.github.eXsio</groupId>
	<artifactId>querydsl-entityql</artifactId>
	<version>3.2.0</version>
</dependency>

Then add some Spring Configuration:

@Bean
SQLTemplates sqlTemplates() {
	return new H2Templates() //choose the implementation that matches your database engine
}

@Bean
SQLQueryFactory queryFactory(DataSource dataSource, SQLTemplates sqlTemplates) {
	return new EntityQlQueryFactory(new com.querydsl.sql.Configuration(sqlTemplates), dataSource);
}

And you're good to go!

 

Mirror, mirror on the wall, who's the sweetest of them all?

OK so how do we exactly write our SQL now? Easy peasy, we just use the SQLQueryFactory bean that we've configured just now:

Q<Book> book = qEntity(Book.class);
Q<Category> category = qEntity(Category.class);
Long result = queryFactory
                .select(count())
		.from(
			select(
				category.string("name")
			)
				.from(book)
				.innerJoin(book.joinColumn("category"), category)
				.groupBy(
						category.string("name"),
						book.bool("inStock")
				)
		).fetchOne();

 

Isn't that nicer? More readable? More modern? Less error prone? It most certainly is. Is it more performant? My JMH-based benchmarks indicate that it's aroud 3 times faster than Criteria API and JPQL. 

Now you can go ahead and rewrite all of your ugly Criteria/JPQL queries to this beautiful, fluent Java API. Don't worry about whether it will work with your Spring-managed DB Transactions. It will. That's what the EntityQlQueryFactory bean is for. You can mix and match your JPA and EntityQL queries however you want.

 

Static Charge

The EntityQL SQL building way described above is superior to Criteria API and JPQL in pretty much every aspect. We still however reference field names of our Entities using Strings. We can do one step further, and generate Static Metamodel classes out of our Entities. If we do that, we will gain some extra-performannce (no Reflection!) and some great type-safety and IDE support for writing our queries, that will now look like this:

Long result = queryFactory.select(count())
                .from(
                        select(
                                qCategory.name
                        )
                                .from(qBook)
                                .innerJoin(qBook.category, qCategory)
                                .groupBy(
                                        qCategory.name,
                                        qBook.inStock
                                )
                ).fetchOne();

 

All you need to do is use Maven or Gradle plugins to generate your classes, and you're Persistence Code will look better that ever before.

 

Conclusion

Coming up with yet another way to write SQL may seem weird these days. Nevertheless I've been using EntityQL in Enterprise Grade production environments for over a year now and I can tell you that I've never felt so productive when it comes to writing SQL Queries. I don't have to choose between 

  • JPA / Hibernate Criteria API
  • JPQL / HQL
  • Native Queries
  • Spring Data JPA Interfaces
  • Spring Data JPA Query by Example
  • using Entity Manager / Hibernate Session directly

I just build my SQL using EntityQL and it always works. And all my Data Access Code looks consistent.

P.S.

If you decide to give it a shot, send a shout out to the guys who maintain QueryDSL. We've had a new 5.0 release with tons of fixes and new stuff :)

Comments

Post a Comment