Access to PostgreSQL Data Transfer Tool
IT Tools

Access to PostgreSQL Data Transfer Tool

2023-03-02 · 26 分鐘 · Ray Lee (System Analyst)

Abstract:This Java program retrieves the latest data from an Access database table via ODBC and inserts it into a PostgreSQL database table. The program also handles a timestamp column and multiple rows of data.

Usage

  1. Install the JDBC driver for PostgreSQL in your Java environment.
  2. Set up the ODBC connection to the Access database and the PostgreSQL database connection details in the program.
  3. Modify the SQL statements in the program to match the table structures and column names of the Access and PostgreSQL tables.
  4. Compile the program and run it using the command java Main.
  5. The program will retrieve the latest data from the Access table and insert it into the PostgreSQL table. The timestamp column will be handled automatically, and multiple rows of data can be processed in a single run.
import java.sql.*;
import java.util.Calendar;

public class AccessToPostgres {
    public static void main(String[] args) {
        Connection accessConn = null;
        Connection postgresConn = null;
        Statement accessStmt = null;
        PreparedStatement postgresStmt = null;
        ResultSet rs = null;

        try {
            // Connect to Access database via ODBC
            String accessUrl = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\path\\to\\access\\database.accdb;";
            String accessUser = "";
            String accessPass = "";
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            accessConn = DriverManager.getConnection(accessUrl, accessUser, accessPass);

            // Connect to PostgreSQL database
            String postgresUrl = "jdbc:postgresql://localhost:5432/mydb";
            String postgresUser = "myuser";
            String postgresPass = "mypassword";
            Class.forName("org.postgresql.Driver");
            postgresConn = DriverManager.getConnection(postgresUrl, postgresUser, postgresPass);

            // Prepare statement for inserting data into PostgreSQL table
            String insertSql = "INSERT INTO mytable (id, name, timestamp) VALUES (?, ?, ?)";
            postgresStmt = postgresConn.prepareStatement(insertSql);

            // Retrieve the latest records from Access table
            accessStmt = accessConn.createStatement();
            String selectSql = "SELECT TOP 10 id, name, timestamp FROM mytable ORDER BY timestamp DESC";
            rs = accessStmt.executeQuery(selectSql);

            // Loop through the result set and insert the data into PostgreSQL
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Timestamp timestamp = rs.getTimestamp("timestamp");
                postgresStmt.setInt(1, id);
                postgresStmt.setString(2, name);
                postgresStmt.setTimestamp(3, timestamp);
                postgresStmt.executeUpdate();
            }

            System.out.println("Data transfer completed successfully!");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (accessStmt != null) accessStmt.close();
                if (accessConn != null) accessConn.close();
                if (postgresStmt != null) postgresStmt.close();
                if (postgresConn != null) postgresConn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

English Version

Abstract:This Java program retrieves the latest data from an Access database table via ODBC and inserts it into a PostgreSQL database table. The program also handles a timestamp column and multiple rows of data.

Usage

  1. Install the JDBC driver for PostgreSQL in your Java environment.
  2. Set up the ODBC connection to the Access database and the PostgreSQL database connection details in the program.
  3. Modify the SQL statements in the program to match the table structures and column names of the Access and PostgreSQL tables.
  4. Compile the program and run it using the command java Main.
  5. The program will retrieve the latest data from the Access table and insert it into the PostgreSQL table. The timestamp column will be handled automatically, and multiple rows of data can be processed in a single run.
import java.sql.*;
import java.util.Calendar;

public class AccessToPostgres {
    public static void main(String[] args) {
        Connection accessConn = null;
        Connection postgresConn = null;
        Statement accessStmt = null;
        PreparedStatement postgresStmt = null;
        ResultSet rs = null;

        try {
            // Connect to Access database via ODBC
            String accessUrl = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\path\\to\\access\\database.accdb;";
            String accessUser = "";
            String accessPass = "";
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            accessConn = DriverManager.getConnection(accessUrl, accessUser, accessPass);

            // Connect to PostgreSQL database
            String postgresUrl = "jdbc:postgresql://localhost:5432/mydb";
            String postgresUser = "myuser";
            String postgresPass = "mypassword";
            Class.forName("org.postgresql.Driver");
            postgresConn = DriverManager.getConnection(postgresUrl, postgresUser, postgresPass);

            // Prepare statement for inserting data into PostgreSQL table
            String insertSql = "INSERT INTO mytable (id, name, timestamp) VALUES (?, ?, ?)";
            postgresStmt = postgresConn.prepareStatement(insertSql);

            // Retrieve the latest records from Access table
            accessStmt = accessConn.createStatement();
            String selectSql = "SELECT TOP 10 id, name, timestamp FROM mytable ORDER BY timestamp DESC";
            rs = accessStmt.executeQuery(selectSql);

            // Loop through the result set and insert the data into PostgreSQL
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Timestamp timestamp = rs.getTimestamp("timestamp");
                postgresStmt.setInt(1, id);
                postgresStmt.setString(2, name);
                postgresStmt.setTimestamp(3, timestamp);
                postgresStmt.executeUpdate();
            }

            System.out.println("Data transfer completed successfully!");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (accessStmt != null) accessStmt.close();
                if (accessConn != null) accessConn.close();
                if (postgresStmt != null) postgresStmt.close();
                if (postgresConn != null) postgresConn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

日本語版

Abstract:This Java program retrieves the latest data from an Access database table via ODBC and inserts it into a PostgreSQL database table. The program also handles a timestamp column and multiple rows of data.

使用方法

  1. Java環境にPostgreSQL用のJDBCドライバをインストールします。
  2. プログラム内でAccessデータベースへのODBC接続とPostgreSQLデータベースの接続情報を設定します。
  3. プログラム内のSQL文を、AccessとPostgreSQLのテーブル構造とカラム名に合わせて変更します。
  4. プログラムをコンパイルし、java Mainコマンドで実行します。
  5. プログラムはAccessテーブルから最新のデータを取得し、PostgreSQLテーブルに挿入します。タイムスタンプカラムは自動的に処理され、1回の実行で複数行のデータを処理できます。
import java.sql.*;
import java.util.Calendar;

public class AccessToPostgres {
    public static void main(String[] args) {
        Connection accessConn = null;
        Connection postgresConn = null;
        Statement accessStmt = null;
        PreparedStatement postgresStmt = null;
        ResultSet rs = null;

        try {
            // Connect to Access database via ODBC
            String accessUrl = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\path\\to\\access\\database.accdb;";
            String accessUser = "";
            String accessPass = "";
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            accessConn = DriverManager.getConnection(accessUrl, accessUser, accessPass);

            // Connect to PostgreSQL database
            String postgresUrl = "jdbc:postgresql://localhost:5432/mydb";
            String postgresUser = "myuser";
            String postgresPass = "mypassword";
            Class.forName("org.postgresql.Driver");
            postgresConn = DriverManager.getConnection(postgresUrl, postgresUser, postgresPass);

            // Prepare statement for inserting data into PostgreSQL table
            String insertSql = "INSERT INTO mytable (id, name, timestamp) VALUES (?, ?, ?)";
            postgresStmt = postgresConn.prepareStatement(insertSql);

            // Retrieve the latest records from Access table
            accessStmt = accessConn.createStatement();
            String selectSql = "SELECT TOP 10 id, name, timestamp FROM mytable ORDER BY timestamp DESC";
            rs = accessStmt.executeQuery(selectSql);

            // Loop through the result set and insert the data into PostgreSQL
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Timestamp timestamp = rs.getTimestamp("timestamp");
                postgresStmt.setInt(1, id);
                postgresStmt.setString(2, name);
                postgresStmt.setTimestamp(3, timestamp);
                postgresStmt.executeUpdate();
            }

            System.out.println("Data transfer completed successfully!");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (accessStmt != null) accessStmt.close();
                if (accessConn != null) accessConn.close();
                if (postgresStmt != null) postgresStmt.close();
                if (postgresConn != null) postgresConn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Ray Lee (System Analyst)
作者 Ray Lee (System Analyst)

iDempeire ERP Contributor, 經濟部中小企業處財務管理顧問 李寶瑞

相關文章