jdbc：oracle数据库更改通知&重复事件 [英] jdbc: oracle database change notification & duplicate events

问题描述

问题：我的桌面上通过单一更新获得了许多检测。

我认为它的oracle缓存等。

可能只有真正的变化来检测？

我的代码：

  import java.sql.ResultSet; 
 import java.sql.SQLException; 
 import java.sql.Statement; 
 import java.util.Properties; 
 import oracle.jdbc.OracleConnection; 
 import oracle.jdbc.OracleDriver; 
 import oracle.jdbc.OracleStatement; 
 import oracle.jdbc.dcn.DatabaseChangeEvent; 
 import oracle.jdbc.dcn.DatabaseChangeListener; 
 import oracle.jdbc.dcn.DatabaseChangeRegistration; 
 
 public class OracleDCN {
 static final String USERNAME =scott; 
 static final String PASSWORD =tiger; 
 static String URL =jdbc：oracle：thin：@localhost：1521：stingdev; 
 
 public static void main（String [] args）{
 OracleDCN oracleDCN = new OracleDCN（）; 
 try {
 oracleDCN.run（）; 
} catch（Exception ex）{
 ex.printStackTrace（）; 
} 
} 
 
 private void run（）throws异常{
 OracleConnection conn = connect（）; 
属性prop = new Properties（）; 
 prop.setProperty（OracleConnection.DCN_NOTIFY_ROWIDS，true）; 
 DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification（prop）; 
 
 try {
 dcr.addListener（new DatabaseChangeListener（）{
 
 public void onDatabaseChangeNotification（DatabaseChangeEvent dce）{
 System.out.println（更改行ID：+ dce.getTableChangeDescription（）[0] .getRowChangeDescription（）[0] .getRowid（）。stringValue（））; 
} 
}）; 
 
声明stmt = conn.createStatement（）; 
（（OracleStatement）stmt）.setDatabaseChangeRegistration（dcr）; 
 ResultSet rs = stmt.executeQuery（select * from EXAMPLE where ID = 1）; 
 while（rs.next（））{
} 
 rs.close（）; 
 stmt.close（）; 
} catch（SQLException ex）{
 if（conn！= null）
 {
 conn.unregisterDatabaseChangeNotification（dcr）; 
 conn.close（）; 
} 
 throw ex; 
} 
} 
 
 OracleConnection connect（）throws SQLException {
 OracleDriver dr = new OracleDriver（）; 
属性prop = new Properties（）; 
 prop.setProperty（user，OracleDCN.USERNAME）; 
 prop.setProperty（password，OracleDCN.PASSWORD）; 
 return（OracleConnection）dr.connect（OracleDCN.URL，prop）; 
} 
} 
  

输出：

 更改行ID：AAAFSzAAAAAAAG8AAA 
更改行ID：AAAFSzAAAAAAAG8AAA 
更改的行ID：AAAFSzAAAAAAAG8AAA 
更改行ID：AAAFSzAAAAAAAG8AAA 
  

解决方案

问题是我多次注册到数据库。解决方案是在注册之前检查注册用户的更改事件。

我用这个查询检查了：

  select TABLE_NAME 
来自USER_CHANGE_NOTIFICATION_REGS 
  

I need some Listener for any change(update, insert, delete) of Oracle database table.

Problem: I get many detection by single update on my table.

I think its oracle cache etc.

Is it possible only real changes to detect?

my code:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;

public class OracleDCN {
    static final String USERNAME = "scott";
    static final String PASSWORD = "tiger";
    static String URL = "jdbc:oracle:thin:@localhost:1521:stingdev";

    public static void main(String[] args) {
        OracleDCN oracleDCN = new OracleDCN();
        try {
            oracleDCN.run();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void run() throws Exception{
        OracleConnection conn = connect();
        Properties prop = new Properties();
        prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
        DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);

        try{
            dcr.addListener(new DatabaseChangeListener() {

                public void onDatabaseChangeNotification(DatabaseChangeEvent dce) {
                    System.out.println("Changed row id : "+dce.getTableChangeDescription()[0].getRowChangeDescription()[0].getRowid().stringValue());
                }
            });

            Statement stmt = conn.createStatement();
            ((OracleStatement) stmt).setDatabaseChangeRegistration(dcr);
            ResultSet rs = stmt.executeQuery("select * from EXAMPLE where ID=1");
            while (rs.next()) {
            }
            rs.close();
            stmt.close();
        }catch(SQLException ex){
            if (conn != null)
            {
                conn.unregisterDatabaseChangeNotification(dcr);
                conn.close();
            }
            throw ex;
        }
    }

    OracleConnection connect() throws SQLException {
        OracleDriver dr = new OracleDriver();
        Properties prop = new Properties();
        prop.setProperty("user", OracleDCN.USERNAME);
        prop.setProperty("password", OracleDCN.PASSWORD);
        return (OracleConnection) dr.connect(OracleDCN.URL, prop);
    }
}

Output:

Changed row id : AAAFSzAAAAAAAG8AAA
Changed row id : AAAFSzAAAAAAAG8AAA
Changed row id : AAAFSzAAAAAAAG8AAA
Changed row id : AAAFSzAAAAAAAG8AAA

解决方案

Problem was I registered to database many times. The solution was check registered user for change events before register.

I checked that with this query:

select TABLE_NAME 
from USER_CHANGE_NOTIFICATION_REGS

这篇关于jdbc：oracle数据库更改通知&重复事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！