Learn how to apply Java Persistence query language commands to a query method.
- [Instructor] We've seen some pretty cool ways to make query methods using property expressions in clauses with the method signature. Another way to declare a query method is by applying the @Query annotation. The contents of the the @Query annotation can be a source specific query language, like JPQL, or a query language native to the actual data source management system like MySQL SQL. When declaring a query method with @Query the method signature can be anything you like.
It does not have to follow the property expression rules. The course repository will hold our @Query examples. Let's look at some reasons where using @Query is the better choice. Method simplification. Sometimes property expressions can get too long and awkward. Doesn't find by chair last name seem more natural than, find by department chair member last name? Parameter substitution is possible with the @Param annotation. If I didn't supply @Param then the @?, in a number works just as well.
Another reason to use @Query is that the query is too complex for property expressions. In the first query, we need to make a JPQL join of course, to prerequisite courses. The second query has a JPL select new, which returns a whole new object that's not even a JPA entity. There are times when you need to go native. Meaning, actually make a call to the native data management system. You can do this by setting the native query attribute to, true.
Even for a native query, the resulting rows are still mapped back to the JPA entity. In this example, I am making a call out to MySQL SQL to find the three youngest students. It may sound strange but as of Java EE 6 JPQL does not have the ability to limit the results of a query. Something which is possible with property expressions. So, let's look at our course repository and here's where we have the usage of the @Query.
And I'm going to go into query demos the JPQL queries method and as we step through these, I've actually put in the comment, the JPQL associated with it so we don't have to go back and forth to the repository. So, first I'm gonna show how method simplification is better with @Query so this is the long signature just to look up department by the member last name. So find by chair last name, is Jones.
See the console. So this is going to find all the courses that are in a department where the department's head or chair, has a last name of Jones. So, if you remember Dean Jones was the department chair for both Humanities and Social Sciences. So that's why we see all those courses. So now we're gonna do some complex queries. First I'm looking up the English 101 course. And it's gonna help us as parameters to our query. So, we want to look up all the courses where English 101 is the prerequisite.
Find course by prerequisite, passing in the english 101 ID. So, here's all the courses where you have to take English 101 before moving on. And now we're going to use the JPQL Select New where it allows us to actually instantiate a new object that's not necessarily a JPA entity. And so we're passing in English 101 and the ID of that. Here is our course view.
Where we don't want all of the other attributes like the ID. We just wanna know the name of the course, the instructor's last name and the name of the department it's in.
In this course, learn how to easily implement JPA-based repositories using Spring Data JPA. Mary Ellen Bowman describes the Spring Data umbrella project, and helps you understand JPA for object-relational mapping. She also covers querying, and dives into other Spring Data Commons features such as QueryDSL and auditing.
- Spring Data Commons
- Using JPA for object-relational mapping
- Declaring Spring Data Repositories
- Creating query methods with property expressions and @Query
- Query by example
- QueryDSL Spring Data Extension
- Spring Data REST
- Introduction to Spring Data Mongo
- Common pitfalls