SSH配置双数据源

要求：

项目默认数据源是Sqlserver,在Sqlserver数据库中创建一个表A,表结构与Oracle数据库中的表A一样,每天读取Oracle数据库中表A新数据插入到Sqlserver数据库表A中

 

1. applicationContext-hibernate.xml配置增加了Oracle数据源的配置代码

 

<!-- Oracle 数据源 -->
    <bean id="dataSourceOracle" class="com.mchange.v2.c3p0.ComboPooledDataSource" 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" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource" 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" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
        <property name="triggers">
           <list>                
                <!-- 读取Oracle触发器 -->
                <ref bean="readOracleDataCronTrigger"/>
            </list>
        </property>
        <property name="configLocation" value="classpath:conf/quartz.properties"/>
    </bean>    
    <!-- 【【【【读取Oracle数据】】】】 -->
    <bean id="readOracleDataCronTrigger" class="org.springframework.scheduling.quartz.CronTriggerBean">
        <property name="jobDetail" ref="readOracleDataJobDetail"/>
        <!--每天23:10执行一次-->
        <property name="cronExpression" value="0 10 23 * * ?"/>
    </bean>
    <bean id="readOracleDataJobDetail" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
        <property name="targetObject" ref="readOracleDataInsertSqlserverTask"/>
        <property name="targetMethod" value="readOracleDataInsertSqlserver"/>
    </bean>    
    <bean id="readOracleDataInsertSqlserverTask" class="alpha.vehicle.quartz.ReadOracleDataInsertSqlserverTask">
    	<!-- 配置Oracle的SessionFactory -->
    	<property name="sessionFactory">
			<ref bean="sessionFactoryOracle" />
		</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数据源的SessionFactory
	private 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数据源的template
	protected HibernateTemplate getHibernateTemplateSqlServer(){
		HibernateTemplate template = new HibernateTemplate(sessionFactorySqlserver);
		return template;
	}
	//得到sqlserver中Realrec的最大序号
	public int getMaxRecBySqlserverRealrec(){		
		Long count = (Long) getHibernateTemplateSqlServer().execute(new HibernateCallback(){
			@Override
			public 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(){
			@Override
			public 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(){
			@Override
			public 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;
			}});
	}