java - 使用 JDBC 将表从 SQL Server 复制到 Oracle

Question

我正在尝试将三个单独的表从 SQL Server 2012 加载到 Oracle 中。我已经在不同的类中建立了与 SQL Server 和 Oracle 的连接，如下所示:

SQL Server 连接:

public class TestSqlUtil {
    public Connection getConnection() throws SQLException, IOException
    {
        String propsFile = "tasmania.properties";
        Properties props = new Properties();
        InputStream inputStream = 
            this.getClass().getClassLoader().getResourceAsStream(propsFile);

        if (inputStream == null)
        {
            throw new FileNotFoundException("property file '" + propsFile
                + "' not found in the classpath");
        }

        props.load(inputStream);

        String dblogin = props.getProperty("sqlfdb.LOGIN");
        String dbpasswd = props.getProperty("sqlfdb.PASSWD");
        String jdbcDrv = props.getProperty("sqlfdb.JDBCOCIDRV");
        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        Connection con = DriverManager.getConnection(jdbcDrv, dblogin, dbpasswd);
        System.out.printf("successfull connection");
        System.out.println();
        return con;
    }
    public void cleanUp(Connection con, PreparedStatement ps, ResultSet rs)
        throws SQLException
    {
        if (rs != null) rs.close();
        if (ps != null) ps.close();
        if (con != null) con.close();
    }
    public static void main(String a[]) throws SQLException, IOException
    {
        DaoUtil tasmaniaUtil = new DaoUtil();
        tasmaniaUtil.getConnection();
    }
}

甲骨文连接:

public class DaoUtil
{
    public Connection getConnection() throws SQLException, IOException 
    {
        String propsFile = "tasmania.properties";
        Properties props = new Properties();
        InputStream inputStream = 
            this.getClass().getClassLoader().getResourceAsStream(propsFile);

        if (inputStream == null)
        {
            throw new FileNotFoundException("property file '" + propsFile
                + "' not found in the classpath");
        }

        props.load(inputStream);

        String dblogin = props.getProperty("intfdb.LOGIN");
        String dbpasswd = props.getProperty("intfdb.PASSWD");
        String jdbcDrv = props.getProperty("intfdb.JDBCOCIDRV");
        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        Connection con = DriverManager.getConnection(jdbcDrv, dblogin, dbpasswd);
        System.out.printf("successfull connection");
        System.out.println();
        return con;
    }
    public void cleanUp(Connection con, PreparedStatement ps, ResultSet rs)
        throws SQLException 
    {
        if (rs != null) rs.close();
        if (ps != null) ps.close();
        if (con != null) con.close();
    }

    public static void main(String a[]) throws SQLException, IOException
    {
        DaoUtil tasmaniaUtil = new DaoUtil();
        tasmaniaUtil.getConnection();
    }
}

我已将所有登录详细信息存储在一个属性文件中。

我的第一个问题是如何将这两个类结合起来以合并这两个连接？我的第二个问题是如何使用 select 语句从 SQL Server 中获取所有数据，然后使用 insert 语句插入到 Oracle 数据库中？

SQL Server和Oracle中的表(从SQL Server到Oracle)

<强>1。对于 MATERIAL_BATCH:

在 SQL Server 中:

MATERIAL NUMBER|BATCH NUMBER|VENDOR BATCH NUMBER|VENDOR NUMBER|EXPIRATION DATE|
MODIFIED_DATETIME 

在 SQL Developer 中:

MATERIAL NUMBER|BATCH NUMBER|VENDOR BATCH NUMBER|VENDOR NUMBER|GOODS_SUPPLIER_NUMBER|
EXPIRATION DATE|INSTIME 

<强>2。对于 MATERIAL_MASTER:

在 SQL Server 中:

PLANT|MATERIAL_NUMBER|MATERIAL_DESC|MODIFIED_DATETIME 

在 SQL Developer 中:

PLANT|MATERIAL_NUMBER|MATERIAL_DESC|PROFIT_CENTER_NAME|STATUS|INSTIME  

<强>3。对于供应商:

在 SQL Server 中:

VENDOR_NUMBER|VENDOR_NAME|MODIFIED_DATETIME 

在 SQL Developer 中:

VENDOR_NUMBER|VENDOR_NAME|VENDOR_LOCATION|INSTIME

SQL Developer (Oracle) 中有一些 SQL Server 中没有的字段。对于那些我将保留 NULL。

Answer 1

这里有一些关于如何进行的草拟代码——它不会编译，因为缺少一些方法——但你应该能够从这里填补空白。

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBCopy {

    public static void main(String[] args) {

        try {
            //You know how to do this - just rename and copy in...
            Connection sourceCon = getOracleConnection(); 
            Connection targetCon = geSqlServerConnection();

            copyMaterialBatch(sourceCon, targetCon);
            copyMaterialMaster(sourceCon, targetCon);
            copyVendor(sourceCon, targetCon);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    private static void copyMaterialBatch(Connection sourceCon, Connection targetCon) throws SQLException {
        //Try-with-Resource to close all cursors once we're done
        try(//SELECT from source
                PreparedStatement ps = sourceCon.prepareStatement(
                    "SELECT \"MATERIAL NUMBER\", "
                    + "\"BATCH NUMBER\", "
                    + "\"VENDOR BATCH NUMBER\", "
                    + "\"VENDOR NUMBER\", "
                    + "\"EXPIRATION DATE\", "
                    + "\"MODIFIED_DATETIME\" FROM MATERIAL_BATCH");
            //INSERT into target
                PreparedStatement ins = targetCon.prepareStatement(
                        "INSERT INTO MATERIAL_BATCH([MATERIAL NUMBER], "
                    + "[BATCH NUMBER], "
                    + "[VENDOR BATCH NUMBER], "
                    + "[VENDOR NUMBER], "
                    + "[GOODS_SUPPLIER_NUMBER], "
                    + "[EXPIRATION DATE]"
                    + "[INSTIME]) VALUES (?,?,?,?,NULL,?,?)");
            //Perform select / open Cursor
                ResultSet rs = ps.executeQuery()) {
            int batchnr = 0;
            int MAXBATCH = 100;
            while(rs.next()) {
                //Set into INSERT the values we SELECTEd
                ins.setInt(1, rs.getInt("MATERIAL NUMBER")); 
                ins.setInt(2, rs.getInt("BATCH NUMBER")); 
                ins.setInt(3, rs.getInt("VENDOR BATCH NUMBER")); 
                ins.setInt(4, rs.getInt("VENDOR NUMBER"));              
                ins.setTimestamp(5, rs.getTimestamp("EXPIRATION DATE")); 
                ins.setTimestamp(6, rs.getTimestamp("MODIFIED_DATETIME"));
                //Add to Batch (you could executeUpdate here but if you have los of rows...)
                ins.addBatch();
                if(++batchnr % MAXBATCH == 0) {
                    ins.executeBatch();
                }
            }
            //if number of rows was not aligned on MAXBATCH size...
            ins.executeBatch();                     
        }                   
    }
}

请注意，Oracle 和 SQL-Server 对包含空格的列名使用不同的转义。 Oracle 需要 "COLUMN NAME" 而 SQL-Server 需要 [COLUMN NAME]。

祝你好运。

编辑

适应你的真实参数类型:

//Set into INSERT the values we SELECTEd
            ins.setString(1, rs.getString("MATERIAL_NUMBER")); 
            ins.setString(2, rs.getString("BATCH_NUMBER")); 
            ins.setString(3, rs.getString("VENDOR_BATCH_NUMBER")); 
            ins.setString(4, rs.getString("VENDOR_NUMBER"));      
            ins.setString(5, rs.getString("GOODS_SUPPLIER_NUMBER"));
            //6th value is always null as specified in INSERT
            ins.setTimestamp(6, rs.getTimestamp("MODIFIED_DATETIME"));