Spring Data JPA Query Methods/Repository Methods

By
Rakesh
-

In this tutorial, we will see Spring Data JPA Query Methods Example using Spring Boot and Oracle.

Let’s see how to define the Query method(Query creation from method names) using Spring Data JPA.

Consider we have an entity called Student.java as below.

package com.javatute.entity;
 
@Entity
public class Student {
 
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;
 
	@Column(name = "name")
	private String name;
 
	@Column(name = "roll_number")
	private String rollNumber;
 
	@Column(name = "university")
	String university;
	
 
}

and we have some record in the database.

Spring Data JPA Query Methods

Consider we have our repository interface (i.e StudentRepository extending JpaRepository interface) as below.

@Repository
public interface StudentRepository extends JpaRepository<Student, Serializable> {

	public List<Student> findByName(String name);

}

Let’s see some basic Query Methods.

Get all students basis of a given name.
public List<Student> findByName(String name);

Get all students basis of rollNumber or university.
public List<Student> findByRollNumber(String rollNumber);
public List<Student> findByUniversity(String university);

Here is the complete example of the above Query Method.

We have seen some basic example of defining Query Methods. Spring Data JPA provides predefined keywords, using that we can define different types of query methods according to our requirement. Let’s see some more example.

Query Methods using And Keyword.

public List<Student> findByNameAndRollNumber(String name, String rollNumber);

Generated Query –

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.name=?
and student0_.roll_number=?

Query method using Or Keyword.

public List<Student> findByNameOrRollNumber(String name, String rollNumber);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.name=?
or student0_.roll_number=?

Query method using And and Or Keyword together .

public List<Student> findByNameAndRollNumberOrUniversity(String name, String rollNumber, String university);

Generated Query –

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.name=?
and student0_.roll_number=?
or student0_.university=?

Query method using Between Keyword.

public List<Student> findByRollNumberBetween(String start, String end);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.roll_number between ? and ?

 

Query method using LessThan Keyword.

public List<Student> findByRollNumberLessThan(String rollnumber);

Generated Query.

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where student0_.roll_number<?

Query method using LessThanEqual Keyword.

public List<Student> findByRollNumberLessThanEqual(String rollnumber);

Generated Query.

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where student0_.roll_number<=?

We will see some more example for Spring Data JPA Query Methods

Query method using GreaterThan Keyword.

public List<Student> findByRollNumberGreaterThan(String rollnumber);

Generated Query.

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where student0_.roll_number>?

Query method using GreaterThanEqual Keyword.

public List<Student> findByRollNumberGreaterThanEqual(String rollnumber);

Generated Query.

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where student0_.roll_number>=?

Query method using IsNull Keyword.

public List<Student> findByNameIsNull();

Generated Query.

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where student0_.name is null

Query method using IsNotNull Keyword.

public List<Student> findByNameOrNameIsNull(String name);

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where student0_.name=? or student0_.name is null

Query method using Like Keyword.

public List<Student> findByNameLike(String name);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.name like ?

Note –  Value will get passed as below. Did you notice value is not wrapped with % i.e %john%. For that we have Containing.

Spring Data JPA Query Methods

Query method using Containing Keyword.

public List<Student> findByNameContaining(String name);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.name like ?

Note –  Value will get passed as below. Did you notice value is wrapped with % i.e %john%.

Spring Data JPA Query Methods

Query method using StartingWith Keyword.

public List<Student> findByNameStartingWith(String name);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.name like ?

Note – how value is passing in query as below. suppose we pass jo as value.

2020-03-20 09:32:36.412 TRACE 1200 — [nio-9091-exec-4] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] – [jo%]

Query method using EndingWith Keyword.

public List<Student> findByNameEndingWith(String name);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.name like ?

Note – how value is passing in query as below. suppose we pass jo as value.

2020-03-20 09:36:39.787 TRACE 1200 — [nio-9091-exec-9] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] – [%k]

Query method using OrderBy Keyword.

List<Student> findAllByOrderByNameAsc();

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ order by student0_.name asc

Query method using Not Keyword.

public List<Student> findByNameNot(String name);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.name<>?

Query method using In Keyword.

public List<Student> findByRollNumberIn(List<String> rollNumbers);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.roll_number in (
? , ?
)

Query method using NotIn Keyword.

public List<Student> findByRollNumberNotIn(List<String> rollNumbers);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
student0_.roll_number not in (
? , ? , ?
)

Query method using IgnoreCase Keyword.

public List<Student> findByNameContainingIgnoreCase(String name);

Generated Query.

Hibernate:
select
student0_.id as id1_0_,
student0_.name as name2_0_,
student0_.roll_number as roll_number3_0_,
student0_.university as university4_0_
from
student student0_
where
upper(student0_.name) like upper(?)

Query method for Nested Property.

Consider we have two entities Student.java and Address.java. Student and Address entities are in one to one relationship and we want to fetch all students from the database who belongs to city pune.

Note – city property defined in Address entity.

List<Student> findByAddressCity(String city);

Generated Query –

Hibernate:
select
address0_.id as id1_0_0_,
address0_.city as city2_0_0_,
address0_.house_number as house_number3_0_0_
from
address address0_
where
address0_.id=?

See a complete example here.

 

Query method for Case Insensitive Search.

public List<Student> findByNameIgnoreCase(String name);

Generated Query –

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where upper(student0_.name)=upper(?)

See compelete example here.

Query method for Sorting.

List<Student> findAllByOrderByNameAsc();

Generated Query –

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ order by student0_.name asc

See a complete example here.

That’s all about Spring Data JPA Query Methods Using Spring Boot and Oracle.

You may like.

Other Spring Data JPA and Hibernate tutorials.

Spring Data JPA Docs.

RELATED ARTICLESMORE FROM AUTHOR