Module 5

JDBC Objects: The Concept of JDBC; JDBC Driver Types; JDBC Packages; A Brief Overview of the
JDBC process; Database Connection; Associating the JDBC/ODBC Bridge with the Database;
Statement Objects; ResultSet; Transaction Processing; Metadata, Data types; Exceptions.

JDBC

JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the query
with the database, and processing the results.
We can use JDBC API to access tabular data stored in any relational database. By the help of JDBC
API, we can save, update, delete and fetch data from the database.

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 that 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 which is platform independent).

The Need for JDBC

It is important to understand why we need Java Database connectivity. Java applications are required to
connect with the database. Java applications are written in Java programming language, but the
database only understands Structured Query Language (SQL). To establish a connection between Java
application and database, JDBC is used. JDBC contains a set of interfaces and classes which helps to
connect Java applications to the database.

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.\
General tasks of JDBC

Using JDBC, you can write code that:

 Connects to one or more data servers
 Executes any SQL statement
 Obtains a result set so that you can
 navigate through query results
 Obtains metadata from the data

Types of JDBC

JDBC Drivers

JDBC drivers are client-side adapters (installed on the client machine rather than the server) that
translate requests from Java programs into a protocol understood by the DBMS. These drivers are
software components that implement the interfaces in the JDBC API, allowing Java applications to
interact with a database. Sun Microsystems (now Oracle) defines four types of JDBC drivers, which are
outlined below:

Type-1 driver or JDBC-ODBC bridge driver

Type-2 driver or Native-API driver
Type-3 driver or Network Protocol driver
Type-4 driver or Thin driver

1. JDBC-ODBC Bridge Driver – Type 1 Driver

Type-1 driver or JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-
ODBC bridge driver converts JDBC method calls into the ODBC function calls. Type-1 driver is also
called Universal driver because it can be used to connect to any of the databases.
Advantages

This driver software is built-in with JDK so no need to install separately.

It is a database independent driver.

Disadvantages

As a common driver is used in order to interact with different databases, the data transferred through
this driver is not so secured.
The ODBC bridge driver is needed to be installed in individual client machines.
Type-1 driver isn’t written in java, that’s why it isn’t a portable driver.

2. Native-API Driver – Type 2 Driver ( Partially Java Driver)

The Native API driver uses the client -side libraries of the database. This driver converts JDBC method
calls into native calls of the database API. In order to interact with different database, this driver needs
their local API, that’s why data transfer is much more secure as compared to type-1 driver. This driver
is not fully written in Java that is why it is also called Partially Java driver.

Advantage

Native-API driver gives better performance than JDBC-ODBC bridge driver. More secure compared to
the type-1 driver.

Disadvantages
Driver needs to be installed separately in individual client machines
The Vendor client library needs to be installed on client machine.
Type-2 driver isn’t written in java, that’s why it isn’t a portable driver
It is a database dependent driver.
3. Network Protocol Driver – Type 3 Driver (Fully Java Driver)

The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or
indirectly into the vendor-specific database protocol. Here all the database connectivity drivers are
present in a single server, hence no need of individual client-side installation.

Advantages

Type-3 drivers are fully written in Java, hence they are portable drivers.
No client side library is required because of application server that can perform many tasks like
auditing, load balancing, logging etc.
Switch facility to switch over from one database to another database.

Disadvantages
Network support is required on client machine.
Maintenance of Network Protocol driver becomes costly because it requires database-specific coding to
be done in the middle tier.

4. Thin Driver – Type 4 Driver (Fully Java Driver)

Type-4 driver is also called native protocol driver. This driver interact directly with database. It does
not require any native database library, that is why it is also known as Thin Driver.
Advantages

Does not require any native library and Middleware server, so no client-side or server-side installation.
It is fully written in Java language, hence they are portable drivers.

Disadvantage
If the database changes, a new driver may be needed.

Which Driver to use When?

If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is
type-4.
If your Java application is accessing multiple types of databases at the same time, type 3 is the
preferred driver.
Type 2 drivers are useful in situations, where a type 3 or type 4 driver is not available yet for your
database.
The type 1 driver is not considered a deployment-level driver, and is typically used for development
and testing purposes only.

JDBC Components

There are generally 4 main components of JDBC through which it can interact with a database. They
are as mentioned below:

1. JDBC API

It provides various methods and interfaces for easy communication with the database. It includes two
key packages

JDBC Packages

java.sql: This package, is the part of Java Standard Edition (Java SE) , which contains the core
interfaces and classes for accessing and processing data in relational databases. It also provides
essential functionalities like establishing connections, executing queries, and handling result sets

javax.sql: This package is the part of Java Enterprise Edition (Java EE) , which extends the capabilities
of java.sql by offering additional features like connection pooling, statement pooling, and data source
management.

It also provides a standard to connect a database to a client application.

2. JDBC Driver Manager

Driver manager is responsible for loading the correct database-specific driver to establish a connection
with the database. It manages the available drivers and ensures the right one is used to process user
requests and interact with the database.

3. JDBC Test Suite

It is used to test the operation(such as insertion, deletion, updating) being performed by JDBC Drivers.
4. JDBC Drivers
JDBC drivers are client-side adapters (installed on the client machine, not on the server) that convert
requests from Java programs to a protocol that the DBMS can understand. There are 4 types of JDBC
drivers:

Type-1 driver or JDBC-ODBC bridge driver

Type-2 driver or Native-API driver (partially java driver)
Type-3 driver or Network Protocol driver (fully java driver)
Type-4 driver or Thin driver (fully java driver) – It is deprecated and no longer supported since Java 8.
Instead modern drivers like the Type – 4 driver are widely used.

Database Connection and Associating the JDBC/ODBC Bridge with the Database

Database Connections in Java

Database connectivity is a fundamental concept in Java programming that allows applications to
interact with databases. Let me explain the key components and provide some simple Java programs to
demonstrate.

JDBC Overview
Java Database Connectivity (JDBC) is an API that provides methods for connecting to and interacting
with databases. It serves as a bridge between Java applications and various database systems.

Basic Components of JDBC

1. JDBC Driver: Software that allows Java applications to interact with databases
2. Connection: Represents the connection to a specific database
3. Statement: Used to execute SQL queries
4. ResultSet: Contains data retrieved from the database

JDBC/ODBC Bridge
The JDBC/ODBC Bridge (historically known as the JDBC-ODBC Bridge) was developed to allow
Java applications to connect to databases through ODBC drivers. ODBC (Open Database Connectivity)
is a standard API for accessing database management systems.

Simple Java Programs for Database Connection

Example 1: Basic Database Connection

package jdbc_connect;
import java.sql.*;

public class JDBC_Connect {

static Connection conn = null;

public static void main(String[] args) {

Connection con = null;
try {
// Register the JDBC driver - needs to be caught or declared
Class.forName("com.mysql.cj.jdbc.Driver");

// Creating connection object using DriverManager

con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test",
"root",
""
);
if (con != null) {
System.out.println("Database successfully opened!");
}
}
catch (ClassNotFoundException e) {
// Handle the ClassNotFoundException
System.err.println("MySQL JDBC Driver not found!");
e.printStackTrace();
}
catch (SQLException e) {
// Handle SQL exceptions
System.err.println("Database connection failed!");
e.printStackTrace();
}
finally {
// Close the connection
if (con != null) {
try {
con.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
o/p

Database successfully opened!

Example 2: Executing a Query

package com.mysql;
import java.sql.*;

public class TableDisplay {

public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "";

try {
// Register JDBC driver (not needed for newer versions of JDBC)
// Class.forName("com.mysql.jdbc.Driver");

// Establish connection
Connection connection = DriverManager.getConnection(url, username, password);

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

String query = "SELECT * FROM coffee";

// create the java statement

Statement st = connection.createStatement();

// execute the query, and get a java resultset

ResultSet rs = st.executeQuery(query);

// iterate through the java resultset

while (rs.next())
{
int id = rs.getInt("id");
String coffee_name = rs.getString("coffee_name");
int price= rs.getInt("price");

// print the results

System.out.format("\n%d, %s, %d", id,coffee_name,price);
}

// Close resources
rs.close();
st.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
 }
}

o/p

102, Royal, 950

103, Diamond, 890
267, Delux, 950

Example 3: Using PreparedStatement

package com.mysql;
import java.sql.*;

public class PreparedStatementExample {

public static void main(String[] args) {

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

String username = "root";
String password = "";

try {
Connection connection = DriverManager.getConnection(url, username, password);

// Create a prepared statement

String sql = "insert into coffee (coffee_name, price) values (?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);

// Set parameters
preparedStatement.setString(1, "Bru Coffee");
preparedStatement.setString(2, "1200");

// Execute the update

int rowsAffected = preparedStatement.executeUpdate();
System.out.println(rowsAffected + " row(s) inserted.");

// Close resources
preparedStatement.close();
connection.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
o/p

102, Royal, 950

103, Diamond, 890
267, Delux, 950
268, Delux, 950
269, Delux, 950
270, Tata Coffine, 1200
271, Bru Coffee, 1200

Here's a concise overview of the JDBC (Java Database Connectivity) process:

JDBC Process Steps:

1. Load Database Driver

 Use Class.forName() to load the specific database driver

 Registers the driver with DriverManager

2. Establish Database Connection

 Use DriverManager.getConnection()
 Provide connection URL, username, and password
 Creates a Connection object

3. Create SQL Statement

 Prepare statement using Connection object

Options include:

 Statement (static SQL)

 PreparedStatement (parameterized queries)
 CallableStatement (stored procedures)

4. Execute Query

 Use methods like executeQuery() for SELECT

 Use executeUpdate() for INSERT, UPDATE, DELETE
 Retrieves ResultSet for SELECT queries

5.Process Results

 Iterate through ResultSet

 Extract data using getter methods (getString(), getInt(), etc.)
 6. Close Resources

 Close ResultSet
 Close Statement
 Close Connection

Example Code Snippet:

try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users");
ResultSet rs = pstmt.executeQuery();

while (rs.next()) {
// Process results
}
} catch (SQLException e) {
e.printStackTrace();
}

Example program for JDBC Process

Read all the existing records from the table coffee which is from the database test and insert a
new coffee product into it

/*
The following MySQL commands have to be executed in MySQL before executing the following
JDBC process.

A Java MySQL operations example

MySQL commands to be executed

use test;

CREATE TABLE IF NOT EXISTS `coffee` (`id` int(5) NOT NULL

AUTO_INCREMENT,`coffee_name` varchar(100) NOT NULL,`price` int(5), PRIMARY KEY
(`id`) );

INSERT INTO coffee values (101,'Febary',120);

INSERT INTO coffee values (102,'Royal',140);
INSERT INTO coffee values (103,'Diamond',180);
*/
/**
* A Java MySQL SELECT and insert statement example.
* Demonstrates the use of a SQL SELECT statement against a
* MySQL database, called from a Java program.
*
*/

package com.mysql;
import java.sql.*;
import java.util.Properties;

public class Mysql_select

{
public static void main(String[] args)
{
try
{
Connection dbConnection = null;
try
{ String url = "jdbc:mysql://localhost/test";
Properties info = new Properties();
info.put("user", "root");
info.put("password", "");
dbConnection = DriverManager.getConnection(url, info);
if (dbConnection != null)
{
System.out.println("Successfully connected to MySQL database test");
}
}
catch (SQLException ex)
{
System.out.println("An error occurred while connecting MySQL databse");
ex.printStackTrace();
}

// our SQL SELECT query.

// if you only need a few columns, specify them by name instead of using "*"
String query = "SELECT * FROM coffee";

// create the java statement

Statement st = dbConnection.createStatement();

// execute the query, and get a java resultset

ResultSet rs = st.executeQuery(query);

// iterate through the java resultset

while (rs.next())
{
 int id = rs.getInt("id");
String coffee_name = rs.getString("coffee_name");
int price= rs.getInt("price");

// print the results

System.out.format("\n%d, %s, %d", id,coffee_name,price);
}

// inserting records

String query1 = "insert into coffee (coffee_name, price) values ('Tajmahal', 950)";

// Inserting record

PreparedStatement stmt=dbConnection.prepareStatement("insert into coffee values(?,?)");

// rds into mysql preparedstatement

stmt.execute(query1);

stmt.close();

}
catch (Exception e)
{
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}

o/p

Successfully connected to MySQL database test

101, Febary, 120

102, Royal, 140
103, Diamond, 180
264, Delux, 950

Meta data in JDBC

Metadata in JDBC refers to data that describes the structure and characteristics of a database. It
provides information about the database itself rather than the actual data stored within it. JDBC (Java
Database Connectivity) offers several metadata interfaces that allow you to programmatically discover
information about database objects.

Key JDBC Metadata Interfaces

1. DatabaseMetaData: Provides information about the entire database, including capabilities,
supported SQL features, and available database objects.
2. ResultSetMetaData: Provides information about a specific result set, such as column names,
types, and properties.

DatabaseMetadataExample

package com.mysql;

import java.sql.*;
import java.util.Properties;

public class DatabaseMetadataExample {

public static void main(String[] args) {

try
{
//

Connection con = null;

// Register the JDBC driver - needs to be caught or declared

Class.forName("com.mysql.cj.jdbc.Driver");

con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test",
"root",
""
);
if (con != null)
{ System.out.println("Successfully connected to MySQL database test");

}
DatabaseMetaData dbmd = con.getMetaData();
System.out.println("Database: " + dbmd.getDatabaseProductName());
System.out.println("Version: " + dbmd.getDatabaseProductVersion());
System.out.println("Driver: " + dbmd.getDriverName());
System.out.println("Driver Version: " + dbmd.getDriverVersion());
 // List all tables in the database
System.out.println("\nTables in database:");
ResultSet tables = dbmd.getTables(null, null, "%", new String[]{"TABLE"});
while (tables.next()) {
System.out.println(tables.getString("TABLE_NAME"));
}

// Get information about primary keys for a specific table

System.out.println("\nPrimary keys for 'employee' table:");
ResultSet pks = dbmd.getPrimaryKeys(null, null, "employee");
while (pks.next()) {
System.out.println("Column name: " + pks.getString("COLUMN_NAME"));
System.out.println("Key name: " + pks.getString("PK_NAME"));
}
}
catch (ClassNotFoundException e) {
System.out.println("MySQL JDBC Driver not found!");
e.printStackTrace();
}
catch (SQLException ex)
{
System.out.println("An error occurred while connecting MySQL databse");
ex.printStackTrace();
}
}
}

o/p

Successfully connected to MySQL database test

Database: MySQL
Version: 5.5.5-10.4.20-MariaDB
Driver: MySQL Connector/J
Driver Version: mysql-connector-j-8.1.0 (Revision: 7b6f9a337afe6ccb41823df485bf848ca7952b09)

Tables in database:
Employee
Employee1
emp
emp1
emp2
emp3
mail
tutorials_tbl

Primary keys for 'employees' table:

Column name: Emp_NO
Key name: PRIMARY
ResultSetMetaData Example Program

package com.mysql;
import java.sql.*;
public class ResultSetMetaDataExample {
public static void main(String[] args) {
// Register the JDBC driver - needs to be caught or declared
try
{
Class.forName("com.mysql.cj.jdbc.Driver");

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

String username = "root";
String password = "";

Connection conn = DriverManager.getConnection(url, username, password);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM coffee");

ResultSetMetaData rsmd = rs.getMetaData();

// Print column information

int columnCount = rsmd.getColumnCount();
System.out.println("Total columns: " + columnCount);

for (int i = 1; i <= columnCount; i++) {

System.out.println("\nColumn " + i + " Information:");
System.out.println("Name: " + rsmd.getColumnName(i));
System.out.println("Type: " + rsmd.getColumnTypeName(i));
System.out.println("Size: " + rsmd.getColumnDisplaySize(i));
System.out.println("Nullable: " + (rsmd.isNullable(i) ==
ResultSetMetaData.columnNullable));
}

// Print actual data with column headers

System.out.println("\nQuery Results:");
for (int i = 1; i <= columnCount; i++) {
System.out.print(rsmd.getColumnName(i) + "\t");
}
System.out.println();

while (rs.next()) {
for (int i = 1; i <= columnCount; i++) {
System.out.print(rs.getString(i) + "\t");
}
System.out.println();
}
}
 catch (ClassNotFoundException e) {
System.out.println("MySQL JDBC Driver not found!");
e.printStackTrace();
}

catch (SQLException e) {
e.printStackTrace();
}
}
}

o/p

Total columns: 3

Column 1 Information:
Name: id
Type: INT
Size: 10
Nullable: false

Column 2 Information:
Name: coffee_name
Type: VARCHAR
Size: 100
Nullable: false

Column 3 Information:
Name: price
Type: INT
Size: 10
Nullable: true

Query Results:
id coffee_name price
102 Royal 950
103 Diamond 890
267 Delux 950
268 Delux 950
269 Delux 950
270 Tata Coffine 1200
271 Bru Coffee 1200