要求:
项目默认数据源是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;
}});
}