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
- Install the JDBC driver for PostgreSQL in your Java environment.
- Set up the ODBC connection to the Access database and the PostgreSQL database connection details in the program.
- Modify the SQL statements in the program to match the table structures and column names of the Access and PostgreSQL tables.
- Compile the program and run it using the command
java Main. - 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();
}
}
}
}