Oracle触发器将数据推送到RabbitMq

引言：最新项目有个需求，通过触发器把最新预警信息推送到App，这里就涉及到了Oracle RabbitMq的集成。

oracle11g仅支持jdk1.6及以下版本，通过无数次的尝试 ，最终选定jdk1.5配合amqp-client-2.8.7

一、新建工程，编写RabbitMq的生产端代码

工程总体结构：

1.1、新建maven工程，添加RabbitMq依赖

<dependency>
    <groupId>com.rabbitmq</groupId>
    <artifactId>amqp-client</artifactId>
    <version>2.8.7</version>
</dependency>

1.2、编写RabbitMq生产端代码

import com.rabbitmq.client.Channel;
import com.rabbitmq.client.Connection;
import com.rabbitmq.client.ConnectionFactory;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
 * 类  名：BasicSender
 * 类描述：
 * 创建人：cpei
 * 创建时间：2020/11/2616:31
 * 修改人：
 * 修改时间：
 * 修改备注：
 *
 * @version 1.0.0
 */
public class BasicSender {

    protected static String host;
    protected static int port;
    protected static String username;
    protected static String password;
    protected static String fire = "fire";


    public static void sendMq(Integer id){

        InputStream inputStream = BasicSender.class.getClassLoader().getResourceAsStream("mq.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        host = properties.getProperty("host");
        port = Integer.valueOf(properties.getProperty("port"));
        username = properties.getProperty("username");
        password = properties.getProperty("password");

        ConnectionFactory factory = new ConnectionFactory();
        factory.setUsername(username);
        factory.setPassword(password);
        factory.setHost(host);
        factory.setPort(port);

        try {
            Connection conn = factory.newConnection();
            Channel channel = conn.createChannel();
            channel.exchangeDeclare(fire, "direct", true);
            channel.queueDeclare(fire, true, false, false, null);
            channel.queueBind(fire, fire, fire);
            byte[] messageBodyBytes = new String(id+"").getBytes();
            channel.basicPublish(fire, fire, null, messageBodyBytes);
            channel.close();
            conn.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

1.3、添加配置文件mq.properties,内容如下：

host = *.*.*.*  #RabbitMqIp
port = 5672
username = guest
password = guest

二、将工程打成jar包

打包前记得把jdk设置成1.5

mvn clean package

三、将jar包load到oracle

打开cmd，输入指令：

loadjava -r -f -o -u 用户名/密码@ip:1521/orcl -v  -resolve   D:\work\**\oracle2mq-0.0.1-SNAPSHOT-jar-with-dependencies.jar

四、进入oracle客户端，新建存储过程

CREATE OR REPLACE PROCEDURE sendmq (id number)
AS LANGUAGE JAVA
NAME 'com/**/BasicSender.sendMq(java.lang.Integer)';

备注：此处路径是jar包内java的全路径，此处做了脱敏处理

五、新建触发器，将新数据的主键发送到RabbitMq

CREATE OR REPLACE TRIGGER busi_firealarm_tri
AFTER INSERT ON busi_firealarm
FOR EACH ROW
CALL sendmq(:new.id)

六、给用到的消息队列授权，否则触发器执行失败

call dbms_java.grant_permission( 'oracle用户名', 'SYS:java.net.SocketPermission', 'rabbitmq的IP:5672', 'connect, resolve' );