Connect to multiple database with Java

這應該是常會遇到的狀況，當要從一個資料庫轉出資料到另一種資料庫時，如果import、export不符合使用，就必須手動來撰寫符合這樣需求的工具了。

Java是透過JDBC來連接資料庫，所以只要找到資料庫適當的driver，並透過Class.forName method來載入和註冊jdbc driver，就可以操作資料庫了。

下面是一個從Oracle轉出資料，再將資料轉入SQLite的例子(使用前請先將相關資料庫資料和SQL語法改成符合你所需的)

。

Oracle2SOLite.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

// http://www.oracle.com/technetwork/indexes/downloads/index.html
// JDBC Driver->ojdbc14.jar
// http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
// JDBC Driver->sqlite-jdbc-3.7.3.jar

public class Oracle2SQLite {
    public static void main(String[] args) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@ip:port:name";
        String user = "user";
        String password = "pwd";
       
        String driver1 = "org.sqlite.JDBC";
        String url1 = "jdbc:sqlite:D:/Work/sample.db";
       
        Connection conn = null;
        Connection connection = null;
        Statement stmt = null;
        Statement statement = null;

        System.out.println("***** Start *****");
        try {
            System.out.println("1. connect to " + url);
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();

            System.out.println("2. connect to " + url1);
            Class.forName(driver1);
            connection = DriverManager.getConnection(url1);
            statement = connection.createStatement();
           
            StringBuffer sqlStr = new StringBuffer();
            // 組合相關 Select 語法
            sqlStr.append("SELECT * FROM Sample");

            System.out.println("3. query");
            StringBuffer sqlOut = new StringBuffer();
            ResultSet result = stmt.executeQuery(sqlStr.toString());
            System.out.println("4. insert");
            while(result.next()) {
                sqlOut.delete(0, sqlOut.length());
                // 組合 Insert 語法
                sqlOut.append("INSERT INTO TxnData VALUES('XXX', 'YYY', 'ZZZ')");
               
                statement.executeUpdate(sqlOut.toString());
            }
        }
        catch(ClassNotFoundException e) {
            System.out.println("找不到驅動程式: " + e.getMessage());
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            if(stmt != null) {
                try {
                    stmt.close();
                }  
                catch(SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                }
                catch(SQLException e) {
                    e.printStackTrace();
                }
            }

            if(statement != null) {
                try {
                    statement.close();
                }  
                catch(SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection != null) {
                try {
                    connection.close();
                }
                catch(SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}