Spring Data JPA @Query | Code Factory

1. Overview

2. Select Query

2.1. JPQL

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

2.2. Native

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

3. Define Order in a Query

3.1. Sorting for JPA Provided and Derived Methods

userRepository.findAll(new Sort(Sort.Direction.ASC, "name"));
userRepository.findAll(new Sort("LENGTH(name)"));

3.2. JPQL

@Query(value = "SELECT u FROM User u")
List<User> findAllUsers(Sort sort);
userRepository.findAllUsers(new Sort("name"));
userRepository.findAllUsers(JpaSort.unsafe("LENGTH(name)"));
new Sort("LENGTH(name)");

3.3. Native

4. Pagination

4.1. JPQL

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

4.2. Native

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

4.3. Spring Data JPA Versions Prior to 2.0.4

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

5. Indexed Query Parameters

5.1. JPQL

@Query("SELECT u FROM User u WHERE u.status = ?1")
User findUserByStatus(Integer status);

@Query("SELECT u FROM User u WHERE u.status = ?1 and u.name = ?2")
User findUserByStatusAndName(Integer status, String name);

5.2. Native

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

6. Named Parameters

6.1. JPQL

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

6.2. Native

@Query(value = "SELECT * FROM Users u WHERE u.status = :status and u.name = :name", 
nativeQuery = true)
User findUserByStatusAndNameNamedParamsNative(
@Param("status") Integer status, @Param("name") String name);

7. Collection Parameter

SELECT u FROM User u WHERE u.name IN :names
@Query(value = "SELECT u FROM User u WHERE u.name IN :names")
List<User> findUserByNameList(@Param("names") Collection<String> names);

8. Update Queries with @Modifying

8.1. JPQL

@Modifying
@Query("update User u set u.status = :status where u.name = :name")
int updateUserSetStatusForName(@Param("status") Integer status,
@Param("name") String name);

8.2. Native

@Modifying
@Query(value = "update Users u set u.status = ? where u.name = ?",
nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

8.3. Inserts

@Modifying
@Query(value = "insert into Users (name, age, email, status) values (:name, :age, :email, :status)",
nativeQuery = true)
void insertUser(@Param("name") String name, @Param("age") Integer age,
@Param("status") Integer status, @Param("email") String email);

9. Dynamic Query

9.1. Example of a Dynamic Query

SELECT u FROM User u WHERE u.email LIKE '%email1%'
or u.email LIKE '%email2%'
...
or u.email LIKE '%emailn%'

9.2. Custom Repositories and the JPA Criteria API

public interface UserRepositoryCustom {
List<User> findUserByEmails(Set<String> emails);
}
public class UserRepositoryCustomImpl implements UserRepositoryCustom {

@PersistenceContext
private EntityManager entityManager;

@Override
public List<User> findUserByEmails(Set<String> emails) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> user = query.from(User.class);

Path<String> emailPath = user.get("email");

List<Predicate> predicates = new ArrayList<>();
for (String email : emails) {
predicates.add(cb.like(emailPath, email));
}
query.select(user)
.where(cb.or(predicates.toArray(new Predicate[predicates.size()])));

return entityManager.createQuery(query)
.getResultList();
}
}

9.3. Extending the Existing Repository

public interface UserRepository extends JpaRepository<User, Integer>, UserRepositoryCustom {
// query methods from section 2 - section 7
}

9.4. Using the Repository

Set<String> emails = new HashSet<>();
// filling the set with any number of items

userRepository.findUserByEmails(emails);

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Saving Private Instances

Creating an AWS Custom VPC.

CSS Beer Glass with animations

Advantages and Disadvantages of Laravel

The Basics: Javascript VS Ruby

Precise footage of Vince’s bot farm at perform

What is CI CD Pipeline? Why it is Important to DevOps Professionals?

CS 373 Fall — Kevin Li

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Code Factory

Code Factory

More from Medium

Updating POSTGRESQL sequences to max ID

Learn to Access Java Database With Jakarta Data

TIL 0510 Java Lambda

2 Ways to Upload Files to Amazon S3 In Your Spring Boot Project