Q1) Illustrate Different Types of JDBC

Drivers. (10 Marks)

JDBC (Java Database Connectivity) drivers are software components that enable Java
applications to communicate with databases. JDBC driver specification classifies JDBC drivers
into four types based on the mechanism used to establish communication with the database.

1. Type 1 JDBC-ODBC Bridge Driver

Diagram
Java Application
|
V
JDBC Driver
|
V
ODBC Driver
|
V
Database

Explanation

●​ Type 1 driver is also called JDBC-ODBC Bridge Driver.

●​ It translates JDBC calls into ODBC calls.
●​ It requires ODBC driver installation on the client machine.
●​ Additional translation causes performance degradation.
●​ It is a thick driver.
●​ It is database independent and platform dependent.

Advantages

●​ Easy to implement.
●​ Supports all ODBC databases.

Disadvantages
 ●​ Slow performance.
●​ Requires ODBC installation.

2. Type 2 Java/Native API Driver

Diagram
Java Application
|
V
Type 2 Driver
|
V
Native Library
|
V
Database

Explanation

●​ Uses Java classes and native database libraries.

●​ Native libraries are supplied by the database vendor.
●​ Follows two-tier architecture.
●​ It is platform dependent and database dependent.
●​ It is a thick driver.
●​ Provides better performance than Type 1 driver.

Advantages

●​ Faster than Type 1 driver.

●​ Better performance.

Disadvantages

●​ Requires installation of native libraries.

●​ Platform dependent.

3. Type 3 JDBC Network Protocol Driver

Diagram
Java Application
|
V
Type 3 Driver
|
V
Middleware Server
|
V
Database

Explanation

●​ Also called Java Protocol Driver.

●​ Converts SQL queries into JDBC formatted statements.
●​ Middleware server translates requests into DBMS-specific format.
●​ Developed entirely using Java.
●​ It is platform independent and database independent.
●​ It is a thick driver.

Advantages

●​ Supports multiple databases.

●​ Platform independent.

Disadvantages

●​ Requires middleware server.

●​ Additional network overhead.

4. Type 4 JDBC Driver

Diagram
Java Application
|
V
Type 4 Driver
|
V
Database

Explanation

●​ Also called Native Protocol Driver or Thin Driver.

●​ Directly converts SQL queries into database-specific protocol.
●​ No ODBC driver or middleware server is required.
●​ Fastest JDBC driver.
●​ Implemented completely using Java.
●​ Platform independent and database dependent.
●​ Follows two-tier architecture.

Advantages

●​ High performance.
●​ Easy deployment.
●​ Most widely used JDBC driver.

Disadvantages

●​ Separate driver required for each database.

Comparison of JDBC Drivers

Type Driver Name Platform Database Driver
Independent Independent Type

Type JDBC-ODBC Bridge No Yes Thick

1

Type Native API Driver No No Thick

2
 Type Network Protocol Yes Yes Thick
3 Driver

Type Thin Driver Yes No Thin

4

Conclusion
JDBC drivers provide communication between Java applications and databases. Among all
driver types, Type 4 JDBC Driver is the most efficient and widely used because it provides direct
communication with the database, high performance, and platform independence.

Q2) Explain the Process of JDBC

Connectivity with Java Application with
Code Snippets. (10 Marks)
Introduction
JDBC (Java Database Connectivity) is an API used to establish communication between Java
applications and databases. JDBC connectivity involves a sequence of steps that allow a Java
application to connect to a database, execute SQL queries, process the results, and close the
connection.

JDBC Connectivity Process

The JDBC process consists of the following steps:
 1.​ Loading the JDBC Driver
2.​ Establishing Connection with Database
3.​ Creating Statement Object
4.​ Executing SQL Query
5.​ Processing ResultSet
6.​ Closing the Connection

Diagram
Java Application
|
V
Load JDBC Driver
|
V
Create Connection
|
V
Create Statement
|
V
Execute SQL Query
|
V
Process ResultSet
|
V
Close Connection

1. Loading the JDBC Driver

The JDBC driver must be loaded before the Java application communicates with the database.

Code Snippet
[Link]("[Link]");

The [Link]() method loads the MySQL JDBC driver into memory.
2. Establishing Connection with Database
The connection is established using the [Link]() method.

Code Snippet
String url = "jdbc:mysql://localhost:3306/persondb";
String user = "root";
String pass = "root123";

Connection conn =
[Link](url,user,pass);

The method returns a Connection object which represents the connection between Java
application and database.

3. Creating Statement Object

A Statement object is used to send SQL commands to the database.

Code Snippet
Statement st = [Link]();

The createStatement() method creates a Statement object.

4. Executing SQL Query

The SQL query is executed using the executeQuery() method.

Code Snippet
ResultSet rs =
[Link]("SELECT * FROM Students");

The method sends the query to the database and returns the result in the form of a ResultSet
object.
5. Processing the ResultSet
The ResultSet object contains the records returned by the query.

Code Snippet
while([Link]())
{
[Link](
[Link](1)+" "+
[Link](2));
}

●​ next() moves the cursor to the next row.

●​ getInt() retrieves integer data.
●​ getString() retrieves string data.

The loop continues until all records are processed.

6. Closing the Connection

After processing the data, the connection should be closed.

Code Snippet
[Link]();

The close() method releases database resources and terminates the connection.

Complete JDBC Program

import [Link].*;

class Main {
public static void main(String[] args) throws Exception {
 // Step 1: Load Driver
[Link]("[Link]");

// Step 2: Establish Connection

String url = "jdbc:mysql://localhost:3306/persondb";
String user = "root";
String pass = "root123";

Connection conn =
[Link](url,user,pass);

// Step 3: Create Statement

Statement st = [Link]();

// Step 4: Execute Query

ResultSet rs =
[Link]("SELECT * FROM Students");

// Step 5: Process Result

while([Link]())
{
[Link](
[Link](1)+" "+
[Link](2));
}

// Step 6: Close Connection

[Link]();
}
}

Conclusion
JDBC connectivity enables a Java application to communicate with a database through a
sequence of steps: loading the driver, establishing connection, creating statement, executing
query, processing ResultSet, and closing the connection. JDBC provides a standard and
efficient way to access relational databases from Java applications.
Before the exam answer, let’s actually understand these two because students often mug them
up and then mix them up.

Learning Mode
Why do we need Statement Interfaces?
Suppose we already have a database connection.

Connection conn =
[Link](...);

Now we want to execute SQL queries.

JDBC provides 3 Statement Interfaces:

1. Statement
2. PreparedStatement
3. CallableStatement

Think of them as different tools for different jobs.

1. Statement
Suppose you want to execute a query immediately.

Statement st = [Link]();

ResultSet rs =
[Link](
"SELECT * FROM Students");

Query is written and executed directly.

Problem with Statement

Suppose user enters Student ID.

int id = 101;

Query becomes:

SELECT * FROM Students

WHERE id=101;

Every time the ID changes:

id=102
id=103
id=104

Database has to:

Compile Query
Execute Query

Compile Query
Execute Query

Compile Query
Execute Query

again and again.

This wastes time.

PreparedStatement
Engineers thought:

Why compile the same query repeatedly?

Let’s compile once and reuse it.

This became PreparedStatement.

Example
Instead of writing:

SELECT * FROM Students

WHERE id=101

we write:

SELECT * FROM Students

WHERE id=?

Notice:

This is called a placeholder.

Create PreparedStatement
String query =
"SELECT * FROM Students WHERE id=?";

PreparedStatement ps =
[Link](query);

At this stage:

Query Compiled

only once.

Insert Values
[Link](1,101);

means:

1st ? = 101

Query becomes:
SELECT * FROM Students
WHERE id=101

Execute
ResultSet rs =
[Link]();

Notice:

executeQuery();

No query inside.

Because query was already supplied during compilation.

Why is PreparedStatement Faster?

Normal Statement:

Compile
Execute

Compile
Execute

Compile
Execute

PreparedStatement:

Compile Once

Execute
Execute
Execute

Major Advantages
1 Faster
Query compiled only once.

2 More Secure

Protects against SQL Injection.

3 Reusable

Same query can be executed many times.

CallableStatement
Now suppose the database already contains a program.

Example:

CalculateSalary()
GenerateReport()
FindEmployee()

These programs are called:

Stored Procedures

What is a Stored Procedure?

A stored procedure is a block of SQL code stored inside the database.

Example:

CREATE PROCEDURE GetEmployee()

BEGIN
SELECT * FROM Employee;
END

It stays inside the database.

Question
How do we call this stored procedure from Java?

Answer:

CallableStatement

Creating CallableStatement
CallableStatement cs =
[Link](
"{CALL GetEmployee()}"
);

Execute
[Link]();

Stored procedure runs inside the database.

Parameters in CallableStatement
There are 3 types.

IN Parameter
Data goes:

Java ---> Procedure

Example:

[Link](1,101);

Send employee ID.

OUT Parameter
Data comes back:

Java <--- Procedure

Example:

[Link](
1,[Link]);

Procedure returns value.

INOUT Parameter
Both directions.

Java ---> Procedure

Java <--- Procedure

Same parameter sends and receives data.

Difference
Statement PreparedStatement CallableStatement

Executes normal SQL Executes precompiled SQL Executes stored procedures

Slow Fast Used for procedures

Not reusable Reusable Procedure based

VTU 10 Marks Answer

Explain the Following Statement

Interfaces
(i) PreparedStatement
Definition

PreparedStatement is a sub-interface of Statement used to execute precompiled SQL queries. It

improves performance by compiling the query only once and executing it multiple times.

Features

●​ Query is precompiled before execution.

●​ Uses ? as placeholder.
●​ Values are supplied using setXxx() methods.
●​ Faster than Statement.
●​ Protects against SQL Injection.
●​ Suitable for repeated execution of similar queries.

Syntax
String query =
"SELECT * FROM Customers WHERE CustNumber=?";

PreparedStatement ps =
[Link](query);

[Link](1,101);

ResultSet rs =
[Link]();

Advantages

1.​ Faster execution.

2.​ Query compiled only once.
3.​ Reusable.
4.​ More secure.
(ii) CallableStatement
Definition

CallableStatement is a sub-interface of PreparedStatement used to call stored procedures from

a Java application.

Features

●​ Executes stored procedures.

●​ Supports IN, OUT and INOUT parameters.
●​ Uses prepareCall() method.
●​ Allows data exchange between Java and stored procedures.

Parameter Types

IN Parameter

Passes data from Java to the stored procedure.

Java ---> Procedure

OUT Parameter

Returns data from procedure to Java.

Java <--- Procedure

INOUT Parameter

Used for both sending and receiving data.

Java ---> Procedure ---> Java

Syntax
String query =
"{CALL LastOrderNumber(?)}";

CallableStatement cs =
[Link](query);

[Link](
1,[Link]);

[Link]();

String result =
[Link](1);

[Link]();

Advantages

1.​ Executes stored procedures efficiently.

2.​ Reduces network traffic.
3.​ Improves performance.
4.​ Supports IN, OUT and INOUT parameters.

Conclusion
PreparedStatement is used to execute precompiled SQL queries efficiently, whereas
CallableStatement is used to invoke stored procedures from Java applications. Both provide
better performance and flexibility compared to the basic Statement interface.
Illustrate Various Types of Exceptions in
JDBC
Introduction
Exceptions are abnormal conditions that occur during program execution. JDBC methods may
throw exceptions when errors occur while communicating with the database. JDBC mainly
provides three types of exceptions: SQLException, SQLWarning and DataTruncation.

1. SQLException
Definition

SQLException is the most common exception in JDBC. It occurs whenever a database access
error or SQL syntax error occurs.

Causes

●​ Invalid SQL query

●​ Database connection failure
●​ Wrong username/password
●​ Accessing a closed object
●​ Network failure

Example

try

Connection con =

[Link](

url,user,password);

catch(SQLException e)
{

[Link](e);

Important Methods

getNextException()

getErrorCode()

●​ getNextException() returns details of the next exception.

●​ getErrorCode() returns vendor-specific error code.

2. SQLWarning
Definition

SQLWarning represents warning messages generated by the DBMS. Unlike SQLException, it

does not stop program execution.

Features

●​ Indicates non-critical problems.

●​ Program continues execution.
●​ Warning information can be retrieved from the Connection object.

Methods

getWarnings()

getNextWarning()

●​ getWarnings() retrieves warning messages.

●​ getNextWarning() retrieves subsequent warnings.

3. DataTruncation
Definition

DataTruncation exception occurs whenever data is lost due to truncation while transferring data
between Java application and database.

Example

Database field:

VARCHAR(5)

Value inserted:

"AnishKakkar"

Stored value:

"Anish"

Remaining characters are truncated, resulting in DataTruncation exception.

Features

●​ Indicates loss of data.

●​ Occurs during data insertion or retrieval.
●​ Helps detect incomplete data storage.

Summary Table

Exception Type Purpose

SQLException Database access and SQL errors

SQLWarning Non-critical warning messages

DataTruncation Data loss due to truncation

What is Connection Pool? Explain the
Process of Connection Pool.
Introduction
Connecting and reconnecting to a database for every client request is time-consuming and
causes performance degradation. To overcome this problem, JDBC provides Connection
Pooling. Connection Pool is a collection of pre-created database connections that are stored in
memory and reused whenever required.

Definition
Connection Pool is a collection of database connections that remain open and loaded into
memory so that they can be reused without repeatedly connecting to the database.

Need for Connection Pool

●​ Establishing database connection is expensive.
●​ Repeated connection creation decreases performance.
●​ Open connections consume resources.
●​ Large number of users may exhaust available connections.

Diagram
Connection Pool

-----------------------------

| Conn1 | Conn2 | Conn3 |

-----------------------------

/ | \

/ | \
 Client1 Client2 Client3

Process of Connection Pool

Step 1: Creation of Physical Connections

The application server creates physical database connections and stores them in the pool using
PooledConnection objects. These connections remain open and ready for use.

Step 2: Client Requests Connection

A client requests a connection from the connection pool.

Step 3: Logical Connection Creation

The DataSource object provides a logical connection to the client using:

Connection db =

[Link]();

The logical connection uses one of the existing physical connections.

Step 4: Database Operations

The client performs database operations such as:

SELECT INSERT UPDATE DELETE

Step 5: Returning Connection

After completing the work, the client closes the connection.

[Link]();

The connection is returned to the pool and reused instead of being destroyed.

Types of Connections
1. Physical Connection
 ●​ Actual connection between application server and database.
●​ Created using PooledConnection objects.
●​ Stored and reused in the pool.

2. Logical Connection

●​ Connection provided to the client.

●​ Obtained using [Link]().
●​ Uses an existing physical connection.

Advantages
1.​ Improves performance.
2.​ Reduces connection creation overhead.
3.​ Efficient utilization of resources.
4.​ Supports large number of users.
5.​ Faster database access.

Conclusion
Connection Pooling improves the performance of JDBC applications by maintaining a pool of
reusable database connections. It reduces connection creation overhead and enables efficient
database access for multiple clients.
6) Write the Java code to connect to database for
retrieval of empid and ename form Employee Table?
import [Link].*;

class Main {

public static void main(String[] args) throws Exception {

[Link]("[Link]");

String url = "jdbc:mysql://localhost:3306/Employedb";

String user = "root";

String pass = "root123";

Connection conn = [Link](url,user,pass);

Statement st = [Link]();

ResultSet rs = [Link]("SELECT * FROM Employee");

while([Link]()){

[Link]([Link](1)+" "+[Link](2));

[Link]();

}
Define Metadata. Illustrate Different Types
of Metadata Available in JDBC.
Definition
Metadata is data about data. It provides information about database objects such as tables,
columns, indexes, primary keys, stored procedures and database properties. JDBC provides
metadata interfaces to retrieve such information.

Types of Metadata in JDBC

1. DatabaseMetaData

The DatabaseMetaData interface is used to obtain information about the database, tables,
columns, indexes and other DBMS details. It is obtained using the getMetaData() method of
the Connection object.

Syntax

Connection con =

[Link](url,user,pass);

DatabaseMetaData dm =

[Link]();

Common Methods

Method Purpose

getDatabaseProductName() Returns database product name

 getUserName() Returns user name

getURL() Returns database URL

getSchemas() Returns schema names

getPrimaryKeys() Returns primary key details

getProcedures() Returns stored procedure names

getTables() Returns table names

Example

DatabaseMetaData dm =

[Link]();

[Link](

[Link]());

[Link](

[Link]());

[Link](

[Link]());
2. ResultSetMetaData

ResultSetMetaData is used to retrieve information about the ResultSet returned by a query.

Syntax

ResultSet rs =

[Link](

"SELECT * FROM Employee");

ResultSetMetaData rm =

[Link]();

Common Methods

Method Purpose

getColumnCount() Returns number of columns

getColumnName() Returns column name

getColumnTypeName() Returns column datatype

getColumnDisplaySize() Returns column size

Example

ResultSetMetaData rm =

[Link]();
[Link](

[Link]());

[Link](

[Link](1));

Difference Between DatabaseMetaData and

ResultSetMetaData

DatabaseMetaData ResultSetMetaData

Provides database information Provides query result information

Obtained from Connection object Obtained from ResultSet object

Gives tables, schemas, keys Gives columns, types and sizes

Conclusion
Metadata is data about data. JDBC provides DatabaseMetaData and ResultSetMetaData
interfaces to retrieve information about databases and query results respectively, making
database applications more flexible and dynamic.
Define Transaction. How Multiple
Transactions Take Place Through JDBC
with AutoCommit()?
Definition
A transaction is a group of SQL statements that are executed as a single unit of work. All
statements in the transaction must execute successfully; otherwise the transaction is rolled
back.

Need for Transaction Processing

●​ Maintains database consistency.
●​ Ensures all operations are completed successfully.
●​ Prevents partial updates.
●​ Supports commit and rollback operations.

Transaction Processing in JDBC

JDBC provides transaction management using:

commit()
rollback()
setAutoCommit()

methods of the Connection object.

AutoCommit Mode
By default:

AutoCommit = true

In this mode every SQL statement is automatically committed after execution.

Example
[Link](query1);

Automatically committed.

[Link](query2);

Automatically committed.

Each SQL statement acts as a separate transaction.

Handling Multiple Transactions

To execute multiple SQL statements as a single transaction:

Step 1: Disable AutoCommit

[Link](false);

Step 2: Execute Multiple SQL Statements

[Link](query1);

[Link](query2);

[Link](query3);

Step 3: Commit Transaction

[Link]();

All changes are permanently stored in the database.

Step 4: Rollback if Error Occurs

[Link]();

All previously executed statements are cancelled.

Step 5: Enable AutoCommit

[Link](true);

JDBC Program for Transaction Processing

import [Link].*;

class TransactionDemo
{
public static void main(String args[])
throws Exception
{
Connection con =
[Link](
url,user,pass);

try
{
[Link](false);

Statement st =
[Link]();

[Link](
"UPDATE Account SET balance=balance-1000 WHERE id=1");

[Link](
"UPDATE Account SET balance=balance+1000 WHERE id=2");

[Link]();

[Link](
"Transaction Successful");
}
catch(Exception e)
{
[Link]();

[Link](
"Transaction Failed");
}

[Link](true);

[Link]();
}
}
Conclusion
A transaction is a collection of SQL statements executed as a single unit. JDBC manages
transactions using commit(), rollback(), and setAutoCommit() methods. By disabling
AutoCommit, multiple SQL statements can be grouped into a single transaction and committed
together.

Explain Statement Interface Usage in

JDBC. Illustrate with Example.
Introduction
The Statement interface is a part of the [Link] package and is used to execute SQL queries
and updates against a database. A Statement object is created from the Connection object and
is used whenever a query needs to be executed immediately without precompilation.

Creation of Statement Object

A Statement object is created using the createStatement() method.

Syntax
Statement st =
[Link]();

The Statement object is used to send SQL commands to the database.

Methods of Statement Interface

1. executeQuery()

●​ Used for SELECT statements.

 ●​ Returns a ResultSet object.

Syntax
ResultSet rs =
[Link](
"SELECT * FROM Employee");

2. executeUpdate()

●​ Used for INSERT, UPDATE, DELETE and DDL statements.

●​ Returns the number of affected rows.

Syntax
int rows =
[Link](
"UPDATE Employee SET salary=60000");

3. execute()

●​ Used when multiple types of results may be returned.

●​ Returns a boolean value.

Syntax
boolean b =
[Link](sql);

●​ true → ResultSet available.

●​ false → Update count available.

Example Program
import [Link].*;

class Main {
public static void main(String[] args)
throws Exception {

[Link](
"[Link]");
 String url =
"jdbc:mysql://localhost:3306/persondb";

String user = "root";

String pass = "root123";

Connection con =
[Link](
url,user,pass);

Statement st =
[Link]();

ResultSet rs =
[Link](
"SELECT * FROM Employee");

while([Link]())
{
[Link](
[Link](1)+" "+
[Link](2));
}

[Link]();
}
}

Advantages
1.​ Simple to use.
2.​ Suitable for immediate query execution.
3.​ Supports SELECT, INSERT, UPDATE and DELETE operations.
4.​ Provides methods for processing query results.

Conclusion
The Statement interface is used to execute SQL commands directly on a database. The
important methods provided by Statement are executeQuery(), executeUpdate() and
execute(). It is widely used for immediate execution of SQL queries in JDBC applications.

Differentiate Between JDBC and ODBC

Introduction
ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) are technologies
used to connect applications with databases. ODBC was introduced by Microsoft, whereas
JDBC was introduced by Sun Microsystems specifically for Java applications.

Difference Between JDBC and ODBC

ODBC JDBC

ODBC stands for Open Database Connectivity JDBC stands for Java
Database Connectivity

Introduced by Microsoft in 1992 Introduced by Sun

Microsystems in 1997
 Can be used with languages like C, C++, Java, etc. Used specifically for Java
applications

Mainly used on Windows platform Platform independent

Drivers are developed using native languages such as Drivers are mostly developed
C/C++ using Java

Requires ODBC drivers Requires JDBC drivers

Java applications using ODBC may suffer performance Better performance for Java
loss due to internal conversion applications

Platform dependent Platform independent

Not recommended for Java applications Highly recommended for Java

applications

Uses ODBC API Uses JDBC API

JDBC Architecture
Java Application
|
V
JDBC
|
V
Database
ODBC Architecture
Application
|
V
ODBC
|
V
Database

Advantages of JDBC over ODBC

1.​ Platform independent.
2.​ Better performance for Java applications.
3.​ Pure Java implementation.
4.​ Easier integration with Java programs.
5.​ No platform dependency issues.

Show Any Two Syntax of Establishing a

Connection to a Database (5 Marks)
Introduction
In JDBC, a connection between a Java application and a database is established using the
[Link]() method. The method returns a Connection object which is
used to communicate with the database. The getConnection() method is an overloaded
method and can be used in different forms.

Syntax 1: Using URL, Username and Password

String url =
"jdbc:mysql://localhost:3306/persondb";
String user = "root";
String password = "root123";

Connection con =
[Link](
url,user,password);

Explanation

●​ url specifies the database location.

●​ user specifies the username.
●​ password specifies the password.
●​ Returns a Connection object.

Syntax 2: Using Only URL

String url =
"jdbc:mysql://localhost:3306/persondb";

Connection con =
[Link](url);

Explanation

●​ Used when authentication is not required.

●​ Database URL alone is sufficient to establish the connection.

Conclusion
The [Link]() method is used to establish a connection with a
database. The two commonly used syntaxes are:

1.​ Using URL, username and password.

2.​ Using only the database URL.