As recently announced in our newsletter, the upcoming jOOQ 3.5 will include an awesome new feature for those of you using the Oracle database: Native support for Oracle AQ! And your client code will be so easy to write, you’ll be putting those AQs all over your database immediately.
How does it work?
jOOQ rationale
The biggest reason why many of our users love jOOQ is our code generator. It generates a Java representation of your database schema, with all the relevant objects that you need when writing SQL. So far, this has included tables, sequences, user-defined-types, packages, procedures.
What’s new is that AQ objects are now also generated and associated with the generated object type.
CREATE OR REPLACE TYPE book_t
AS OBJECT (
ID NUMBER(7),
title VARCHAR2(100 CHAR),
language VARCHAR2(2 CHAR)
)
/
CREATE OR REPLACE TYPE books_t
AS VARRAY(32) OF book_t
/
CREATE OR REPLACE TYPE author_t
AS OBJECT (
ID NUMBER(7),
first_name VARCHAR2(100 CHAR),
last_name VARCHAR2(100 CHAR),
books books_t
)
/
CREATE OR REPLACE TYPE authors_t
AS VARRAY(32) OF author_t
/
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => 'new_author_aq_t',
queue_payload_type => 'author_t'
);
DBMS_AQADM.CREATE_QUEUE(
queue_name => 'new_author_aq',
queue_table => 'new_author_aq_t'
);
DBMS_AQADM.START_QUEUE(
queue_name => 'new_author_aq'
);
COMMIT;
END;
/
So, essentially, we have both OBJECT and VARRAY types for books and authors. You might prefer using TABLE types rather than VARRAY types, but for the sake of simplicity, we stick with VARRAY (as it isn’t so easy to use nested TABLE types with AQs in Oracle).
We have also created a queue that notifies listeners every time a new author is added to the database – along with their books. Imagine enqueue operations being done in a trigger on either the author or the book table.
jOOQ-generated code
When you run the jOOQ codegenerator (version 3.5 upwards) against the above schema, you’ll get a new Queues.java file, which contains:
public class Queues {
public static final Queue<AuthorT> NEW_AUTHOR_AQ
= new QueueImpl<AuthorT>(
"NEW_AUTHOR_AQ", SP, AUTHOR_T);
}
The above code is not really nicely formatted on this blog, but you don’t see any of this in your every day work. Because when you want to enqueue a message to this queue, you can simply write:
// Create a new OBJECT type with nested
// VARRAY type
AuthorT author = new AuthorT(
1,
"George",
"Orwell",
new BooksT(
new BookT(1, "1984", "en"),
new BookT(2, "Animal Farm", "en")
)
);
// ... and simply enqueue that on NEW_AUTHOR_AQ
DBMS_AQ.enqueue(configuration, NEW_AUTHOR_AQ, author);
Seriously? That easy? Yes!
Compare the above to anything you’ve written before through JDBC, or using Oracle’s native APIs. You’ll find a couple of examples about how to serialise / deserialise RAW types, but frankly, queues are awesome because you can send OBJECT types through the database, and we don’t see those examples from Oracle. In fact, trust us, you don’t want to serialise OBJECT, VARRAY, or TABLE types through JDBC. You don’t. That’s our job. We’re hacking JDBC so you don’t have to.
Of course, you can also pass MESSAGE_PROPERTIES_T, ENQUEUE_OPTIONS_T, and DEQUEUE_OPTIONS_T types as arguments to the enqueue() and dequeue() methods.
Dequeuing is just as easy. The following will generate a blocking call and wait for the next AUTHOR_T message to arrive:
With the above simple API and Java 8, we can do what Oracle must’ve known long ago, when they renamed Oracle AQ’s marketing name to Oracle Streams. Let’s create a Java 8 Stream of AQ-produced OBJECT types with jOOQ. Easy as pie. Just write:
The transactional context, as always, is transparent for jOOQ and hidden in the Configuration and its SPIs, e.g. ConnectionProvider. jOOQ only delegates to the PL/SQL API for DBMS_AQ.
From the DBMS_AQ perspective, indeed, when you roll back your transaction, the message is “put back into the queue”. In fact, behind the scenes, the “obscure” FOR UPDATE SKIP LOCKED pessimistic locking technique is used to “check out” a message for individual consumption, and other consumers will SKIP over the LOCKED messages until the message is really removed from the queue through a COMMIT, or until the lock is released through a ROLLBACK.
AQ: One of the most underrated Oracle Database features!
Pretty good API! That’s awesome!
I’ve never worked with Oracle AQ before but let me ask you something: How about transactional context?
What happens in case of failure? Rollback? All consumed messages are enqueued again?
The transactional context, as always, is transparent for jOOQ and hidden in the Configuration and its SPIs, e.g. ConnectionProvider. jOOQ only delegates to the PL/SQL API for DBMS_AQ.
From the DBMS_AQ perspective, indeed, when you roll back your transaction, the message is “put back into the queue”. In fact, behind the scenes, the “obscure” FOR UPDATE SKIP LOCKED pessimistic locking technique is used to “check out” a message for individual consumption, and other consumers will SKIP over the LOCKED messages until the message is really removed from the queue through a COMMIT, or until the lock is released through a ROLLBACK.
AQ: One of the most underrated Oracle Database features!