How Oracle triggers call Java implementation OpenFire message sending

Write in front, in order to achieve the successful execution of the whole process, please prepare the following documents first:

1. Login OpenFire server and Spark client related programs (Openfire_4_0_1.exe, Spark_2_7_6.exe)

2. Connect OpenFire and Oracle-related jar packages (Presence.jar, Smack.jar, Smackx-debug.jar, Smackx.jar, Ojdbc.jar)

STEP1: Install the OpenFire server and configure the database connection, configuration Reference "OpenFire Installation and Configuration Tutorial"

STEP2: Writing Java application programs and importing Smack 3 jar packages in IDE development tools such as Eclipse

Import Java.io.bufferedreader;import java.io.inputstream;import java.io.inputstreamreader;import Java.net.URL; Import Java.net.urlconnection;import Org.jivesoftware.smack.chat;import org.jivesoftware.smack.XMPPConnection; Import Org.jivesoftware.smack.packet.message;import org.jivesoftware.smackx.messageeventmanager;/** * OpenFire server sends a message to the specified client account * * @author Xiaolj * @date 2016-01-05 */public class Sendmsgfunc {public static void main (Strin G args[]) {//parameter 1:openfire the account created by the server (please log in with the Spark client 10000 account before sending the message)//Parameter 2: Message content to be sent int status = Sendimmessage ("10000", "main reduction assembly line equipment abnormal, please timely processing!")       "Feedback person: Zhang San; tel: 13883638681" ");  SYSTEM.ERR.PRINTLN (status); }/** * Confirm sending success based on return status */public static int Sendimmessage (string userid, string content) {System.out.pri        Ntln ("Start execution message push"); string ip = "127.0.0.1";//service IP or server corresponding host name String port = "5222";//client PID default is 5222 String domain = "server-pc"; server hostname or IP address String adminid = "admin";//fixed initiator (OpenFire server default highest levelLimited account) String pwd = "admin";//fixed (OpenFire server default highest privilege account password) int on_line = 0;//online try {// Determine if the user is online:--Error setting plugin online status any permission to access String URL = "http://127.0.0.1:9090/plugins/presence/status?jid=" + userid + "            @server-pc&type=xml ";            On_line = judguseronline (URL);            Establish connection send client message xmppconnection con = new Xmppconnection (IP, Integer.parseint (port));            Con.login (Adminid, PWD);            Chat chat = con.createchat (userid + "@" + domain);            Message msg = Chat.createmessage ();            Msg.setsubject ("System notification message");            Msg.setbody (content);            Messageeventmanager.addnotificationsrequests (MSG, True, True, true, true);            Chat.sendmessage (msg);            SYSTEM.ERR.PRINTLN ("Execute message push end");        Close connection con.close ();            } catch (Exception e) {System.err.println ("Execute message push error"); System.out.println ("〖integration〗 Send message to OpenFire error.            "); E.printstackTrace ();       return 3;   } return on_line; }/********** "Determine whether the current user is online" ***********/public static int judguseronline (String URL) {//default not line int online       state = 0;           try {URL ourl = new URL (URL);           URLConnection oconn = Ourl.openconnection ();           InputStream InputStream = Oconn.getinputstream (); if (oconn! = null) {//Read return value: OpenFire server needs to install plug-in and specify any permissions, restart server BufferedReader oIn = new Bufferedread             ER (new InputStreamReader (InputStream));                if (null! = oIn) {String Strflag = Oin.readline ();                Oin.close ();                if (Strflag.indexof ("type=\" error\ "") >= 0) {//Access insufficient: OpenFire user does not exist onlinestate =-1;                } else if (Strflag.indexof ("type=\" unavailable\ ") >= 0) {//user not in line onlinestate = 0;            } else if (Strflag.indexof ("priority") >= 0 | | strflag.indexof ("id=\" ") >= 0) {//= user Online       Onlinestate = 1; } else {onlinestate = 2;//Server Exception}}}} catch (Exception E          {System.err.println ("perform message push validation error");          E.printstacktrace ();      return 4;      } System.err.println ("Perform message push verification End");   return onlinestate; }}

STEP3: Load the Java program and the jar package from the runtime into the Oracle database

3.1) Import the OpenFire related jar package into the Oracle database:

Loadjava-r-f-o-user User/password @ip: Port/Instance name D:\smack.jarloadjava-r-f-o-user user/password @ip: Port/Instance name D:\smackx.jarloadjava-r-f-o -user User/Password @ip: Port/Instance Name D:\smackx-debug.jar

3.2) Create a new SQL window in Oracle creating a Java source program and then copy and compile the above Java code (source file name customization)

Create or replace and compile Java source named "Openfireclient_func" Asimport java.io.bufferedreader;import Java.io.Inpu Tstream;import Java.io.inputstreamreader;import Java.net.url;import Java.net.urlconnection;import Org.jivesoftware.smack.chat;import Org.jivesoftware.smack.xmppconnection;import Org.jivesoftware.smack.packet.message;import org.jivesoftware.smackx.messageeventmanager;/** * OpenFire server sends a message to the specified client account * * @author Xiaolj * @date 2016-01-05 */public class Sendmsgfunc {public static void main (Strin G args[]) {//parameter 1:openfire the account created by the server (please log in with the Spark client 10000 account before sending the message)//Parameter 2: Message content to be sent int status = Sendimmessage ("10000", "main reduction assembly line equipment abnormal, please timely processing!")       "Feedback person: Zhang San; tel: 13883638681" ");  SYSTEM.ERR.PRINTLN (status); }/** * Confirm sending success based on return status */public static int Sendimmessage (string userid, string content) {System.out.pri        Ntln ("Start execution message push"); string ip = "127.0.0.1";//service IP or server corresponding host name String port = "5222";//client PID default is 5222 String domain = "server-pc"; //server hostname or IP address string adminid = "admin";//fixed initiator (openfire server default highest privilege account) string pwd = "admin";//fixed unchanged (OpenFire service The default top-level rights account password) int on_line = 0;//online try {//Determine if the user is online:--If error settings plugin online status any access Strin            G URL = "http://127.0.0.1:9090/plugins/presence/status?jid=" + userid + "@server-pc&type=xml";            On_line = judguseronline (URL);            Establish connection send client message xmppconnection con = new Xmppconnection (IP, Integer.parseint (port));            Con.login (Adminid, PWD);            Chat chat = con.createchat (userid + "@" + domain);            Message msg = Chat.createmessage ();            Msg.setsubject ("System notification message");            Msg.setbody (content);            Messageeventmanager.addnotificationsrequests (MSG, True, True, true, true);            Chat.sendmessage (msg);            SYSTEM.ERR.PRINTLN ("Execute message push end");        Close connection con.close ();            } catch (Exception e) {System.err.println ("Execute message push error"); SystEm.out.println ("〖integration〗 Send message to OpenFire error.            ");            E.printstacktrace ();       return 3;   } return on_line; }/********** "Determine whether the current user is online" ***********/public static int judguseronline (String URL) {//default not line int online       state = 0;           try {URL ourl = new URL (URL);           URLConnection oconn = Ourl.openconnection ();           InputStream InputStream = Oconn.getinputstream (); if (oconn! = null) {//Read return value: OpenFire server needs to install plug-in and specify any permissions, restart server BufferedReader oIn = new Bufferedread             ER (new InputStreamReader (InputStream));                if (null! = oIn) {String Strflag = Oin.readline ();                Oin.close ();                if (Strflag.indexof ("type=\" error\ "") >= 0) {//Access insufficient: OpenFire user does not exist onlinestate =-1;                } else if (Strflag.indexof ("type=\" unavailable\ ") >= 0) {//user not in line onlinestate = 0; } else if (Strflag.indexof ("PrioriTy ") >= 0 | |                Strflag.indexof ("id=\") >= 0) {//indicates user online onlinestate = 1; } else {onlinestate = 2;//Server Exception}}}} catch (Exception E          {System.err.println ("perform message push validation error");          E.printstacktrace ();      return 4;      } System.err.println ("Perform message push verification End");   return onlinestate; }}

STEP4: Write an Oracle function (the trigger simply calls the function to execute the Java program, the function name is customized, and the Java name must be a class that exists in the Java program.) method (the parameter type is the same), otherwise the compilation fails, and the return value is defined as needed)

Create or Replace function Openfire_notice_func (f_user varchar2, f_msg varchar2) return numberaslanguage Java name ' Sendms Gfunc.sendimmessage (java.lang.String, java.lang.String) return int ';

STEP5: This step is critical, otherwise the message cannot be sent successfully (Oracle privilege mechanism, message sending requires socket and file read, so need to give Oracle user authorization, many novice easy to ignore problems)

Please use the SYS login database to authorize the user to execute "SQL command":

EXEC sys.dbms_java.grant_permission (' username ', ' SYS:java.net.SocketPermission ', ' 127.0.0.1:9090 ', ' connect,resolve '); (IP address is openfire server IP) exec sys.dbms_java.grant_permission (' username ', ' SYS:java.net.SocketPermission ', ' 127.0.0.1:5222 ', ' Connect,resolve '); (port invariant) Exec sys.dbms_java.grant_permission (' username ', ' SYS:java.lang.RuntimePermission ', ' getClassLoader ', '); exec sys.dbms_java.grant_permission (' username ', ' SYS:java.io.FilePermission ', ' D:\Oracle directory \product \11.2.0\dbhome_1\javavm\lib\security\cacerts ', ' read '); exec sys.dbms_java.grant_permission (' username ', ' sys: Java.io.FilePermission ', ' <<all file>> ', ' read,write,execute,delete ');

If the user is not authorized, the following exception is thrown:

STEP6: Writing an Oracle Trigger program

--Trigger Call Java program Execution message send create or replace trigger Trigger_ofuser after insert or update in Ofuser for each Rowdeclare--Define variable     Message_status number; --Message sent successfully return value status Message_info varchar2 (50);  --message content to be sent begin Dbms_output.put_line (' Start execution trigger ');  --raise_application_error (-20000, ' Simulate an error code, the program will terminate ');      --One, insert operation, if inserting then--1, insert user record (slightly)--insert into ofuser values (...); --2, send the message according to the return status record to inform the user whether to receive the notification, historical tracking, to determine whether the current user sent a message is successful message_info: = ' test message sent ' test person: ' | |:new.username| | '      】';        Begin Select Openfire_notice_func (: New.username, Message_info) into the message_status from dual;        if (Message_status =-1) then dbms_output.put_line (' notification succeeded (user not registered client) ');        elsif (message_status = 0) then Dbms_output.put_line (' Notification sent successfully (user not online) ');        elsif (message_status = 1) then dbms_output.put_line (' Notification sent successfully (user received notification) ');        elsif (message_status = 2) then dbms_output.put_line (' notification sent failed (server exception, please retry later) '); else Dbms_Output.put_line (' notification sent failed (Server message exception, please contact system Administrator) ');      End If;   End  End If;  --Second, update operation if updating then update Ofuser t set T.plainpassword = ' 123456 ' where t.username =: new.username; End If; End Trigger_ofuser;

STEP7: Writing a test window for testing

The above Oracle how to call Java program execution message to send a tutorial, if in doubt can contact the blogger.

Statement: The above tutorial for Bo Master original, if you need to reprint please indicate the source, thank you.

How Oracle triggers call Java implementation OpenFire message sending