Answers to Your Scala JDBC Cross-Database Questions #

First, let's tackle your second question directly because it's tied to a core JDBC design rule:

• Can you pass two database connections to a single getConnection() method?
  No, this isn't possible. A JDBC Connection object represents a dedicated, one-to-one session with a single database instance. The getConnection() method is built to establish and return a connection to one specific database (using its unique URL, credentials, etc.). There’s no way to merge two separate database sessions into one Connection—this goes against how JDBC handles database communication.

Now, for your first question: How to query both PostgreSQL and Oracle data using a streamlined approach (or single-connection workflow) in Scala JDBC?

You have two practical, production-ready options, depending on your infrastructure and dataset size:

Option 1: Fetch Data Separately, Join in Scala Memory

This is the simplest approach if you don’t want to modify database configurations. Pull data from each database into Scala collections, then handle the join/association in your code.

Here’s a simplified Scala example using raw JDBC:

import java.sql.{Connection, DriverManager, ResultSet}
import scala.collection.mutable.ListBuffer

// Define case classes to map your table data (adjust fields to match your schema)
case class OracleRecord(id: Int, customerName: String)
case class PostgresRecord(id: Int, orderTotal: Double)

// Helper to get Oracle connection
def getOracleConn(): Connection = {
  DriverManager.getConnection("jdbc:oracle:thin:@//oracle-host:1521/ORCL", "oracle-user", "oracle-pass")
}

// Helper to get PostgreSQL connection
def getPostgresConn(): Connection = {
  DriverManager.getConnection("jdbc:postgresql://postgres-host:5432/db-name", "pg-user", "pg-pass")
}

// Fetch data from Oracle
def fetchOracleData(): List[OracleRecord] = {
  val conn = getOracleConn()
  val stmt = conn.createStatement()
  val rs: ResultSet = stmt.executeQuery("SELECT id, customer_name FROM customers")
  
  val records = ListBuffer[OracleRecord]()
  while (rs.next()) {
    records += OracleRecord(rs.getInt("id"), rs.getString("customer_name"))
  }
  
  // Clean up resources (or use try-with-resources for auto-closing)
  rs.close()
  stmt.close()
  conn.close()
  
  records.toList
}

// Fetch data from PostgreSQL
def fetchPostgresData(): List[PostgresRecord] = {
  val conn = getPostgresConn()
  val stmt = conn.createStatement()
  val rs: ResultSet = stmt.executeQuery("SELECT id, order_total FROM orders")
  
  val records = ListBuffer[PostgresRecord]()
  while (rs.next()) {
    records += PostgresRecord(rs.getInt("id"), rs.getDouble("order_total"))
  }
  
  rs.close()
  stmt.close()
  conn.close()
  
  records.toList
}

// Perform the join in Scala
val oracleData = fetchOracleData()
val postgresData = fetchPostgresData()

val joinedResults = for {
  oracleRow <- oracleData
  postgresRow <- postgresData if oracleRow.id == postgresRow.id
} yield (oracleRow.customerName, postgresRow.orderTotal)

// Use the joined results as needed

Option 2: Database-Level Federation (Single Connection Query)

If you want to handle the join at the database level (more efficient for large datasets), set up a cross-database link so one database can query the other. Then you only need a single connection to run the joined query.

If using Oracle as the primary database:

1. Create a DBLink from Oracle to PostgreSQL (you’ll need the PostgreSQL JDBC driver installed in Oracle’s environment):

   CREATE DATABASE LINK postgres_link
   CONNECT TO pg-user IDENTIFIED BY pg-pass
   USING 'jdbc:postgresql://postgres-host:5432/db-name';
   

2. Run a cross-database join query using only your Oracle connection:

   SELECT c.customer_name, o.order_total
   FROM customers c
   JOIN orders@postgres_link o ON c.id = o.id
   

   In your Scala code, use the Oracle Connection to execute this query—your ResultSet will return the pre-joined data.

If using PostgreSQL as the primary database:

1. Install the oracle_fdw foreign data wrapper extension in PostgreSQL.
2. Create a server and user mapping to Oracle:

   CREATE EXTENSION oracle_fdw;
   CREATE SERVER oracle_server
   FOREIGN DATA WRAPPER oracle_fdw
   OPTIONS (dbserver '//oracle-host:1521/ORCL');
   
   CREATE USER MAPPING FOR pg-user
   SERVER oracle_server
   OPTIONS (user 'oracle-user', password 'oracle-pass');
   

3. Import the Oracle table as a foreign table in PostgreSQL:

   CREATE FOREIGN TABLE customers (
     id INT,
     customer_name VARCHAR(100)
   ) SERVER oracle_server
   OPTIONS (table 'CUSTOMERS');
   

4. Run a joined query using only your PostgreSQL connection:

   SELECT c.customer_name, o.order_total
   FROM customers c
   JOIN orders o ON c.id = o.id
   

Choose the option that fits your infrastructure, dataset size, and performance needs—both will resolve your cross-database query issue.

内容的提问来源于stack exchange，提问作者anonymous