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:

Keyword	Sample	JPQL 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
Is, Equals	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
IsNull, Null	findByAge(Is)Null	… where x.age is null
IsNotNull, NotNull	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)