Learn how to create simple query methods.
- [Narrator] For general purpose database lookups, the built in find all and find one repository methods are sufficient. But what should we do if we want to look up all full-time students, or students with a certain last name, or students within an age range, or even all students taking a certain course. This chapter demonstrates how to use spring data commons to query a data source. The exercise files use spring data JPA, but the lessons learned here apply to any spring data module that depends on commons.
I want to show you the expanded domain model for the university application that we use in this chapter. So along with student, it has a person attribute. A student can be enrolled in several courses, and each course has an instructor which is of type staff, and a list of prerequisite courses. Several courses are associated with one department and each department has a chair which is also of type staff.
The staff entity encapsulates a person attribute as a member. This chapter focuses on querying with spring data, but before that we need to load up the H2 database when the application starts. We can do this in the main class called University Application. So in University Application we inject the repository interfaces, implement the command line runner interface, and we have to then implement the run method for that and override that.
And we will populate the in memory store with students, staff, departments, courses, and course prerequisites. So here's our list of students. We only have four students in this school so far. We have several staff members. Some are deans, and some are professors. Let me make this a little bigger to fit it in. There's three departments, humanities, social sciences, and natural sciences. And for each of those there is a chair of the department: Dean Jones, Dean Martin, and Dean Jones is also chair of social sciences as well as humanities.
Within humanities we have courses for English, and for each of the courses we have a professor. And we also reference within that course the department it's in, and we also note after we've persisted we want to add any prerequisites to it. So you can't take English 202 or English 201 without first taking English 101. And so then we have our natural science courses and any prerequisites and social science courses with their titles.
Three is the number of credits the course is, the professor and the department it's in. And for some reason you cannot take psychology without taking history or English. So it can cross departments. So at application startup this will save it to the H2 memory database, and when it stops the memory goes away so this has to run each time at startup. Let's look at the student repository.
And now we actually have methods in our crud repository for student repository. We're just going to look at these first three. So I have find by full-time, find by age, and find by attendee last name. Which your typical things you would like to look up on a student. And full-time we know is an attribute of student, and it's a Boolean type. We know that age is an attribute of student, and that's a type integer. And we know that there is an attendee attribute on a student and within attendee there is a last name attribute, and that's a string or having that as a parameter.
So these query methods are declared, but who implements the methods? Well the good news is it's not me. And I'll run it just to prove that. So in query demos we're going to look at this simple query example method. So at startup by just running this test, it will invoke the university application run method and instantiate all of our university objects and come into the break point here in simple query examples.
I'll make this a little easier to read on one page. So first we're going to find all students that are 20. There's only one student that's 20. Then we're gonna find full-time students. We have three full-time students. And we're gonna find students that have the last name of Doe. We have two students with the last name of Doe. Is it a miracle? Is it magic? Well no, it's actually not magical at all.
Via be-an utils and reflection, spring implements the method under the covers for us. However, we must follow the rules when declaring methods and correctly map the entity properties to the method signatures. So these are the rules when using property expressions for our query methods. First we have to declare the return type. Then you need to begin the method signature with findBy followed by the property name in camel case. Optionally, chain sub-property names following camel case rules as we did in findByAttendeeLastName.
And then add any query parameters whose type matches the property type. Spring data facilitates fast failure. Here attendee is misspelled. At bootstrap, spring data throws a PropertyReferenceException. Without spring data you would not know there was a syntax error until the query actually runs.
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