What is JDBC?
JDBC (Java Database Connectivity) is a Sun
Microsystems specification. It is the Java API that is
responsible for connecting to a database, issuing queries
and commands, and processing database result sets. To
access spread sheets and databases, JDBC and database
drivers operate together. The components of JDBC that
are utilized to connect to the database are defined by the
design of JDBC. The JDBC API classes and interfaces
enable an application to send a request to a specific
database.
Why JDBC?
Before it was created, we utilized the ODBC API
database to connect to the database and run queries
against it. The ODBC API, on the other hand, uses the
ODBC drive in C. Furthermore, it is platform-
dependent and unprotected. This is why Java created
the JDBC API, which uses JDBC drivers and is written
in the Java programming language.
Applications of JDBC
JDBC enables you to create Java applications that
handle the following three programming tasks:
Make a connection to a data source, such as a
database.
Send database queries and update statements.
Retrieve and process the database results that were
returned in response to your query.
Let’s discuss a real-world example that uses JDBC.
When you search for a movie on a specific date, the
database retrieves the number of tickets available on
that day, and if you purchase a ticket, the database is
updated accordingly.
In addition to this domain, JDBC is used in a variety of
other fields such as banking, reservation systems, online
retail websites, government portals, and so on.
It is used in almost every Java programme that connects
to an SQL database management system. Java
developers will almost probably utilize JDBC at the
lowest level, regardless of any higher-level libraries,
frameworks, object-relational mappers, or database-
access layers they use.
JDBC Architecture and Components
There are two architectures of JDBC:
Two-Tier Architecture
A Java applet and application communicates directly
with the data source in the two-tier paradigm. This
necessitates the use of a JDBC driver that can interface
with the data source in question. The user’s commands
are transmitted to the database or other data source, and
the statements’ results are returned to the user. The data
source could be on another machine to which the user
has a network connection. A client/server configuration
is one in which the user’s machine acts as the client and
the system that houses the data source acts as the server.
An intranet, for example, can connect people within a
company, or the Internet can be used as a network.
Three-Tier Architecture
Commands are sent to a “middle tier” of services in the
three-tier paradigm, which subsequently transmits the
commands to the data source. The data source interprets
the commands and provides the results to the middle
tier, which ultimately passes them on to the user. The
three-tier architecture appeals to MIS directors because
the intermediate tier allows them to maintain control
over access and the types of changes that can be made
to company data. Another benefit is that it makes
application deployment easier. Finally, the three-tier
architecture can bring performance benefits in many
circumstances.
The components of JDBC are listed below. These
elements assist us in interacting with a database. The
following are the JDBC components:
1. JDBC Driver Manager: In a JDBC application,
the Driver Manager loads database-specific drivers.
This driver manager makes a database connection.
To handle the user request, it additionally makes a
database-specific call to the database.
2. Driver: A driver is an interface that manages
database server connectivity. Communication is
handled using DriverManager objects.
3. JDBC-ODBC Bridge Drivers: They are used to
link database drivers to the database. The JDBC
method calls are translated into ODBC method
calls by the bridge. To access the ODBC (Open
Database Connectivity) characteristics, it uses the
[Link] package, which includes the native
library.
4. JDBC API: Sun Microsystem has provided JDBC
API, which allows you to write a Java program that
talks with any database without modifying the
code. The JDBC API is implemented by the JDBC
Driver.
5. JDBC Test Suite: The JDBC Test Suite aids in the
testing of JDBC Driver operations such as
insertion, deletion, and updating. It aids in
determining whether or not the JDBC Drivers will
run the program. It ensures that the program will be
run by JDBC Drivers with confidence and
conformity.
6. Database Server: This is the database server that
the JDBC client wants to communicate with, such
as Oracle, MySQL, SQL Server, and so on.
7. Statement: To send SQL statements to the
database, you use objects built using this interface.
In addition to performing stored procedures,
certainly derived interfaces accept parameters.
8. RuleSet: These objects retain data retrieved from a
database when you use Statement objects to
conduct a SQL query. It functions as an iterator,
allowing you to cycle through the data it contains.
9. SQL Exception: This class is responsible for any
errors that occur in a database application.
Features of JDBC 4.0
Autoloading of Driver by JVM: We used to load
the driver with the [Link]() function in
previous versions. This is no longer necessary, as
the DriverManager class’s getConnection() method
can now load the proper driver.
Standard Connection Factory Management: To
build the connection before, we needed a data
source URL. However, you may now supply data
source settings to the connection factory, and it will
generate the data source for you.
New interface RowID to support the ROWID
data type: JDBC 4.0 included a new interface
RowId for databases that support the ROWID
datatype, such as Oracle.
New Exception classes: Exception Handling
classes, which are subclasses of SQLException and
handle transient and non-transient exceptions, were
added to JDBC 4.0. To iterate over-created
exceptions, SQLException added a new ‘for each’
loop functionality.
Enhanced Connection and Statement
Interface: To accommodate the new features, Jdbc
4.0 included new methods. The Connection
interface, for example, has methods to acquire and
set driver-supported client information, as well as
the isValid method to check whether the
Connection state is valid. The isClosed method of
the Statement interface is used to determine
whether or not the connection is closed. These
interfaces now have a slew of new methods.
XML support: To handle the XML database data
type, JDBC provides the [Link]
interface. The XML type is used to store XML
values in a row as column values. It has numerous
ways of getting XML values in the form of a
String, Reader or Writer, or Stream. DOM, SAX,
and other tools can parse XML data.
National Character Set Conversion Support: To
handle the National Character Set, Jdbc 4.0
includes additional data types and functions.
NCHAR, NVARCHAR, LONG VARCHAR, and
NCLOB data types, as well as methods like
setNString(), getNClob(), updateNClob(), and
others, have been added.
Enhanced support for BLOBs and CLOBs: Jdbc
4.0 introduced additional ways for dealing with
huge objects such as BLOB and CLOB. The
createBlob(), createClob(), and createNClob()
functions, for example, can be used to make
BLOB, CLOB, and NCLOB objects.
Advantages of JDBC Architecture
It can read any database. The only condition for it
to do so is that all of the drivers be properly
installed.
It pulls information from a database and converts it
to XML.
It does not necessitate the conversion of the
content.
Software maintenance is centralized with no client
settings necessary. Because the driver is built in
Java, the JDBC URL or a DataSource object has all
of the information required to establish a
connection.
It supports queries and stored procedures
completely.
The JDBC API contains a DataSource object that
can be used to identify and connect to a data
source. This improves the code’s portability and
maintainability.
Both synchronous and asynchronous processing is
supported.
The Java API and the JDBC API work together to
make application development simple and cost-
effective.
Modules are supported.
Even if data is housed on various database
management systems, businesses can continue to
use their installed databases and access
information.
Conclusion
Now that you have learned about various aspects of
JDBC, you must have got an idea of its importance.
Many companies use it to develop their software and
for this, they require effective candidates who have
good knowledge of JDBC.
JDBC DRIVERS
JDBC drivers are software components that enable
Java applications to communicate with different types
of databases. Each database (like MySQL, Oracle, or
PostgreSQL) requires a specific JDBC driver that
translates Java JDBC calls into the database-specific
protocol.
The JDBC classes are contained in the Java packages
[Link] and [Link].
JDBC allows Java applications to perform the
following
1. Connect to a data source (e.g., MySQL,
PostgreSQL).
2. Send SQL queries and update statements to the
database.
3. Retrieve and process results from the database.
Structure of JDBC Driver
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 [Link] Driver Structure
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.
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.
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.
Easy to switch databases
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.
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.
When to Use Which Driver?
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.
CRUD OPERATIONS
CRUD operations are a common task in many
applications and JDBC (Java Database Connectivity) is
a Java API that allows you to connect to a database and
perform these operations. We will guide you through
the steps of setting up a simple CRUD (Create, Read,
Update, Delete) operation using JDBC.
What are CRUD Operations
CRUD stands for:
C (Create) -> Insert new records into the database.
R (Read) -> Retrieve records from the database.
U (Update) -> Modify existing records.
D (Delete) -> Remove records from the database.
Prerequisites:
MySQL Database installed.
MySQL Connector jar.
CRUD Operations in JDBC with MySQL
Step 1. Database Setup in MySQL
In this step, we create a database and a table where
student records will be stored.
Run the following commands in MySQL Workbench
/ CLI:
CREATE DATABASE studentdb;
USE studentdb;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT NOT NULL,
email VARCHAR(150) UNIQUE
);
Step 2. Dependency for MySQL
We add the MySQL connector dependency in
[Link] so Java can talk to MySQL.
This downloads the driver automatically from Maven
Central.
[Link]:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>9.0.0</version>
</dependency>
Step 3. Create JDBC connection
This utility class sets up a connection with MySQL
using JDBC.
Change username and password as per your local
MySQL setup.
import [Link];
import [Link];
public class Geeks{
private static final String URL =
"jdbc:mysql://localhost:3306/studentdb";
private static final String USER = "root"; // change
if different
private static final String PASSWORD =
"yourpassword"; // change to your MySQL password
public static Connection getConnection() {
try {
return [Link](URL,
USER, PASSWORD);
} catch (Exception e) {
[Link]();
return null;
}
}
}
Step 4. Create Operation (Insert Student)
Use INSERT query to add new student records into
the database.
We use PreparedStatement to prevent SQL Injection.
This will add new student records into the database.
import [Link];
import [Link];
public class CreateStudent {
public static void insertStudent(String name, int age,
String email) {
String sql = "INSERT INTO students (name, age,
email) VALUES (?, ?, ?)";
try (Connection conn = [Link]();
PreparedStatement stmt =
[Link](sql)) {
[Link](1, name);
[Link](2, age);
[Link](3, email);
int rows = [Link]();
[Link](rows + " student inserted
successfully.");
} catch (Exception e) {
[Link]();
}
}
}
Step 5 Read Operation (Fetch Students)
Use SELECT query to retrieve student details from
the table.
Results are stored in a ResultSet which we loop
through to display records.
import [Link];
import [Link];
import [Link];
public class ReadStudent {
public static void getAllStudents() {
String sql = "SELECT * FROM students";
try (Connection conn = [Link]();
Statement stmt = [Link]();
ResultSet rs = [Link](sql)) {
while ([Link]()) {
[Link]([Link]("id") + " | " +
[Link]("name") + " | " +
[Link]("age") + " | " +
[Link]("email"));
}
} catch (Exception e) {
[Link]();
}
}
}
Step 6. Update Operation (Modify Student Email)
Use UPDATE query to change existing student
details.
Here we update a student’s email by using their id.
import [Link];
import [Link];
public class UpdateStudent {
public static void updateStudent(int id, String
newEmail) {
String sql = "UPDATE students SET email = ?
WHERE id = ?";
try (Connection conn = [Link]();
PreparedStatement stmt =
[Link](sql)) {
[Link](1, newEmail);
[Link](2, id);
int rows = [Link]();
[Link](rows + " student updated
successfully.");
} catch (Exception e) {
[Link]();
}
}
}
Step 7. Delete Operation (Remove Student)
Use DELETE query to remove student records by
ID.
Once deleted, the record will no longer appear in the
table.
import [Link];
import [Link];
public class DeleteStudent {
public static void deleteStudent(int id) {
String sql = "DELETE FROM students WHERE id
= ?";
try (Connection conn = [Link]();
PreparedStatement stmt =
[Link](sql)) {
[Link](1, id);
int rows = [Link]();
[Link](rows + " student deleted
successfully.");
} catch (Exception e) {
[Link]();
}
}
}
Step 8. Main Class (Testing All CRUD Operations)
This is the driver class where we test all four
operations step by step.
Insert -> Display -> Update -> Delete -> Display
again.
public class MainApp {
public static void main(String[] args) {
// CREATE
[Link]("Alice", 22,
"alice@[Link]");
[Link]("Bob", 23,
"bob@[Link]");
// READ
[Link]("\nAll Students:");
[Link]();
// UPDATE
[Link](1,
"alice_new@[Link]");
// DELETE
[Link](2);
// READ again
[Link]("\nAfter Update/Delete:");
[Link]();
}
}
Step 9. Run Application
Run the MainApp class and check the output in
IntelliJ console.
You should see the effect of each operation one after
the other.
Output: