Friday, March 31, 2023

Spring Data JPA Query Methods

 Spring Data JPA Queries are a great way to create queries derived from the method name without needing to write tons of boiler-plate code to create connection & query, execute it and then process the result set. This generally works fine for simple queries and method names are very explicit in pointing out what is being returned by that method so the code is more readable and maintainable. To make this work successfully, the method name has to follow certain conventions:

  1. Method names usually start with the word "find"
  2. Filter clauses are added to the function name as "findBy<ColumnName>" which creates queries of the form "where <ColumnName> = ?"
  3. Multiple Filter clauses are supported using And / Or clauses 
    1. And can be used as "findBy<Col1>And<Col2>"  which creates the query "where <Col1> = ? and <Col2> = ?"
    2. Or can be used as "findBy<Col1>Or<Col2>"  which creates the query "where <Col1> = ? or <Col2> = ?"
  4. Similarly other operators are supported on the lines of Between, After, Before, LessThan, GreaterThan, Like, etc
  5. Ordering of the results can be delegated to the database by adding "OrderBy" to the function name as "findBy<Col1>OrderBy<Col2><Asc|Desc><Col3><Asc|Desc>"
Here is a handy table of supported keywords inside method names taken from the Spring Data documentation:

KeywordSampleJPQL snippet

Distinct

findDistinctByLastnameAndFirstname

select distinct …​ where x.lastname = ?1 and x.firstname = ?2

And

findByLastnameAndFirstname

… where x.lastname = ?1 and x.firstname = ?2

Or

findByLastnameOrFirstname

… where x.lastname = ?1 or x.firstname = ?2

IsEquals

findByFirstname,findByFirstnameIs,findByFirstnameEquals

… where x.firstname = ?1

Between

findByStartDateBetween

… where x.startDate between ?1 and ?2

LessThan

findByAgeLessThan

… where x.age < ?1

LessThanEqual

findByAgeLessThanEqual

… where x.age <= ?1

GreaterThan

findByAgeGreaterThan

… where x.age > ?1

GreaterThanEqual

findByAgeGreaterThanEqual

… where x.age >= ?1

After

findByStartDateAfter

… where x.startDate > ?1

Before

findByStartDateBefore

… where x.startDate < ?1

IsNullNull

findByAge(Is)Null

… where x.age is null

IsNotNullNotNull

findByAge(Is)NotNull

… where x.age not null

Like

findByFirstnameLike

… where x.firstname like ?1

NotLike

findByFirstnameNotLike

… where x.firstname not like ?1

StartingWith

findByFirstnameStartingWith

… where x.firstname like ?1 (parameter bound with appended %)

EndingWith

findByFirstnameEndingWith

… where x.firstname like ?1 (parameter bound with prepended %)

Containing

findByFirstnameContaining

… where x.firstname like ?1 (parameter bound wrapped in %)

OrderBy

findByAgeOrderByLastnameDesc

… where x.age = ?1 order by x.lastname desc

Not

findByLastnameNot

… where x.lastname <> ?1

In

findByAgeIn(Collection<Age> ages)

… where x.age in ?1

NotIn

findByAgeNotIn(Collection<Age> ages)

… where x.age not in ?1

True

findByActiveTrue()

… where x.active = true

False

findByActiveFalse()

… where x.active = false

IgnoreCase

findByFirstnameIgnoreCase

… where UPPER(x.firstname) = UPPER(?1)


LinkWithin

Related Posts Plugin for WordPress, Blogger...