The best way to call SQL Server stored procedures with jOOQ
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
And there is more!
You can earn a significant passive income stream from promoting all these amazing products that I have been creating.
If you're interested in supplementing your income, then join my affiliate program.
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.
