Hi, I’m Thorben Janssen from thoughts-on-java.org with another video. In this video, I will show you how to define
and use custom queries with Spring Data JPA. You will learn how to execute JPQL and native queries, use an expression language in your queries and execute queries that INSERT, UPDATE, or REMOVE records. But before we start, if this is your first time here, and you want to learn how to create your entity mappings with ease, build incredible efficient persistence layers with Hibernate and all types of other Java persistence related stuff, start now by subscribing and clicking the bell, so you don't miss anything. Derived queries are very comfortable to use as long as the queries are not too complicated. But as soon as you use more than 2-3 query parameters or need to define multiple joins to other entities, you need a more flexible approach. In these situations, you better use Spring Data JPA’s @Query annotation to specify a custom JPQL or native SQL query. The @Query annotation gives you full flexibility
over the executed statement, and your message name doesn’t need to follow any conventions. The only thing you need to do is to define
a method in your repository interface, annotate it with @Query, and provide the statement
that you want to execute. Spring Data JPA provides the required JPA
code to execute the statement as a JPQL or native SQL query. Your preferred JPA implementation, e.g., Hibernate or EclipseLink, will then execute the query and map the result. Let’s take a more detailed look at the two query options. Most developers prefer JPQL queries because
they allow you to define your query based on your domain model. Another advantage is that your JPA implementation
handles the different database dialects so that the JPQL query is database agnostic. But these queries also have a downside. JPQL only supports a subset of the SQL standard. So, it’s not a great fit for complex queries. When you define a JPQL query in your repository definition, Spring Data JPA only provides the required JPA code to execute that query. The query itself gets processed and executed by your JPA implementation. So, you can still use everything you learned
about JPQL queries with Hibernate or EclipseLink with Spring Data JPA. It just gets a little bit easier. In the following parts of the video, I will
focus on the Query annotation and expect that you are already familiar with JPQL. If that’s not the case, please take a look
at my free Ultimate Guide to JPQL Queries or signup for my JPA for Beginners course. Creating a JPQL query with Spring Data JPA’s Query annotation is pretty straightforward. You need to annotate a method on your repository
interface with the Query annotation and provide a String with the JPQL query statement. Here you can see an example containing 2 queries
that select Author entities by their firstName or by their firstName and lastName. Please ignore the method and query parameters for now. We will take a closer look at them in a later part of this video. As you can see here, both methods return a
List of Author entities. But their SELECT clauses look very different. The findByFirstName method doesn’t define
any SELECT clause, and the findByFirstNameAndLastName method uses the Author entity to specify the projection. If you take a look at the executed SQL statements,
you can see that Hibernate, my preferred JPA implementation, generates the same SELECT clause for both queries. The SELECT clause of the findByFirstName query
gets automatically generated so that it selects all columns mapped by the entities referenced in the FROM clause. In this example, these are all columns mapped by the Author entity. Based on this approach, you can define and
execute any JPQL query that you might need for your use cases. So, feel free to give it a try and to write your own queries. For some use cases, you might want to retrieve
the query result in a specific order. Using Spring Data JPA, you can define the
sorting of your query results in 2 ways: You can add an ORDER BY clause to your JPQL query or You can add a parameter of type Sort to your
method signature. You’re probably familiar with the first option. The ORDER BY clause is defined in the JPA standard, and it is very similar to the ORDER BY clause you know from SQL. You simply reference one or more entity attributes and use ASC or DESC to specify if you want to sort them in ascending or descending order. I explained the ORDER BY clause and all other
parts of a JPQL query in more details my guide to JPQL. With Spring Data JPA, you can also add a parameter
of type Sort to your method definition. Spring Data JPA will then generate the required
ORDER BY clause. That is the same approach as you can use in a derived query. When you want to call the findAuthors method,
you need to provide a Sort object. Its constructor expects an enumeration that defines the sorting direction and one or more Strings that reference the entity attributes, which you want to use to order the result, by their name. Independent of the approach you choose to
define the sorting of your query result, your JPA implementation will
add an ORDER BY clause to the generated SQL query. In addition to sorting, Spring Data JPA also
provides very comfortable support for pagination. If you add a method parameter of type Pageable
to your repository method, Spring Data JPA generates the required code to handle the pagination of the query result. When you call the findByFirstName method,
you need to provide an object that implements the Pageable interface. You can do that by calling the of method on
the PageRequest method with the number of the page and the size of each page as parameters. Spring Data JPA then calculates the required
limit and offset values based on the provided page number and size and generates the JPQL query. For this JPQL query, your JPA implementation,
for example Hibernate, then creates a SQL query and sends it to the database. In addition to the previously discussed query features, Spring Data JPA also supports SpEL expressions within your query. You can use it to avoid hard references to the name of an entity or to create advanced like expressions. In the previous queries, I always referenced
entities by their name. That prevents you from defining queries for
generic repositories. It also causes a lot of work if you decide to rename an entity because you then need to update all queries that reference the old name of the entity. You can avoid that by using the entityName
variable in an expression. I do that in the following query to avoid
referencing the Author entity by its name. Spring Data JPA replaces the entityName expression with the entityName of the domain type of the repository. So, in this example, Spring Data JPA replaces entityName with Author. Another great feature that you get with the
SpEL expressions is the definition of advanced like expressions. You can, for example, append percentage sign to the beginning and end of a parameter and change the provided bind parameter value to upper
case. As you can see in the log statements, Spring Data JPA called the toUpperCase method on the value of the method parameter firstName and added a percentage sign to the beginning and end of it. In the next step, it set the modified String as a bind parameter value. You can also use the @Query annotation to
execute a native SQL query. As explained earlier, Spring Data JPA provides
the required JPA code to execute the query. The query itself still gets handled by Hibernate
or EclipseLink. So, you can use all your experience with these frameworks and read articles about them to get a deeper understanding of the JPA standard. Next, I will focus on Spring Data JPA’s
support for native queries. If you want to learn more about native queries
in general, please read my article Native Queries - How to call native SQL queries with JPA & Hibernate. Similar to the custom JPQL query, you can
use the Query annotation to specify a custom native SQL query. But you need to tell Spring Data JPA, that
you are defining a native query, so that it can execute the query in the right way. You can do that by setting the nativeQuery attribute of the Query annotation to true. Here you can see an example of a repository
method that uses a native SQL query to load all Author entities with a give firstName. The query that you provide as the value of
the annotation will not be parsed or changed by your JPA implementation. It gets directly send to your database. So, you need to make sure that you provide
a valid SQL statement and follow the specific dialect of your database. To use both kinds of queries with Spring Data JPA, you need to be familiar with bind parameters. Bind parameters act as placeholders in your query for which you can set values before the query gets executes. They are regularly used in the WHERE clause of a query. You should always prefer them over inlining
a parameter value because bind parameters: prevent SQL injection vulnerabilities, get automatically converted to the correct SQL type and enable your persistence provider and database
to optimize your query. Spring Data JPA handles the bind parameters
of JPQL and native SQL queries in the same way. For each query, you can choose if you want
to use positional or named bind parameters. A positional bind parameter gets referenced by its position. In the query, positional bind parameters consist of a question mark followed by a number that specifies the position. Spring Data JPA then provides the required code to set the bind parameter values. It sets the value of each method parameter as the value of a bind parameter with the same position. So, the value of the first method parameter
gets set as the value of the bind parameter at position 1, value of the second method parameter as the value of the bind parameter at position 2 and so on. Named bind parameters specify a name for each parameter. In your query, named bind parameters start
with a column followed by the name of the parameter. That makes them much more expressive and easier to reference. Spring Data JPA can provide the required code
to set the values of these bind parameters. But it needs a little bit of help to match the method parameters with the bind parameters in your query. You either need to annotate the method parameter with a Param and specify the name of the parameter, or you need to compile your application with
Java 8 or higher and set the compiler flag -parameters. Here you can see an example of both bind parameter
types. As you can see in the code snippets, named bind parameters are much easier to read and are not as likely to cause problems during
refactoring. You should, therefore, prefer named bind parameters in your queries. You can not only use the Query annotation to define queries that read data from your database. You can also create queries that add, change,
or remove records in your database. That enables you to implement bulk update
or remove operations which can provide substantial performance improvements. These queries require a slightly different JPA code. That’s why you need to tell Spring Data
JPA that your query modifies data by annotating your repository method with an additional
Modifying annotation. It will then execute the provide JPQL or native
query as an update operation. I use this approach here, to specify a query
that adds a provided prefix to the firstName of all Authors. Spring Data JPA provides multiple options
to define a query on your repository interface. You can use derived queries for which Spring Data JPA generates the required query based on the name of the repository method. I explained that in great details in a previous tutorial. or you can use the Query annotation to define your own query. That’s what we discussed in this video or you can reference a named query which you specified
on one of your entities. We will take a look at that in a future tutorial. By using the @Query annotation, you get full control over the executed query. You can choose between a native SQL or a JPQL query. By default, Spring Data JPA expects that you provide a JPQL query. If you want to execute a native query, you need to set the nativeQuery parameter of the @Query annotation to true. Both kinds of queries get processed by the underlying JPA implementation. That enables you to reuse all your Hibernate
and EclipseLink knowledge and to use the query features supported by these frameworks. You can also specify modifying queries which insert, update, or remove records from the database. Spring Data JPA needs to generate a slightly
different JPA code for these. You, therefore, need to annotate repository methods that execute modifying queries with the Modifying annotation. OK, that’s it for today. If you want to learn more about Hibernate, you should join the free Thoughts on Java Library. It gives you free access to a lot of member-only content like a cheat for this video and an ebook about using native queries with JPA
and Hibernate. I’ll add the link to it to the video description below. And if you like today’s video, please give
it a thumbs up and subscribe below. Bye
