4

The best way to call SQL Server stored procedures with jOOQ

 1 year ago
source link: https://vladmihalcea.com/jooq-sql-server-stored-procedures/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

The best way to call SQL Server stored procedures with jOOQ

Last modified: Apr 19, 2023

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:

SQL Server audit log 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.

Transactions and Concurrency Control eBook

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Comment *

Before posting the comment, please take the time to read the FAQ page

Name *

Email *

Website

Notify me of follow-up comments by email.

This site uses Akismet to reduce spam. Learn how your comment data is processed.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK