SSH配置双数据源
要求：
项目默认数据源是Sqlserver,在Sqlserver数据库中创建一个表A,表结构与Oracle数据库中的表A一样,每天读取Oracle数据库中表A新数据插入到Sqlserver数据库表A中
?
1. applicationContext-hibernate.xml配置增加了Oracle数据源的配置代码
?
<!-- Oracle 数据源 --> <bean id="dataSourceOracle" destroy-method="close"> <property name="driverClass" value="${jdbc.oracle.driverClassName}"/> <property name="jdbcUrl" value="${jdbc.oracle.url}"/> <property name="user" value="${jdbc.oracle.username}"/> <property name="password" value="${jdbc.oracle.password}"/> <property name="initialPoolSize" value="10"/> <property name="minPoolSize" value="10"/> <property name="maxPoolSize" value="50"/> <property name="checkoutTimeout" value="5000"/> <property name="maxIdleTime" value="1800"/> <property name="idleConnectionTestPeriod" value="3000"/> <property name="acquireIncrement" value="5"/> </bean> <bean id="sessionFactoryOracle" ref="dataSourceOracle"/> <property name="annotatedClasses"> <list> </list> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.cache.provider_class">${hibernate.cache.provider_class}</prop> <prop key="hibernate.cache.use_query_cache">${hibernate.cache.use_query_cache}</prop> <prop key="hibernate.dialect">${hibernate.dialect}</prop> <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop> <prop key="hibernate.show_sql">${hibernate.show_sql}</prop> <prop key="hibernate.jdbc.fetch_size">${hibernate.jdbc.fetch_size}</prop> <prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop> </props> </property> </bean>?
?
2. applicationContext-quartz.xml 定时任务重点代码：
?
<bean name="quartzScheduler" value="classpath:conf/quartz.properties"/> </bean> <!-- 【【【【读取Oracle数据】】】】 --> <bean id="readOracleDataCronTrigger" ref="readOracleDataJobDetail"/> <!--每天23:10执行一次--> <property name="cronExpression" value="0 10 23 * * ?"/> </bean> <bean id="readOracleDataJobDetail" ref="readOracleDataInsertSqlserverTask"/> <property name="targetMethod" value="readOracleDataInsertSqlserver"/> </bean> <bean id="readOracleDataInsertSqlserverTask" /></property><!-- 配置Sqlserver的SessionFactory --><property name="sessionFactorySqlserver"><ref bean="sessionFactory" /></property> </bean>
?
?
3. ReadOracleDataInsertSqlserverTask.java读取数据插入数据类：
?
private static DateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");//默认的SessionFactory是Oracle数据源的//这里要用到Sqlserver数据源的SessionFactoryprivate SessionFactory sessionFactorySqlserver;public SessionFactory getSessionFactorySqlserver() {return sessionFactorySqlserver;}public void setSessionFactorySqlserver(SessionFactory sessionFactorySqlserver) {this.sessionFactorySqlserver = sessionFactorySqlserver;}//执行数据读取并插入 public void readOracleDataInsertSqlserver(){ logger.info("oracle到sqlserver数据开始..."); batchInsert(); logger.info("oracle到sqlserver数据结束..."); } //得到sqlserver数据源的templateprotected HibernateTemplate getHibernateTemplateSqlServer(){HibernateTemplate template = new HibernateTemplate(sessionFactorySqlserver);return template;}//得到sqlserver中Realrec的最大序号public int getMaxRecBySqlserverRealrec(){Long count = (Long) getHibernateTemplateSqlServer().execute(new HibernateCallback(){@Overridepublic Object doInHibernate(Session session)throws HibernateException, SQLException {Query query = session.createQuery("select max(realrec.rec) from Realrec realrec");return query.uniqueResult();}});return count==null?0:count.intValue();}//得到Oracle的REALREC表中的10条记录@SuppressWarnings("unchecked")public List<Object[]> getRealrecByOracleRealrec(final int maxRecBySqlserverRealrec){ return getHibernateTemplate().executeFind(new HibernateCallback(){@Overridepublic Object doInHibernate(Session session)throws HibernateException, SQLException {String sql = "SELECT REC, DESK, OILNUM, PRICE, LIT, MONEY, RQ, CUSCRDNUM, MON, TRACKNUM, UNCODE, UNIT, RESULT FROM REALREC WHERE REC > "+maxRecBySqlserverRealrec+" ORDER BY REC";logger.info("========>"+sql);Query query = session.createSQLQuery(sql);return query.list();}}); } //往sqlserver中添加数据public void batchInsert() {getHibernateTemplateSqlServer().execute(new HibernateCallback(){@Overridepublic Object doInHibernate(Session session)throws HibernateException, SQLException {//sqlserver的Realrec表中最大序号(最后一条记录序号(总记录数))int maxRecBySqlserverRealrec = getMaxRecBySqlserverRealrec();//Oracle的Realrec表中的10条记录List<Object[]> listByOracleRealrec = getRealrecByOracleRealrec(maxRecBySqlserverRealrec);Realrec realrec = null;int i = 0;//循环插入到Sqlserver中去for(Object[] realrecByOracle : listByOracleRealrec){i++;realrec = new Realrec();realrec.setRec(realrecByOracle[0] == null?null:Long.valueOf(realrecByOracle[0].toString()));realrec.setDesk(realrecByOracle[1] == null?null:Long.valueOf(realrecByOracle[1].toString()));realrec.setOilNum(realrecByOracle[2] == null?null:realrecByOracle[2].toString());realrec.setPrice(realrecByOracle[3] == null?null:Double.valueOf(realrecByOracle[3].toString()));realrec.setLit(realrecByOracle[4] == null?null:Double.valueOf(realrecByOracle[4].toString()));realrec.setMoney(realrecByOracle[5] == null?null:Double.valueOf(realrecByOracle[5].toString()));try {realrec.setRq(realrecByOracle[6] == null?null:dateformat.parse(realrecByOracle[6].toString()));} catch (ParseException e) {e.printStackTrace();}realrec.setCuscrdNum(realrecByOracle[7] == null?null:realrecByOracle[7].toString());realrec.setMon(realrecByOracle[8] == null?null:Double.valueOf(realrecByOracle[8].toString()));realrec.setTrackNum(realrecByOracle[9] == null?null:realrecByOracle[9].toString());realrec.setUnCode(realrecByOracle[10] == null?null:realrecByOracle[10].toString());realrec.setUnit(realrecByOracle[11] == null?null:realrecByOracle[11].toString());realrec.setResult(realrecByOracle[12] == null?null:realrecByOracle[12].toString());getHibernateTemplateSqlServer().save(realrec);if(i % 50 == 0){session.flush();session.clear();}}return null;}});}?