posts - 189,comments - 115,trackbacks - 0
数据迁移(从mysql3.1迁移到oralce10G)
ssuupv 发表于 2006-3-2 15:29:00

最近做了一项目数据移植工作:根据客户的需求。把原先,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能跑完,根据各个机器配置高低,会有些差别。

posted on 2006-03-31 13:10 MEYE 阅读(314) 评论(0)  编辑  收藏 所属分类: Study

只有注册用户登录后才能发表评论。


网站导航: