Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Use JMS listener Oracle AQ, trigger the execution of Java programs in the database changes     - Nginx + Tomcat static and dynamic load balancing and separation configuration essentials under Linux (Server)

- iOS persistence of data archiving NSKeyedArchiver (Programming)

- Ubuntu 14.04 installation and configuration environment variable JDK1.8.0_25 (Linux)

- Linux Log Clear (Linux)

- Effective Java - lazy initialization (Programming)

- Setting up Linux machine through a proxy firewall (Linux)

- Openfire Hazelcast cluster Detailed (Server)

- Linux operating system security settings initial understanding (Linux)

- Ubuntu 14.04.02 LTS startup items erroneous writing / dev / sda1 (win 7 loader) Repair (Linux)

- MYSQL root password for the database user passwords are weak attack analysis (Linux)

- Linux file permissions and access modes (Linux)

- To get Java class / jar package path (Programming)

- Oracle SQL statement tracking (Database)

- JavaScript event handling Detailed (Programming)

- Arduino UNO simulation development environment set up and run simulation (Linux)

- CentOS 7 Test Marathon start Docker container (Server)

- DataGuard add temporary data files bug (Database)

- To install Redis under Linux (Database)

- CentOS7 install JAVA notes (Linux)

- Android gets the global process information and the memory used by the process (Programming)

 
         
  Use JMS listener Oracle AQ, trigger the execution of Java programs in the database changes
     
  Add Date : 2017-08-31      
         
         
         
  Environment Description

The experimental environment based on Oracle 12C and JDK1.8, Oracle 12C which supports multi-tenant properties, compared to the previous Oracle versions, use 'C ## user name' indicates that the user, for example, if a database user called kevin, using C ## landing kevin login.

One, Oracle Advanced Message Queuing AQ

Oracle AQ is the Oracle Message Queuing, Oracle is in an advanced application, each version are constantly strengthened by using DBMS_AQ corresponding operation system package is the default component of Oracle, just install the Oracle database, you can use . AQ can use for data transmission in multiple Oracle databases, Oracle and Java, C and other systems.

The following step by step instructions on how to create Oracle AQ

1. Create a message load payload

Oracle AQ messages passed is called the payload (payloads), the format can be user-defined object or XMLType or ANYDATA. In this example we create a simple object type is used to pass messages.

create type demo_queue_payload_type as object (message varchar2 (4000));
2. Create a list of team

Team list is used to store the message, when the team is automatically stored in the table automatically deleted from the team. Use DBMS_AQADM packets create data tables, simply write the name of the table, set the appropriate properties at the same time. For queues multiple_consumers needs to be set to false, if you are using a publish / subscribe model needs to be set to true.

begin
  dbms_aqadm.create_queue_table (
    queue_table => 'demo_queue_table',
    queue_payload_type => 'demo_queue_payload_type',
    multiple_consumers => false
  );
end;
After the implementation of the table you can view the oracle automatically generated demo_queue_table table, you can see the impact of sub-paragraph (meaning clearer).

3. Create and start the queue

Create and start the queue 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, team lists and queues. You can view the following system creates which related objects:

SELECT object_name, object_type FROM user_objects WHERE object_name = 'DEMO_QUEUE_PAYLOAD_TYPE'!;

OBJECT_NAME OBJECT_TYPE
------------------------------ ---------------
DEMO_QUEUE_TABLE TABLE
SYS_C009392 INDEX
SYS_LOB0000060502C00030 $$ LOB
AQ $ _DEMO_QUEUE_TABLE_T INDEX
AQ $ _DEMO_QUEUE_TABLE_I INDEX
AQ $ _DEMO_QUEUE_TABLE_E QUEUE
AQ $ DEMO_QUEUE_TABLE VIEW
DEMO_QUEUE QUEUE
1
We saw a queue with a series of auto-generated objects, some of which are to be used directly behind it. But interestingly, it created a second queue. This is called an exception queue (exception queue). If AQ can not receive messages from our cohort, it will be recorded in the exception queue.

Error message multiple treatments, etc. are automatically transferred to the exception queue, the queue for exception handling at present we have not found the appropriate wording, because I'm using the scene does not require one to one message must be processed, as long as play a notification the role can be. So if the message is transferred to an exception queue, the team can perform data empty list

delete from demo_queue_table;
4. Stop and delete queue

If you need to remove or rebuild can use the following method of operation:

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. queued messages

The column operation is a basic transactional operations (like the team to list Insert), so we need to be submitted.

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;
SQL statement to see if the message through the normal into the team:

select * from aq $ demo_queue_table;
select user_data from aq $ demo_queue_table;
6. dequeue message

Using Oracle team carried out the operation, I did not experiment was a success (not sure about DBMS_OUTPUT and execute permissions), as follows, the reader can be debugged:

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;
Two, Java using JMS monitor and deal with Oracle AQ queue

Java using JMS appropriate treatment required jar Oracle provided in the Oracle installation directory can be found: In linux you can use the find command to find, for example,

find `pwd` -name 'jmscommon.jar'
jar is required:

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

Configuration parameters can be in practical use properties files, using Spring injection.

package org.kevin.jms;
/ **
 *
 * @author Li Kai
 * 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 message transformation class

Because the message payload is Oracle data types, you need to provide a facility to convert the Oracle class type to 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 Li Kai
 * Data type conversion classes
 *
 * /
@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 represents the string
    static int [] _sqlType = {12};
    static CustomDatumFactory [] _factory = new CustomDatumFactory [1];
    static final QUEUE_MESSAGE_TYPE _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. The main message handling class

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 Levin Kai message handling 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);
    }

}
Using Oracle block to enqueue operation, see when there is no data exist to start Java team list. After starting Java, console correct message output; write messages again by Oracle block and found the console correctly handle the message. Java's JMS listener is not treated immediately, there may be a few seconds of time difference, time flies.

Third, the monitor table record changes notifications Java

The following example creates a data table, and then add the trigger on a table, when the data changes trigger calls a stored procedure Oracle AQ to send a message, and then use the Java JMS message processing.

1. Create a table

Create a student table, including username and age two sub-segments, wherein when username varchar2 type, age when the number type.

2. Create a stored procedure

Send_aq_msg create a stored procedure, because the call dbms data packet stored procedure, the system package in the stored procedure execution must be authorized (using sys user authorization):

grant execute on dbms_aq to c ## kevin;
1
Note that 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 Trigger

Create triggers student table, when data is written or updated, if age = 18, then the enqueued operation. Need to call a stored procedure to send a message, but can not contain things that trigger commit statement, so the need to use the pragma autonomous_transaction; declared free of things:

CREATE OR REPLACE TRIGGER STUDENT_TR
AFTER INSERT OR UPDATE OF AGE ON STUDENT FOR EACH ROW
DECLARE
pragma autonomous_transaction;
BEGIN
  if: new.age = 18 then
      send_aq_msg (: new.username);
  end if;
END;
After you create a trigger to execute an insert or update operation:

insert into student (username, age) values ( 'jack.lee.3k', 18);
update student set age = 18 where username = 'jack003';
Java JMS can correctly process the message.
     
         
         
         
  More:      
 
- Django Signals from practice to source code analysis (Programming)
- Redis-- persistence articles (Database)
- Windows SmartGit installation (Linux)
- Three details reflect the Unix system security (Linux)
- awk variables (Linux)
- Swift acquaintance of values and references, the circular reference, Agent Precautions (Programming)
- Hadoop upload files error solved (Server)
- Oracle database import and export (Database)
- Oracle Client Easy Connection error ORA-12154, TNS-03505 (Database)
- iOS GCD multithreading simple to use (Programming)
- Use Linux built-in firewall to improve network access control (Linux)
- Python variable type (Programming)
- Elixir: the future of programming languages (Programming)
- Linux server security - the web server configuration (Linux)
- ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], [] (Database)
- Hadoop scheduling availability of workflow platform - Oozie (Server)
- MySQL is configured to access external networks under Ubuntu (Database)
- Common DDOS attacks (Linux)
- Installation Strongswan: on a Linux IPsec-based VPN tool (Linux)
- Android custom title bar (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.