L. K.

Gavel

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.

1
 L. K. Gavel

 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

2
 L. K. Gavel

 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:
1. Type-1 driver or JDBC-ODBC bridge driver
2. Type-2 driver or Native-API driver (partially java driver)
3. Type-3 driver or Network Protocol driver (fully java driver)
4. 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.

3
 L. K. Gavel

JDBC Classes and Interfaces

Class/Interfaces Description

Manages JDBC drivers and establishes database

DriverManager
connections.

Connection Represents a session with a specific database.

Statement Used to execute static SQL queries.

Precompiled SQL statement, used for dynamic

PreparedStatement
queries with parameters.

Used to execute stored procedures in the

CallableStatement
database.

Represents the result set of a query, allowing

ResultSet
navigation through the rows.

Handles SQL-related exceptions during database

SQLException
operations.

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”,
4
 L. K. Gavel

“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, ‘Gavel’)”;
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 SAGEMMC {

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

5
 L. K. Gavel

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());
}

6
 L. K. Gavel

catch (SQLException e) {
System.err.println("SQL Error: "
+ e.getMessage());
}
}
}

Key Features
 Platform Independence: It enables database operations across different
platforms.
 Standard API: It provides a uniform interface for various databases.
 Support for Multiple Databases: It works with popular databases like MySQL,
PostgreSQL, Oracle, etc.
 Extensibility: It offers features like batch processing, connection pooling, and
transaction management.

7
 L. K. Gavel

JDBC Drivers
Java Database Connectivity (JDBC) is an application programming interface (API) for
the Java programming language that defines how a client can access and interact with
any kind of tabular data, especially a relational database. JDBC Drivers uses JDBC
APIs which was developed by Sun Microsystem, but now this is a part of Oracle.
There are 4 types of JDBC drivers. It is part of the Java Standard Edition platform,
from Oracle Corporation. It acts as a middle-layer interface between Java applications
and databases.
The JDBC classes are contained in the Java Package java.sql and javax.sql.
JDBC helps you to write Java applications that manage these three programming
activities:
1. Connect to a data source, like a database.
2. Send queries and update statements to the database
3. Retrieve and process the results received from the database in answer to your
query

Structure of JDBC Driver

8
 L. K. Gavel

The above JDBC Driver structure illustrates the architecture of JDBC driver, where
an application interacts with the JDBC API. The API communicates with the JDBC
Driver Manager, which manages different database drivers e.g. SQL server, Oracle
to establish database connectivity.

JDBC Drivers
JDBC drivers are client-side adapters (installed on the client machine rather than
the server) that translate requests from Java programs into a protocol understood by
the DBMS. These drivers are software components that implement the interfaces in
the JDBC API, allowing Java applications to interact with a database. Sun
Microsystems (now Oracle) defines four types of JDBC drivers, which are outlined
below:
1. Type-1 driver or JDBC-ODBC bridge driver
2. Type-2 driver or Native-API driver
3. Type-3 driver or Network Protocol driver
4. Type-4 driver or Thin driver
1. JDBC-ODBC Bridge Driver – Type 1 Driver
Type-1 driver or JDBC-ODBC bridge driver uses ODBC driver to connect to the
database. The JDBC-ODBC bridge driver converts JDBC method calls into the
ODBC function calls. Type-1 driver is also called Universal driver because it can be
used to connect to any of the databases.

9
 L. K. Gavel

Advantages
 This driver software is built-in with JDK so no need to install separately.
 It is a database independent driver.
Disadvantages
 As a common driver is used in order to interact with different databases, the data
transferred through this driver is not so secured.
 The ODBC bridge driver is needed to be installed in individual client machines.
 Type-1 driver isn’t written in java, that’s why it isn’t a portable driver.
2. Native-API Driver – Type 2 Driver (Partially Java Driver)
The Native API driver uses the client -side libraries of the database. This driver
converts JDBC method calls into native calls of the database API. In order to
interact with different database, this driver needs their local API, that’s why data
transfer is much more secure as compared to type-1 driver. This driver is not fully
written in Java that is why it is also called Partially Java driver.

Advantage
 Native-API driver gives better performance than JDBC-ODBC bridge driver.
 More secure compared to the type-1 driver.
Disadvantages
 Driver needs to be installed separately in individual client machines
 The Vendor client library needs to be installed on client machine.
 Type-2 driver isn’t written in java, that’s why it isn’t a portable driver
 It is a database dependent driver.

10
 L. K. Gavel

3. Network Protocol Driver – Type 3 Driver (Fully Java Driver)

The Network Protocol driver uses middleware (application server) that converts
JDBC calls directly or indirectly into the vendor-specific database protocol. Here all
the database connectivity drivers are present in a single server, hence no need of
individual client-side installation.

Advantages
 Type-3 drivers are fully written in Java, hence they are portable drivers.
 No client side library is required because of application server that can perform
many tasks like auditing, load balancing, logging etc.
 Switch facility to switch over from one database to another database.
Disadvantages
 Network support is required on client machine.
 Maintenance of Network Protocol driver becomes costly because it requires
database-specific coding to be done in the middle tier.
4. Thin Driver – Type 4 Driver (Fully Java Driver)
Type-4 driver is also called native protocol driver. This driver interact directly with
database. It does not require any native database library, that is why it is also known
as Thin Driver.

11
 L. K. Gavel

Advantages
 Does not require any native library and Middleware server, so no client-side or
server-side installation.
 It is fully written in Java language, hence they are portable drivers.
Disadvantage
 If the database changes, a new driver may be needed.
Which Driver to use When?
 If you are accessing one type of database, such as Oracle, Sybase, or IBM, the
preferred driver type is type-4.
 If your Java application is accessing multiple types of databases at the same time,
type 3 is the preferred driver.
 Type 2 drivers are useful in situations, where a type 3 or type 4 driver is not
available yet for your database.
 The type 1 driver is not considered a deployment-level driver, and is typically
used for development and testing purposes only.

Establishing JDBC Connection in Java

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.

12
 L. K. Gavel

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

13
 L. K. Gavel

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:

14
 L. K. Gavel

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.

15
 L. K. Gavel

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.
Pseudo Code:
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

16
 L. K. Gavel

*4. create a statement

*5. execute the query
*6. process the results
*7. close
*/

import java.io.*;
import java.sql.*;

class SAGEMMC {
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....");
}
}

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:

17
 L. K. Gavel

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 + "')";

18
 L. K. Gavel

// 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);
}
}
}

19
 L. K. Gavel

Types of Statements in JDBC

In Java, the Statement interface in JDBC (Java Database Connectivity) is used to
create and execute SQL queries in Java applications. JDBC provides three types of
statements to interact with the database:
 Statement
 Prepared Statement
 Callable Statement

1. Statement
A Statement object is used for general-purpose access to databases and is useful for
executing static SQL statements at runtime.
Syntax:
Statement statement = connection.createStatement();

Implementation: Once the Statement object is created, there are three ways to
execute it.
 execute(String SQL): It is used to executes any SQL statements (like SELECT,
INSERT, UPDATE or DELETE). If the ResultSet object is retrieved, then it
returns true else false is returned.
 executeUpdate(String SQL): It is used to executes SQL statements (like
INSERT, UPDATE or DELETE). It returns the number of rows affected by the
SQL statement.
 ResultSet executeQuery(String SQL): It is used to executes the SELECT query.
It returns a ResultSet object that contains the data retrieved by the query.
Example:
// Java Program illustrating Create Statement in JDBC
import java.sql.*;

public class SAGEMMC {

20
 L. K. Gavel

public static void main(String[] args) {

try {
// Load the driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish the connection
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/world", "root", "12345");
// Create a statement
Statement st = con.createStatement();
// Execute a query
String sql = "SELECT * FROM people";
ResultSet rs = st.executeQuery(sql);
// Process the results
while (rs.next()) {
System.out.println("Name: " + rs.getString("name") +
", Age: " + rs.getInt("age"));
}

// Close resources
rs.close();
st.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

2. Prepared Statement
A PreparedStatement represents a precompiled SQL statement that can be executed
multiple times. It accepts parameterized SQL queries, with ? as placeholders for
parameters, which can be set dynamically.
Illustration:

21
 L. K. Gavel

Considering in the people database if there is a need to INSERT some values, SQL
statements such as these are used:
INSERT INTO people VALUES (“Gavel”,25);
INSERT INTO people VALUES(“LK”,32);

To do the same in Java, one may use Prepared Statements and set the values in the?
holders, setABC() of a prepared statement is used as shown:
String query = “INSERT INTO people(name, age)VALUES(?, ?)”;
PreparedStatement pstmt = con.prepareStatement(query);
// where pstmt is an object name
pstmt.setString(1,”Ayan”);
ptstmt.setInt(2,25);

Implementation: Once the PreparedStatement object is created, there are three

ways to execute it:
 execute(): This returns a boolean value and executes a static SQL statement that
is present in the prepared statement object.
 executeQuery(): This returns a ResultSet from the current prepared statement.
 executeUpdate(): This returns the number of rows affected by the DML
statements such as INSERT, DELETE, and more that is present in the current
Prepared Statement.
Example:
// Java Program illustrating Prepared Statement in JDBC
import java.sql.*;
import java.util.Scanner;
class SAGEMMC {
public static void main(String[] args) {
// try block to check for exceptions
try {
// Loading drivers using forName() method
Class.forName("com.mysql.cj.jdbc.Driver");

22
 L. K. Gavel

// Scanner class to take input from user

Scanner sc = new Scanner(System.in);
System.out.println( "What age do you want to search?? ");
// Reading age an primitive datatype from user
// using nextInt() method
int age = sc.nextInt();
// Registering drivers using DriverManager
Connection con = DriverManager.getConnection(
"jdbc:mysql:///world", "root", "12345");
// Create a statement
PreparedStatement ps = con.prepareStatement(
"select name from world.people where age = ?");
// Execute the query
ps.setInt(1, age);
ResultSet res = ps.executeQuery();
// Condition check using next() method
// to check for element
while (res.next()) {
// Print and display elements(Names)
System.out.println("Name : " + res.getString(1));
}
}
// Catch block to handle database exceptions
catch (SQLException e) {
// Display the DB exception if any
System.out.println(e);
}
// Catch block to handle class exceptions
catch (ClassNotFoundException e) {
// Print the line number where exception occurred
// using printStackTrace() method if any
e.printStackTrace();
}
}
}

23
 L. K. Gavel

3. Callable Statement
A CallableStatement is used to execute stored procedures in the database. Stored
procedures are precompiled SQL statements that can be called with parameters.
They are useful for executing complex operations that involve multiple SQL
statements.
Syntax:
CallableStatement cstmt = con.prepareCall(“{call ProcedureName(?, ?)}”);

 {call ProcedureName(?, ?)}: Calls a stored procedure named ProcedureName with

placeholders ? for input parameters.
Methods to Execute:
 execute(): Executes the stored procedure and returns a boolean indicating
whether the result is a ResultSet (true) or an update count (false).
 executeQuery(): Executes a stored procedure that returns a ResultSet.
 executeUpdate(): Executes a stored procedure that performs an update and
returns the number of rows affected.
Example:
// Java Program illustrating
// Callable Statement in JDBC
import java.sql.*;
public class SAGEMMC {
public static void main(String[] args) {
// Try block to check if any exceptions occur
try {
// Load and register the driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a connection
Connection con = DriverManager
.getConnection("jdbc:mysql:///world", "root", "12345");
// Create a CallableStatement
CallableStatement cs =

24
 L. K. Gavel

con.prepareCall("{call GetPeopleInfo()}");
// Execute the stored procedure
ResultSet res = cs.executeQuery();
// Process the results
while (res.next()) {
// Print and display elements (Name and Age)
System.out.println("Name : " + res.getString("name"));
System.out.println("Age : " + res.getInt("age"));
}
// Close resources
res.close();
cs.close();
con.close();
}
// Catch block for SQL exceptions
catch (SQLException e) {
e.printStackTrace();
}
// Catch block for ClassNotFoundException
catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}

Explanation:
 This Java code demonstrates how to use a CallableStatement in JDBC to execute a
stored procedure.
 It connects to a MySQL database and prepares a CallableStatement to call a stored
procedure named peopleinfo with two parameters.
 After executing the procedure, it runs a SELECT query to retrieve and display all
records from the people table.
 Exception handling is included to manage potential SQL and class loading errors.

25