JDBC

JDBC- Java Database Connectivity

JDBC is an API (Application Programming Interface) in Java that allows Java
applications to interact with databases like MySQL, Oracle, PostgreSQL, etc. It
provides a set of classes and interfaces to connect, execute queries, and
retrieve data from a database.

y
er
ch
la
Ve
es
gi
lo

Key Features of JDBC:

no

1. Connectivity: Establishes a connection between Java applications and

ch

databases.
Te

2. Query Execution: Allows execution of SQL queries (SELECT, INSERT,

UPDATE, DELETE).
nt

3. Data Retrieval: Retrieves and processes data from databases.

sa

4. Database Independence: Works with diƯerent databases using

Be

diƯerent JDBC drivers.

5. Transaction Management: Supports transactions (commit, rollback).

Besant Technologies Velachery

Architecture of JDBC
JDBC Architecture

y
er
ch
la
Ve
es
gi
lo
no
ch

Components of JDBC Architecture:

Te

1. JDBC API:
Provides methods to connect to a database, execute queries, and
nt

o
handle results.
sa

Key Interfaces: Connection, Statement, PreparedStatement,

o
Be

ResultSet, DriverManager.

2. JDBC Driver Manager:

o Manages diƯerent types of JDBC drivers and establishes database

connections.
Loads the appropriate driver based on the connection request.
o

Besant Technologies Velachery

 3. JDBC Drivers:
o Enables Java applications to communicate with diƯerent
databases.

o Types of JDBC Drivers:

 Type-1: JDBC-ODBC Bridge Driver (Deprecated)


Type-2: Native-API Driver (Database vendor-specific)

y
 Type-3: Network Protocol Driver (Middleware-based)

er
 Type-4: Thin Driver (Direct database communication, most

ch
commonly used)

la
4. Database:

Ve
o Stores the actual data in a structured format using tables.
es
o Supports SQL queries for data manipulation.
gi

5. Application: It is a Java applet or a servlet that communicates with a

lo

data source.
no

JDBC Architecture: Two-Tier and Three-Tier Models

ch

JDBC architecture followstwo-tier and three-tier processing models to

access a database eƯiciently.
Te

1. Two-Tier Architecture (Client-Server Model)

nt

In this model, theJava application directly communicates with the

sa

database using a JDBC driver. Queries are sent, and results are returned
without any intermediate processing.
Be

JDBC (Java Database Connectivity) follows a two-layer architecture, which

includes:

1. JDBC API Layer – The interface between Java applications and JDBC
drivers.

Besant Technologies Velachery

 2. JDBC Driver Layer – The communication bridge between the Java
application and the database.
Structure:
Client Application (Java) → JDBC Driver → Database

脥? Pros:

Simple and easy to implement.

y


er
 Faster communication since there is no intermediate layer.

ch
 Cons:

la
 Less secure, as the database is directly exposed to the client.

Ve
 Not scalable for large applications.

2. Three-Tier Architecture (Client-Middleware-Database)

es
In this model, queries from the client application go through middleware
a
gi

(Application Server) before reaching the database. The middleware

lo

processes the request, interacts with the database, and sends the results
no

back to the client.

Structure:
ch

Client Application → Application Server → JDBC Driver → Database

Te

脥? Pros:
nt

 More secure (database is hidden behind an application server).

sa

 Scalable (handles multiple clients eƯiciently).

Be


Supports business logic processing before sending data to the client.

 Cons:

 Slightly slower due to an additional layer.

 More complex implementation.

Besant Technologies Velachery

Key Points:
 Two-Tier Architecture is ideal for small applications requiring direct
database access.
 Three-Tier Architecture is preferred for large, enterprise-level
applications requiringsecurity, scalability, and business logic
processing.

y
er
Steps to connect Java application with a Database using JDBC

ch
la
1) Import Packages

Ve
2) Load Driver
3) Register Driver
4) Create Connection
es
5) Create Statement
gi

6) Execute Statement
7) Close
lo
no

After JDBC 4.0 i.e. introduced in java 6 jdbc registration and loading is not compulsory.
ch

SAMPLE CODE: For interaction with any database

Te
nt
sa
Be

Besant Technologies Velachery

CODE: For interaction with oracle(sql) database

y
er
ch
la
Ve
es
gi
lo
no
ch
Te
nt
sa

When to use simple Statement & when to use preparedStatement

Be

Use Statement when:

1. Static Queries – If the SQL query is fixed and doesn’t change

dynamically.
Simple Execution
2. – For one-time query execution without parameters.

Besant Technologies Velachery

 3. DDL Queries – Useful for CREATE, DROP, or ALTER statements.
4. Performance is not a concern – Suitable when query execution
happens infrequently.

5. No User Input – Use Statement when queries don’t involve user inputs
to avoid SQL injection risks.

y
er
ch
Use PreparedStatement when:

la
1. Parameterized Queries – When the query has variables that change

Ve
dynamically (e.g., ? placeholders).
2. Preventing SQL Injection – Prevents malicious injections by
es
automatically handling special characters.
gi

3. Performance Optimization – Compiled once, executed multiple times

lo

eƯiciently.
no

4. Batch Execution – Supports executing multiple queries in a batch for

eƯiciency.
ch

5. Frequent Query Execution – If the same query is run multiple times

Te

with diƯerent values.

nt
sa
Be

Here are someimportant methods used in JDBC and SQL:

1. JDBC Important Methods

a) Connection Interface (Managing Database Connection)

Besant Technologies Velachery

Method Description
createStatement() Creates a simple SQL statement.

prepareStatement(String sql) Creates a precompiled SQL statement.

commit() Commits a transaction.

rollback() Rolls back a transaction.

y
er
setAutoCommit(boolean status) Enables/disables auto-commit mode.

ch
close() Closes the database connection.

la
Ve
b) Statement Interface (Executing SQL)
es
Method Description
gi

executeQuery(String
sql) Executes SELECT queries and returns a ResultSet.
lo

executeUpdate(String
no

Executes INSERT, UPDATE, DELETE queries and

sql)
returns aƯected row count.
ch

execute(String sql)
Can execute both SELECT and DML statements.
Te

close()
Closes the statement object.
nt
sa

c) PreparedStatement Interface (For Parameterized Queries)

Be

Method Description

setInt(int index, int value) Sets an integer parameter.

setString(int index, String value) Sets a string parameter.

Besant Technologies Velachery

Method Description
setDouble(int index, double
value) Sets a double parameter.
executeQuery()
Executes a SELECT query.

Executes an INSERT, UPDATE, DELETE

executeUpdate()
query.

y
er
ch
d) ResultSet Interface (Processing Query Results)

la
Method Description

Ve
next() es Moves to the next row.

getInt(String columnLabel) Retrieves an integer value.

gi

getString(String columnLabel) Retrieves a string value.

lo

getDouble(String columnLabel) Retrieves a double value.

no

close() Closes the ResultSet.

ch
Te

2. SQL Important Methods

nt

a) Data Manipulation Language (DML)

sa

Method Description
Be

SELECT Retrieves data from a table.

INSERT INTO Inserts new data into a table.

UPDATE Modifies existing data.

Besant Technologies Velachery

Method Description
DELETE Removes data from a table.

b) Data Definition Language (DDL)

Method Description

y
CREATE Creates a new table.

er
ch
TABLE ALTER Modifies an existing table.

la
TABLE DROP Deletes a table.

Ve
TABLE
TRUNCATE TABLE Removes all records from a table without logging.
es
gi

c) Data Control Language (DCL)

lo

Method Description
no

GRANT Gives user permissions.

ch

REVOKE Removes user permissions.

Te
nt

d) Transaction Control Language (TCL)

sa

Method Description
Be

COMMIT Saves all changes.

ROLLBACK Reverts changes since the last COMMIT.

SAVEPOINT Creates a rollback checkpoint.

These methods help eƯiciently interact with JDBC and

SQL databases.

Besant Technologies Velachery

If table is already present then don’t create new table ,if not present then
create

y
er
ch
Example-

la
Problem Statement:

Ve
Write a java code that manages employee payroll information stored in an
Oracle [Link] has following features:
es
1. Add new employee records (employee ID, name, department, salary).
gi

2. View all employee records.

lo

3. Update an employee's salary.

no

4. Delete employee records

ch
Te

[Link]
nt

import [Link].*;
sa

public class JdbcConnection {

Be

public static Connection getConnection() {

Connection con = null;
try {
// Load Oracle JDBC Driver
[Link]("[Link]");

Besant Technologies Velachery

 // Establish connection
con =
[Link]("jdbc:oracle:thin:@localhost:1521:XE",
"system", "anupam");

[Link]("Database connected successfully!");

// Check if table 'abc' exists

y
DatabaseMetaData dbm = [Link]();

er
ResultSet tables = [Link](null, null, "ABC", new String[] {

ch
"TABLE" });

la
Ve
if (![Link]()) { // Table does not exist
es
String createTableQuery = "CREATE TABLE abc (EmpId NUMBER
PRIMARY KEY, Name VARCHAR(20), Dept VARCHAR(25), Salary NUMBER)";
gi

Statement stmt = [Link]();

lo
no

[Link](createTableQuery);
[Link]("Table 'abc' created successfully!");
ch

} else {
Te

[Link]("Table 'abc' already exists.");

nt

}
sa

} catch (Exception e) {
Be

[Link]("Error: " + [Link]());

}
return con;

Besant Technologies Velachery

}

[Link]
import [Link].*;
import [Link].*;

y
er
public class MainOperation {

ch
static Scanner sc = new Scanner([Link]);

la
static Connection con = [Link]();

Ve
es
public static void addEmployee() {
gi

try {
lo

String s = "INSERT INTO abc VALUES(?,?,?,?)";

no

PreparedStatement q = [Link](s);
ch

[Link]("Enter employeeId: ");

Te

int empId = [Link]();

nt

[Link]();
sa
Be

[Link]("Enter the employee name: ");

String name = [Link]();

[Link]("Enter the department: ");

Besant Technologies Velachery

 String dept = [Link]();

[Link]("Enter the salary: ");

int salary = [Link]();

y
[Link](1, empId);

er
[Link](2, name);

ch
[Link](3, dept);

la
[Link](4, salary);

Ve
[Link]();
es
gi

[Link]("Employee added successfully!");

lo

} catch (Exception e) {
no

[Link]("Error: " + [Link]());

}
ch

}
Te
nt

public static void viewEmployee() {

sa

try {
Be

String sql = "SELECT * FROM abc";

Statement s = [Link]();
ResultSet rs = [Link](sql);

Besant Technologies Velachery

 while ([Link]()) {
[Link]("EMP: " + [Link](1) + ", Name: " + [Link](2) +
", Department: " + [Link](3) + ", Salary: " + [Link](4));

}
} catch (Exception e) {

[Link]("Error: " + [Link]());

y
er
}

ch
}

la
Ve
public static void updateSalary() {
try {
es
[Link]("Enter the employee name whose salary is to be
gi

updated: ");
lo

int empId = [Link]();

no
ch

[Link]("Enter the new salary: ");

Te

int salary = [Link]();

nt
sa

String sql = "UPDATE abc SET Salary=? WHERE EmpId=?";

Be

PreparedStatement ps = [Link](sql);
[Link](1, salary);
[Link](2, empId);
[Link]();

Besant Technologies Velachery

 int rowsAƯected = [Link](); // Returns the number of rows
updated

if (rowsAƯected > 0) {
[Link]("Employee salary updated successfully!");

} else {

y
er
[Link]("Error: No employee found with ID " + empId);

ch
} } catch (Exception e) {

la
[Link]("Error: " + [Link]());

Ve
}
}
es
gi

public static void deleteEmployee() {

lo

try {
no

[Link]("Enter 1 to delete complete table or 2 to delete by

ch

employee ID: ");

Te

int n = [Link]();
nt
sa

if (n == 1) {
Be

String sql = "DROP TABLE abc";

Statement s = [Link]();
[Link](sql);
[Link]("Table deleted successfully!");

} else {

Besant Technologies Velachery

 [Link]("Enter the employee name to delete: ");
int empId = [Link]();

String sql = "DELETE FROM abc WHERE EmpId=?";

PreparedStatement ps = [Link](sql);

y
[Link](1, empId);

er
[Link]();

ch
la
[Link]("Employee record deleted successfully!");

Ve
} es
} catch (Exception e) {
gi

[Link]("Error: " + [Link]());

lo

}
no

}
ch

public static void main(String[] args) {

Te

while (true) {
nt

[Link]("\nEmployee Payroll System");

sa

[Link]("1. Add Employee");

Be

[Link]("2. View Employees");

[Link]("3. Update Salary");
[Link]("4. Delete Employee");
[Link]("5. Exit");

Besant Technologies Velachery

 [Link]("Enter your choice: ");

int choice = [Link]();

switch (choice) {

case 1:

y
addEmployee();

er
break;

ch
case 2:

la
viewEmployees();

Ve
break; es
case 3:
gi

updateSalary();
lo

break;
no

case 4:
ch

deleteEmployee();
break;
Te

case 5:
nt

[Link]("Exiting...");
sa

return;
Be

default:

[Link]("Invalid choice. Try again.");

}

Besant Technologies Velachery

 }
}

Output

y
er
ch
la
Ve
es
gi
lo
no
ch

Stored Procedure
Te

A Stored Procedure is a precompiled SQL block that is stored in a database

and can be executed multiple times with diƯerent parameters. It improves
nt

performance, security, and maintainability.

sa
Be

Besant Technologies Velachery

Basic Sample Example in Oracle SQL
Step 1: Create a Stored Procedure

y
er
ch
la
Ve
꼡 Explanation: es
 The procedure insert_employee takes4 input parameters (EmpId,
Name, Dept, Salary).
gi

It inserts the values into the abc table and commits the transaction.
lo


no

Step 2: Call the Stored Procedure in SQL

ch
Te
nt

OR
sa
Be

This willinsert records into the table.

Besant Technologies Velachery

Step3: Calling a Stored Procedure in Java (JDBC)

y
er
ch
la
Ve
es
gi
lo
no
ch
Te
nt
sa
Be

Besant Technologies Velachery