Database Change Notification的作用就是当数据库中的被监听table变化时(包括数据和表结构的更改),自动向应用程序发出一个通知。这对读多写少应用的缓存更新,以及避免轮询数据库很有用。
需要3个步骤:
1. 注册。
2. 用一个查询表示监听哪个(些)表的修改通知。
3. 响应。
在11g中,可以对查询结果集监听,而不是对所有记录的改变触发通知事件。
下面的代码在java1.6,ojdbc6.jar驱动, oracle10g r2环境下通过运行。
<%@ page language="java" contentType="text/html;charset=GBK" isELIgnored="false"%>
<%@ page import="javax.naming.InitialContext, java.util.*, java.math.BigDecimal, javax.sql.DataSource, java.sql.*, oracle.sql.*, oracle.jdbc.*, oracle.jdbc.dcn.*" %>
<%!
Connection getConnection(){
try{
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("auditDatasource");
ctx.close();
Connection conn=ds.getConnection();
return conn;
}catch(Exception e){
throw new RuntimeException(e.getMessage());
}
}
%>
<% String type=request.getParameter("type"); %>
<%
if("register".equals(type)){
Connection conn = getConnection();
OracleConnection oconn = (OracleConnection)conn.unwrap(OracleConnection.class);
DatabaseChangeRegistration dcr = (DatabaseChangeRegistration)application.getAttribute("DatabaseChangeRegistration");
if(dcr!=null){ //撤销以前的注册
try{
oconn.unregisterDatabaseChangeNotification(dcr);
}catch(java.sql.SQLException e){} //避免ORA-29970: 指定的注册 ID 不存在
application.removeAttribute("DatabaseChangeRegistration");
System.out.println("unregisterDatabaseChangeNotification, regId="+dcr.getRegId());
dcr=null;
}
Properties prop = new Properties();
//ROWIDs may be rolled-up if the total shared memory consumption due to ROWIDs is too large (exceeds 1% of the dynamic shared pool size), OR if too many rows were modified
prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,"true"); //要取得更改记录的rowid
prop.setProperty(OracleConnection.DCN_IGNORE_DELETEOP,"true"); //忽略delete
//default: prop.setProperty(OracleConnection.NTF_LOCAL_TCP_PORT, 47632); //这里可设置程序的tcp监听端口,默认是47632
//Specifies the time in seconds after which the registration will be automatically expunged by the database. If 0 or NULL, then the registration persists until the client explicitly unregisters it.
prop.setProperty(OracleConnection.NTF_TIMEOUT, "3600"); //设置超时,这里是1个小时,届时数据库和驱动器的资源将自动释放。如果为0或不设置,则永不过期,直到程序停止监听,当数据库发送更新通知时,因没有监听端口,数据库随后释放资源。
//prop.setProperty(OracleConnection.NTF_QOS_PURGE_ON_NTFN, "true"); //在第一次通知后,注册就作废撤销
dcr = oconn.registerDatabaseChangeNotification(prop); //注册
System.out.println("registerDatabaseChangeNotification, regId="+dcr.getRegId()); //打印注册ID,在oracle里 select * from dba_CHANGE_NOTIFICATION_REGS 可以看到记录。
application.setAttribute("DatabaseChangeRegistration", dcr);
try
{
dcr.addListener(new DatabaseChangeListener(){ //增加事件监听
public void onDatabaseChangeNotification(DatabaseChangeEvent e){
DatabaseChangeEvent.EventType etype = e.getEventType();
System.out.println("receive "+ etype+" event, RegId="+ e.getRegId());
if(etype != DatabaseChangeEvent.EventType.OBJCHANGE) return; //如果不是数据改变事件,如表结构更改或删表,则返回。
TableChangeDescription[] tcds = e.getTableChangeDescription();
for( TableChangeDescription tcd : tcds){
System.out.println("changed table="+ tcd.getTableName());
java.util.EnumSet<TableChangeDescription.TableOperation> tops = tcd.getTableOperations(); //获得表操作类型
for( TableChangeDescription.TableOperation top: tops)
System.out.println("... TableOperation="+ top);
RowChangeDescription[] rcds = tcd.getRowChangeDescription();
for(RowChangeDescription rcd: rcds){ //获得更改的记录描述,包括更改类型insert, update, delete,以及rowid.
System.out.println("... RowOperation=" + rcd.getRowOperation()+", rowid="+rcd.getRowid().stringValue());
}
}
}
}
);
Statement stmt = oconn.createStatement();
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
ResultSet rs = stmt.executeQuery("select 1 from RPT_CHENJUN_DDJH_ERR_CODE where 1=2"); //这里表示关联RPT_CHENJUN_DDJH_ERR_CODE表的更改事件。
//while (rs.next()) {}
for(String tableName: dcr.getTables())
System.out.println("DatabaseChangeRegistration ON "+tableName);
rs.close();
stmt.close();
}
catch(SQLException ex)
{
if(oconn != null)
oconn.unregisterDatabaseChangeNotification(dcr);
throw ex;
}
finally
{
try
{conn.close();}catch(Exception innerex){ innerex.printStackTrace(); }
}
}
%>
<% if("unregister".equals(type)){
int regId = Integer.parseInt( request.getParameter("regId"));
System.out.println("unregister regId="+regId);
Connection conn = getConnection();
try{
OracleConnection oconn = (OracleConnection)conn.unwrap(OracleConnection.class);
//oconn.unregisterDatabaseChangeNotification(regId); //不支持的特性
//NPE, bug!
DatabaseChangeRegistration dcr = oconn.getDatabaseChangeRegistration(regId);
oconn.unregisterDatabaseChangeNotification(dcr); //The registration will be destroyed in the server and in the driver
}finally{
try{conn.close();}catch(Exception innerex){ innerex.printStackTrace(); }
}
}
%>
<%
if("insert".equals(type)){
Connection conn =null;
try{
conn = getConnection();
OracleConnection oconn = (OracleConnection)conn.unwrap(OracleConnection.class);
oconn.setAutoCommit(false);
Statement stmt2 = oconn.createStatement();
stmt2.executeUpdate("insert into RPT_CHENJUN_DDJH_ERR_CODE values (-21, '',null)", Statement.RETURN_GENERATED_KEYS);
ResultSet autoGeneratedKey = stmt2.getGeneratedKeys();
if(autoGeneratedKey.next())
System.out.println("inserted one row with ROWID="+autoGeneratedKey.getString(1));
stmt2.close();
oconn.commit();
oconn.close();
}
catch(SQLException ex) { ex.printStackTrace(); }
finally{
if(conn!=null) conn.close();
}
}
%>