Java数据转移,Oracle转到MySQL
应公司项目需求,需要将一个项目中数据经过处理转移到另一项目中用。特此做了定时程序,采用SSM框架,调用spring的定时任务。
先用maven搭建好ssm框架,引用好必须的jar包,写xml配置文件。
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<!-- 导入java ee jar包(可以去除index.jsp报错) -->
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-api</artifactId>
<version>7.0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<!-- 表示开发的时候引入,发布的时候不会加载此包 -->
<scope>test</scope>
</dependency>
<!-- spring核心包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-oxm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- mybatis核心包 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!-- mybatis/spring包 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<!-- 导入Mysql数据库链接jar包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<!-- <version>5.1.30</version> -->
<version>8.0.11</version>
</dependency>
<!-- 导入Oracle数据库链接jar包 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0</version>
</dependency>
<!-- 导入dbcp的jar包,用来在applicationContext.xml中配置数据库 -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.2</version>
</dependency>
<!-- MBG -->
<!-- https://mvnrepository.com/artifact/org.mybatis.generator/mybatis-generator-core -->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.5</version>
</dependency>
<!-- JSTL标签类 -->
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- 日志文件管理包 -->
<!-- log start -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>
<!-- 格式化对象,方便输出日志 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.1.41</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
</dependency>
<!-- log end -->
<!-- 映入JSON -->
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.13</version>
</dependency>
<!-- 上传组件包 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.9</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-email</artifactId>
<version>1.4</version>
</dependency>
<!-- 自动生成 -->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.6</version>
</dependency>
配置数据源,创建DynamicDataSource类,继承AbstractRoutingDataSource(spring提供的轻量级数据源切换)。
<!-- 配置MySQL数据源 -->
<bean id="dataSource_first" class="org.apache.commons.dbcp.BasicDataSource">
<!-- 数据库驱动 -->
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!-- 数据库连接池启动时创建的连接数量 我设置的是10个-->
<property name="initialSize" value="10" />
<!-- 池里不会被释放的最多空闲连接数量。设置为0时表示无限制。 -->
<property name="maxIdle" value="5" />
<!-- 在不新建连接的条件下,池中保持空闲的最少连接数。 -->
<property name="minIdle" value="5" />
<!--同一时间可以从池分配的最多连接数量。设置为0时表示无限制。 -->
<property name="maxActive" value="10" />
<!-- -->
<property name="removeAbandoned" value="true" />
<!-- 自动回收超时时间(以秒数为单位) -->
<property name="removeAbandonedTimeout" value="180" />
<!--超时等待时间以毫秒为单位-->
<property name="maxWait" value="3000" />
<!-- 默认的SQL语句自动提交状态(开启或关闭)设置由连接池本身设置(false由连接池定),不设置该值setAutoCommit方法不被调用; -->
<!-- <property name="defaultAutoCommit" value="false" /> -->
<!--要求必需是个SELECT类型的SQL语句,至少返回一行,由于它会在所有应用的SQL语句执行之前运行一次,
所以原则上应该对数据库服务器带来的压力越小越好,推荐使用“SELECT 1” -->
<property name="validationQuery">
<value>SELECT 1</value>
</property>
<!-- 表示从连接池中获取连接前是否运行validationQuery,true=运行[默认],false=不运行 -->
<property name="testOnBorrow">
<value>true</value>
</property>
<!--表示将连接归还连接池前是否运行validationQuery,true=运行,false=不运行[默认]; -->
<property name="testOnReturn">
<value>false</value>
</property>
</bean>
<!-- 配置Oracle数据源 -->
<bean id="dataSource_second" class="org.apache.commons.dbcp.BasicDataSource">
<!-- 数据库驱动 -->
<property name="driverClassName" value="${jdbc1.driverClassName}"/>
<property name="url" value="${jdbc1.url}"/>
<property name="username" value="${jdbc1.user}"/>
<property name="password" value="${jdbc1.password}"/>
<!-- <property name="connectionProperties">
<value>oracle.jdbc.ReadTimeout=60000</value>
</property> -->
<property name="minIdle">
<value>10</value>
</property>
<property name="maxActive" value="50"/>
<property name="maxWait" value="180000"/>
<property name="initialSize" value="10"></property>
<property name="removeAbandoned">
<value>true</value>
</property>
<property name="removeAbandonedTimeout">
<value>1800</value>
</property>
<property name="defaultAutoCommit" value="true"/>
<!-- 打开检查,用异步线程evict进行检查 -->
<property name="testWhileIdle">
<value>true</value>
</property>
<property name="testOnBorrow">
<value>false</value>
</property>
<property name="testOnReturn">
<value>false</value>
</property>
<property name="validationQuery">
<value>select sysdate from dual</value>
</property>
<property name="timeBetweenEvictionRunsMillis">
<value>30000</value>
</property>
<!-- <property name="oracle.net.CONNECT_TIMEOUT" >
<value>10000</value>
</property>
<property name="oracle.jdbc.ReadTimeout" >
<value>100000</value>
</property> -->
</bean>
<!-- 动态DataSource配置 -->
<bean id="dynamicDataSource" class="com.test.utils.DynamicDataSource">
<!--默认数据源 -->
<property name="defaultTargetDataSource" ref="dataSource_second"/>
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="dataSource_first" value-ref="dataSource_first"/>
<entry key="dataSource_second" value-ref="dataSource_second"/>
</map>
</property>
</bean>
public class DynamicDataSourceHolder {
private static final ThreadLocal<String> dataSourceKey = new InheritableThreadLocal<String>();
public static String getDataSource() {
return dataSourceKey.get();
}
public static void setDataSource(String dataSource) {
dataSourceKey.set(dataSource);
}
public static void clearDataSource() {
dataSourceKey.remove();
}
}
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource{
@Override
protected Object determineCurrentLookupKey() {
// TODO Auto-generated method stub
return DynamicDataSourceHolder.getDataSource();
}
}
//切换数据源Mysql
DynamicDataSourceHolder.setDataSource("dataSource_first");
//切换Oracle数据源
DynamicDataSourceHolder.setDataSource("dataSource_second");
spring定时任务,采用注解方式。添加配置。
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:task="http://www.springframework.org/schema/task"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/task
http://www.springframework.org/schema/task/spring-task-3.2.xsd
">
<!-- 配置包扫描器,扫描@Controller注解的类 -->
<context:component-scan base-package="com.*.controller"/>
<!-- 加载注解驱动 -->
<mvc:annotation-driven/>
<task:scheduler id="scheduler" pool-size="10" />
<task:annotation-driven executor="scheduler" proxy-target-class="true"/>
</beans>
设在凌晨2点开始执行。cron表达式可以百度。
@Scheduled(cron = "0 0 2 ? * *")
执行方法,先切换Oracle数据源查出数据,进行处理,再切换MySQL数据源插入。进行插入时通过唯一标识进行有则改之无则增加。添加事务,失败回滚。
全部执行完后,发送邮件。