Spring JPA - newgeekorder/TechWiki GitHub Wiki

Query

Query, written in Java Persistence Query Language (JPQL) syntax.

@Query("SELECT u FROM User u WHERE u.status = 1")
Collection<User> findAllActiveUsers();

Native Queries

Writing queries in native sql

@Query(
  value = "SELECT * FROM USERS u WHERE u.status = 1", 
  nativeQuery = true)
Collection<User> findAllActiveUsersNative();

JPQL - Order and Pagination

Pagination

Pass the pagination object to a query

@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> findAllUsersWithPagination(Pageable pageable);

Pagination and native is also possible

@Query(
  value = "SELECT * FROM Users ORDER BY id", 
  countQuery = "SELECT count(*) FROM Users", 
  nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

Passing Parameters

Parameters can be passed numbered or named

Numbered

@Query(
  value = "SELECT * FROM Users u WHERE u.status = ?1", 
  nativeQuery = true)
User findUserByStatusNative(Integer status);

Named parameters

@Query("SELECT u FROM User u WHERE u.status = :status and u.name = :name")
User findUserByStatusAndNameNamedParams(
  @Param("status") Integer status, 
  @Param("name") String name);