Hướng dẫn tối ưu hóa Native Query với @Transactional trong Spring Boot để lấy danh sách thông báo

Trong bài viết này, mình sẽ trình bày một cách chi tiết về cách sử dụng Native SQL Query kết hợp với annotation @Transactional trong Spring Boot nhằm lấy danh sách thông báo (notification) từ cơ sở dữ liệu. Chúng ta sẽ cùng phân tích đoạn code thực tế và khám phá những cách tối ưu hóa cho truy vấn SQL này, giúp quá trình truy xuất dữ liệu hiệu quả hơn.

1. Đoạn code gốc

Dưới đây là đoạn code sử dụng Native SQL để lấy danh sách thông báo:

java Copy

@Transactional
public List<NotificationTerm> getNotifications(SearchNotificationDto searchNotificationDto, String userId) throws Exception {
    String sql = "WITH user_notification AS ( " +
                  " SELECT " +
                  " un.notification_id, " +
                  " CASE " +
                  " WHEN un.is_read = 't' THEN 2 " +
                  " ELSE 1 " +
                  " END AS status, " +
                  " un.created_date AS date, " +
                  " un.group_id " +
                  " FROM notification.en_laand_user_notification un " +
                  " WHERE un.user_id = :userId), " +
                  "notification AS ( " +
                  " SELECT " +
                  " n.id AS notification_id, " +
                  " CASE " +
                  " WHEN n.is_global = 't' THEN 1 " +
                  " ELSE 0 " +
                  " END AS status, " +
                  " n.created_date AS date " +
                  " FROM notification.en_laand_notification n " +
                  " WHERE " +
                  " (n.is_deleted = 'f') " +
                  " AND (n.notification_type = :notification_type) " +
                  " AND (n.expired_date IS NULL OR (NOW() BETWEEN n.effective_date AND n.expired_date)) " +
                  " AND (n.is_global = 't' OR n.user_id = :userId) " +
                  " OR EXISTS ( " +
                  " SELECT 1 " +
                  " FROM user_notification un " +
                  " WHERE n.group_id = un.group_id) " +
                  " AND (n.content IS NULL OR n.content LIKE '%page1%') " +
                  " UNION ALL " +
                  " SELECT " +
                  " un.notification_id, " +
                  " un.status, " +
                  " un.date AS date " +
                  " FROM user_notification un) " +
                  "SELECT " +
                  " notification_id, " +
                  " SUM(status) " +
                  " FROM notification " +
                  " GROUP BY notification_id " +
                  " ORDER BY " +
                  " CASE " +
                  " WHEN SUM(status) > 1 THEN 1 " +
                  " ELSE 0 " +
                  " END, " +
                  " MIN(date) " +
                  " LIMIT :limit OFFSET :offset";

    Query query = this.entityManager.createNativeQuery(sql, NotificationTerm.class);

    query.setParameter("userId", userId);
    query.setParameter("notification_type", 3);
    query.setParameter("limit", searchNotificationDto.getSize());
    query.setParameter("offset", searchNotificationDto.getSize() * searchNotificationDto.getPage());

    return query.getResultList();
}

2. Phân tích đoạn code

a. Sử dụng CTE (Common Table Expression)

user_notification: chứa danh sách thông báo của người dùng dựa trên user_id.
notification: truy vấn kết hợp giữa thông báo toàn cầu (is_global = 't') và thông báo cá nhân hoặc của nhóm.

b. Điều kiện WHERE quan trọng

sql Copy

WHERE (n.is_deleted = 'f')
    AND (n.notification_type = :notification_type)
    AND (n.expired_date IS NULL OR (NOW() BETWEEN n.effective_date AND n.expired_date))
    AND (n.is_global = 't' OR n.user_id = :userId)

Điều kiện EXISTS giúp tìm các thông báo nếu group_id của người dùng khớp với group_id của thông báo.

c. Phân trang (LIMIT và OFFSET)

Biến :limit và :offset giúp giới hạn kết quả trả về theo trang và kích thước trang.

3. Những điểm cần tối ưu

a. Sử dụng dấu ngoặc để logic đúng

Khi xây dựng điều kiện chứa AND và OR, cần sử dụng dấu ngoặc để tránh lỗi logic:

sql Copy

AND ((n.is_global = 't' OR n.user_id = :userId)
    OR EXISTS (SELECT 1 FROM user_notification un WHERE n.group_id = un.group_id))

b. Sử dụng JPQL thay vì Native SQL

Khi có thể, hãy sử dụng JPQL với Constructor Expression để mã dễ hiểu hơn và tránh lỗi trong SQL raw:

java Copy

SELECT new NotificationTerm(n.id, n.createdDate, n.status)
FROM Notification n WHERE n.userId = :userId

c. Kiểm tra giá trị phân trang

Kiểm tra giá trị của :limit và :offset trước khi thực hiện truy vấn để tránh lỗi do giá trị không hợp lệ.

Kết luận

Đoạn code trên là một ví dụ rõ ràng về cách sử dụng Native Query cùng @Transactional trong Spring Boot để lấy dữ liệu từ nhiều bảng và thực hiện xử lý dữ liệu phức tạp. Việc áp dụng CTE (Common Table Expression) không chỉ cải thiện khả năng đọc hiểu mà còn nâng cao tính quản lý cho những truy vấn phức tạp. Hãy nhớ tối ưu hóa và kiểm tra điều kiện WHERE để đảm bảo tránh những lỗi không mong muốn trong quá trình phát triển ứng dụng.

Hướng dẫn tối ưu hóa Native Query với @Transactional trong Spring Boot để lấy danh sách thông báo hiệu quả