Using JPQL with Spring Data JPA
Tags: java, spring, jpa, jpql
Published: 2020-01-30 03:35:50

This is the post #4 of the series "Querying your Spring Data JPA Repository".

Let's recap: by now you should have an app like [this](https://jpa-queries-blog-post.herokuapp.com/). The frontend (Thymeleaf + a very bad UI design) doesn't matter since our goal is to understand several ways to query your Spring Data JPA repository. Regardless, I [added Bootstrap](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/020142ef12d5f2413837df50d341c459e8f10154) to make make it easier on the eyes.

# Adding a few properties

Let's add `city`, `grabAndGo` and `active` properties to our `Restaurant` entity. You can see how on [this commit](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/ab7a2e4307a84a4208d5bb0b8755ab2c9c88821a).

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/il9urv4xz85gh1529iuy.png)

# The requirement

Let's say now that you want to create a new custom search on our awesome search area:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/32hl2ruu78g21j7lto8m.png)

This searches for active restaurants with Grab'n'Go enabled in a city specified by the user. 

# "I know how to do this already"

Yes, if you're following the series you already know how to do this with Query Methods. Here you go:

- Create a new method on the RestaurantRepository interface:
```java
List<Restaurant> findAllByActiveTrueAndGrabngoTrueAndCityContaining(String city);
```

That's going to work, but look at the size of the method name! C'mon! Imagine having to use this huge method name all over your code. That's not [clean](https://dzone.com/articles/naming-conventions-from-uncle-bobs-clean-code-phil)!

# `@Query` to the rescue

That's the exact scenario to use a JPQL query. Let's refactor the code above:

- Rename the method to something more acceptable, like `activeGrabngoByCity`.
- Add the annotation `@Query` above the method name.
- Create your custom JPQL query.

Here's the result:

```java
@Query("from Restaurant r where r.active = true and r.grabngo = true and r.city like %:city%")
List<Restaurant> activeGrabngoByCity(String city);
```

Notice that you don't have to follow the Query Methods' rules to name the method. You're telling Spring that you're providing the query for this method via the `@Query` annotation.

For now that's all that I'm going to cover. You can take a look at [this documentation](https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#hql) from Hibernate to do your own cool stuff, JPQL is very powerful!

# The example app

The working app is [here](https://jpa-queries-blog-post.herokuapp.com/) (wait for Heroku to load the app, it takes a few seconds on the free tier).

# Commits related to this post

Adds Bootstrap: [020142](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/020142ef12d5f2413837df50d341c459e8f10154).
Adds new properties to the Restaurant:  [ab7a2e](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/ab7a2e4307a84a4208d5bb0b8755ab2c9c88821a).
Refactors to JPQL: [8758bf](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/8758bfb7544c9f2d7a7001467e9c317e59753509).

{% github https://github.com/brunodrugowick/jpa-queries-blog-post no-readme %}