Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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
873 Views
