Monitoring Oracle AQ via JMS, triggering execution of Java programs as the database changes

• • Environment description
  • An Oracle advanced Message Queuing AQ
    • Create Message Payload payload
    • Create a team list
    • Create a queue and start
    • Stop and delete of queues
    • Queued News
    • Message from the team
  • Two Java uses JMS to listen for and process Oracle AQ queues
    • To create a connection parameter class
    • Creating a Message Transformation class
    • Main class for message processing
  • Three monitor table record change notification Java
    • Create a table
    • Create a stored procedure
    • Create a Trigger

Environment description

This lab environment is based on Oracle 12C and JDK1.8, where Oracle 12C supports multi-tenancy features, using ' C # #用户名表示用户 ' compared to previous versions of Oracle, such as if the database user is called Kevin, then use C # #kevin进行登陆 when logging in.

I. Oracle advanced Message Queuing AQ

Oracle AQ is a message queue in Oracle, an advanced application in Oracle, each of which is continuously enhanced with the DBMS_AQ System package, which is the default component of Oracle and can be used as long as the Oracle database is installed. With AQ, you can transfer data across multiple Oracle databases, Oracle and Java, and C systems.

The following steps explain how to create an Oracle AQ

1. Create Message Payload payload

Messages passed in Oracle AQ are called payloads (payloads), and can be in the form of a user-defined object or a 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 table is used to store messages, which are automatically saved to the table when queued and automatically deleted when the team is out. To create a data table using the DBMS_AQADM package, simply write the table name and set the appropriate properties. For queues that need to be set to Multiple_consumers to False, set to True if using 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 execution you can view the Demo_queue_table table automatically generated in the Oracle table to see the affected sub-segments (meaning clearer).

3. Create a queue and start

To create a queue and start a queue:

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

At this point, we have created queue payloads, queue tables, and queues. You can view which related objects are created by the following systems:

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

We see a queue that brings out a series of auto-generated objects, some of which are used directly from behind. But interestingly, the second queue was created. This is known as the exception Queue (exception). If AQ cannot receive messages from our queue, it is logged in the exception queue.

When a message is processed more than once, the error is automatically transferred to the exception queue, and the author has not found the appropriate wording for the exception queue, because the scenario I use does not require that the message be handled one-on-one, as long as the notification is useful. So if the message is transferred to the exception queue, you can run the data in the empty queue table

delete from demo_queue_table;

4. Stopping and deleting a queue

If you need to delete or rebuild, you can do so using the following methods:

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 News

The into row operation is a basic transaction (like an INSERT into a queue table), so we need to commit.

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;

To see if a message is queued properly with an SQL statement:

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

6. Message from the team

Using Oracle for the outbound operation, I did not experiment successfully (not sure whether it is related to dbms_output execution permissions), the code is as follows, the reader can debug:

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;

Second, Java listens to and processes Oracle AQ queues using JMS

Java uses JMS for appropriate processing and requires the use of Oracle-provided jars, which can be found in the Oracle installation directory: You can use the Find command in Linux for lookups, such as

find `pwd‘jmscommon.jar‘

The required jars are:

• 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 practice, parameter information can be configured in the properties file and injected using spring.

package org.kevin.jms;/** *  * @author 李文锴 *  连接参数信息 * */publicclass JmsConfig {    public"c##kevin";    public"a111111111";    public"jdbc:oracle:thin:@127.0.0.1:1521:orcl";    public"demo_queue";}

2. Create a Message transformation class

Because of the Oracle data type at the time of the message, you need to provide a conversion factory class to convert the Oracle type to Java type.

 PackageORG.KEVIN.JMS;ImportJava.sql.SQLException;ImportOracle.jdbc.driver.OracleConnection;ImportOracle.jdbc.internal.OracleTypes;ImportOracle.jpub.runtime.MutableStruct;ImportOracle.sql.CustomDatum;ImportOracle.sql.CustomDatumFactory;ImportOracle.sql.Datum;ImportOracle.sql.STRUCT;/** * * @author Li Wenxian * Data type conversion class * */@SuppressWarnings("Deprecation") Public  class queue_message_type implements customdatum, customdatumfactory  {     Public Static FinalString _sql_name ="Queue_message_type"; Public Static Final int_sql_typecode = oracletypes.struct; Mutablestruct _struct;//12 = string    Static int[] _sqltype = { A};Staticcustomdatumfactory[] _factory =Newcustomdatumfactory[1];Static FinalQueue_message_type _messagefactory =NewQueue_message_type (); Public StaticCustomdatumfactorygetfactory() {return_messagefactory; } Public Queue_message_type() {_struct =NewMutablestruct (Newobject[1], _sqltype, _factory); } PublicDatumtodatum(OracleConnection c)throwsSQLException {return_struct.todatum (c, _sql_name); } PublicCustomdatumCreate(Datum D,intSqlType)throwsSQLException {if(d = =NULL)return NULL; Queue_message_type o =NewQueue_message_type (); O._struct =NewMutablestruct (STRUCT) d, _sqltype, _factory);returnO } PublicStringgetcontent()throwsSQLException {return(String) _struct.getattribute (0); }}

3. Main class for message processing

 PackageORG.KEVIN.JMS;ImportJava.util.Properties;ImportJavax.jms.Message;ImportJavax.jms.MessageConsumer;ImportJavax.jms.MessageListener;ImportJavax.jms.Queue;ImportJavax.jms.QueueConnection;ImportJavax.jms.QueueConnectionFactory;ImportJavax.jms.Session;ImportOracle.jms.AQjmsAdtMessage;ImportOracle.jms.AQjmsDestination;ImportOracle.jms.AQjmsFactory;ImportOracle.jms.AQjmsSession;/** * * @author Li Wenxian Message Processing class * */ Public  class Main {     Public Static void Main(string[] args)throwsException {jmsconfig config =NewJmsconfig (); Queueconnectionfactory queueconnectionfactory = aqjmsfactory.getqueueconnectionfactory (CONFIG.JDBCURL,NewProperties ());        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 (NewMessageListener () {@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); }}

Queue up with an Oracle block and see the data in the Queues table when Java is not started. After starting Java, the console correctly outputs the message, and writes the message again through the Oracle program block, discovering that the console is handling the message correctly. The JMS listener in Java is not processed immediately, and there may be a time difference in seconds.

Third, monitoring table record changes notice Java

The following example creates a data table and then adds a trigger to the table, and when the data changes, the trigger calls the stored procedure to send a message to Oracle AQ and then uses Java JMS to process the message.

1. Create a table

Create a student table that contains username and age two sub-segments, where username is the VARCHAR2 type, age number type.

2. Create a stored procedure

Create the send_aq_msg stored procedure, because the DBMS packet is called in the stored procedure, and the System package executes in the stored procedure that requires authorization (authorization with the SYS user):

grant execute on dbms_aq to c##kevin;

Note 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 that, if age=18, is queued when the data is written to or updated. You need to call a stored procedure to send a message, but the trigger cannot contain a commit statement, so you need to use pragma autonomous_transaction; declare free things:

 CREATE or REPLACE TRIGGER student_tr  after INSERT or  UPDATE of age  on STUDENT   for each  ROW declare< /c14>pragma autonomous_transaction; BEGINif : new.age =   send_aq_msg (: new.username);       End if;   END;

To perform an INSERT or update operation after the trigger is created:

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

Java JMS can handle messages correctly.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Monitoring Oracle AQ via JMS, triggering execution of Java programs as the database changes