

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
- Related Questions & Answers
- How to write a JDBC application which connects to multiple databases simultaneously?
- How to retrieve multiple ResultSets from a stored procedure using a JDBC program?
- How to extract data from a Matplotlib plot?
- How to extract multiple integers from a String in Java?
- How to retrieve binary data from a table using JDBC?
- Python - Write multiple files data to master file
- Write a C program to read a data from file and display
- How to get the list of all databases using JDBC?
- How to extract data from a string with Python Regular Expressions?
- How to extract data from SAP using .NET provider.
- Python program to extract Keywords from a list
- How to read/retrieve data from Database to JSON using JDBC?
- How to write data into BLOB and CLOB type columns in a table using JDBC?
- How to extract data from a plot created by ggplot2 in R?
- How to extract required data from structured strings in Python?