Java code sample to acquire and display DBLINK source and destination data in Oracle Database using DBLINK

This is a sample Java code that retrieves and displays the DBLINK source and destination data in an Oracle Database using DBLINK. When I was asked "Is there a source (source)?" In a certain question in a certain chat, it became "Gununu.", So I wrote it. 彡 (゜) (゜)

1. Environment and configuration

I tried to verify with the following configuration.

Java → (JDBC) → User_A → (DBLINK) → User_B

The environment uses the following environment of Virtualbox. It's easy to set up because it's included from the beginning. 彡 (゜) (゜)

Effortlessly build an Oracle DB 19c environment with OTN's VirtualBox image https://qiita.com/ora_gonsuke777/items/b41f37637e59319796b4

2. Create User_A and insert data

First, create User_A and insert the data.

export ORACLE_HOME=/u01/app/oracle/product/version/db_1
export PATH=${PATH}:${ORACLE_HOME}/bin

sqlplus /nolog

CONNECT SYS/oracle@ORCL AS SYSDBA

CREATE USER USER_A IDENTIFIED BY USER_A
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT CREATE SESSION TO USER_A;
GRANT CREATE TABLE TO USER_A;
ALTER USER USER_A QUOTA UNLIMITED ON USERS;

CONNECT USER_A/xxxxxxxx@orcl;

CREATE TABLE TBL_A(
  COL1 NUMBER
);
INSERT INTO TBL_A VALUES(100);
COMMIT;


Connected.

User created.

Grant succeeded.

Grant succeeded.

User altered.

Connected.

Table created.

1 row created.

Commit complete.

3. Create User_B and insert data

Then create User_B and insert the data. What to do is with User_A 彡 (ﾟ) (ﾟ)

export ORACLE_HOME=/u01/app/oracle/product/version/db_1
export PATH=${PATH}:${ORACLE_HOME}/bin

sqlplus /nolog

CONNECT SYS/oracle@ORCL AS SYSDBA

CREATE USER USER_B IDENTIFIED BY USER_B
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT CREATE SESSION TO USER_B;
GRANT CREATE TABLE TO USER_B;
ALTER USER USER_B QUOTA UNLIMITED ON USERS;

CONNECT USER_B/xxxxxxxx@orcl;

CREATE TABLE TBL_B(
  COL1 NUMBER
);
INSERT INTO TBL_B VALUES(200);
COMMIT;


Connected.

User created.

Grant succeeded.

Grant succeeded.

User altered.

Connected.

Table created.

1 row created.

Commit complete.

4. DBLINK creation and operation check

Create DBLINK in User_A and check the operation. You can see that the TBL_B created for User_B can only be referenced via DBLINK.

export ORACLE_HOME=/u01/app/oracle/product/version/db_1
export PATH=${PATH}:${ORACLE_HOME}/bin

sqlplus /nolog

CONNECT SYS/oracle@ORCL AS SYSDBA

GRANT CREATE DATABASE LINK TO USER_A;

CONNECT USER_A/xxxxxxxx@orcl;

CREATE DATABASE LINK DBL_USER_B
CONNECT TO USER_B IDENTIFIED BY xxxxxxxx
USING 'ORCL';

SELECT * FROM TBL_A;

SELECT * FROM TBL_B;

SELECT * FROM USER_B.TBL_B;

SELECT * FROM TBL_B@DBL_USER_B;

Connected.

Grant succeeded.

Connected.

Database link created.

      COL1
----------
       100

              *
ERROR at line 1:
ORA-00942: table or view does not exist

                     *
ERROR at line 1:
ORA-00942: table or view does not exist

      COL1
----------
       200

5. Java code sample

This is a sample that connects with User_A and SELECTs and displays TBL_A and TBL_B (via DBLINK) respectively.

import java.sql.*;

public class GetDblinkData {
    public static void main(String[] args) {
        final String path = "jdbc:oracle:thin:@localhost:1521/orcl";
        final String id = "USER_A";  //ID
        final String pw = "xxxxxxxx";  //password

        try (
            Connection conn  = DriverManager.getConnection(path, id, pw);
            Statement  stmt  = conn.createStatement();
            ResultSet  rs    = stmt.executeQuery("SELECT COL1 FROM TBL_A");
            Statement  stmt2 = conn.createStatement();
            ResultSet  rs2   = stmt2.executeQuery("SELECT COL1 FROM TBL_B@DBL_USER_B");
        ) {
            while (rs.next()) {
                int i = rs.getInt("COL1");
                System.out.println("TBL_A COL1 => " + i);
            }
            while (rs2.next()) {
                int j = rs2.getInt("COL1");
                System.out.println("TBL_B COL1 => " + j);
            }
        } catch(SQLException ex) {
            ex.printStackTrace();  //Error
        }
    }
}

5. Compile and run

Well, finally compile and run. When you execute the following command ... 彡 (ﾟ) (ﾟ)

javac GetDblinkData.java

java -classpath /u01/app/oracle/product/version/db_1/jdbc/lib/ojdbc8.jar:. GetDblinkData

TBL_A COL1 => 100
TBL_B COL1 => 200

The DBLINK original data (TBL_A) and DBLINK destination data (TBL_B) could be acquired and displayed! 彡 (^) (^)

6. Summary

I was able to confirm that the data of the DBLINK source and the DBLINK destination can be acquired with a single DB connection. However, DBLINK is a double-edged sword. It's a tightly coupled mechanism, so don't abuse it. 彡 (゜) (゜)