Java Database Connectivity (JDBC)
Java Database Connectivity (JDBC) is a Java API that allows applications
to access and manage databases. JDBC is used to connect to databases,
send queries, and retrieve results.
Key principles of JDBC
JDBC API: A set of classes and interfaces that define how Java
applications interact with databases
JDBC drivers: Connect Java applications to database systems
Connection string: A URL that loads the driver and specifies the
connection properties
Statements: Used to execute SQL or PL/SQL queries against the
database
Prepared statements: Used when an application plans to reuse a
statement multiple times
Callable statements: Used to call stored procedures that return values
Steps to connect to a database using JDBC
1. Import package dependencies
2. Load and register the driver
3. Connect to the database
4. Frame query
5. Execute query
6. Process result
7. Close statement
8. Close connection
Benefits of JDBC
Supports multiple databases, including MySQL, PostgreSQL, and Oracle
Offers features like batch processing, connection pooling, and transaction
management
Enables database operations across different platforms
JDBC (Java Database Connectivity)
JDBC (Java Database Connectivity) is an API in Java that enables applications to interact with
databases. It allows a Java program to connect to a database, execute queries, and retrieve and
manipulate data. By providing a standard interface, JDBC ensures that Java applications can work
with different relational databases like MySQL, Oracle, PostgreSQL, and more.
JDBC Architecture
Explanation:
Application: It is a Java applet or a servlet that communicates with a data source.
The JDBC API: It allows Java programs to execute SQL queries and retrieve results. Key interfaces
include Driver, ResultSet, RowSet, PreparedStatement, and Connection. Important classes include
DriverManager, Types, Blob, and Clob.
DriverManager: It plays an important role in the JDBC architecture. It uses some database-specific
drivers to effectively connect enterprise applications to databases.
JDBC drivers: These drivers handle interactions between the application and the database.
The JDBC architecture consists of two-tier and three-tier processing models to access a database.
They are as described below:
1. Two-Tier Architecture
A Java Application communicates directly with the database using a JDBC driver. Queries are sent to
the database, and results are returned directly to the application. In a client/server setup, the user’s
machine (client) communicates with a remote database server.
Structure:
Client Application (Java) -> JDBC Driver -> Database
2. Three-Tier Architecture
In this, user queries are sent to a middle-tier services, which interacts with the database. The database
results are processed by the middle tier and then sent back to the user.
Structure:
Client Application -> Application Server -> JDBC Driver -> Database
JDBC Components
There are generally 4 main components of JDBC through which it can interact with a database. They
are as mentioned below:
1. JDBC API
It provides various methods and interfaces for easy communication with the database. It includes two
key packages
java.sql: This package, is the part of Java Standard Edition (Java SE) , which contains the core
interfaces and classes for accessing and processing data in relational databases. It also provides
essential functionalities like establishing connections, executing queries, and handling result sets
javax.sql: This package is the part of Java Enterprise Edition (Java EE) , which extends the
capabilities of java.sql by offering additional features like connection pooling, statement pooling, and
data source management.
It also provides a standard to connect a database to a client application.
2. JDBC Driver Manager
Driver manager is responsible for loading the correct database-specific driver to establish a
connection with the database. It manages the available drivers and ensures the right one is used to
process user requests and interact with the database.
3. JDBC Test Suite
It is used to test the operation(such as insertion, deletion, updating) being performed by JDBC
Drivers.
4. JDBC Drivers
JDBC drivers are client-side adapters (installed on the client machine, not on the server) that convert
requests from Java programs to a protocol that the DBMS can understand. There are 4 types of JDBC
drivers:
Type-1 driver or JDBC-ODBC bridge driver
Type-2 driver or Native-API driver (partially java driver)
Type-3 driver or Network Protocol driver (fully java driver)
Type-4 driver or Thin driver (fully java driver) – It is deprecated and no longer supported since Java
8. Instead modern drivers like the Type – 4 driver are widely used.
JDBC Classes and Interfaces
Steps to Connect to MySQL Database Using JDBC
Step 1: Load the JDBC Driver
Class.forName(“com.mysql.cj.jdbc.Driver”);
Step 2: Establish a Connection
Connection connection = DriverManager.getConnection(
“jdbc:mysql://localhost:3306/your_database”,
“your_username”,
“your_password”
);
Step 3: Create a Statement
Statement statement = connection.createStatement();
Step 4: Execute a Query
String query = “INSERT INTO students (id, name) VALUES (101, ‘John Doe’)”;
int rowsAffected = statement.executeUpdate(query);
System.out.println(“Rows affected: ” + rowsAffected);
Step 5: Close the Connection
statement.close();
connection.close();
Create a Simple JDBC Application
The below Java program demonstrates how to establish a MYSQL database connection using JDBC
and execute a query.
// Java program to implement a simple JDBC application
import java.sql.*;
public class Geeks {
public static void main(String[] args)
{
// Database URL, username, and password
// Replace with your database name
String url
= "jdbc:mysql://localhost:3306/your_database";
// Replace with your MySQL username
String username = "your_username";
// Replace with your MySQL password
String password = "your_password";
// Updated query syntax for modern databases
String query
= "INSERT INTO students (id, name) VALUES (109, 'bhatt')";
// Establish JDBC Connection
try {
// Load Type-4 Driver
// MySQL Type-4 driver class
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish connection
Connection c = DriverManager.getConnection(
url, username, password);
// Create a statement
Statement st = c.createStatement();
// Execute the query
int count = st.executeUpdate(query);
System.out.println(
"Number of rows affected by this query: "+ count);
// Close the connection
st.close();
c.close();
System.out.println("Connection closed.");
}
catch (ClassNotFoundException e) {
System.err.println("JDBC Driver not found: "+ e.getMessage());
}
catch (SQLException e) {
System.err.println("SQL Error: " + e.getMessage());
}
}
}
Establishing JDBC Connection in Java
Last Updated : 10 Jan, 2025
Before Establishing a JDBC Connection in Java (the front end i.e. your Java
Program and the back end i.e. the database) we should learn what precisely a
JDBC is and why it came into existence. Now let us discuss what exactly JDBC
stands for and will ease out with the help of real-life illustration to get it
working.
What is JDBC?
JDBC stands for Java Database Connectivity. JDBC is a Standard API that
enables Java applications to interact with databases like (MYSQL, PostgreSQL,
etc). This API consists of classes and interfaces written in Java, In other words,
we can also say that JDBC acts as a bridge between your Java
application(frontend) and the database(backend), allowing you to send and
retrieve data between the two systems.
The diagram below demonstrates the workings of JDBC by co-relating its
steps to real-world examples.
Steps to Connect Java Applications with Database
Below are the steps that explains how to connect to Database in Java:
Step 1: Import the Packages
Step 2: Load the drivers using the forName() method
Step 3: Register the drivers using DriverManager
Step 4: Establish a connection using the Connection class object
Step 5: Create a statement
Step 6: Execute the query
Step 7: Close the connections
Java Database Connectivity
Let us discuss these steps in brief before implementing by writing suitable code
to illustrate connectivity steps for JDBC.
Step 1: Import the Packages
First, we need to import the packages.
Step 2: Loading the drivers
In order to begin with, you first need to load the driver or register it before using
it in the program. Registration is to be done once in your program. You can
register a driver in one of two ways mentioned below as follows:
Class.forName()
Here we load the driver’s class file into memory at the runtime. No need of using
new or create objects. The following example uses Class.forName() to load the
Oracle driver as shown below as follows:
Class.forName(“oracle.jdbc.driver.OracleDriver”);
DriverManager.registerDriver()
DriverManager is a Java inbuilt class with a static member register. Here we call
the constructor of the driver class at compile time. The following example uses
DriverManager.registerDriver()to register the Oracle driver as shown below:
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver())
Step 3: Establish a connection using the Connection class object
After loading the driver, establish connections as shown below as follows:
Connection con = DriverManager.getConnection(url,user,password)
user: Username from which your SQL command prompt can be accessed.
password: password from which the SQL command prompt can be accessed.
con: It is a reference to the Connection interface.
Url: Uniform Resource Locator which is created as shown below:
String url = “ jdbc:oracle:thin:@localhost:1521:xe”
Where oracle is the database used, thin is the driver used, @localhost is the IP
Address where a database is stored, 1521 is the port number and xe is the service
provider. All 3 parameters above are of String type and are to be declared by the
programmer before calling the function. Use of this can be referred to form the
final code.
Step 4: Create a statement
Once a connection is established you can interact with the database. The
JDBCStatement, CallableStatement, and PreparedStatement interfaces define the
methods that enable you to send SQL commands and receive data from your
database.
Use of JDBC Statement is as follows:
Statement st = con.createStatement();
Note: Here, con is a reference to Connection interface used in previous step.
Step 5: Execute the query
Now comes the most important part i.e executing the query. The query here is an
SQL Query. Now we know we can have multiple types of queries. Some of them
are as follows:
The query for updating/inserting a table in a database.
The query for retrieving data.
The executeQuery() method of the Statement interface is used to execute
queries of retrieving values from the database. This method returns the object of
ResultSet that can be used to get all the records of a table.
The executeUpdate(sql query) method of the Statement interface is used to
execute queries of updating/inserting.
int m = st.executeUpdate(sql);
if (m==1)
System.out.println(“inserted successfully : “+sql);
else
System.out.println(“insertion failed”);
on
Example: The below Java program demonstrates how to connect to a MYSQL
database, execute a Query, retrieve data and display it.
Note: Here sql is SQL query of the type String.
// This code is for establishing connection with MySQL
// database and retrieving data
// from db Java Database connectivity
/*
*1. import --->java.sql
*2. load and register the driver ---> com.jdbc.
*3. create connection
*4. create a statement
*5. execute the query
*6. process the results
*7. close
*/
import java.io.*;
import java.sql.*;
class Geeks {
public static void main(String[] args) throws Exception
{
String url = "jdbc:mysql://localhost:3306/table_name"; // table details
String username = "rootgfg"; // MySQL credentials
String password = "gfg123";
String query = "select *from students"; // query to be run
Class.forName("com.mysql.cj.jdbc.Driver"); // Driver name
Connection con = DriverManager.getConnection(url, username, password);
System.out.println("Connection Established successfully");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query); // Execute query
rs.next();
String name = rs.getString("name"); // Retrieve name from db
System.out.println(name); // Print result on console
st.close(); // close statement
con.close(); // close connection
System.out.println("Connection Closed....");
}
}
Output:
Step 6: Closing the Connections
So, finally we have sent the data to the specified location and now we are on the
verge of completing our task. By closing the connection, objects of Statement and
ResultSet will be closed automatically. The close() method of the Connection
interface is used to close the connection. It is shown below as follows:
con.close();
Example: The below Java program demonstrates how to establish a JBDC
Connection with an Oracle database.
// Java Program to Establish Connection
// in JDBC with Oracle Database
// Importing database
import java.sql.*;
// Importing required classes
import java.util.*;
// Main class
class Main {
// Main driver method
public static void main(String a[])
{
// Creating the connection using Oracle DB
// Note: url syntax is standard, so do grasp
String url = "jdbc:oracle:thin:@localhost:1521:xe";
// Username and password to access DB
// Custom initialization
String user = "system";
String pass = "12345";
// Entering the data
Scanner k = new Scanner(System.in);
System.out.println("enter name");
String name = k.next();
System.out.println("enter roll no");
int roll = k.nextInt();
System.out.println("enter class");
String cls = k.next();
// Inserting data using SQL query
String sql = "insert into student1 values('" + name+ "'," + roll + ",'" + cls + "')";
// Connection class object
Connection con = null;
// Try block to check for exceptions
try {
// Registering drivers
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
// Reference to connection interface
con = DriverManager.getConnection(url, user,pass);
// Creating a statement
Statement st = con.createStatement();
// Executing query
int m = st.executeUpdate(sql);
if (m == 1)
System.out.println("inserted successfully : " + sql);
else
System.out.println("insertion failed");
// Closing the connections
con.close();
}
// Catch block to handle exceptions
catch (Exception ex) {
// Display message when exceptions occurs
System.err.println(ex);
}
}
}
Output:
