DEV Community

Judy
Judy

Posted on

2 1 1 1 1

How to Execute Multi-database Queries via JDBC #eg76

Problem description & analysis

We have two MySQL databases. There is an employee table in MySQL_A, whose data is as follows:

Image description
There is a family table in MySQL_B, whose data is as follows:

Image description
We need to use JDBC to do a cross-database join query, which is to get the relationship (RELATION) between each employee (ENAME) and their corresponding family member (FNAME) by joining the two tables through EID. Below is the expected result table:

Image description
Solution:

We write the following script p1.dfx in esProc:

Image description
Code description:

A1   Connect to database MySQL_A.

A2  Connect to database MySQL_B.

A3  Read employee table from MySQL_A.

A4  Read family table from MySQL_B.

A5  Join the two tables up through EID.

A6  Get the relationship (RELATION) between each employee (ENAME) and their corresponding family member (FNAME).

See How to Call an SPL Script in Java to learn about the way of integrating the SPL code with a Java program.

Open source SPL source address

Download

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay