Introduction

In this article, we are going to see what is the best way to call SQL Server stored procedures with jOOQ.

I decided to write this article because stored procedures and database functions are extremely useful for data-intensive applications, and sometimes, they are the only solution to process data efficiently.

While SQL remains the de-facto way to query data, when it comes to processing records, stored procedures allow us to control the transaction boundaries so that we can release the locks acquired for the modified records sooner and make sure that the Undo Log doesn’t grow too large.

Domain Model

Let’s assume we have the following database tables:

The post is the root table, and it has a one-to-one relationship with the post_details child table and a one-to-many relationship with the post_comment child table.

Each of these tables has an associated Audit Log table. For every INSERT, UPDATE, and DELETE on the post, post_details, and post_comment table, a database trigger will insert a record into the associated Audit Log table.

For instance, the database trigger that intercepts the INSERT statement on the post table looks like this:

CREATE TRIGGER tr_insert_post_audit_log ON post FOR INSERT AS
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = cast(
        SESSION_CONTEXT(N'loggedUser') as varchar(255)
    )
    
    DECLARE @transactionTimestamp datetime = SYSUTCDATETIME()
    
    INSERT INTO post_audit_log (
        id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by,
        trx_timestamp
    )
    VALUES(
        (SELECT id FROM Inserted),
        null,
        (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        'INSERT',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

For more details about using database triggers and JSON columns to create an Audit Log table, check out this article.

Cleaning up the Audit Log tables

Since the Audit Log tables can grow indefinitely, we need to remove records periodically to avoid running out of space.

While you could use a Bulk Delete query to achieve this task, this solution might cause several problems. For instance, if the volume of data is rather large, SQL Server might escalate the row-level locks could escalate to table-level locks, therefore impacting other concurrent transactions.

More, in case of transaction failures, a rollback would be very costly if the Undo Log has grown too large.

So, to avoid these issues, we want to use a database store procedure that can commit after deleting a given number of records, and the SQL Server stored procure can look as follows:

CREATE PROCEDURE clean_up_audit_log_table(
    @table_name NVARCHAR(100),
    @before_start_timestamp DATETIME,
    @batch_size INT,
    @deleted_row_count INT OUTPUT
)
AS
BEGIN                         
    DROP TABLE IF EXISTS #AUDIT_LOG_ROW_ID_TABLE
    CREATE TABLE #AUDIT_LOG_ROW_ID_TABLE (
        id BIGINT, 
        dml_type VARCHAR(10), 
        dml_timestamp DATETIME
    )
    DECLARE
        @audit_log_table_name NVARCHAR(1000),
        @insert_audit_logs_sql NVARCHAR(1000)

    SET @audit_log_table_name = @table_name + N'_audit_log '

    SET @insert_audit_logs_sql =
        N'INSERT INTO #AUDIT_LOG_ROW_ID_TABLE ' +
        N'SELECT TOP (@batch_size) id, dml_type, dml_timestamp ' +
        N'FROM ' + @audit_log_table_name +
        N' WHERE dml_timestamp <= @before_start_timestamp'

    EXECUTE sp_executesql @insert_audit_logs_sql,
        N'@batch_size INT, @before_start_timestamp DATETIME',
        @batch_size=@batch_size, @before_start_timestamp=@before_start_timestamp

    SET @deleted_row_count=0
    DECLARE @DeletedBatchRowCount INT

    WHILE (SELECT COUNT(*) FROM #AUDIT_LOG_ROW_ID_TABLE) > 0
    BEGIN       
        SET @DeletedBatchRowCount=0

        BEGIN TRY
            BEGIN TRANSACTION

            DECLARE @delete_audit_logs_sql NVARCHAR(1000)
            SET @delete_audit_logs_sql =
                N'DELETE FROM ' + @audit_log_table_name +
                N'WHERE EXISTS ( ' +
                N'  SELECT 1 ' +
                N'  FROM #AUDIT_LOG_ROW_ID_TABLE ' +
                N'  WHERE ' +
                N'    ' + @audit_log_table_name + N'.id' +
                N'      = #AUDIT_LOG_ROW_ID_TABLE.id AND ' +
                N'    ' + @audit_log_table_name + N'.dml_type ' +
                N'      = #AUDIT_LOG_ROW_ID_TABLE.dml_type AND ' +
                N'    ' + @audit_log_table_name + N'.dml_timestamp ' +
                N'      = #AUDIT_LOG_ROW_ID_TABLE.dml_timestamp ' +
                N')'

            EXECUTE sp_executesql @delete_audit_logs_sql
            
            SET @DeletedBatchRowCount+=@@ROWCOUNT
                                                       
            COMMIT TRANSACTION
            SET @deleted_row_count+=@DeletedBatchRowCount
        END TRY
        BEGIN CATCH
            IF (XACT_STATE()) = -1
                -- The current transaction cannot be committed.
                BEGIN
                    PRINT
                        N'The transaction cannot be committed. ' +
                        N'Rolling back transaction.'
                    ROLLBACK TRANSACTION
                END
            ELSE
                IF (XACT_STATE()) = 1
                -- The current transaction can be committed.
                    BEGIN
                        PRINT
                            N'Exception was caught, ' +
                            N'but the transaction can be committed.'
                        COMMIT TRANSACTION
                    END
        END CATCH
                   
        TRUNCATE TABLE #AUDIT_LOG_ROW_ID_TABLE

        EXECUTE sp_executesql @insert_audit_logs_sql,
            N'@batch_size INT, @before_start_timestamp DATETIME',
            @batch_size=@batch_size, @before_start_timestamp=@before_start_timestamp
    END
    
    DROP TABLE IF EXISTS #AUDIT_LOG_ROW_ID_TABLE
END

The clean_up_audit_log_table stored procedure is generic, so we can call it for any table that has an associated Audit Log table.

Calling SQL Server stored procures with jOOQ

Traditionally, calling stored procedures and database functions has been rather cumbersome with JDBC. However, as illustrated by this article, even JPA and Hibernate don’t excel when it comes to calling stored procedures and database functions.

Luckily, jOOQ takes this task more seriously and provides us with a type-safe approach that’s unparallel when it comes to developer productivity.

The jOOQ code generator can scan the database stored procedures and functions and generate a Java class that we can use instead.

For instance, in our case, for the clean_up_audit_log_table stored procedure, jOOQ has generated a CleanUpAuditLogTable Java class that we can use to clean up our audit log tables.

To clean up the post_audit_log rows that are older than 30 days, we can use the CleanUpAuditLogTable Java Object like this:

CleanUpAuditLogTable cleanUpPostAuditLog = new CleanUpAuditLogTable();
cleanUpPostAuditLog.setTableName(POST.getName());
cleanUpPostAuditLog.setBatchSize(500);
cleanUpPostAuditLog.setBeforeStartTimestamp(
    LocalDateTime.now().minusDays(30)
);
cleanUpPostAuditLog.execute(sql.configuration());

int deletedRowCount = cleanUpPostAuditLog.getDeletedRowCount();
assertSame(1000, deletedRowCount);

And to clean up the post_comment_audit_log records that are older than 30 days, we can call the clean_up_audit_log_table SQL stored procedure via the CleanUpAuditLogTable jOOQ API as follows:

CleanUpAuditLogTable cleanUpPostCommentAuditLog = new CleanUpAuditLogTable();
cleanUpPostCommentAuditLog.setTableName(POST_COMMENT.getName());
cleanUpPostCommentAuditLog.setBatchSize(500);
cleanUpPostCommentAuditLog.setBeforeStartTimestamp(
    LocalDateTime.now().minusDays(30)
);
cleanUpPostCommentAuditLog.execute(sql.configuration());

int deletedRowCount = cleanUpPostCommentAuditLog.getDeletedRowCount();
assertSame(10_000, deletedRowCount);

And that’s not all!

Let’s say we want to call a single stored procedure that cleans up all the audit tables we have in our application.

To do that, we are going to use the following clean_up_audit_log_tables SQL Server stored procedure that calls the previous clean_up_audit_log_table procedure for each table that we’re auditing:

CREATE PROCEDURE clean_up_audit_log_tables(
    @before_start_timestamp DATETIME,
    @json_report NVARCHAR(4000) output
) AS
BEGIN
    DECLARE
        @table_name NVARCHAR(100),
        @batch_size int,
        @deleted_row_count int

    DECLARE @CLEAN_UP_REPORT TABLE (
        id INT,
        table_name NVARCHAR(100),
        deleted_row_count INT DEFAULT 0
    )
    INSERT @CLEAN_UP_REPORT(id, table_name)
    VALUES (1, 'post'),
           (2, 'post_details'),
           (3, 'post_comment')

    DECLARE @AUDIT_LOG_TABLE_COUNT INT = (SELECT COUNT(*) FROM @CLEAN_UP_REPORT)
    DECLARE @I INT = 0

    SET @batch_size = 500

    WHILE @I < @AUDIT_LOG_TABLE_COUNT BEGIN
        SELECT @table_name=[table_name]
        FROM @CLEAN_UP_REPORT
        ORDER BY id DESC
        OFFSET @I
        ROWS FETCH NEXT 1 ROWS ONLY

        EXEC clean_up_audit_log_table
             @table_name = @table_name,
             @before_start_timestamp = @before_start_timestamp,
             @batch_size = @batch_size,
             @deleted_row_count = @deleted_row_count OUTPUT

        UPDATE @CLEAN_UP_REPORT
        SET deleted_row_count=@deleted_row_count
        WHERE table_name=@table_name

        SET @I += 1
    END
    SET @json_report = (
        SELECT
            table_name,
            deleted_row_count
        FROM @CLEAN_UP_REPORT
        FOR JSON AUTO
    )
END

Not only that calling the SQL Server clean_up_audit_log_tables stored procedure is very easy with jOOQ, but we are going to get a nice JSON report that we can send back to the UI:

CleanUpAuditLogTables cleanUpPostAuditLogTables = new CleanUpAuditLogTables();
cleanUpPostAuditLogTables.setBeforeStartTimestamp(
    LocalDateTime.now().minusDays(30)
);
cleanUpPostAuditLogTables.execute(sql.configuration());

String jsonReport = cleanUpPostAuditLogTables.getJsonReport();
LOGGER.info("Clean-up report: {}", jsonReport);

When executing the aforementioned test case, we get the following JSON report printed into the application log:

Clean-up report: [
    {
        "table_name":"post",
        "deleted_row_count":1000
    },
    {
        "table_name":"post_details",
        "deleted_row_count":1000
    },
    {
        "table_name":"post_comment",
        "deleted_row_count":10000
    }
]

Cool, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Compared to any other Java data access framework, jOOQ provides the most elegant and danced way to call stored procedures and database functions.

If you are developing data-intensive applications, jOOQ can help you get the most out of the underlying SQL-specific dialect that the database is offering. That’s why the High-Performance Java Persistence book has been dedicating a part for the jOOQ framework since 2016.

This research was funded by Data Geekery GmbH and conducted in accordance with the blog ethics policy.

While the article was written independently and reflects entirely my opinions and conclusions, the amount of work involved in making this article happen was compensated by Data Geekery.

Insert details about how the information is going to be processed

DOWNLOAD NOW