Chapter Two: Java Database Connectivity (JDBC)
CHAPTER TWO: JAVA DATABASE CONNECTIVITY (JDBC)
2.1 Introduction
2.1.1 What is JDBC?
JDBC (Java Database Connectivity) is the standard Java API that allows Java programs to interact with relational databases in a database-independent manner.
It is essential for data-driven Java applications, including banking systems, university portals, and enterprise web applications.
Key Functions of JDBC:
Connect to relational databases
Execute SQL statements
Retrieve and manipulate data
2.1.2 Key Features of JDBC
Database independence
Standard SQL support
Strong exception handling
Transaction management
Secure data access
2.2 JDBC Architecture
2.2.1 The Layers of JDBC Architecture
The architecture is a layered model consisting of two main layers: the JDBC API and the JDBC Driver API.
Core components of the JDBC architecture include:
Java Application:
This is the top layer where the user’s Java program (e.g., application, applet, servlet) makes database requests.
JDBC API:
Provides standard interfaces and classes (Connection, Statement, ResultSet) located in the java.sql and javax.sql packages, abstracting the underlying database specifics.
JDBC Driver Manager:
Responsible for loading the correct database-specific drivers and establishing connections with the database. Facilitates the process with the
getConnection()method.JDBC Driver:
A vendor-specific software implementation acting as a bridge between the Java application's JDBC calls and the database's native protocol.
Database:
This is the actual relational database (e.g., Oracle, MySQL, SQL Server, PostgreSQL, MongoDB, SQLite) where data is stored and retrieved.
2.2.2 The JDBC Architecture Workflow
The general workflow follows these steps:
Load the necessary JDBC Driver using the Driver Manager.
Request a Connection object from the Driver Manager using a database URL and credentials.
Use Statement or PreparedStatement objects to send SQL queries to the database via the driver.
The JDBC Driver translates JDBC calls into the database's native protocol commands.
The database executes the commands and returns results.
The JDBC Driver translates the results back into standard Java objects, such as ResultSet.
Process data from the ResultSet and close all JDBC resources (Connection, Statement, ResultSet) to free up system resources.
2.2.3 Types of JDBC Drivers
There are four categories based on how they interact with the database:
Type 1: JDBC-ODBC Bridge Driver
Uses the existing Open Database Connectivity (ODBC) driver to connect. It's now deprecated.
Type 2: Native-API Driver (Partially Java)
Converts JDBC calls into database native API calls and requires native libraries on the client machine (platform-dependent).
Type 3: Network Protocol Driver (Fully Java)
Uses a pure Java network protocol to communicate with a middleware server that translates requests into database-specific calls.
Type 4: Thin Driver (Pure Java)
The most widely used type, written entirely in Java, directly communicates with the database using the native protocol.
2.2.4 Essential Interfaces and Classes
Driver Manager Class: Manages JDBC drivers and establishes the database connection.
Connection Interface: Represents an active connection to the database.
Statement Interface: Used to execute basic, static SQL queries.
PreparedStatement Interface: Used for precompiled, parameterized queries improving performance and preventing SQL injection.
CallableStatement Interface: Used for executing stored procedures in the database.
ResultSet Interface: An iterator holding the data retrieved from a database query.
2.3 Connecting Java to a Relational Database (PostgreSQL)
Required Steps
Add the PostgreSQL JDBC driver to the class path.
Load the driver (optional in modern Java).
Create a connection.
Execute SQL.
Close resources.
2.3.1 Example 2.1: Connecting to PostgreSQL
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/futo";
String user = "postgres";
String password = "admin";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
System.out.println("Connected to PostgreSQL successfully!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.4 Executing SQL Queries (CRUD Operations)
CRUD stands for Create, Read, Update, and Delete operations.
SQL Operations for CRUD
CREATE (INSERT): Used to insert new records.
READ (SELECT): Used to retrieve records.
UPDATE (UPDATE): Used to modify records.
DELETE (DELETE): Used to remove records.
Examples of SQL Operations
Example 2.2: INSERT
String sql = "INSERT INTO students(name, department) VALUES('James', 'Computer Science')";
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate(sql);
System.out.println(rows + " record inserted");
Example 2.3: SELECT
String sql = "SELECT * FROM students";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt("id") + " " + rs.getString("name"));
}
Example 2.4: UPDATE
String sql = "UPDATE students SET department='Software Engineering' WHERE id=1";
stmt.executeUpdate(sql);
Example 2.5: DELETE
String sql = "DELETE FROM students WHERE id=1";
stmt.executeUpdate(sql);
2.5 Processing ResultSet Objects
Processing ResultSet involves retrieving and navigating the data returned from an SQL query using the ResultSet object.
To iterate through the rows, methods such as
next()can be used, and column values accessed using getter methods likegetInt(),getString(), orgetDouble().
Common ResultSet Methods
Method | Description |
|---|---|
next() | Moves the cursor to the next row |
getInt() | Retrieves integer value |
getString() | Retrieves string value |
getDouble() | Retrieves double value |
Example 2.6: Processing data using ResultSet object
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String dept = rs.getString("department");
System.out.println(id + " " + name + " " + dept);
}
2.6 Prepared Statements (Secure & Efficient)
Prepared Statements are precompiled SQL statements using placeholders (?) for parameters, which improve performance, enhance security, and allow for executing the same SQL statement multiple times with different values