Unit VI: Interacting with Database
This unit covers key concepts related to connecting and interacting with databases using Java. It
focuses on JDBC (Java Database Connectivity) and ODBC (Open Database Connectivity), their
architecture, types of JDBC drivers, and several core interfaces used in database programming.
6.1 Introduction to JDBC and ODBC
ODBC (Open Database Connectivity): ODBC is a standard API for accessing database
management systems (DBMS). It allows different applications to interact with different types
of databases using a common interface. It works by using database drivers for specific
databases. Although ODBC provides database independence, it is primarily used in languages
like C and C++.
JDBC (Java Database Connectivity): JDBC is a Java-based API that allows Java applications to
interact with databases. Unlike ODBC, which is language-independent, JDBC is specifically
designed for Java. It provides a set of interfaces and classes that enable Java applications to
connect to relational databases, execute queries, and retrieve results. JDBC abstracts the
complex database interactions and makes it easy to work with databases.
6.2 JDBC Architecture: Two-Tier and Three-Tier Models
Two-Tier Model: In a two-tier architecture, the client application interacts directly with the
database. The client sends SQL queries to the database server, which processes them and
sends back the results. The two-tier architecture is simple and suitable for smaller
applications where the database and the application reside on the same machine or a direct
connection can be established between the client and the database server.
Components:
o Client: The application that connects directly to the database.
o Database Server: The server that processes queries and manages the database.
Advantages:
o Simplicity and ease of implementation.
o Direct communication between the client and the database.
Disadvantages:
o Scalability issues when the number of clients increases.
o Maintenance challenges due to tight coupling between the client and the database.
Three-Tier Model: The three-tier architecture adds an intermediary layer between the client
and the database. This layer, often referred to as the middle layer or application server,
manages the business logic and interactions with the database. In this model, the client
communicates with the application server, which then communicates with the database. This
model is more scalable and flexible.
Components:
o Client: The user interface or application that interacts with the application server.
o Application Server: The middle tier that processes business logic, handles user
requests, and interacts with the database.
o Database Server: The backend database server that stores data and processes SQL
queries.
Advantages:
o Scalability: The application server can handle multiple clients efficiently.
o Better maintainability as the client and database are decoupled.
Disadvantages:
o Increased complexity and cost due to the additional application server layer.
6.3 Types of JDBC Drivers
There are four types of JDBC drivers, each with its own use case and performance characteristics.
1. JDBC-ODBC Bridge Driver (Type 1):
o This driver uses ODBC to connect to the database. It acts as a bridge between JDBC
and ODBC.
o Advantages: It can connect to any database that provides an ODBC driver.
o Disadvantages: It is slower than other types because it involves an extra layer of
translation between JDBC and ODBC.
2. Native-API Driver (Type 2):
o This driver uses a native database API to connect directly to the database. It converts
JDBC calls into database-specific calls.
o Advantages: It is faster than the Type 1 driver as it does not involve ODBC.
o Disadvantages: It is not database-independent; each database requires its own
driver.
3. Network Protocol Driver (Type 3):
o This driver uses a middleware server to connect to the database. The client sends
JDBC calls to the middleware, which then communicates with the database.
o Advantages: It is database-independent, as the middleware server can handle
different types of databases.
o Disadvantages: Performance can be slower because of the additional layer
(middleware).
4. Thin Driver (Type 4):
o This driver communicates directly with the database using the database's native
protocol. It does not require any native database client or middleware.
o Advantages: It is the fastest and most efficient driver. It is database-independent and
platform-independent.
o Disadvantages: Requires specific drivers for each database type.
6.4 Key JDBC Classes and Interfaces
1. Class Class:
o The Class class is part of the Java Reflection API. It is used to load database drivers
dynamically at runtime. You can use the Class.forName() method to load a specific
JDBC driver class.
2. Class.forName("com.mysql.cj.jdbc.Driver");
This code loads the MySQL JDBC driver dynamically.
3. DriverManager Class:
o The DriverManager class manages a list of database drivers. It is responsible for
selecting the appropriate driver when a connection is requested.
o It maintains a list of available drivers and provides methods to establish database
connections.
o Example:
o Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "username",
"password");
4. Connection Interface:
o The Connection interface represents a connection to the database. It provides
methods for creating statements, managing transactions, and closing the
connection.
o Methods:
createStatement(): Creates a Statement object.
prepareStatement(String sql): Creates a PreparedStatement object.
setAutoCommit(boolean autoCommit): Controls whether the connection
should commit transactions automatically.
close(): Closes the connection.
5. Statement Interface:
o The Statement interface is used for executing SQL queries against the database. It
provides methods to execute SQL statements and retrieve results.
o Methods:
executeQuery(String sql): Executes a SELECT query and returns a ResultSet.
executeUpdate(String sql): Executes an update, insert, or delete query and
returns the number of rows affected.
execute(String sql): Executes any SQL query (SELECT, INSERT, UPDATE,
DELETE).
6. PreparedStatement Interface:
o The PreparedStatement interface is used for executing precompiled SQL queries. It
allows you to set input parameters dynamically before executing the query.
o Advantages:
Prevents SQL injection by separating SQL code from data.
Improves performance because the query is precompiled.
o Example:
o PreparedStatement stmt = conn.prepareStatement("SELECT * FROM students
WHERE age > ?");
o stmt.setInt(1, 20);
o ResultSet rs = stmt.executeQuery();
7. ResultSet Interface:
o The ResultSet interface represents the result set of a query. It allows you to iterate
over the result and retrieve data from it.
o Methods:
next(): Moves the cursor to the next row in the result set.
getString(String columnName): Retrieves the value of a column as a string.
getInt(String columnName): Retrieves the value of a column as an integer.
Example Code for JDBC Operations
import java.sql.*;
public class JDBCExample {
public static void main(String[] args) {
try {
// Load the JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a connection to the database
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname",
"username", "password");
// Create a statement
Statement stmt = conn.createStatement();
// Execute a query
ResultSet rs = stmt.executeQuery("SELECT * FROM students");
// Process the result
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name"));
// Close the connection
conn.close();
} catch (Exception e) {
e.printStackTrace();
