数据从oracle转换到mysql

原创

蓝枫居士 2022-08-18 19:06:55 博主文章分类：数据库 ©著作权

文章标签 oracle mysql spring 数据迁移 xml 文章分类 后端开发

©著作权归作者所有：来自51CTO博客作者蓝枫居士的原创作品，请联系作者获取转载授权，否则将追究法律责任

因为项目变更,需要把数据从oracle里转到mysql里。

第一个想法,自己写代码。
20分钟后,算了,还是找找工具吧。

第二步:
下了一个工具,二十分钟后,师兄发现,表的结构是倒完了,但是有的表数据全部倒好了,有的表数据只倒了一半。
算了,换个思路吧。

第三步:
A
算了,自己动手丰衣足食,在第二步已经有mysql表结构的基础上,数据通过代码来倒吧。
自己使用的是原生的jdbc,写了10分钟,看到满篇的Statement,ResultSet,算了,再换个思路吧。
B
我借用了spring的jdbctemplate。spring的xml如下:

<?xml version="1.0" encoding="UTF-8"?>  
<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:aop="http://www.springframework.org/schema/aop" 
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jee="http://www.springframework.org/schema/jee"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="  
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">  






    <!-- 数据库连接池c3p0配置 -->
    <bean id="dataSourceOracle" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="jdbcUrl" value="jdbc:oracle:thin:@10.150.0.888:1521:orcl"></property>
        <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"></property>
        <property name="user" value=""></property>
        <property name="password" value=""></property>
        <property name="maxPoolSize" value="40"></property>
        <property name="minPoolSize" value="1"></property>
        <property name="initialPoolSize" value="1"></property>
        <property name="maxIdleTime" value="20"></property>
    </bean>
    <bean id="jdbcTemplateOracle" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSourceOracle" />
    </bean>
    
    
        <!-- 数据库连接池c3p0配置 -->
    <bean id="dataSourceMysql" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="jdbcUrl" value="jdbc:mysql://10.150.0.888/cdm2"></property>
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="user" value=""></property>
        <property name="password" value=""></property>
        <property name="maxPoolSize" value="40"></property>
        <property name="minPoolSize" value="1"></property>
        <property name="initialPoolSize" value="1"></property>
        <property name="maxIdleTime" value="20"></property>
    </bean>
    <bean id="jdbcTemplateMysql" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSourceMysql" />
    </bean>
</beans>

下面的源代码

package db;


import java.sql.Timestamp;
import java.util.List;
import java.util.Map;


import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;


public class DBTransfer {
  public static void main(String[] args) {
    ClassPathXmlApplicationContext factory = new ClassPathXmlApplicationContext(
        "applicationContext2.xml");
    JdbcTemplate jdbcTemplateOracle = (JdbcTemplate) factory
        .getBean("jdbcTemplateOracle");
    JdbcTemplate jdbcTemplateMysql = (JdbcTemplate) factory
        .getBean("jdbcTemplateMysql");
    List<Map<String, Object>> tableNames = jdbcTemplateOracle
        .queryForList("select table_name from user_tables");
    for (Map<String, Object> map : tableNames) {
      String tableName = (String) map.get("table_name");   //开始转换一张新的表
      System.out.println("正在转换:" + tableName);
      List<Map<String, Object>> datas = jdbcTemplateOracle
          .queryForList("select * from " + tableName);
      List<Map<String, Object>> columnNames = jdbcTemplateOracle
          .queryForList("SELECT COLUMN_NAME,DATA_TYPE FROM SYS.All_Tab_Columns tc "
              + " where tc.TABLE_NAME='" + tableName + "' ");
      StringBuilder prefix = null;                //每张表的前缀都是一样的 第一次统一获取
      prefix = new StringBuilder();
      prefix.append("insert into " + tableName + " ( ");
      for (Map<String, Object> column : columnNames) {
        prefix.append(column.get("COLUMN_NAME"));
        prefix.append(",");
      }


      prefix = new StringBuilder(prefix.substring(0, prefix.length() - 1));
      prefix.append(")");
      StringBuilder sb = null;
      for (Map<String, Object> data : datas) {    //处理表中的每一行数据




        sb = new StringBuilder(prefix);   //把之前的前缀拿过来
        sb.append(" values( ");
        String columnName = "";
        for (Map<String, Object> column : columnNames) {
          columnName = (String) column.get("COLUMN_NAME");
          if (data.get(columnName)!=null) {    //
            
            //处理各种类型的数据
            if (column.get("DATA_TYPE").equals("VARCHAR2")) {
              sb.append("'" + data.get(columnName) + "'");
            }
            if (column.get("DATA_TYPE").equals("NUMBER")) {
              sb.append(data.get(columnName));
            }
            if (column.get("DATA_TYPE").equals("DATE")) {
              Timestamp dateStr=(Timestamp) data.get(columnName);
              sb.append("'" + dateStr + "'");
            }
            
          }else {
            sb.append("null");            
          }
          
          sb.append(",");
        }
        sb = new StringBuilder(sb.substring(0, sb.length() - 1));
        sb.append(")");
        
      
        jdbcTemplateMysql.update(sb.toString());
      }


    }


  }
}