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);