Introduction

If you are a backend developer you will eventually have to query the database, there is no way around it. Depending on the specific requirements, this is easy or tedious, so naturally there are multiple ways of handling these interactions are offered inside the Spring framework. Today we are going to explore some of the most popular tools and we are going to do it by solving the same simple task with each of them.

‍

Our simple task to solve‍

The requirement for today will be to select all employees with a specific job name and hired on a specific day.

This is how our “employee” table looks and the data it contains:

Mapped to this table we have the “Employee” entity inside our Java application. It looks like this:
Note: Constructors, getters and setters were omitted for brevity.

‍

Now let’s get to work and see how this task can be accomplished.

‍

Derived queries

This is arguably the easiest way to write a query in Spring. Best used for simple queries with few parameters, we can let Spring Data generate the SQL syntax from the method name, as long as we respect the correct naming pattern. Let’s solve the above-mentioned problem using this method and see how that looks.

The solution:

Because the name starts with “findBy”, Spring Data will interpret this as a query and will select all the employees with a matching jobname and hire date.

Read more about derived queries here Ultimate guide for derived queries.

JPQL queries

Another method of retrieving data from our database is by writing the queries ourselves using JPQL syntax and using the “@Query” annotation in our repository. JPQL stands for “Jakarta Persistence Query Language” and it looks very similar to SQL which makes it easy to understand, but at the same time has the advantage of using the entities defined in our Java code. This is great because sometimes our database tables are not named like the correspondent Java entity, so there will be no confusion when reading the query. Let’s see the result:

This is so similar to SQL one could understandably think it actually is, at a quick glance. The first giveaway is the reference to “Employee” object instead of the “employee” table and the second one is the column names “jobName” and “hireDate” being camel case (like in the “Employee” entity) rather than snake case, used in the table.

Another mention should be made for how parameters are passed from the method signature to the query. Using the “@Param(“paramName”)”annotation we can provide a name for the parameter that will be used inside our JPQL query and then access it’s value by using “:paramName” inside the “@Query”annotation.

More info about JPQL queries: JPQL.

Native queries

This method will be very comfortable for most programmers since we will be using SQL. It’s also easy to implement. Using the same “@Query” annotation from the previous method, we can write SQL code and then set the “nativeQuery” parameter as true. This is how it looks:

Look closely and you will notice the slight differences in the naming cases being used. We are no longer referencing Java entities andtheir fields, instead we use the names of tables and columns from our database.

More about native queries: Native queries.

Query by example

Using the Spring Data Query by Example API we can retrieve entries from our database by simply building an object, populating the field that interest us with the desired values and then using the result as an example to match other entries from the database. It’s easier to explain if we first see how we would implement our solution:

‍

‍

‍

‍

‍

‍

‍

First, we build our “ExampleMatcher” object to specify the rules. In our case, we want the “jobname” and “hireDate” to be an exact match with the values we will provide and we also want the matching to be case insensitive, hence the “exact()” and “ignoreCase()” calls. We also want to get only the entries where both of these values are matched and that is the reason for the “matchingAll()”call in the beginning. Next, we create our “Employee” entity instance and we provide the job name and the date of hiring we want. Using the matcher and employee that we created we finally build our example and then pass it to the “findAll()” query from our repository. You should know that the repository must implement the “QueryByExampleExecutor” interface, which is included in the “JpaRepository” that we use.

More about Query by Example API: Query by example.

Querydsl

The Querydsl (Query Domain Specific Language) library is a great way to create easy to read queries and even build them at runtime, if depending on some external conditions your query will differ.

Before we get to the solution of our problem, I must mention this approach means a few preparations will have to be done in order to have all the dependencies and generate the required meta classes. I recommend reading this guide if you want to setup your project to use Querydsl.

Let’s see the solution for our problem:

As you can see, constructing a query is very easy and it has a similar structure to SQL. Using an instance of the “JPAQueryFactory” and the metaclass generated for the “Employee” entity, named “QEmployee”, we can fetch the data we want from the database in an elegant manner. Make sure to read the linked guide for an in-depth explanation of what goes on behind the scenes.

Conclusion

Today we explored some of the most popular ways of interrogating a database in Spring, however we did not go into details, and we also didn’t compare these approaches between them, because we wanted to keep this relatively short. We hope you now have a better idea on the many possibilities at your disposal when you interact with your database. We encourage you to take time and research each method because depending on your specific task, the right path will differ. Good luck!