Native query & SQLExpression

Bên cạnh Specification cho JPQL, govex-cloud-data-jpa cung cấp SQLExpression — API tương tự để xây dựng truy vấn native SQL động, an toàn và dễ bảo trì. Phù hợp cho các truy vấn SQL phức tạp mà JPQL không đáp ứng được: hàm đặc thù của database, JOIN không theo quan hệ JPA, chọn/ẩn cột động hoặc UPDATE theo điều kiện động.

Khi nào sử dụng

Truy vấn native cần điều kiện động theo form tìm kiếm nhưng không muốn nối chuỗi SQL thủ công.
Cần dùng hàm/syntax riêng của database (window function, date_part, SUM + GROUP BY...).
Cần viết lại câu SELECT (ẩn cột nhạy cảm, thêm cột) hoặc UPDATE SET động ngay trên câu SQL do Hibernate sinh ra.
Cần JOIN native với điều kiện ON tự khai báo (SQLPath).
Cần ánh xạ kết quả native về VO theo alias snake_case.

Nếu bài toán chỉ dùng JPQL và quan hệ entity sẵn có, ưu tiên Truy vấn JPA nâng cao (Specification) để giữ type-safety.

Cài đặt

Dùng chung dependency với module JPA:

<dependency>
    <groupId>vn.govex.cloud</groupId>
    <artifactId>govex-cloud-data-jpa</artifactId>
</dependency>

Version quản lý qua BOM — xem Cài đặt. Để dùng BaseRepository.findAll(SQLExpression) cần bật @EnableExtendJpaRepositories như mô tả trong Truy vấn JPA nâng cao.

Native query trong repository

`@JpaNativeQuery` — native query trả về VO

@JpaNativeQuery là meta-annotation của @NativeQuery, bổ sung resultTransformer với mặc định UnderscoreTransformer: alias snake_case trong câu SQL được chuyển thành camelCase rồi gán vào property của VO.

public interface UserRepository extends BaseRepository<User, String> {

    @JpaNativeQuery(
        value = "select u.id as user_id, u.full_name as full_name, u.status as status " +
                "from users u where u.status = 'ACTIVE'",
        countQuery = "select count(*) from users u where u.status = 'ACTIVE'")
    Page<UserVO> findActiveUsers(Pageable pageable);
}

VO chỉ cần các field userId, fullName, status (constructor không tham số); framework tự tạo instance và set property qua BeanWrapper.

Các thuộc tính của @JpaNativeQuery:

Thuộc tính	Mô tả
`value`	Câu truy vấn native
`countQuery`	Câu đếm riêng cho phân trang; để trống thì suy ra từ câu gốc hoặc `countProjection`
`countProjection`	Phần projection dùng để sinh câu đếm
`name` / `countName`	Tên named query (mặc định `${domainClass}.${methodName}`)
`queryRewriter`	`QueryRewriter` áp dụng sau khi lắp ráp câu truy vấn
`sqlResultSetMapping`	Tên `@SqlResultSetMapping` áp dụng cho truy vấn
`resultTransformer`	Class kế thừa `AbstractTupleTransformer`; mặc định `UnderscoreTransformer`

`@Query` native kèm tham số `SQLExpression`

Câu native khai báo bằng @Query(nativeQuery = true) có thể nhận tham số SQLExpression/UpdateSQLExpression:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = "SELECT * FROM users u WHERE u.deleted_date IS NULL",
           nativeQuery = true)
    List<User> findUsersByFilter(SQLExpression<User> expression);

    @Query(value = "UPDATE users SET name = :name WHERE deleted = 0",
           nativeQuery = true)
    int updateUsers(String name, UpdateSQLExpression<User> expression);
}

Khi gọi, tham số expression được module tự thiết lập vào context cho luồng hiện tại, Hibernate StatementInspector viết lại câu SQL theo biểu thức rồi mới thực thi. Không cần marker hay placeholder trong câu query:

QuerySQLExpression<User> expr = SQLExpressions.where()
    .like("email", email)
    .in("status", statuses);

List<User> users = userRepository.findUsersByFilter(expr);

UpdateSQLExpression<User> updateExpr = SQLExpressions.whereUpdate()
    .set("status", "INACTIVE")
    .set("lastModifiedDate", new Date())
    .equal("id", userId);

int updatedCount = userRepository.updateUsers("test", updateExpr);

Tạo SQLExpression

Khởi tạo từ lớp tiện ích SQLExpressions:

import vn.govex.cloud.data.jpa.expression.SQLExpressions;

QuerySQLExpression<Entity> expr = SQLExpressions.where();          // điều kiện theo tên cột
TypedSQLExpression<Entity> typedExpr = SQLExpressions.whereTyped(); // theo method reference
UpdateSQLExpression<Entity> updateExpr = SQLExpressions.whereUpdate();

QuerySQLExpression<Entity> beanExpr = SQLExpressions.ofBean(searchRequest);
TypedSQLExpression<Entity> typedBeanExpr = SQLExpressions.ofBeanTyped(searchRequest);

API	Trả về	Dùng khi
`where()`	`QuerySQLExpression<T>`	Điều kiện theo tên cột dạng chuỗi
`where(SQLExpression<T>)`	`QuerySQLExpression<T>`	Dựng từ biểu thức có sẵn
`whereTyped()` / `whereTyped(Class<T>)`	`TypedSQLExpression<T>`	Method reference
`whereUpdate()` / `whereUpdate(SQLExpression<T>)`	`UpdateSQLExpression<T>`	Câu UPDATE động
`ofBean(bean)` / `ofBean(bean, paths)`	`QuerySQLExpression<T>`	Dựng từ DTO annotation
`ofBeanTyped(bean)` / `ofBeanTyped(bean, paths)`	`TypedSQLExpression<T>`	Dựng từ DTO annotation, type-safe

Cơ chế tự động bỏ qua giá trị null/rỗng giống Specification: phương thức không có tham số match chỉ thêm điều kiện khi giá trị khác null và không rỗng.

expr.equal("username", username); // chỉ thêm khi username có giá trị
expr.like("email", email);        // chỉ thêm khi email có giá trị
expr.in("status", statusList);    // chỉ thêm khi statusList khác null và không rỗng

Các loại SQLExpression

QuerySQLExpression: điều kiện theo tên cột dạng chuỗi, dùng cho SELECT.
TypedSQLExpression: điều kiện theo method reference (an toàn kiểu), chuyển từ dạng chuỗi bằng typed().
UpdateSQLExpression: câu UPDATE — thiết lập giá trị cột qua set/setNull.

TypedSQLExpression<User> typedExpr = SQLExpressions.where()
    .equal("username", "admin")
    .typed();

Điều kiện truy vấn

SQLExpression hỗ trợ đầy đủ nhóm toán tử tương tự Specification, mỗi toán tử có phiên bản or*:

equal / orEqual, notEqual / orNotEqual
like / orLike, notLike / orNotLike, startsWith / orStartsWith, endsWith / orEndsWith
greatThan / orGreatThan, greatThanOrEqualTo / orGreatThanOrEqualTo
lessThan / orLessThan, lessThanOrEqualTo / orLessThanOrEqualTo
between / orBetween, notBetween / orNotBetween
in / orIn, notIn / orNotIn
isNull / orIsNull, isNotNull / orIsNotNull

Riêng nhóm LIKE/EQUAL có thêm tham số ignoreCase; các phương thức không truyền match tự kiểm tra giá trị rỗng.

Nhúng mệnh đề SQL thô khi cần hàm đặc thù:

// Thêm điều kiện SQL tùy chỉnh
expr.sql("price * quantity > 1000");

// Thêm điều kiện SQL tùy chỉnh có điều kiện
expr.sql(hasMinimumPurchase, "price * quantity > 1000");

// Thêm điều kiện SQL tùy chỉnh với OR
expr.orSql("date_part('year', order_date) = 2023");

Áp dụng điều kiện theo alias / tên bảng

Khi câu SQL có nhiều bảng, giới hạn điều kiện theo alias hoặc tên bảng cụ thể:

// Theo alias
expr.andFor("u", subExpr -> subExpr
    .like("email", "@company.com")
    .greatThan("age", 18));
expr.orFor("o", subExpr -> subExpr.equal("role", "ADMIN"));
expr.sqlFor("d", "name LIKE '%Department%'");
expr.orSqlFor("o", "order_date > '2023-01-01'");

// Theo tên bảng
expr.andForTable("users", subExpr -> subExpr
    .equal("status", "ACTIVE")
    .isNotNull("email"));
expr.orForTable("orders", subExpr -> subExpr.greatThan("total", 1000));
expr.sqlForTable("products", "price > 100");
expr.orSqlForTable("orders", "total_amount > 1000");

Path, Paths và SQLPath

Path/Paths dùng để xác định chính xác bảng/alias cho điều kiện:

Path usernamePath = Path.of("username").entityAlias("u");
Path fromGetter = Path.of(User::getStatus); // kèm entityType để resolve tên cột

expr.equal(usernamePath, "admin")
    .like(fromGetter, "ACTIVE");

Paths paths = Paths.field("username")
    .entityAlias("u")
    .field("department")
    .entityAlias("d")
    .build();

expr.equal(paths.get("username"), "admin")
    .like(paths.get("department"), "IT");

SQLPath mở rộng Path, định nghĩa JOIN native. Điều kiện JOIN được truyền trực tiếp dưới dạng chuỗi SQL, tên method không có hậu tố On:

// LEFT JOIN: alias/tên bảng + điều kiện
SQLPath.of(User::getDepartment)
    .leftJoin("dept", "dept.id = u.department_id AND dept.deleted = false");

// INNER JOIN và RIGHT JOIN dùng cùng dạng tham số
SQLPath.of(User::getRole)
    .join("role", "role.id = u.role_id")
    .rightJoin("org", "org.id = u.org_id");

Method	Mô tả
`leftJoin(aliasOrTable, condition)`	LEFT JOIN theo alias/tên bảng với điều kiện
`join(aliasOrTable, condition)`	INNER JOIN
`rightJoin(aliasOrTable, condition)`	RIGHT JOIN
`joinPath(condition)`	Gán điều kiện JOIN trực tiếp

ghi chú

Phân biệt với JPAPath (dùng cho Specification/JPQL): method JOIN của JPAPath có hậu tố On và nhận điều kiện là Specification<?>, còn SQLPath truyền điều kiện JOIN trực tiếp dưới dạng chuỗi SQL.

Chọn cột, GROUP BY, ORDER BY

QuerySQLExpression<User> queryExpr = SQLExpressions.where()
    .select("id", "username", "email")
    .equal("status", "ACTIVE");

QuerySQLExpression<Order> expr = SQLExpressions.where()
    .select("customerId", "SUM(totalAmount)")
    .groupBy("customerId")
    .orderByDesc("SUM(totalAmount)")
    .greatThan("orderDate", startDate);

Viết lại SELECT động

Ẩn cột nhạy cảm: cột bị ignore được thay bằng NULL giữ nguyên alias.

expr.select(Path.of("password").ignore(true)); // password → NULL AS password

Thêm cột mới với alias:

expr.select(Path.of("fullName").entityType(User.class).alias("ten"));

Viết lại UPDATE động

UpdateSQLExpression cho phép thiết lập giá trị cột động:

UpdateSQLExpression<User> updateExpr = SQLExpressions.whereUpdate()
    .set("status", "ACTIVE")
    .set("lastModifiedDate", new Date())
    .setNull("deletedDate")
    .equal("id", userId);

Có thể khai báo set bằng Path kèm entityType để module resolve đúng tên bảng/cột khi câu UPDATE do Hibernate sinh ra:

UpdateSQLExpression<User> updateExpr = SQLExpressions.whereUpdate()
    .set(Path.of("updatedAt").entityType(User.class), LocalDateTime.now())
    .set(Path.of("status").entityType(User.class), "INACTIVE");

Cơ chế hoạt động

SQLExpressionContext (ThreadLocal) lưu biểu thức của luồng hiện tại, gồm biểu thức cố định và biểu thức hiện hành (có thể lồng nhau, kết hợp bằng AND).
Biểu thức được thiết lập tự động:
- BaseRepository.findAll(SQLExpression) thiết lập context trong lúc thực thi rồi khôi phục trạng thái cũ.
- Query method có tham số SQLExpression/UpdateSQLExpression được module phát hiện và thiết lập context cho từng lời gọi.
RewriteStatementInspector (Hibernate StatementInspector) nhận mọi câu SQL, parse bằng JSqlParser (SQLStatementVisitor) và chuyển cho SQLSelectRewriter/SQLDeleteRewriter/SQLUpdateRewriter.
Rewriter inject điều kiện vào WHERE, thay select item và ghi đè cột trong mệnh đề UPDATE SET; alias/tên bảng dùng để target điều kiện đúng entity.
TableMetadata quét toàn bộ JPA entity khi EntityManagerFactory khởi tạo: map Class ↔ tên bảng, tên field ↔ tên cột (từ @Column hoặc chuyển camelCase → snake_case) và phát hiện cột @Id.

Best practices

Ưu tiên TypedSQLExpression khi có thể để an toàn kiểu dữ liệu.
Tận dụng cơ chế tự bỏ qua null/rỗng cho form tìm kiếm.
Chỉ định rõ entityAlias/tên bảng khi truy vấn nhiều bảng.
Dùng annotation trên DTO cho điều kiện cố định, fluent API cho điều kiện động.
Dùng sql()/sqlFor() chỉ cho trường hợp thật sự đặc thù (hàm SQL riêng) — không nối chuỗi từ dữ liệu người dùng.
Chia nhỏ biểu thức phức tạp thành các biểu thức con dễ đọc.

So sánh Specification và SQLExpression

Tính năng	Specification	SQLExpression
Loại truy vấn	JPQL	Native SQL
Hiệu năng	Tốt cho hầu hết trường hợp	Tối ưu cho truy vấn phức tạp
Khả năng tùy biến	Giới hạn bởi JPQL	Đầy đủ khả năng SQL
Cấu trúc API	Fluent API	Fluent API
Hỗ trợ annotation DTO	Có	Có
Hỗ trợ `Path`	Có	Có (thêm `SQLPath`)
Dynamic UPDATE SET	Không	Có
EXISTS/NOT EXISTS subquery	Có	Không
So sánh cột-với-cột	Có	Không
Ánh xạ VO	`PropertyTupleTransformer`	`UnderscoreTransformer`
Target theo alias/tên bảng	Qua `JPAPath` khi JOIN	Có (`andFor`, `andForTable`...)

Lưu ý

Không cần khai báo marker trong câu query: module tự parse câu SQL mà Hibernate phát ra và viết lại.
Giá trị truyền vào SQLExpression được render thành literal trong câu SQL viết lại (chuỗi được escape nháy đơn), không phải bind parameter — tuyệt đối không đưa dữ liệu người dùng vào sql()/sqlFor*() mà không kiểm soát.
Context là ThreadLocal và được khôi phục sau mỗi lời gọi; không tự set context thủ công trừ trường hợp có kiểm soát.
Khi dùng entityType cho Path, entity phải được JPA quản lý để TableMetadata resolve được tên bảng/cột.
Câu SQL sau khi sinh phải parse được bằng JSqlParser; cú pháp đặc thù chỉ nên nằm trong literal/sql() của điều kiện.
Dependency ghi đè statement inspector của Hibernate; khi ứng dụng đã tự cấu hình, khai báo bean govexJpaHibernatePropertiesCustomizer để kiểm soát property này.
VO của native query cần alias khớp tên property (sau chuyển snake_case → camelCase) và constructor không tham số.

Khi nào sử dụng​

Cài đặt​

Native query trong repository​

@JpaNativeQuery — native query trả về VO​

@Query native kèm tham số SQLExpression​

Tạo SQLExpression​

Các loại SQLExpression​

Điều kiện truy vấn​

Áp dụng điều kiện theo alias / tên bảng​

Path, Paths và SQLPath​

Chọn cột, GROUP BY, ORDER BY​

Viết lại SELECT động​

Viết lại UPDATE động​

Cơ chế hoạt động​

Best practices​

So sánh Specification và SQLExpression​

Lưu ý​