home
Spring Data cheat sheet
Spring Data, accès aux données sans implémentation
print

Create query from properties

Create your interface implementing the CrudRepository :

public interface PersonRepository extends CrudRepository<User, Long>

Create a "find by" query :

List<Person> findByLastname(String lastname);

Using several class attributes :

List<Person> findByEmailAddressAndLastname(EmailAddress emailAddress, String lastname);

Using several properties and distinct:

List<Person> findDistinctPeopleByLastnameOrFirstname(String lastname, String firstname);

Ignoring case for all properties :

List<Person> findByLastnameAndFirstnameAllIgnoreCase(String lastname, String firstname);

Using ORDER BY for a query

List<Person> findByLastnameOrderByFirstnameAsc(String lastname);

Counting :

Long countByLastname(String lastname);

Deleting by property :

Long deleteByLastname(String lastname);

Using property expressions (zipCode attribute in address object) :

List<Person> findByAddress_ZipCode(ZipCode zipCode);

Paging and sorting requests

Finding data using paging

List<User> findByLastname(String lastname, Pageable pageable);

Sorting data

List<User> findByLastname(String lastname, Sort sort);

Paging first 10 by property

Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);

Supported Keywords

The supported keywords are: And, Or, Is, Equals, Between, LessThan, LessThanEqual, GreaterThan, GreaterThanEqual, findByAgeGreaterThanEqual, After, Before, IsNull, IsNotNull, NotNull, Like, NotLike, StartingWith, EndingWith, Containing, OrderBy, Not, In, NotIn, True, False, IgnoreCase

Predicates

Implements QueryDslPredicateExecutor:

interface UserRepository extends CrudRepository<User, Long>, QueryDslPredicateExecutor<User>

Working with predicates:

Predicate predicate = user.firstname.equalsIgnoreCase("dave")
.and(user.lastname.startsWithIgnoreCase("mathews"));
userRepository.findAll(predicate);

Using @Query

Implements the JPA repository

public interface UserRepository extends JpaRepository<User, Long>

Named queries in an interface

@Query("select u from User u where u.firstname like %?1")
List<User> findByFirstnameEndsWith(String firstname);

Streaming the result of a query with java 8

@Query("select u from User u")
Stream<User> findAllByCustomQueryAndStream();

Native queries

Native queries can be executed using an EntityManager

@PersistenceContext private EntityManager em;
...
Query q = em.createNativeQuery("SELECT a.id, a.version, a.firstname, a.lastname FROM Author a", Author.class);
List<Author> authors = q.getResultList();

Native queries using @Query

@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",nativeQuery = true)Page<User> findByLastname(String lastname, Pageable pageable);