Introduction to Java EE

Web Component
UNIT-II
JDBC
JDBC (Java Database Connectivity) is a Java-based API (Application
Programming Interface) that allows Java applications to interact with relational
databases. JDBC provides a standard interface for connecting to databases,
executing SQL queries, and retrieving data.
JDBC Architecture
The JDBC (Java Database Connectivity)
architecture is a framework that provides
a standard interface for connecting Java
applications to relational databases. It
allows Java applications to interact with
databases through a set of classes and
interfaces. The JDBC architecture
typically consists of the following
components:
1. JDBC API
2. JDBC Driver Manager
3. JDBC Drivers
4. Database Server
JDBC Architecture
JDBC API (Application Programming
Interface):
● This is the top-level layer of the JDBC
architecture.
● It provides a set of Java classes and
interfaces that developers use in their
applications to interact with databases.
● Key interfaces in the JDBC API include
Connection, Statement, ResultSet, Driver,
and DataSource.
● The JDBC API abstracts database-specific
details, allowing developers to write portable
database code.
JDBC Architecture
JDBC Manager (or Driver Manager):
● The JDBC Manager is responsible for
managing a list of database drivers.
● It selects an appropriate driver from the list
and establishes a connection to the
database.
● It is typically used when you need to
connect to a database and manage
multiple drivers.
JDBC Architecture
JDBC Drivers:
● JDBC drivers are platform-specific implementations that provide the low-level
communication between Java applications and the actual database.
● There are different types of JDBC drivers:
● Type-1 (JDBC-ODBC Bridge Driver): Converts JDBC calls into ODBC calls to communicate with
the database. It is not commonly used today due to its limitations and platform dependencies.
● Type-2 (Native-API Driver): Uses a database-specific native library to interact with the database.
It offers better performance than Type-1 but may still have platform dependencies.
● Type-3 (Network Protocol Driver): Communicates with a middleware server that, in turn, interacts
with the database. It provides a level of indirection and can be platform-independent.
● Type-4 (Thin Driver, Direct-to-Database Driver): Communicates directly with the database server
using a database-specific protocol. It is platform-independent and is often the preferred choice
for modern JDBC applications.
JDBC Architecture
Database Server:
● The database server is where the actual database is hosted.
● The JDBC driver interacts with the database server to perform operations like executing SQL statements,
fetching results, and managing transactions.

The typical flow of JDBC operations involves the application using the JDBC API to request a
connection to the database.
The JDBC driver translates these requests into database-specific actions and communicates
with the database server.
The database server processes the requests and returns the results to the application
through the JDBC driver.
In summary, the JDBC architecture provides a standardized way for Java applications to
connect to and interact with relational databases, with the JDBC API acting as the interface
between the application and the database driver, which, in turn, communicates with the
database server. Different JDBC drivers are available to support various database systems
while maintaining a consistent API for developers.
JDBC Classes & Interfaces
JDBC (Java Database Connectivity) provides a set of classes and interfaces that allow Java applications to
interact with relational databases
DriverManager (Class): This is often the starting point. It is used to load the appropriate JDBC driver and
establish a database connection. The connection is obtained through DriverManager.getConnection().
Connection (Interface): Once a connection is established, you obtain a Connection object. The
connection object is used to create and manage statements and transactions.
Statement (Interface): You use the Statement (or its subtypes like PreparedStatement or
CallableStatement) to execute SQL queries or updates. These statements can be created from the
Connection object.
ResultSet (Interface): After executing a query, the result is obtained as a ResultSet. You use the
ResultSet to retrieve and manipulate the data returned by the query.
SQLException (Class): While not explicitly used as part of the application flow, handling SQLException is
crucial to deal with potential errors that may occur during database operations. It's important for error
handling and logging.
The typical flow starts with DriverManager for setting up the connection, then involves Connection for
managing the connection and transactions, followed by Statement for executing SQL statements, and finally,
ResultSet for processing query results. SQLException is used for error handling throughout this process.
Creating a Simple JDBC Application
Creating a simple JDBC application involves the following steps:
1. Import JDBC Libraries: Ensure you have the necessary JDBC driver for your database
system (e.g., MySQL, Oracle) and import the JDBC libraries into your Java project.
2. Establish a Database Connection: Use the DriverManager to establish a connection to
your database. You'll need to provide the connection URL, username, and password.
3. Create a Statement: Create a Statement or a more specific statement type (e.g.,
PreparedStatement) to execute SQL queries.
4. Execute SQL Queries: Use the statement to execute SQL queries or updates against
the database.
5. Process Query Results: If you execute a query, you'll get a ResultSet object. Use this
to retrieve and process the results.
6. Close Resources: Always close your database resources (connection, statement, result
set) to release them when you're done.
7. Handle Exceptions: Implement error handling using SQLException to catch and
manage any database-related errors.
 Creating a Simple JDBC Application

A Java program that connects to a MySQL database, executes a query to retrieve data, and prints the results:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class SimpleJDBCApp {

public static void main(String[] args) {
// JDBC URL, username, and password of MySQL server
String url = "jdbc:mysql://localhost:3306/your_database";
String user = "your_username";
String password = "your_password";

try {
// Establish a connection
Connection connection = DriverManager.getConnection(url, user, password);

// Create a statement
Statement statement = connection.createStatement();

// Execute a query
String sqlQuery = "SELECT * FROM your_table";
ResultSet resultSet = statement.executeQuery(sqlQuery);

// Process the query results

while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println("ID: " + id + ", Name: " + name);
}

// Close resources
resultSet.close();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Make sure to replace "your_database", "your_username", and "your_password" with your actual database details, and adjust the SQL query and processing logic to match your database schema and
Database Operations on JDBC
Performing database operations using JDBC involves executing SQL statements and
queries to interact with a relational database. Here are some common database
operations using JDBC:
1. Establishing a Database Connection
2. Executing SQL queries
3. Inserting data
4. Updating data
5. Deleting data
6. Retrieving data
7. Transaction Management
8. Handling Exceptions
9. Closing Resources
Database Operations on JDBC
1. Establishing a Database Connection:
a. Use DriverManager to establish a connection to your database by providing the database URL,
username, and password.
b. Example:
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "username", "password");

2. Executing SQL Queries:

a. Create a Statement, PreparedStatement, or CallableStatement to execute SQL queries or
updates.
b. Example:
Statement statement = connection.createStatement();
String sqlQuery = "SELECT * FROM mytable";
ResultSet resultSet = statement.executeQuery(sqlQuery);
Database Operations on JDBC
3. Inserting Data:
● Use an INSERT statement to add data to the database.
● Example:
String insertSQL = "INSERT INTO mytable (column1, column2) VALUES ('value1',
'value2')";
int rowsAffected = statement.executeUpdate(insertSQL);

4. Updating Data:
● Use an UPDATE statement to modify existing data in the database.
● Example:
String updateSQL = "UPDATE mytable SET column1 = 'new_value' WHERE column2 = 'condition'";
int rowsAffected = statement.executeUpdate(updateSQL);
Database Operations on JDBC
5. Deleting Data:
● Use a DELETE statement to remove data from the database.
● Example:
String deleteSQL = "DELETE FROM mytable WHERE column1 = 'condition'";
int rowsAffected = statement.executeUpdate(deleteSQL);

6. Retrieving Data (SELECT):

● Execute a SELECT query to retrieve data from the database.
● Use a ResultSet to fetch and process the results.
● Example:
String selectSQL = "SELECT * FROM mytable";
ResultSet resultSet = statement.executeQuery(selectSQL);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
// Process retrieved data
}
Database Operations on JDBC
7. Transaction Management:
● Use connection.setAutoCommit(false) to disable auto-commit mode, allowing you to group multiple SQL
statements into a single transaction.
● Use commit() to save changes or rollback() to undo them based on the transaction's success or failure.
8. Handling Exceptions:
● Always use try-catch blocks to handle SQLExceptions when working with JDBC. Proper error handling is
crucial to manage database-related exceptions.
9. Closing Resources:
● It's essential to close database resources (ResultSet, Statement, Connection) when you're done with them to
release connections and free up resources.
● Use close() on these resources in a finally block to ensure they are closed, even in the event of exceptions.

These are fundamental database operations in a JDBC application. You can combine these
operations to create more complex and sophisticated database interactions based on your
application's requirements.
Types of Statement (Statement Interface, Prepared Statement, Callable Statement)

In JDBC, there are three main types of statements that you can use to execute
SQL queries and updates against a database: Statement, PreparedStatement,
and CallableStatement.

1. Statement Interface
2. PreparedStatement
3. CallableStatement
Types of Statement (Statement Interface, Prepared Statement, Callable Statement)

Statement:
● The Statement interface is the simplest and most commonly used statement type in JDBC.
● It is used for executing general SQL queries without any parameters or placeholders.
● You construct the SQL query as a string and pass it to the executeQuery() or
executeUpdate() method.
● Since Statement does not handle parameters, it is more susceptible to SQL injection
vulnerabilities if you directly insert user input into the SQL query string.

Example

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery("SELECT * FROM mytable");

Types of Statement (Statement Interface, Prepared Statement, Callable Statement)

PreparedStatement:
● The PreparedStatement interface is a subinterface of Statement and is used when you have SQL queries
with parameters.
● It allows you to create a prepared statement with placeholders (?) for parameter values. This helps prevent
SQL injection by automatically escaping and handling parameters.
● Prepared statements are precompiled by the database, which can improve performance when executing the
same query multiple times with different parameter values.

Example

String sql = "SELECT * FROM mytable WHERE column1 = ?";

PreparedStatement preparedStatement = connection.prepareStatement(sql);

preparedStatement.setString(1, "value1");

ResultSet resultSet = preparedStatement.executeQuery();

Types of Statement (Statement Interface, Prepared Statement, Callable Statement)

CallableStatement:
● The CallableStatement interface is used for executing stored procedures in the database.
● A stored procedure is a precompiled SQL block stored in the database.
● Callable statements can accept input parameters, output parameters, and return values
from stored procedures.

Example
String sql = "{CALL my_procedure(?, ?)}";
CallableStatement callableStatement = connection.prepareCall(sql);
callableStatement.setString(1, "input_value");
callableStatement.registerOutParameter(2, Types.INTEGER); // Output parameter
callableStatement.execute();
int returnValue = callableStatement.getInt(2);
Exploring Result Set Operations

Result Set operations in JDBC involve retrieving and processing data from a database
query result. Once you execute a query using a Statement or PreparedStatement, you
obtain a ResultSet object, which you can use to navigate and work with the data. Here's
how to explore and perform operations on a ResultSet:

1. Navigating the Result Set

2. Retrieving Data
3. Scrolling and Updating
4. Moving to Specific Rows
5. Getting Metadata
6. Closing the Result Set
7. Error Handling
8. Handling Null Values
Exploring Result Set Operations

1. Navigating the Result Set

You can use methods like next(), previous(), first(), last(), and absolute(rowNumber) to navigate
through the rows of the result set.
Example
ResultSet resultSet = statement.executeQuery("SELECT * FROM mytable");
while (resultSet.next()) {
// Process the current row
}
2. Retrieving Data

You can use methods like getString(), getInt(), getDouble(), etc., to retrieve data from specific
columns in the current row.
Example:
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
Exploring Result Set Operations

3. Scrolling and Updating:

● You can use ResultSet.CONCUR_UPDATABLE to create an updatable ResultSet, allowing
you to update data in the result set and commit changes to the database.
Example:

ResultSet updatableResultSet = statement.executeQuery("SELECT * FROM mytable");

updatableResultSet.updateString("name", "new_name");
updatableResultSet.updateRow();

4. Moving to Specific Rows:

● You can use absolute() and relative() methods to move to a specific row or relative to the
current row.
Example:

resultSet.absolute(3); // Move to the 3rd row

resultSet.relative(2); // Move 2 rows forward
Exploring Result Set Operations

4. Getting Metadata:
● You can use ResultSetMetaData to retrieve information about the result set,
such as column names, data types, and column properties.
Example:
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
String columnName = metaData.getColumnName(1);

5. Closing the Result Set:

● Always close the ResultSet after you are done with it to release resources
and avoid memory leaks.
Example:
resultSet.close();
Exploring Result Set Operations

7. Error Handling:
● Surround result set operations with try-catch blocks to handle exceptions, especially SQLExceptions.
Example:
try {
// ResultSet operations
} catch (SQLException e) {
e.printStackTrace();
}

8. Handling Null Values:

● Check for null values in the result set using the wasNull() method to ensure safe processing of data.
Example:
String value = resultSet.getString("nullable_column");
if (resultSet.wasNull()) {
// Handle null value
}

By effectively exploring and using the operations available in the ResultSet, you can retrieve, process, and
manipulate data from your database queries in your Java application.