Spring Data Query Methods

In this article we’ll see how query is generated in Spring Data for the repository interface methods and what all options are there in Spring Data for query methods.

Defining Query Methods

In Spring Data repository proxy has two ways to derive a persistent store specific query from the method name-

  • By deriving the query from the method name directly.
  • By using a manually defined query.

Available options also depend on the persistent store being used.

Query Lookup Strategies in Spring Data

The following strategies are available for the repository infrastructure to resolve the query. With XML configuration, you can configure the strategy at the namespace through the query-lookup-strategy attribute. For Java configuration, you can use the queryLookupStrategy attribute of the Enable${store}Repositories annotation.

Available Query lookup strategies can be accessed using the Enum QueryLookupStrategy.Key which has the values-

  • CREATE– If this option is used then Spring framework attempts to automatically construct a query from the query method name.
  • USE_DECLARED_QUERY– For this option Spring framework tries to find a declared query. The query can be defined by an annotation like @NamedQuery (Used with Spring Data JPA) or @Query.
  • CREATE_IF_NOT_FOUND– This is the default option and it combines CREATE and USE_DECLARED_QUERY. It looks up a declared query first, and, if no declared query is found, it creates a custom method name-based query.

Example using queryLookupStrategy attribute with @EnableJpaRepositories annotation.

@Configuration
@EnableJpaRepositories(basePackages = "com.knpcode.springproject.dao", queryLookupStrategy=Key.CREATE)
@EnableTransactionManagement
@PropertySource("classpath:config/db.properties")
public class JPAConfig {
	...
	...

}

Query creation in Spring Data

By extending Spring Data’s Repository interface like CrudRepository, JpaRepository, ReactiveCrudRepository you get out-of-the-box access to set of CRUD operations (i.e. save, saveAll, findById, findAll, findAllById, count, delete, deleteById, and more). We can also add custom query methods like given in the following definition of the EmployeeRepository.

public interface EmployeeRepository extends CrudRepository<Employee, Integer> {
	List<Employee> findByLastName(String lastName);
}

When Spring Data sees an interface extending its Repository marker interface the query execution engine creates proxy instances for those interfaces at runtime. It scans every method, and parses their method signatures. The parsing mechanism strips the prefixes find…By, read…By, query…By, count…By, get…By from the method and starts parsing the rest of it.

For example in case of out custom method findByLastName, seeing findBy it parses the rest of the method and start looking for the same property name in the domain class (Employee). Because it can see that Employee has lastName property it now has enough information to know how to create a query for this particular method.

The method name may contain further expressions, such as a Distinct to set a distinct flag on the query to be created. However, the first “By” with in the method name acts as delimiter to indicate the start of the actual criteria.

The following table describes the keywords supported for JPA and what a method containing that keyword translates to:

 

Keyword Sample JPQL snippet
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 findByAgeIsNull … 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.firstame) = UPPER(?1)

Many of these operators may work with other supported persistent stores like MongoDB, Apache Cassandra but be sure to check the specific reference guide.

Declaring Query in Spring Data

To declare a query you can use @NamedQuery annotation in case of Spring Data JPA. The queries for these configuration elements have to be defined in the JPA query language.

See example of Spring Data JPA using @NamedQuery Annotation here- Spring Data JPA @NamedQuery Annotation Example

You can also annotate your query method with @Query with in the Repository interface. For Spring Data JPA, Spring Data JDBC, Spring Data MongoDB, Spring Data Neo4J @Query annotation is available to define query. Be sure to check the specific reference guide.

Spring Data JDBC module only supports defining a query manually using @Query annotation. Deriving a query from the name of the method is currently not supported.

Related Posts

That’s all for the topic Spring Data Query Methods. If something is missing or you have something to share about the topic please write a comment.


You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.