JDBC

• JDBC stands for Java Database Connectivity.

• JDBC is a Java API to connect java programs to any

relational DB and execute the query with the database.

• We can use JDBC API to access data stored in any relational

database.

• With the help of JDBC API, we can store, update, delete and
fetch data from the database.

NOTE : What is API : API (Application programming interface) is a collection

of classes and interfaces hat allows any two applications to connect and
communicate with each other.
 JDBC Driver
• JDBC Driver is a software component that enables java
application to interact with the database.

• It acts as a translator between java application and the database.

• There are four types of JDBC Drivers :

1. JDBC-ODBC bridge driver
2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)
• Thin driver
• Thin driver is the fastest Driver as it converts JDBC calls( java
instructions ) directly into the specific database protocol.
• It is also known as Type 4 Driver and is fully written in Java
language.
• It is the most commonly used / highly recommended JDBC
Driver.
Why Should We Use JDBC ?
• Before JDBC, ODBC API was the database API to connect and
execute the query with the database. But, ODBC API uses ODBC
driver which is written in C language (i.e. platform dependent
and unsecured).

• That is why Java has defined its own API (JDBC API) that uses
JDBC drivers (written in Java language).

We can use JDBC API to handle database using Java program

and can perform the following activities :

• Connect to the database

• Execute queries and update statements to the database
• Retrieve the result received from the database.
 JDBC ARCHITECTURE

Java Application:
This is the client-side application that needs to interact with a database.
It uses JDBC API to send SQL queries and retrieve results.
JDBC API:
• This is the set of Java classes and interfaces (from the [Link]
package) that allow communication with the database.
• It provides methods to connect to a database, execute queries, and
process results.
JDBC Driver:
• This is a software component that translates JDBC API calls into
database-specific communication.
• It acts as a bridge between the Java application and the database.
• There are four types of JDBC drivers (Type 1 to Type 4).
Database:
• This is the actual database (like MySQL, PostgreSQL, Oracle, etc.) that
stores the data.
• The JDBC driver sends SQL queries to the database and retrieves the
required results.
Flow of Communication:

• The Java Application sends SQL queries via the JDBC API.
• The JDBC API forwards the request to the JDBC Driver.
• The JDBC Driver communicates with the Database and
executes the SQL query.
• The Database returns the result back to the JDBC Driver.
• The JDBC Driver sends the results to the JDBC API, which
then makes it accessible to the Java Application.
Java Database Connectivity with 5 Steps

• There are 5 steps to connect any java application with the

database using JDBC. These steps are as follows :

1. Load and register Driver

2. Establish connection
3. Create a Statement
4. Execute the query
5. Process the result
6. Close the connection
steps to connect Java program to Database
• Register the driver class : The forName() method of ‘Class’ class is used to
register the driver class. This method is used to dynamically load the driver
class.
Example : [Link](“[Link]");

• Establish Connection : The getConnection() method of DriverManager class

is used to establish connection with the database.
Syntax of getConnection() method :
public static Connection getConnection(String url,String name,String password) throws
SQLException
Example to establish connection :
Connection con=[Link]( “jdbc:mysql://localhost:3306”,“root”,“root”);
• Create the Statement : Connection interface is used to create statement. We can create a
statement using Statement / PreparedStatement / CallableStatement interface. The object of these
statement interfaces are responsible to execute queries with the database.
Example to create a Statement :
Statement stmt=[Link]();
PreparedStatement ps = [Link](String sqlQuery);
CallableStatement cs = [Link](sql_string);

• Execute the query : The execute(String SqlQuery) method of Statement interface is used to
execute queries to the database. This method returns Boolean value that can be used to know
whether any resultset is generated or not.
Example to execute a query :
[Link](“create table student(name varchar(20) , age integer , address varchar(20))”);

• Process the result : If any result is returned , we process(use) the results using ResultSet
interface.

• Close the connection : By closing connection object statement and ResultSet will be closed
automatically. The close() method of Connection interface is used to close the connection.
Examlpe to close connection :
[Link]();
 COMPONENTS OF JDBC API
1. DriverManager class :

The DriverManager class is the component of JDBC API and also a member of
the [Link] package. The DriverManager class acts as an interface between users and
drivers.

It is responsible for establishing a connection between a database and the java

program.

It consists of the following method :

public static Connection getConnection(String url,String userName,String
password) throws SQLException

It is used to establish the connection with the specified url, username, and password.
2. Connection interface :

The Connection interface is a factory of Statement and

PreparedStatement, i.e., an object of Connection is used to get
the object of Statement and PreparedStatement.

Methods of Connection interface are :

• public Statement createStatement(): creates and returns a

statement object that can be used to execute static SQL
queries.

• public PreparedStatement prepareStatement(): creates a

PreparedStatement object that can be used to execute dynamic
SQL queries.
2. Connection interface :

The Connection interface is a factory of Statement and

PreparedStatement, i.e., an object of Connection is used to get
the object of Statement and PreparedStatement.

Methods of Connection interface are :

• public Statement createStatement(): creates and returns a

statement object that can be used to execute static SQL
queries.

• public PreparedStatement prepareStatement(): creates a

PreparedStatement object that can be used to execute dynamic
SQL queries.
3. Statement interface :

The Statement interface provides methods to execute static SQL queries with the
database.
The statement interface is a factory of ResultSet i.e. it provides factory method to
get the object of ResultSet.

It consists of following methods for executing the SQL queries :

• public boolean execute(String sql): can be used to execute any SQL queries. It
returns Boolean value based on whether ResultSet is generated or not.

• public int executeUpdate(String sql): is used to execute specifically for DML

queries , such as create, drop, insert, update, delete query etc.

• public ResultSet executeQuery(String sql): is used to execute SELECT query. It

returns the data selected from database as an object of ResultSet.
4. PreparedStatement interface :
The PreparedStatement interface is a sub interface of Statement.
It provides methods to execute dynamic SQL queries with the database.
The PreparedStatement interface also provides a method to get the object of ResultSet.

It consists of following methods for executing the SQL queries :

• public boolean execute(): can be used to execute any SQL queries. It returns Boolean value
based on whether ResultSet is generated or not.

• public int executeUpdate(): is used to execute specifically for DML queries , such as create, drop,
insert, update, delete query etc.

• public ResultSet executeQuery): is used to execute SELECT query. It returns the data selected
from database as an object of ResultSet.

• public void setInt(int paramIndex, int value) : sets the integer value to the given parameter
index.

• public void setString(int paramIndex, String value) : sets the String value to the given parameter
index.
NOTE : setXXX(int paramIndex , int value) methods are present for all datatypes like , setLong(int
paramIndex , long value) , setDouble(int paramIndex , double value) , setFloat(int paramIndex , float value)
and so on..
5. ResultSet interface :

The ResultSet object is used to store the data retrieved from the database. In ResultSet , data
will be stored in rows and columns only.
The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor points
to before the first row.

It consists of following methods for processing / displaying the

results :

• public boolean next() : is used to check whether any row is present in result set or not. If
yes , then it moves the cursor to the next row from the current position.

• public int getInt(int columnIndex): is used to return the data of specified column index of
the current row as int.

• public String getString(int columnIndex):is used to return the data of specified column
index of the current row as String.

NOTE : getXXX(column index) methods are present for all datatypes like , getLong() ,
getDouble() , getFloat() and so on..
DIFFERENCE BETWEEN STATEMENT AND PREPAREDSTATEMENT INTERFACE

STATEMENT PREPAREDSTATEMENT

It is used when SQL query is to be It is used when SQL query is to be executed multiple
executed only once. times.

You can not pass parameters at

You can pass parameters at runtime.
runtime.

Query is compiled each time for

Query is compiled only once.
execution.
Performance is very low. Performance is better than Statement.
It is base interface. It extends statement interface.

Used to execute static SQL queries. Used to execute dynamic SQL queries.
Batch processing in JDBC refers to the ability to group multiple
SQL statements together and execute them as a single unit or batch, rather
than executing each statement individually.

Why Batch Processing?

• Without batching:
• Each statement is sent separately to PostgreSQL.
• Each has its own network round trip.
• This adds overhead and slows things down for bulk operations.

• With batching:
• Multiple statements are sent in one go.
• Fewer round trips.
• Significant performance gain for large inserts/updates.
 Methods for Batch Processing:

• addBatch(String sqlQuery): This method of Statement, PreparedStatement is used to add

individual SQL statements to the batch.

• setAutoCommit(boolean value) : By default, many database connections operate in "auto-

commit" mode. This means that every individual SQL statement executed is automatically
committed to the database as a separate transaction. If a statement completes successfully, its
changes are immediately made [Link] setAutocommit(false) is called, the automatic
committing of individual statements is turned off.

• commit() : It is used to permanently save any transaction into the database.

In JDBC, batch processing allows multiple SQL statements to be sent to the database in a single
round trip, improving performance. The commit() method is then explicitly called on the Connection
object to save these batched operations.
• executeBatch(): This method executes all the statements previously added to the batch. It
returns an array of integers, where each element represents the update count for the
corresponding statement in the batch.

What executeBatch() Returns :

The method returns an int array, where each element represents the update count (number of rows affected)
for each SQL statement in the batch — in the same order they were added.
So:

int[] result = [Link]();

If you added 3 SQL statements using addBatch(), then result [ ] will have 3 elements,
result[0] → number of rows affected by 1st SQL
result[1] → number of rows affected by 2nd SQL
result[2] → number of rows affected by 3rd SQL
In JDBC batch processing, you cannot include a SELECT query in the batch.

Why?

[Link]() is meant for update operations (INSERT, UPDATE,

DELETE, CREATE, DROP, etc.).

A SELECT query returns a ResultSet, and executeBatch() only returns an int[]

representing rows affected, not query results. Hence , we cannot include a
SELECT query in a batch processing.

If you try adding a SELECT to a batch, PostgreSQL JDBC will throw an

exception.
• You can add both INSERT and UPDATE (or even DELETE) statements into the same batch, but

only if you are using a Statement object — not a PreparedStatement.

Why?
• PreparedStatement is meant for one specific SQL template (e.g., only insert or only

update), where you change values dynamically using ? placeholders.

• Statement, on the other hand, can hold completely different SQL commands — each can be

added to the batch separately.

 Stored Procedure
A stored procedure is a set of SQL statements stored in the database that can
perform operations like insert, update, delete, or even complex calculations.

Advantages:

Reusable – Write once, use many times.

Reduces network traffic
Can accept input parameters and return output values.
Improves security – Users can execute procedures without direct table access.
 Syntax to create a stored procedure
PostgreSQL supports procedures using the CREATE PROCEDURE statement (from
version 11 onwards).

CREATE PROCEDURE procedure_name (parameters)

LANGUAGE plpgsql
AS $$
BEGIN
-- SQL statements
END;
$$;
 CREATING STORED PROCEDURE IN PGADMIN
In pgAdmin, creating a procedure is pretty straightforward — you can do it either with the GUI or by
writing SQL in the Query Tool.

1. Using Query Tool (Recommended for learning) :

• Open pgAdmin and connect to your database.
• Right-click on your database → select Query Tool.
• Write your procedure SQL, for example , assume you have employee table with following columns :
• [ empid integer , name text , designation text , salary double precision ]
Example : Simple procedure
CREATE PROCEDURE add_employee (
with IN parameter to insert
IN emp_id INTEGER ,
IN e_name TEXT , employee values
IN e_designation TEXT ,
IN e_salary DOUBLE PRECISION IN means input parameter. If
) we want to accept input values
LANGUAGE plpgsql , then we declare parameter
AS $$ using IN .
BEGIN
INSERT INTO employee ( empid,name,designation,salary ) VALUES ( emp_id,e_name,e_designation,e_salary );
END;$$;

• Click the Execute script button. , that looks like play button [ ] OR press F5.
• The procedure will now appear in pgAdmin → Databases → your_database → Schemas → public →
Procedures.
2. Using GUI (No SQL typing)
• Assume you have employee table with following columns : [ empid integer , name text , designation
text , salary double precision ]
1. In pgAdmin, expand your database → Schemas → public → You can see Procedures.
2. Right-click on Procedures → Create → Procedure.
3. In the General tab , specify procedure name. For example :
Name: add_employee
4. Owner: (leave default : postgres)
5. In the Definition tab:
Language: plpgsql
6. In the same tab , below language option , we have arguments table to specify data type , mode
and arguments name.
Example → data type : INTEGER , Mode : IN , Arugment name : emp_id
data type : TEXT , Mode : IN , Arugment name : e_name
data type : TEXT , Mode : IN , Arugment name : e_designation
data type : DOUBLE PRECISION , Mode : IN , Arugment name : e_salary
7. Go to Code tab and type your sql statement :
BEGIN
INSERT INTO students(name, age) VALUES (p_name, p_age);
END;
8. …. and then Click Save.
 Calling a Stored Procedure in PostgreSQL

To call a stored procedure , right click on procedures and open query tool.
In query tool , we use CALL keyword to call a stored procedure by passing
values to it as shown below:
[ passed values must match the declared arguments in the procedure ]

CALL add_employee(1 , ’Sheela’ , ‘HR’ , 50000);

And then execute the query by clicking on execute query button..

Then check the table in DB. We can see the inserted values.
Example 2: Procedure with IN and OUT parameters to search and fetch salary of
an employee based on name.

CREATE PROCEDURE get_employee_salary (

IN ename TEXT
OUT esal DOUBLE PRECISION
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT salary INTO esal FROM employee WHERE name = ename;
END;
$$;

OUT parameter can be

used to return a value.
 Calling a Stored Procedure in PostgreSQL
To call a stored procedure , right click on procedures and open query tool.
In query tool , we use CALL keyword to call a stored procedure by passing
values to it as shown below:
[ passed values must match the declared arguments in the procedure ]

CALL get_employee_salary(‘sharath’ , 0);

NOTE : Postgres internally considers the procedure signature as having two parameters:
ename → IN (you must supply it)
esal → OUT (Postgres will overwrite it, but you still must pass a placeholder when calling)
Here ,0 acts as a dummy value. Postgres will replace it with the actual salary and return it.

And then execute the query by clicking on execute query button.

For procedures with OUT parameters, PostgreSQL will display the result.
 CallableStatement
• It’s an interface used to call stored procedures from Java program.
• It is defined in [Link] package.
• It extends the PreparedStatement interface

How to Create a CallableStatement?

We can get CallableStatement object it using [Link]().
Syntax :
CallableStatement sc = [Link] ("call procedure_name(?, ?, ?)“ );
NOTE : Placeholder to take input from user at runtime , otherwise , we can directly pass
values to it.

• To Execute the stored procedure , in CallableStatement , execute() is used.

 Methods in CallableStatement
1. Setting Input Parameters (same as PreparedStatement)
For procedures that have IN parameters, you use setter methods (like setInt(), setString(), etc.) to pass input
values.

In SQL :

// sets the first parameter (IN) to integer value 101

In JAVA :

2. registerOutParameter() : When a stored procedure has OUT parameters (values returned from the procedure),
you must tell JDBC which parameter positions will receive output and what data type to expect.
syntax : [Link](parameterIndex, sqlType);
* parameterIndex → the position of the OUT parameter in your SQL procedure call.
* sqlType → the Java SQL type that matches your procedure’s output type (from [Link]).
Example :
[Link](2, [Link]);
3. Retrieving OUT Parameter Values
After executing the procedure, use getter methods to retrieve the returned values using
their parameter position.
Example :
double salary = [Link](2);

4. Executing the Stored Procedure

Finally, run the procedure using:
[Link](); ✅ Summary Example
 Using JDBC to Call Stored Procedure with no return value :
[ to call add_employee (IN emp_id INTEGER , IN e_name TEXT , IN e_designation TEXT , IN
e_salary DOUBLE PRECISION ) procedure ] by passing values during coding.

Run the java program. add_employee() procedure will

be called and input values is sent to it. The procedure
then executes and we can view the inserted values in
the table in DB .
 Using JDBC to Call Stored Procedure with no return value :
[ to call add_ employee (IN emp_id INTEGER , IN e_name TEXT , IN e_designation TEXT , IN e_salary DOUBLE
PRECISION ) procedure ] by passing values during runtime.

Run the java program. It will take input from users at

runtime. Those values are passed to add_employee()
procedure and then it will be called. The procedure
then executes and we can view the inserted values in
the table in DB .
 Using JDBC to Call Stored Procedure which returns value :
[ to call get_employee_salary(IN ename TEXT , OUT esal DOUBLE PRECISION) procedure ]

When you call a stored procedure that has OUT parameters

(values returned from the procedure), you must tell JDBC
which parameter positions will receive output and what data
type to expect.
This id done using registerOutParameter(parameterIndex,
sqlType);
PatameterIndex – to specify the position of OUT parameter.
sqlType – Datatype of return value.