最近做了一項目數據移植工作：根據客戶的需求。把原先，mysql3.1平臺的數據遷移到oracle10g：

　剛開始我想通過oralce提供的oracle migration workbench，可是使用中碰到一些問題，一直沒搞出來，在網上也查找不到解決的方案，具體的問題我也不多說了。

　在用戶催了N次的時候，我忽然來了個靈感通過程序的方式，原理，1，從mysql一張表取出所有數據，2，存放到oracle10g　目標數據表里，3.重復執行1，2步驟，直到所有表導入。

接下來我講一下，我的解決過程。

我們開發的平臺是用webwork+spring+hibernate。

1。配置二個hibernate.配置文件1.hibernate_for_mysql.cfg.xml hibernate_for_oracle.cfg.xml

.hibernate_for_oracle.cfg.xm部分配置：

? <property name="hibernate.connection.url"><![CDATA[
??????????? jdbc:oracle:thin:@localhost:1521:orcl
??????? ]]></property>
??????? <!-- Database JDBC driver-->
??????? <property name="hibernate.connection.driver_class">
??????????? oracle.jdbc.driver.OracleDriver
??????? </property>

hibernate_for_oracle.cfg.xm部分配置：

??????? <property name="hibernate.connection.url"><![CDATA[
??????????? jdbc:mysql://localhost/flydonkey?useUnicode=true&characterEncoding=gbk
??????? ></property>
??????? <!-- Database JDBC driver-->
??????? <property name="hibernate.connection.driver_class">
??????????? org.gjt.mm.mysql.Driver
??????? </property>

2.在spring.xml配置bean主要負責數據遷移的

?<bean id="sessionFactory_for_mysql" class="org.springframework.orm.hibernate.LocalSessionFactoryBean" singleton="true">
??<property name="configLocation">
???<value>classpath:hibernate_for_mysql.cfg.xml</value>
??</property>
?</bean>
?
?<bean id="sessionFactory_for_oracle" class="org.springframework.orm.hibernate.LocalSessionFactoryBean" singleton="true">
??<property name="configLocation">
???<value>classpath:hibernate_for_oracle.cfg.xml</value>
??</property>
?</bean>

?<bean id="getDataFromMysql" class="com.migration.GetDataFromMysqlImpl" singleton="true">
?<property name="sessionFactory">
??<ref bean="sessionFactory_for_mysql"/>
?</property>
?</bean>
?
?? <bean id="insertDataToOracle" class="com.migration.InsertDataToOracleImpl" singleton="true">
?<property name="sessionFactory">
??<ref bean="sessionFactory_for_oracle"/>
?</property>

編寫bean文件，

GetDataFromMysqlImpl代碼

public class GetDataFromMysqlImpl extends EntityDaoImpl implements GetDataFromMysql {

public List getData(String tableName) {
??????? // TODO Auto-generated method stub
????? Session session = null;
????? List result = null;
????? try{
????????? session = getSession(true);
???????? String strSql = "from "+tableName;
???????? result = session.createQuery(strSql).list();?
????? }catch(Exception e){
????????? e.printStackTrace();
????? }finally{
????????? try{
????????????? if(session!=null){
??????????????? session.close();
????????????? }
??????????? }catch(Exception e){
??????????????? e.printStackTrace();
??????????? }
????????? }
????? return result;
??? }
}

InsertDataToOracleImpl代碼

public class GetDataFromMysqlImpl extends EntityDaoImpl implements GetDataFromMysql {???

public void insertData(){

?? this.insertTabelName()

}

?private void insertTableName() {
??????? Session session = null;
??????? Transaction tx = null;
??????? try {
??????????? session = getSession(true);
??????????? GetDataFromMysql gdfm = (GetDataFromMysql) ServiceLocator.getInstance().getService("getDataFromMysql");
??????????? List temp = gdfm.getData("tableName");
??????????? tx = session.beginTransaction();
??????????? if (temp != null) {
??????????????? Iterator it = temp.iterator();
??????????????? while (it.hasNext()) {
??????????????????? TableName?tableName = (TableName) it.next();
??????????????????? session.save(tabeName)
??????????????? }
??????????? }
??????????? tx.commit();
??????? } catch (Exception e) {
??????????? e.printStackTrace();
??????? } finally {
??????????? try {
??????????????? if (session != null) {
??????????????????? session.close();
??????????????? }
??????????? } catch (Exception e) {
??????????????? e.printStackTrace();
??????????? }
??????? }
??? }

??? }

3.隨便寫個客戶端調用些方法。

4編譯打包

5.關閉oracle10g所有constraint

6.運行程序

100萬紀錄估計30min能跑完，根據各個機器配置高低，會有些差別。