Spring Boot – Query Methods

So now, we we are going to look at Query Methods. You use query methods to retrieve data from the repository. Query Methods are much easier to follow than SQL Queries.

 

We’ll cover the following:

  1. Introduction to Query Methods
  2. Values Returned from Query Methods
  3. Passing Parameters to Query Methods
  4. The Like Clause in Query Methods
  5. Summary

 

 

1. Introduction to Query Methods

We use Query Methods to find information from the database. So you don’t need to learn SQL queries. Query methods are declared in the repository interface. For example, we have our Student class. It have member variables of id, name and department.  Recall that we can use the method findById() of the repository interface to get student by id.

Similarly if we want to get student by name we can define findByName() method in the repository. JPA provides the implementation. Now, these are query methods that have been written for us.

 

public interface StudentRepository extends CrudRepository<Student, String> {
	
    //this is a query method already implemented by JPA
   public Optional<Student> findById(String id);
	
}

 

I would recommend you try to change up things a bit and see what results you get.

 

 

2. Values Returned From Query Methods

Also recall that in these method return either a List<Object> or a single object. Generally, a Query method can return either one or more results.

Let’s now write a query method to select only the name. The code below shows what this method would be using query methods.

 

@Query("SELECT t.name FROM Student t where t.id = :id")
public Optional<String> findNameById(@Param("id")String id);

 

In this case, we are returning only the name of the Student where the id is given. This is very much similar to SQL Query.

Again, if we want to return the Student object, we simply replace the String return type with Student. We also replace t.name with *. So we have the code below would return the Student with the given id.

 

@Query("SELECT * FROM Student t where t.id = :id")
public Optional<Student> findById(@Param("id")String id);

 

Now you can modify this queries to get any result you want. For example, you can do findByName() and so on. Let’s now talk about parameters.

 

 

3. Passing Parameters to Query Methods

Now we would see how we can query with more than on criteria. For example, where name =”somename” and department=”somedepartment”. We can do this in two ways:

  • named parameters
  • position-based parameters

 

For position-based parameters, the query contains the parameter placeholders while the method contains the actual parameters. So the placeholders are replaced by the actual parameters provided. The order of the method parameters determine which placeholders are replaced by the parameters.

The first placeholder is replaced by the first parameter, the second placeholder is replaced by the second parameter and so on. A placeholder is made up of a question mark(?) and a number.

Let’s take an example:

 

@Query("SELECT * FROM Student t where t.id = ?1 AND t.department = ?2")
public Optional<Student> findByNameAndDepartment(String name, String department);

 

In the code above, the first placeholder is ?1. This is replaced with the name parameter.

Likewise, the second placeholder is ?2. This is replaced with the department parameter.

I would recommend you try to change up things a bit and see what results you get.

 

 

4. The “Like” Clause in Spring JPA Repository

Now, what if we want to get a Student object by name but we don’t have the exact name. We only know part of the name. Or say we want to get list of students that have names that start with the letter ‘k’. How do we solve this?

Well JPA alos provides a LIKE clause. To use it, you simply surround the placeholder with the percentage symbol (%). Or if you use named parameter, you also surround the name with the percentage symbol as well.

The example below gets a list of students whose names start with the letter ‘k’.

 

@Query("SELECT * FROM Student t where t.name = %?1%")
public Optional<Student> findByCriteria(String criteria);

 

So I would stop here at this point and then make the video lesson. You can find the video lesson in my Youtube Channel(https://www.youtube.com/user/Kindson01).

 

 

5. Summary

We have covered the following in this lesson

  • Query methods are method used in JPA to find information form the database. Query methods are defined in the repository interface
  • Spring data support different return values including primitive data types and object types
  • We can pass parameters to the queries. This we can do using either named parameters or position-based parameters
  • We can search the database using search criteria.