需求:
.在oracle中使用触发器，触发器监听某表中的明确的某些字段，当这个表的某些字段发生变化时，触发器将变化的详
细信息放到一张新增的表中，然后java代码中编写注册监听器的类，监听这个新表，新表中发生变化时，
1.java代码中通过获取数据的rowid查询出变化的新数据，
2.然后使用另外的方法将新数据放到xml文件中，将xml文件发成报文，
3.最后同步将报文发送出去
这3点在同一个地方处理，才能保证自动发送报文。
触发器请看：http://blog.csdn.net/it_wangxiangpan/article/details/8699059
我的任务是：
启动项目监听数据库表，使用listener，ojdbc6.jar, jdk1.7--tomcat7---maven项目
1.配置监听：web.xml
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
package com.army.init;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.ApplicationListener;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.stereotype.Service;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeRegistration;
/**
* This bean's goal is to initialize several caches which contain some frequently-used beans such like funtionRole,
* menuTree or users. These caches can help promoting the performance of this system.
* @author lixinjian
*
*/
@Service
public class DCNRegister
implements ApplicationListener<ContextRefreshedEvent>{
private static ApplicationContext context;
@Autowired
private DatabaseChangeListenerImpl dcli;
@Override//实现ApplicationContextAware后重写的方法
public void setApplicationContext(ApplicationContext context)
throws BeansException {
DCNRegister.context = context;
}
final static String USERNAME = "xxx";
final static String PASSWORD = "xxx";
final static String URL = "jdbc:oracle:thin:@xxx:1521:xxx";
<pre name="code" class="java"> //@Autowired
OracleConnection connect() throws SQLException {
OracleDriver dr = new OracleDriver();
Properties prop = new Properties();
prop.setProperty("user", DCNRegister.USERNAME);
prop.setProperty("password", DCNRegister.PASSWORD);
Connection conn = dr.connect(DCNRegister.URL, prop);
OracleConnection oconn =(OracleConnection) conn.unwrap(OracleConnection.class);
return oconn;
}
<pre name="code" class="java"> @Override
public void onApplicationEvent(ContextRefreshedEvent event) {
void run() throws SQLException {
/*GsonBuilder gsonBuilder = new GsonBuilder();
final Gson gson = gsonBuilder.create(); */
OracleConnection conn = connect();
// 1.在服务器上创建注册
Properties prop = new Properties();
prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true"); //要取得更改记录的rowid
prop.setProperty(OracleConnection.DCN_IGNORE_DELETEOP, "true"); //忽略delete
//在第一次通知后，注册就作废撤销
prop.setProperty(OracleConnection.NTF_QOS_PURGE_ON_NTFN, "true");
//2.注册dcr
DatabaseChangeRegistration dcr =
conn.registerDatabaseChangeNotification(prop);
try {
//增加事件监听
dcr.addListener(new DatabaseChangeListenerImpl());
Statement stmt = conn.createStatement();
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
stmt.executeQuery("select * from 表名");
stmt.close();
//select * from dba_CHANGE_NOTIFICATION_REGS 可以看到记录
} catch (SQLException ex) {
if (conn != null){//撤销以前的注册
conn.unregisterDatabaseChangeNotification(dcr);
throw ex;
}
} finally {
try {
conn.close();
} catch (Exception innerex) {
innerex.printStackTrace();
}
}
}
}
}3.有个引用类
package com.army.init;
import org.springframework.stereotype.Service;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.RowChangeDescription;
import oracle.jdbc.dcn.TableChangeDescription;
@Service
public class DatabaseChangeListenerImpl implements DatabaseChangeListener {
@Override
public void onDatabaseChangeNotification(DatabaseChangeEvent e) {
// TODO Auto-generated method stub
DatabaseChangeEvent.EventType etype = e.getEventType();
//打印注册ID，在oracle里 select * from
//dba_CHANGE_NOTIFICATION_REGS 可以看到记录。
System.out.println("receive :" + etype+"\r\n" +
" -event, RegId:" + e.getRegistrationId()+"\r\n"+
"-event-query:"+e.getQueryChangeDescription()+"\r\n"+
"-event-tablechange:"+ e.getTableChangeDescription()+"\r\n"+
"source: "+e.getSource()+"\r\n"+
"EventType: "+e.getEventType()+"\r\n"+
"Eventclass: "+e.getClass()+"\r\n"+
"Eventdbname: "+e.getDatabaseName()+"\r\n"
);
System.out.println("----------------------------------------------");
TableChangeDescription[] tcDesc=e.getTableChangeDescription();
for(TableChangeDescription tcd:tcDesc){
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()+"\r\n"+
", rowid="+rcd.getRowid().stringValue()+"\r\n"
);
}
}
}
}
4.这样以后应该能够做到，在启动项目时，首先加载这个DCNRegister类，但是我的项目一直包这个错误
八月 24, 2016 6:12:12 下午 org.apache.catalina.core.AprLifecycleListener init
信息: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jdk1.7.0_80\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;D:\app\mlink\product\11.2.0\dbhome_1\bin;C:\Program Files\Broadcom\Broadcom 802.11 Network Adapter\Driver;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Intel\OpenCL SDK\2.0\bin\x86;C:\Program Files (x86)\Intel\OpenCL SDK\2.0\bin\x64;C:\Program Files\Java\jdk1.7.0_80\bin;C:\Program Files\Java\jdk1.7.0_80\jre\bin;C:\drebbase\lib;D:\Program Files\TortoiseSVN\bin;C:\Program Files\Java\apache-maven-3.2.5\bin;C:\Program Files\VisualSVN Server\bin;.
八月 24, 2016 6:12:12 下午 org.apache.tomcat.util.digester.SetPropertiesRule begin
警告: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.j2ee.server:FMS' did not find a matching property.
八月 24, 2016 6:12:13 下午 org.apache.coyote.AbstractProtocolHandler init
信息: Initializing ProtocolHandler ["http-bio-8080"]
八月 24, 2016 6:12:13 下午 org.apache.coyote.AbstractProtocolHandler init
信息: Initializing ProtocolHandler ["ajp-bio-8009"]
八月 24, 2016 6:12:13 下午 org.apache.catalina.startup.Catalina load
信息: Initialization processed in 1728 ms
八月 24, 2016 6:12:13 下午 org.apache.catalina.core.StandardService startInternal
信息: Starting service Catalina
八月 24, 2016 6:12:13 下午 org.apache.catalina.core.StandardEngine startInternal
信息: Starting Servlet Engine: Apache Tomcat/7.0.6
八月 24, 2016 6:12:14 下午 org.apache.catalina.core.ApplicationContext log
信息: Initializing Spring root WebApplicationContext
log4j:WARN No appenders could be found for logger (org.springframework.web.context.ContextLoader).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
八月 24, 2016 6:12:18 下午 org.apache.catalina.core.StandardContext listenerStart
严重: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
java.lang.AbstractMethodError: oracle.jdbc.driver.T4CConnection.unwrap(Ljava/lang/Class;)Ljava/lang/Object;
at com.army.init.DCNRegister.connect(DCNRegister.java:119)
at com.army.init.DCNRegister.run(DCNRegister.java:73)
at com.army.init.DCNRegister.onApplicationEvent(DCNRegister.java:63)
at com.army.init.DCNRegister.onApplicationEvent(DCNRegister.java:1)
at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:151)
at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:128)
at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:331)
at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:775)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:483)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:434)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:306)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:106)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4521)
at org.apache.catalina.core.StandardContext$1.call(StandardContext.java:5004)
at org.apache.catalina.core.StandardContext$1.call(StandardContext.java:4999)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
八月 24, 2016 6:12:18 下午 org.apache.catalina.core.ApplicationContext log
信息: Set web app root system property: 'FMS' = [D:\apache-tomcat-7.0.6\webapps\FMS\]
八月 24, 2016 6:12:18 下午 org.apache.catalina.core.ApplicationContext log
信息: Initializing log4j from [D:\apache-tomcat-7.0.6\webapps\FMS\WEB-INF\classes\conf\log4j\log4j.properties]
八月 24, 2016 6:12:18 下午 org.apache.catalina.core.StandardContext startInternal
严重: Error listenerStart
八月 24, 2016 6:12:18 下午 org.apache.catalina.core.StandardContext startInternal
严重: Context [/FMS] startup failed due to previous errors
八月 24, 2016 6:12:18 下午 org.apache.catalina.core.ApplicationContext log
信息: Shutting down log4j
八月 24, 2016 6:12:18 下午 org.apache.catalina.core.ApplicationContext log
信息: Closing Spring root WebApplicationContext
八月 24, 2016 6:12:18 下午 org.apache.catalina.loader.WebappClassLoader clearReferencesJdbc
严重: The web application [/FMS] registered the JDBC driver [com.mysql.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
八月 24, 2016 6:12:18 下午 org.apache.catalina.loader.WebappClassLoader clearReferencesJdbc
严重: The web application [/FMS] registered the JDBC driver [com.mysql.fabric.jdbc.FabricMySQLDriver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
八月 24, 2016 6:12:18 下午 org.apache.catalina.loader.WebappClassLoader clearReferencesJdbc
严重: The web application [/FMS] registered the JDBC driver [oracle.jdbc.OracleDriver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
八月 24, 2016 6:12:18 下午 org.apache.catalina.loader.WebappClassLoader clearReferencesThreads
严重: The web application [/FMS] appears to have started a thread named [Abandoned connection cleanup thread] but has failed to stop it. This is very likely to create a memory leak.
八月 24, 2016 6:12:18 下午 org.apache.catalina.loader.WebappClassLoader clearReferencesThreads
严重: The web application [/FMS] appears to have started a thread named [FileWatchdog] but has failed to stop it. This is very likely to create a memory leak.
八月 24, 2016 6:12:18 下午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory docs
八月 24, 2016 6:12:18 下午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory examples
八月 24, 2016 6:12:18 下午 org.apache.catalina.core.ApplicationContext log
信息: ContextListener: contextInitialized()
八月 24, 2016 6:12:18 下午 org.apache.catalina.core.ApplicationContext log
信息: SessionListener: contextInitialized()
八月 24, 2016 6:12:18 下午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory host-manager
八月 24, 2016 6:12:18 下午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory manager
八月 24, 2016 6:12:19 下午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory ROOT
八月 24, 2016 6:12:19 下午 org.apache.coyote.AbstractProtocolHandler start
信息: Starting ProtocolHandler ["http-bio-8080"]
八月 24, 2016 6:12:19 下午 org.apache.coyote.AbstractProtocolHandler start
信息: Starting ProtocolHandler ["ajp-bio-8009"]
八月 24, 2016 6:12:19 下午 org.apache.catalina.startup.Catalina start
信息: Server startup in 5920 ms
5.直到晚上，找到一个错误，坑死了，
然后将这里换成ojdbc6,这个错误解决了，花了一天时间
，被领导......
6.下一步准备优化
package com.citic.asp.landtrans.init;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.stereotype.Service;
import com.citic.asp.landtrans.persistent.dao.HibernateDAO;
import com.citic.asp.landtrans.persistent.entity.Consign;
import com.citic.asp.landtrans.report.DBConn;
import com.citic.asp.landtrans.service.Manager;
import com.citic.asp.landtrans.util.datamove.SysProperties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeRegistration;
/**
* This bean's goal is to initialize several caches which contain some frequently-used beans such like funtionRole,
* menuTree or users. These caches can help promoting the performance of this system.
* @author lixinjian
*
*/
@Service
public class DCNRegister
implements ApplicationListener {
//protected HibernateDAO<Consign> consignDao;
@Autowired
private DatabaseChangeListenerImpl dcl;
@Override
public void onApplicationEvent(ApplicationEvent event) {
@SuppressWarnings("deprecation")
//OracleConnection conn=(OracleConnection)consignDao.getSession().connection();
// 1.在服务器上创建注册
Properties prop = new Properties();
prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true"); //要取得更改记录的rowid
prop.setProperty(OracleConnection.DCN_IGNORE_DELETEOP, "true"); //忽略delete
//在第一次通知后，注册就作废撤销
//prop.setProperty(OracleConnection.NTF_QOS_PURGE_ON_NTFN, "true");
//2.注册dcr
DBConn db = new DBConn();
Connection conn =null;
try {
conn= db.getConnection();
DatabaseChangeRegistration dcr = ((OracleConnection) conn).registerDatabaseChangeNotification(prop);
dcr.addListener(dcl);
Statement stmt = conn.createStatement();
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
stmt.executeQuery("select * from 表名");
stmt.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try{
conn.close();
} catch (Exception innerex) {
innerex.printStackTrace();
}
}
}
}
package com.citic.asp.landtrans.init;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.stereotype.Service;
import org.springframework.web.context.support.XmlWebApplicationContext;
import com.citic.asp.landtrans.persistent.dao.HibernateDAO;
import com.citic.asp.landtrans.persistent.entity.Consign;
import com.citic.asp.landtrans.report.DBConn;
import com.citic.asp.landtrans.service.Manager;
import com.citic.asp.landtrans.util.datamove.SysProperties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeRegistration;
/**
* This bean's goal is to initialize several caches which contain some
* frequently-used beans such like funtionRole, menuTree or users. These caches
* can help promoting the performance of this system.
*
* @author lixinjian
*
*/
@Service
public class DCNRegister implements ApplicationListener {
@Autowired
private DatabaseChangeListenerImpl dcl;
@Override
public void onApplicationEvent(ApplicationEvent event) {
if (event.getSource() instanceof XmlWebApplicationContext) {
System.out.println(event.getSource().getClass().getName());
// 1.在服务器上创建注册
Properties prop = new Properties();
prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true"); // 要取得更改记录的rowid
prop.setProperty(OracleConnection.DCN_IGNORE_DELETEOP, "true"); // 忽略delete
//prop.setProperty(OracleConnection.NTF_QOS_PURGE_ON_NTFN, "true"); //在第一次通知后，注册就作废撤销
// 2.注册dcr
DBConn db = new DBConn();
Connection conn = null;
try {
conn = db.getConnection();
DatabaseChangeRegistration dcr =
((OracleConnection) conn).registerDatabaseChangeNotification(prop);
dcr.addListener(dcl);
Statement stmt = conn.createStatement();
((OracleStatement) stmt).setDatabaseChangeRegistration(dcr);
stmt.executeQuery("select * from 表名");
stmt.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (Exception innerex) {
innerex.printStackTrace();
}
}
}
}
}
8解决报错
a
java.sql.SQLException: ORA-29970: 指定的注册 ID 不存在
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:406)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4CTTIokpn.receive(T4CTTIokpn.java:347)
at oracle.jdbc.driver.T4CConnection.doRegisterDatabaseChangeNotification(T4CConnection.java:3751)
at oracle.jdbc.driver.PhysicalConnection.registerDatabaseChangeNotification(PhysicalConnection.java:9507)
at com.citic.asp.landtrans.init.DCNRegister.onApplicationEvent(DCNRegister.java:53)
at org.springframework.context.event.SimpleApplicationEventMulticaster$1.run(SimpleApplicationEventMulticaster.java:78)
at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)
at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:76)
at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:274)
at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:736)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:383)
at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:402)
at org.springframework.web.servlet.FrameworkServlet.initWebApplicationContext(FrameworkServlet.java:316)
at org.springframework.web.servlet.FrameworkServlet.initServletBean(FrameworkServlet.java:282)
at org.springframework.web.servlet.HttpServletBean.init(HttpServletBean.java:126)
at javax.servlet.GenericServlet.init(GenericServlet.java:160)
at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1133)
at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1087)
at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:996)
at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4741)
at org.apache.catalina.core.StandardContext$3.call(StandardContext.java:5062)
at org.apache.catalina.core.StandardContext$3.call(StandardContext.java:5057)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
at java.lang.Thread.run(Thread.java:662)
后来改了电脑ip，好了，
3256
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
