How to write a JDBC program to extract data from multiple databases?

To connect with a data base, you need to

Register the Driver

Select the required database register the Driver class of the particular database using the registerDriver() method of the DriverManager class or, the forName() method of the class named Class.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Get connection

Create a connection object by passing the URL of the database, username and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class.

Connection mysqlCon = DriverManager.getConnection(mysqlUrl, "root", "password");

And, to extract data you need to execute the select query as:

ResultSet rs = stmt.executeQuery("Select * from Employee");

To print the contents of the Result object you need to use the following methods of the ResultSet interface:

next()

This method returns a boolean value which is, true when the result set object contains more rows, false when it doesn’t.

getXXX()

These methods are used to retrieve the column values in each row. If a particular column is of integer type you need to use the method getInt(), and if it is of String type you need to use method getString().

//Executing the query
ResultSet rs = stmt.executeQuery("Select *from Employee");

while(rs.next()) {
   System.out.print("Name: "+rs.getString("Name")+", ");
   System.out.print("Salary: "+rs.getInt("Salary")+", ");
   System.out.print("City: "+rs.getString("Location"));
   System.out.println();
}

Example

Suppose we have a table name Student in the Oracle database as shown below:

NAME    AGE  PERCENTAGE
--------------------------
Raju     19    85
Raja     17    67
Mukthar  18    79
David    19    90

And a table named Employee in MySQL database as shown below:

+---------+--------+----------------+
| Name    | Salary | Location       |
+---------+--------+----------------+
| Amit    | 30000  | Hyderabad      |
| Kalyan  | 40000  | Vishakhapatnam |
| Renuka  | 50000  | Delhi          |
| Archana | 15000  | Mumbai         |
+---------+--------+----------------+

Following the JDBC program extracts contents of these two tables.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Multiple_DBs {
   public Connection connectToOracle() throws SQLException{
      //Registering the Driver
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      //Getting the connection
      String oracleUrl = "jdbc:oracle:thin:@localhost:1521/xe";
      Connection oracleCon = DriverManager.getConnection(oracleUrl, "system", "password");
      System.out.println("Connected to Oracle database.....");
      return oracleCon;
   }

   public Connection connectToMySQL() throws SQLException{
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/testdb";
      Connection mySqlCon = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connected to MySQL database......");
      return mySqlCon;
   }

   public void ExtractDataFromMySQL(Connection con) throws SQLException {
      //Creating the Statement
      Statement stmt = con.createStatement();
      //Executing the query
      ResultSet rs = stmt.executeQuery("Select *from Employee");
      System.out.println("Contents of Employee table in MySQL database: ");
      while(rs.next()) {
         System.out.print("Name: "+rs.getString("Name")+", ");
         System.out.print("Salary: "+rs.getInt("Salary")+", ");
         System.out.print("City: "+rs.getString("Location"));
         System.out.println();
      }
      System.out.println();
   }
   public void ExtractDataFromOracle(Connection con) throws SQLException {
      //Creating the Statement
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("Select *from Student");
      System.out.println("Contents of student table in Oracle database: ");
      while(rs.next()) {
         System.out.print("Name: "+rs.getString("Name")+", ");
         System.out.print("Age: "+rs.getInt("Age")+", ");
         System.out.print("Percentage: "+rs.getString("Percentage"));
         System.out.println();
      }
      System.out.println();
   }

   public static void main(String[] args) throws Exception {
      Multiple_DBs obj = new Multiple_DBs();
      //Connecting to Oracle
      Connection oracleCon = obj.connectToOracle();
      //Extracting data from Oracle
      obj.ExtractDataFromOracle(oracleCon);
      //Connecting to MySQL
      Connection msqlCon = obj.connectToMySQL();
      //Extracting data from MySQL
      obj.ExtractDataFromMySQL(msqlCon);
   }
}

Output

Connected to Oracle database.....
Contents of student table in Oracle database:
Name: Raju, Age: 19, Percentage: 85
Name: Raja, Age: 17, Percentage: 67
Name: Mukthar, Age: 18, Percentage: 79
Name: David, Age: 19, Percentage: 90
Connected to MySQL database......
Contents of Employee table in MySQL database:
Name: Amit, Salary: 30000, City: Hyderabad
Name: Kalyan, Salary: 40000, City: Vishakhapatnam
Name: Renuka, Salary: 50000, City: Delhi
Name: Archana, Salary: 15000, City: Mumbai

Daniol Thomas

Published on 20-Mar-2019 18:17:13