Listens to Oracle AQ through JMS and triggers the execution of Java programs when the database changes

Listens to Oracle AQ through JMS and triggers the execution of Java programs when the database changes
Environment Description

The tutorial environment is based on Oracle 12C and JDK1.8. Oracle 12C supports multi-tenant features. Compared with the previous Oracle version, The 'C # Username 'is used to represent the user, for example, if the database user is kevin, use C # kevin to log on.

I. Oracle Advanced Message Queue AQ

Oracle AQ is a message queue in Oracle and an advanced application in Oracle. Each version is continuously enhanced. It uses the DBMS_AQ system package for corresponding operations and is the default Component of Oracle, you only need to install the Oracle database. AQ can be used for data transmission in multiple Oracle databases, Oracle, Java, C, and other systems.

The following steps describe how to create an Oracle AQ

1. Create a message load payload

Messages transmitted in Oracle AQ are called payloads. The format can be user-defined objects, XMLType, or ANYDATA. In this example, we create a simple object type for passing messages.

create type demo_queue_payload_type as object (message varchar2(4000));

2. Create a team list

The queue list is used to store messages. messages are automatically stored in the table when they are added to the queue and deleted when they are released. To create a data table using the DBMS_AQADM package, you only need to write the table name and set the corresponding attributes. Set multiple_consumers to false for the queue, and true for the publish/subscribe mode.

begin  dbms_aqadm.create_queue_table(    queue_table   => 'demo_queue_table',    queue_payload_type => 'demo_queue_payload_type',    multiple_consumers => false  );end;

After the execution, you can view that the demo_queue_table table is automatically generated in the oracle table. You can view the sub-segments that affect the execution (the meaning is clear ).

3. Create and start a queue

Create a queue and start the queue:

begin  dbms_aqadm.create_queue (    queue_name  => 'demo_queue',    queue_table => 'demo_queue_table'  );  dbms_aqadm.start_queue(    queue_name  =>  'demo_queue'  );end;

So far, we have created a queue payload, a queue list, and a queue. You can view the related objects created by the following system:

SELECT object_name, object_type FROM user_objects WHERE object_name != 'DEMO_QUEUE_PAYLOAD_TYPE';OBJECT_NAME OBJECT_TYPE------------------------------ ---------------DEMO_QUEUE_TABLE TABLESYS_C009392 INDEXSYS_LOB0000060502C00030$$ LOBAQ$_DEMO_QUEUE_TABLE_T INDEXAQ$_DEMO_QUEUE_TABLE_I INDEXAQ$_DEMO_QUEUE_TABLE_E QUEUEAQ$DEMO_QUEUE_TABLE VIEWDEMO_QUEUE QUEUE

• 1

We can see that a queue brings out a series of automatically generated objects, some of which are directly used later. However, it is interesting that the second queue is created. This is the so-called exception queue ). If AQ cannot receive messages from our queue, it will be recorded in this exception queue.

When messages are processed for multiple times and errors occur, they are automatically transferred to the exception queue. For how to handle the exception queue, I have not found the corresponding method yet, because the scenario I use does not require messages to be processed one by one, as long as they serve as notifications. Therefore, if a message is transferred to an exception queue, you can clear the data in the queue list.

delete from demo_queue_table;

4. Stop and delete a queue

To delete or recreate a file, use the following method:

BEGIN   DBMS_AQADM.STOP_QUEUE(      queue_name => 'demo_queue'      );   DBMS_AQADM.DROP_QUEUE(      queue_name => 'demo_queue'      );   DBMS_AQADM.DROP_QUEUE_TABLE(      queue_table => 'demo_queue_table'      );END;

5. Queue message

The column-filling operation is a basic transaction operation (like Insert to the queue list), so we need to submit it.

declare  r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;  r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;  v_message_handle RAW(16);  o_payload demo_queue_payload_type;begin  o_payload := demo_queue_payload_type('what is you name ?');  dbms_aq.enqueue(    queue_name  => 'demo_queue',    enqueue_options => r_enqueue_options,    message_properties => r_message_properties,    payload => o_payload,    msgid => v_message_handle  );  commit;end;

Use SQL statements to check whether a message is properly queued:

select * from aq$demo_queue_table;select user_data from aq$demo_queue_table;

6. Departure message

I have not succeeded in the experiment because I used Oracle for team-out operations (not sure whether it is related to the execution permission of DBMS_OUTPUT). The Code is as follows. You can debug it:

declare  r_dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;  r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;  v_message_handle RAW(16);  o_payload demo_queue_payload_type;begin  DBMS_AQ.DEQUEUE(    queue_name => 'demo_queue',    dequeue_options => r_dequeue_options,    message_properties => r_message_properties,    payload => o_payload,    msgid => v_message_handle  );  DBMS_OUTPUT.PUT_LINE(    '***** Browse message is [' || o_payload.message || ']****'  );end;

Ii. Java listens to and processes Oracle AQ queues using JMS

Java uses JMS for corresponding processing. The jar provided by Oracle can be found in the Oracle Installation Directory: You can use the find command in linux for search. For example:

find `pwd` -name 'jmscommon.jar'

The required jar is:

• App/oracle/product/12.1.0/dbhome_1/rdbms/jlib/jmscommon. jar
• App/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar
• App/oracle/product/12.1.0/dbhome_1/jlib/orai18n. jar
• App/oracle/product/12.1.0/dbhome_1/jlib/jta. jar
• App/oracle/product/12.1.0/dbhome_1/rdbms/jlib/aqapi_g.jar

1. Create a connection parameter class

In actual use, you can configure the parameter information in the properties file and use Spring for injection.

Package org. kevin. jms;/***** @ author li wen Yu * connection parameter information **/public class JmsConfig {public String username = "c # kevin "; public String password = "a111111111"; public String jdbcUrl = "jdbc: oracle: thin: @ 127.0.0.1: 1521: orcl"; public String queueName = "demo_queue ";}

• 1

2. Create a message conversion class

Because the message load is an Oracle data type, you need to provide a conversion factory class to convert the Oracle type to the Java type.

Package org. kevin. jms; import java. SQL. SQLException; import oracle. jdbc. driver. oracleConnection; import oracle. jdbc. internal. oracleTypes; import oracle. jpub. runtime. mutableStruct; import oracle. SQL. customDatum; import oracle. SQL. customDatumFactory; import oracle. SQL. datum; import oracle. SQL. STRUCT;/***** @ author 文* * data type conversion class **/@ SuppressWarnings ("deprecation") public class QUEUE_MESSAGE_TYPE implements CustomDatum, customDatumFactory {public static final String _ SQL _NAME = "QUEUE_MESSAGE_TYPE"; public static final int _ SQL _TYPECODE = OracleTypes. STRUCT; MutableStruct _ struct; // 12 indicates the string static int [] _ sqlType = {12}; static CustomDatumFactory [] _ factory = new CustomDatumFactory [1]; static final construct _ MessageFactory = new QUEUE_MESSAGE_TYPE (); public static CustomDatumFactory getFactory () {return _ MessageFactory;} public QUEUE_MESSAGE_TYPE () {_ struct = new MutableStruct (new Object [1], _ sqlType, _ factory);} public Datum toDatum (OracleConnection c) throws SQLException {return _ struct. toDatum (c, _ SQL _NAME);} public CustomDatum create (Datum d, int sqlType) throws SQLException {if (d = null) return null; QUEUE_MESSAGE_TYPE o = new QUEUE_MESSAGE_TYPE (); o. _ struct = new MutableStruct (STRUCT) d, _ sqlType, _ factory); return o;} public String getContent () throws SQLException {return (String) _ struct. getAttribute (0 );}}

3. Main class for Message Processing

Package org. kevin. jms; import java. util. properties; import javax. jms. message; import javax. jms. messageConsumer; import javax. jms. messageListener; import javax. jms. queue; import javax. jms. queueConnection; import javax. jms. queueConnectionFactory; import javax. jms. session; import oracle. jms. AQjmsAdtMessage; import oracle. jms. AQjmsDestination; import oracle. jms. AQjmsFactory; import oracle. jms. AQjmsSession;/***** @ author 文 message processing class **/public class Main {public static void main (String [] args) throws Exception {JmsConfig config = new JmsConfig (); QueueConnectionFactory queueConnectionFactory = AQjmsFactory. getQueueConnectionFactory (config. jdbcUrl, new Properties (); QueueConnection conn = queueConnectionFactory. createQueueConnection (config. username, config. password); AQjmsSession session = (AQjmsSession) conn. createQueueSession (false, Session. AUTO_ACKNOWLEDGE); conn. start (); Queue queue = (AQjmsDestination) session. getQueue (config. username, config. queueName); MessageConsumer consumer = session. createConsumer (queue, null, QUEUE_MESSAGE_TYPE.getFactory (), null, false); consumer. setMessageListener (new MessageListener () {@ Override public void onMessage (Message message) {System. out. println ("OK"); AQjmsAdtMessage adtMessage = (AQjmsAdtMessage) message; try {QUEUE_MESSAGE_TYPE payload = (QUEUE_MESSAGE_TYPE) adtMessage. getAdtPayload (); System. out. println (payload. getContent ();} catch (Exception e) {e. printStackTrace () ;}}); Thread. sleep (1000000 );}}

Use the Oracle program block to start the queue. When Java is not started, data in the queue table is displayed. After Java is started, messages are correctly output on the console. messages are written again through the Oracle block, and messages are processed correctly on the console. Java's JMS listener does not process it immediately. There may be a time difference between several seconds, and the time may not be equal.

Iii. Monitoring table record change notification Java

In the following example, create a data table and add a trigger to the table. When the data changes, the trigger calls the stored procedure to send a message to Oracle AQ, and then processes the message using Java JMS.

1. Create a table

Create a student table that contains two sub-segments: username and age. The sub-segments are varchar2 and age.

2. Create a stored procedure

Create the send_aq_msg stored procedure. Because the dbms data packet is called during the stored procedure, the system package needs to be authorized during the Stored Procedure (authorization by sys user ):

grant execute on dbms_aq to c##kevin;

• 1

Note that the Stored Procedure contains a commit statement.

create or replace PROCEDURE send_aq_msg (info IN VARCHAR2) as  r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;  r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;  v_message_handle RAW(16);  o_payload demo_queue_payload_type;begin  o_payload := demo_queue_payload_type(info);  dbms_aq.enqueue(    queue_name  => 'demo_queue',    enqueue_options => r_enqueue_options,    message_properties => r_message_properties,    payload => o_payload,    msgid => v_message_handle  );  commit;end send_aq_msg;

3. Create a trigger

Create a trigger in the student table. When data is written or updated, if age is 18, the data is queued. You need to call the stored procedure to send messages, but the trigger cannot contain the transaction commit statement. Therefore, you need to use pragma autonomous_transaction; to declare a free transaction:

CREATE OR REPLACE TRIGGER STUDENT_TR AFTER INSERT OR UPDATE OF AGE ON STUDENT FOR EACH ROW DECLAREpragma autonomous_transaction;BEGIN  if :new.age = 18 then      send_aq_msg(:new.username);    end if;  END;

Insert or update a trigger after creation:

insert into student (username,age) values ('jack.lee.3k', 18);update student set age=18 where username='jack003';

Java JMS can process messages correctly.