Đã đăng vào Chủ Nhật, 12:38 SA 6 phút đọc

Apache Derby Bài 33: Stored Procedure và User-Defined Functions (UDF) viết bằng Java

1. Sự khác biệt cốt lõi của Apache Derby

Trong các hệ quản trị CSDL như Oracle, SQL Server hay MySQL, khi muốn viết một hàm (Function) hay một thủ tục (Stored Procedure) chạy trực tiếp trên máy chủ, bạn phải học một ngôn ngữ kịch bản riêng như PL/SQL hoặc T-SQL.

Apache Derby đi theo một triết lý hoàn toàn khác: Sử dụng chính Java làm ngôn ngữ kịch bản.

Vì Derby được viết bằng Java và chạy trên JVM, nó cho phép bạn nhúng trực tiếp các phương thức (method) Java tĩnh (static) vào trong CSDL và gọi chúng từ các câu lệnh SQL. Điều này mang lại sức mạnh tính toán vô tận, cho phép bạn tận dụng toàn bộ hệ sinh thái thư viện của Java (ví dụ: mã hóa chuỗi, gọi API bên ngoài, xử lý regex phức tạp) ngay bên trong câu lệnh SELECT hoặc CALL.

2. User-Defined Functions (UDF) - Hàm tự định nghĩa

UDF là một hàm nhận vào các tham số và trả về một giá trị duy nhất (hoặc một bảng, nhưng cơ bản nhất là giá trị vô hướng - scalar). Bạn có thể gọi UDF ở bất cứ đâu biểu thức SQL được phép xuất hiện (trong SELECT, WHERE, CHECK constraint).

Bước 1: Viết phương thức Java

Quy tắc bắt buộc: Phương thức phải là public static và các kiểu dữ liệu tham số/trả về phải tương thích với kiểu SQL của Derby.

Tạo một class Java đơn giản để tính thuế VAT và định dạng mã:

Java

package com.hieu.derby.routines;

public class DerbyUDFs {
    
    // Tính thuế VAT (Ví dụ: 8% hoặc 10%)
    public static double tinhThueVAT(double giaTien, double phanTram) {
        if (giaTien <= 0) return 0;
        return giaTien * (phanTram / 100.0);
    }
    
    // Ẩn một phần email (Ví dụ: hieu***@gmail.com)
    public static String cheGiauEmail(String email) {
        if (email == null || !email.contains("@")) return email;
        String[] parts = email.split("@");
        String name = parts[0];
        if (name.length() <= 2) return "***@" + parts[1];
        return name.substring(0, 2) + "***@" + parts[1];
    }
}

Bước 2: Đăng ký hàm trong Derby bằng SQL

Sau khi biên dịch đoạn mã trên, bạn mở kết nối tới Derby và chạy lệnh CREATE FUNCTION để map (ánh xạ) phương thức Java vào một hàm SQL.

SQL

-- Đăng ký hàm tính thuế
CREATE FUNCTION TINH_VAT (GIA DOUBLE, PHAN_TRAM DOUBLE)
RETURNS DOUBLE
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO SQL
EXTERNAL NAME 'com.hieu.derby.routines.DerbyUDFs.tinhThueVAT';

-- Đăng ký hàm che giấu email
CREATE FUNCTION CHE_GIAU_EMAIL (EMAIL VARCHAR(255))
RETURNS VARCHAR(255)
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO SQL
EXTERNAL NAME 'com.hieu.derby.routines.DerbyUDFs.cheGiauEmail';

(Lưu ý: Từ khóa NO SQL báo cho trình tối ưu hóa biết hàm này chỉ tính toán logic, không thực thi các câu lệnh thao tác cơ sở dữ liệu bên trong).

Bước 3: Sử dụng hàm trong truy vấn

Bây giờ, các hàm này đã trở thành một phần của CSDL, bạn có thể gọi chúng tự nhiên như các hàm tích hợp sẵn của SQL:

SQL

SELECT ten_san_pham, gia_ban, TINH_VAT(gia_ban, 10.0) AS tien_thue 
FROM san_pham;

SELECT ho_ten, CHE_GIAU_EMAIL(email) AS email_bao_mat 
FROM nhan_vien;

3. Stored Procedures (Thủ tục lưu trữ)

Khác với UDF (trả về 1 giá trị), Stored Procedure dùng để thực thi một chuỗi các tác vụ nghiệp vụ phức tạp (INSERT, UPDATE, điều khiển Transaction) hoặc trả về nhiều tập kết quả (Result Sets) cùng lúc. Stored Procedure không dùng trong câu SELECT mà phải chạy bằng lệnh CALL.

Khái niệm `jdbc:default:connection`

Khi một mã Java chạy bên trong Derby dưới dạng Procedure, nó cần thao tác với dữ liệu. Để không phải tạo một kết nối mạng mới, Java cung cấp một URL đặc biệt: jdbc:default:connection. Kết nối này gắn liền với chính luồng Transaction của người đang gọi Procedure.

Bước 1: Viết phương thức Java cho Procedure

Ví dụ: Viết một thủ tục nhận vào ID khách hàng, tăng điểm tích lũy, đồng thời trả về danh sách các đơn hàng gần nhất của họ thông qua tham số mảng ResultSet[].

Java

package com.hieu.derby.routines;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DerbyProcedures {

    public static void tangDiemVaLayDonHang(int maKhach, int diemCong, ResultSet[] outData) 
            throws SQLException {
            
        // Sử dụng kết nối ngầm định của Derby
        Connection conn = DriverManager.getConnection("jdbc:default:connection");
        
        // 1. Thực thi UPDATE
        String updateSql = "UPDATE khach_hang SET diem_thuong = diem_thuong + ? WHERE id = ?";
        try (PreparedStatement psUpdate = conn.prepareStatement(updateSql)) {
            psUpdate.setInt(1, diemCong);
            psUpdate.setInt(2, maKhach);
            psUpdate.executeUpdate();
        }
        
        // 2. Trả về ResultSet (Không đóng Connection và ResultSet này)
        String selectSql = "SELECT ma_don, tong_tien FROM don_hang WHERE ma_khach = ? ORDER BY ngay_tao DESC";
        PreparedStatement psSelect = conn.prepareStatement(selectSql);
        psSelect.setInt(1, maKhach);
        
        // Gán kết quả vào mảng output để trả về cho Client
        outData[0] = psSelect.executeQuery();
    }
}

Bước 2: Đăng ký Procedure trong Derby

SQL

CREATE PROCEDURE TANG_DIEM_KHACH_HANG (IN MA_KHACH INT, IN DIEM_CONG INT)
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA
DYNAMIC RESULT SETS 1
EXTERNAL NAME 'com.hieu.derby.routines.DerbyProcedures.tangDiemVaLayDonHang';

(Từ khóa MODIFIES SQL DATA xác nhận thủ tục này có quyền thay đổi dữ liệu; DYNAMIC RESULT SETS 1 khai báo thủ tục này sẽ trả về 1 bảng kết quả).

Bước 3: Gọi Procedure từ Java Client

Trong mã nguồn ứng dụng (Client), bạn dùng CallableStatement để gọi thủ tục này:

Java

CallableStatement cstmt = conn.prepareCall("{CALL TANG_DIEM_KHACH_HANG(?, ?)}");
cstmt.setInt(1, 101); // ma_khach = 101
cstmt.setInt(2, 50);  // diem_cong = 50

boolean hasResultSet = cstmt.execute();

if (hasResultSet) {
    try (ResultSet rs = cstmt.getResultSet()) {
        while (rs.next()) {
            System.out.println("Mã đơn: " + rs.getInt("ma_don"));
        }
    }
}

4. Cách tải Class Java lên CSDL (Triển khai - Deployment)

Trong môi trường Embedded, nếu class Java nằm cùng dự án (cùng CLASSPATH), Derby sẽ tự động tìm thấy. Tuy nhiên, trong môi trường Network Server Production, bạn phải nạp file .jar chứa các hàm này vào thẳng database.

Derby cung cấp các thủ tục hệ thống ngầm định (thuộc schema SQLJ) để làm việc này qua SQL:

Đóng gói mã Java: Dùng Maven hoặc lệnh jar -cvf routines.jar com/hieu/... để tạo file .jar.

Cài đặt vào Database:

SQL

-- Cài đặt file jar từ ổ cứng máy chủ vào CSDL
CALL sqlj.install_jar('/path/to/routines.jar', 'APP.MY_ROUTINES', 0);

Kích hoạt Jar trong Database:

SQL

-- Nói cho Derby biết phải tìm class trong gói jar nào
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'APP.MY_ROUTINES');

(Nếu sau này cập nhật code mới, bạn dùng CALL sqlj.replace_jar(...)).

Mô hình này giúp cơ sở dữ liệu của bạn hoàn toàn đóng gói tự trị. Ngay cả khi bạn sao chép thư mục CSDL sang máy tính khác, các hàm UDF và Procedure tùy chỉnh vẫn hoạt động bình thường mà không cần cấu hình lại thư viện môi trường.

Cơ sở dữ liệu của chúng ta đã sở hữu sức mạnh xử lý nghiệp vụ tại chỗ. Bài tiếp theo sẽ đề cập đến tác vụ quản trị cốt lõi: làm sao để đẩy một lượng lớn dữ liệu có sẵn vào bảng hoặc xuất dữ liệu ra hệ thống bên ngoài: Apache Derby Bài 34: Công cụ Import/Export dữ liệu hàng loạt (Bulk Import/Export).

Apache

1. Sự khác biệt cốt lõi của Apache Derby

2. User-Defined Functions (UDF) - Hàm tự định nghĩa

Bước 1: Viết phương thức Java

Bước 2: Đăng ký hàm trong Derby bằng SQL

Bước 3: Sử dụng hàm trong truy vấn

3. Stored Procedures (Thủ tục lưu trữ)

Khái niệm jdbc:default:connection

Bước 1: Viết phương thức Java cho Procedure

Bước 2: Đăng ký Procedure trong Derby

Bước 3: Gọi Procedure từ Java Client

4. Cách tải Class Java lên CSDL (Triển khai - Deployment)

Mục lục

Khái niệm `jdbc:default:connection`