📚 Bài viết thuộc series Java Backend Zero to Hello 📌 Phần: Phase 4: Database & SQL | Bài 44/86

---

BÀI 4.9: QUERY NÂNG CAO TRONG JPA

Mục tiêu

• Sử dụng JPQL nâng cao
• Sử dụng Criteria API
• Native Query
• Pagination và Sorting

---

1. JPQL NÂNG CAO

1.1 JOIN FETCH

Tránh N+1 problem.

// ❌ N+1 problem
List<User> users = em.createQuery(
    "SELECT u FROM User u", User.class).getResultList();
for (User u : users) {
    System.out.println(u.getOrders().size());  // Query riêng cho mỗi user
}

// ✅ JOIN FETCH
List<User> users = em.createQuery(
    "SELECT DISTINCT u FROM User u JOIN FETCH u.orders", User.class)
    .getResultList();

1.2 Constructor Expression

"SELECT new com.example.dto.UserDTO(u.name, u.email) FROM User u"

1.3 Aggregate Functions

"SELECT COUNT(u) FROM User u"
"SELECT AVG(u.age) FROM User u"
"SELECT MAX(o.total), MIN(o.total) FROM Order o"

1.4 Subquery

"SELECT u FROM User u WHERE u.id IN " +
"(SELECT o.user.id FROM Order o WHERE o.total > 1000)"

1.5 EXISTS

"SELECT u FROM User u WHERE EXISTS " +
"(SELECT 1 FROM Order o WHERE o.user = u AND o.total > 1000)"

1.6 CASE WHEN

"SELECT u.name, " +
"CASE WHEN u.age >= 18 THEN 'Adult' ELSE 'Minor' END " +
"FROM User u"

---

2. CRITERIA API

Type-safe query, xây dựng bằng Java code.

2.1 Cú pháp

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);

cq.select(root).where(cb.equal(root.get("email"), "an@example.com"));

List<User> users = em.createQuery(cq).getResultList();

2.2 Với Predicate

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);

List<Predicate> predicates = new ArrayList<>();

if (name != null) {
    predicates.add(cb.like(root.get("name"), "%" + name + "%"));
}
if (minAge != null) {
    predicates.add(cb.ge(root.get("age"), minAge));
}

cq.where(predicates.toArray(new Predicate[0]));
cq.orderBy(cb.desc(root.get("createdAt")));

List<User> users = em.createQuery(cq).getResultList();

2.3 JOIN

CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);
Join<User, Order> orderJoin = root.join("orders", JoinType.INNER);

cq.select(root).where(cb.gt(orderJoin.get("total"), 1000));

---

3. NATIVE QUERY

SQL thuần, dùng khi JPQL không đủ.

3.1 Cú pháp

@Query(value = "SELECT * FROM users WHERE email = ?1", nativeQuery = true)
User findByEmailNative(String email);

3.2 Native với Entity mapping

@Query(value = "SELECT * FROM users u WHERE u.age > :age",
       nativeQuery = true)
List<User> findByAgeGreaterThan(@Param("age") int age);

3.3 Native với DTO

@Query(value = "SELECT name, email FROM users WHERE age > :age",
       nativeQuery = true)
List<Object[]> findUserInfoByAge(@Param("age") int age);

// Hoặc dùng interface projection
public interface UserInfo {
    String getName();
    String getEmail();
}

@Query(value = "SELECT name, email FROM users WHERE age > :age",
       nativeQuery = true)
List<UserInfo> findUserInfo(@Param("age") int age);

---

4. PAGINATION

4.1 Với EntityManager

TypedQuery<User> query = em.createQuery("SELECT u FROM User u", User.class);
query.setFirstResult(0);   // Offset
query.setMaxResults(10);   // Limit

List<User> users = query.getResultList();

4.2 Với Spring Data JPA

Page<User> findByAgeGreaterThan(int age, Pageable pageable);

// Sử dụng
Pageable pageable = PageRequest.of(0, 10, Sort.by("name").ascending());
Page<User> page = userRepository.findByAgeGreaterThan(18, pageable);

List<User> content = page.getContent();
int totalPages = page.getTotalPages();
long totalElements = page.getTotalElements();

---

5. SORTING

5.1 JPQL

"SELECT u FROM User u ORDER BY u.name ASC, u.age DESC"

5.2 Với Sort

Sort sort = Sort.by(Sort.Direction.DESC, "createdAt");
Pageable pageable = PageRequest.of(0, 10, sort);

5.3 Multiple Sort

Sort sort = Sort.by(
    Sort.Order.asc("lastName"),
    Sort.Order.desc("firstName")
);

---

6. DYNAMIC QUERY

6.1 Với Specification (Spring Data)

public class UserSpecification {
    public static Specification<User> hasName(String name) {
        return (root, query, cb) ->
            name == null ? null : cb.like(root.get("name"), "%" + name + "%");
    }

    public static Specification<User> hasMinAge(Integer age) {
        return (root, query, cb) ->
            age == null ? null : cb.greaterThanOrEqualTo(root.get("age"), age);
    }
}

// Sử dụng
Specification<User> spec = Specification.where(hasName("An"))
    .and(hasMinAge(18));
List<User> users = userRepository.findAll(spec);

6.2 Với QueryDSL

QUser user = QUser.user;

List<User> users = queryFactory.selectFrom(user)
    .where(user.name.contains("An")
        .and(user.age.goe(18)))
    .orderBy(user.createdAt.desc())
    .fetch();

---

7. BATCH OPERATIONS

7.1 Batch Insert/Update

@PersistenceContext
EntityManager em;

@Transactional
public void batchInsert(List<User> users) {
    for (int i = 0; i < users.size(); i++) {
        em.persist(users.get(i));
        if (i % 50 == 0) {
            em.flush();
            em.clear();
        }
    }
}

7.2 Batch với JPQL

@Modifying
@Query("UPDATE User u SET u.status = :status WHERE u.lastLogin < :date")
int updateStatus(@Param("status") String status, @Param("date") Date date);

---

8. PROJECTIONS

8.1 Interface Projection

public interface UserSummary {
    String getName();
    String getEmail();
}

List<UserSummary> findBy();

8.2 DTO Projection

public record UserDTO(String name, String email) {}

@Query("SELECT new com.example.dto.UserDTO(u.name, u.email) FROM User u")
List<UserDTO> findAllDTO();

---

9. BÀI TẬP THỰC HÀNH

Bài 1: JPQL Queries

Viết các query:

• Tìm user có nhiều orders nhất
• Tính tổng giá trị orders theo user
• Tìm sản phẩm bán chạy nhất

Bài 2: Criteria API

Xây dựng dynamic query với nhiều điều kiện.

Bài 3: Pagination

Implement pagination cho danh sách user.

---

10. TÓM TẮT

Khái niệm	Mô tả
JPQL	Query language cho JPA
Criteria API	Type-safe query
Native Query	SQL thuần
JOIN FETCH	Tránh N+1
Pagination	Phân trang
Specification	Dynamic query
Projection	Chọn field cần thiết

---

Bài tiếp theo: 4.10 Transaction & Locking

---

🧭 Điều Hướng Series

⬅️ Bài trước: BÀI 4.8: QUAN HỆ TRONG JPA

📋 Lộ trình tổng quan: Xem Toàn Bộ Series

➡️ Bài tiếp theo: BÀI 4.10: TRANSACTION & LOCKING