Spring JPA Query

in construction

Spring Data JPA  Queries 

• Derived query, a method signature query
• @Query including JPQL or Native SQL 
• DSL query: type-safed, compiling time error checking 
• Projections: interface-based(closed projections or open projections); class(DTO)-based

Derived Query (Simple Query Method Signature )

This method is applied on the entity level. 

a. return type; b. friendly; c. Entity AttributeName(camelCase) d. query parameters matching entity attribute type

The query method can span multiple nested entities, chaining the attribute names and following the camelCase rules. 

List findByPersonAddressCity(String city); 

filters and, LessThan, GreatThan, Contains, Like, containing(string) IgnoreCase; between(a value and b value);  Sort the attributes: OrderBy;

Error reporting: Spring Data JPA facilitates fast failure; it avoids the errors that are thrown in the runtime. 

If the property is misspelled, getting a PropertyReferenceException: no property X found for type Y.  

@Query method

When the query method signature becomes over-complicated, it may be using @Query decorated on the top of a repository method; it could be a JPQL query or native SQL query.  Queries declared by @Query on the top of repository interface methods, take precedence over the named queries.

By default, JPA using position-based parameter binding.  Using named parameter in the query, rather than parameter positioning, ': name' ref. by '@Param("name")', may make the query less error-prone. 

DSL(dynamic query)

sort can be done providing sort or pageable.

Sort sort = new Sort(Sort.Direction.Asc, "title);
bookRepository.findByTitleContains("Hibernate",sort)

Limit the number of results.
findFirst5By

Pagination

Pageable

PageRequestOf.

Projection Types

Instead of using an Entity projection, returning the whole of aggregate root and/or its dependencies; projections may limiting the amount of returned attributes from a database query to what we need; it may optimize the underlying generated SQL query, reducing the query operations on the database and therefore improve the DB performance. 

Interface-based projection

Spring relies on the interface to create a proxy to wrap an entity so as to modify the entity behaviors.

closed projection

a closed projection means a method name exactly matches an attribute name.

a constrain: the closed projection interface is only used as an element type of a collection.

a closed project may carry out a nested projection, but it must root on the owner side, otherwise, on the inverse side, it doesn't have a reference to the nested entity.

open projection

An open projection decorated with SpEL enables us to define interface methods with unmatched names and with return values re-computed at a runtime.

drawback: its query is created during the runtime, so Spring cannot optimize the query in the advance.

Class-based projection

instead of defining interfaces and allowing Spring to create proxy around them, we may create our own classes to project from the root entity via the repository.

a constraint: the class overrides hashcode and equal(the class may be handled in a collection); constructor parameter name must be the same as the counterparts declared in the root entity.

Dynamic projection

a root entity is queried through a repository, it may have different views; the dynamic projection offers a genric way to combine the root entity and its views in one query method.

<T>  List<T> findByLastName(String lastName, Class<T> class); 

Reference

Spring Data JPA Tutorial: Creating Database Queries From Method Names

@Query Annotation in Spring Data JPA