Java Programming (COSC -3053)

Chapter 6
Java - Database connectivity

1
Objectives
 Learn about Introduction on database systems
 provides Structured query language (SQL)

 Understand Basic JDBC programming concept

 Populating database
 Executing queries
 Manipulating query results

2
Java Database Connectivity (JDBC)
 Java Database Connectivity (JDBC): is an API (Application Programming Interface)
that provides a standard way for Java programs to interact with databases.
 In most Java applications, there is always a need to interact with databases to
retrieve, manipulate, and process the data. For this purpose, Java JDBC has been
introduced.
 It allows developers to write Java code that can perform various database operations,
 such as connecting to a database, executing SQL queries, and retrieving and
manipulating data.
 By using JDBC, we can interact with different types of Relational Databases such as
Oracle, MySQL, MS Access, Sybase etc.
 JDBC acts as a bridge between the Java programming language and the database
management system (DBMS).
 It provides a set of classes and interfaces that enable developers to communicate
with different types of databases using a common set of methods and functions.
3
Cont.
● Before JDBC, ODBC API was introduced to connect and perform operations with the
database.
● ODBC uses an ODBC driver which is platform-dependent because it was written in
the C programming language.
● But, JDBC API that written in Java language, is platform-independent, and makes
Java platform-independent itself.
● JDBC API uses JDBC drivers to connect with the database, and it acts as an interface
between the Java program and Database.
● 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.
● The java.sql package contains classes and interfaces for JDBC API.

4
JDBC Architecture
 The JDBC API supports both two-tier and three-tier processing models for database
access but in general, JDBC Architecture consists of two layers:
 JDBC API: This provides the application-to-JDBC Manager connection.
 JDBC Driver API: This supports the JDBC Manager-to-Driver Connection.

5
JDBC API
 The JDBC API uses a driver manager and database-specific drivers to provide
transparent connectivity to heterogeneous databases.
 JDBC API: The JDBC API provides a set of interfaces and classes that define the
methods and behaviors for database access.
 It includes interfaces for connecting to databases, executing SQL queries, retrieving
and updating data, managing transactions, and more.
 The JDBC API is part of the Java SE platform and is included in the java.sql and
javax.sql packages.

The java.sql package contains classes and interfaces for JDBC API.

List of popular classes and interfaces of JDBC API are
JDBC Classes JDBC Interface
- DriverManager - Driver - ResultSet
- Blob - Connection - ResultSetMetaData
- Clob - Statement - DatabaseMetaData
- Types - PreparedStatement - RowSet
- CallableStatement
6
JDBC driver manager
 The JDBC Driver Manager is responsible for managing the available JDBC drivers.
 It provides methods for loading and registering JDBC drivers dynamically at runtime.
 The Driver Manager is used to establish a connection to a database by selecting an
appropriate driver based on the provided database URL.
 It also manages the connection pooling and driver selection mechanisms.
 The JDBC driver manager ensures that the correct driver is used to access each data
source.
 The driver manager is capable of supporting multiple concurrent drivers connected
to multiple heterogeneous databases.

7
JDBC Driver
What is “JDBC Driver”?
● JDBC drivers are software components that implement the JDBC API for a
specific database management system (DBMS).
● Each database vendor provides its own JDBC driver that handles the
communication between the Java program and the database.
● It convert requests from Java programs to a protocol that the DBMS can
understand.
There are 4 types of JDBC Drivers available. They are
 Type I : JDBC-ODBC Bridge Driver
 Type II: Native API Driver (Partially Java Driver)
 Type III: Network Protocol Driver (Fully Java Driver)
 Type IV: Thin Driver (Fully Java Driver)

8
Cont.
Type I: JDBC-ODBC Bridge Driver
 It was commonly used in older versions of JDBC and provided a way to connect to

databases through ODBC (Open Database Connectivity) drivers.

 The Type 1 driver acts as a bridge between the JDBC API and the ODBC API.
 It converts JDBC method calls into equivalent ODBC function calls, allowing Java

applications to access databases that have ODBC drivers available.

 This Driver is platform-dependent because it uses ODBC which depends on the native

library of the OS.

 In this Type, JDBC–ODBC driver should be installed in each client system & the DB

must support ODBC driver.

9
cont.
Advantages:
● easy to use.
● can be easily connected to any database.
Disadvantages:
● Performance degraded because JDBC method call is converted into the ODBC
function calls.
● The ODBC driver needs to be installed on the client machine.
Type II: Native API Driver
● It is similar to Type I Driver. Here, the ODBC part is replaced with native code in
Type II Driver. This native code part is targeted at a specific database product.
● It uses the libraries of the client-side of the database.
● This Driver converts the JDBC method calls to native C/C++ method calls of the
database native API.
● When the database gets the requests from the user, the requests are processed and sent
back with the results in the native format which are to be converted to JDBC format
and give it to the Java application.
10
Cont.
 This type of driver gives faster response and performance than the Type I driver.

Advantage:
● Performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:
● The Native driver needs to be installed on the each client machine.

● The Vendor client library needs to be installed on client machine.

11
Cont.
Type III: Network Protocol Driver
 The Type III driver, also known as the Network Protocol Driver, is a JDBC driver

that uses a middle-tier server as a mediator between the Java application and the
database.
 It communicates with the database server through a network protocol, which is

typically specific to the DBMS being accessed.

 The type III driver is fully written in Java. It is like a 3-tier approach to access the

database.
 It sends the JDBC method calls to an intermediate server.

 On behalf of the JDBC, the intermediate server communicates with the database.

 The Application server (intermediate or middle – tier) converts the JDBC calls either

directly or indirectly to the vendor-specific Database protocol .

12
Cont.

Advantage:
● No client side library is required because of application server.

Disadvantages:
● Network support is required on client machine.

● Requires database-specific coding to be done in the middle tier.

● Maintenance of Network Protocol driver becomes costly because it requires

database-specific coding to be done in the middle tier.
13
Type IV: Thin Driver
 The Type IV JDBC driver is commonly known as the "Thin Driver" or the "Direct-
to-Database Driver.“
 The Type IV driver is a pure Java implementation that communicates directly with
the database server using a DBMS-specific network protocol.
 It does not rely on any external libraries or middleware.
 This driver is considered the most preferred and widely used driver type in JDBC due
to its platform independence and better performance.
 It allows for easy deployment
● It is installed inside the JVM (Java Virtual Machine) of the client, so you don’t have
to install any software on the client or server-side.
● This driver architecture has all the logic to communicate directly with the DB in a
single driver.

14
Cont.

Advantage:
● Better performance than all other drivers.

● No software is required at client side or server side.

Disadvantage:
● Drivers depend on the Database.

15
JDBC Connection Steps
There are 6 basic steps to connect with JDBC. They are
Step 1 – Import Packages
● Irrespective of the JDBC Driver, add the following import statement in the Java
program. import java.sql.*;
● This package provides classes and interfaces to perform most of the JDBC functions
like creating and executing SQL queries.
Step 2 - Load Driver
● Load/register the driver in the program before connecting to the Database.
● You need to register it only once per database in the program.
We can load the driver in the following 2 ways
I. Class.forName()
● The forName() method of Class class is used to register the driver class.
● In this way, the driver’s class file loads into the memory at runtime.
● It explicitly loads the driver.

16
Cont.
 Some Database with their JDBC driver name
DB Name JDBC Driver Name
MySQL com.mysql.cj.jdbc.Driver
Oracle oracle.jdbc.driver.OracleDriver
Sybase com.sybase.jdbcSybDriver
Microsoft SQL Server com.microsoft.sqlserver.jdbc.SQLServerDriver
MS Access net.ucanaccess.jdbc.UcanaccessDriver

II. DriverManager.registerDriver()
● DriverManager is an inbuilt class that is available in the java.sql package.
● Before you connect with the database, you need to register the driver with
DriverManager.

17
Cont.
● The main function of DriverManager is to load the driver class of the Database and
create a connection with DB.
● It implicitly loads the driver. While loading, the driver will register with JDBC
automatically. Example
DriverManager.registerDriver(neworacle.jdbc.driver.OracleDriver())

Step 3 - Establish Connection

● DriverManager class has the getConnection method, that used to establish
connection with the database.
● To call getConnection() method, we need to pass 3 parameters, such as URL, a
username, and a password with string data type to access the database.
● The getConnection() method is an overloaded method.
● The 2 methods are:
 getConnection(URL, username, password);
 getConnection(URL);

18
Cont.
 Some of the JDBC connection strings for the different databases:
DB Name Connection String/DB URL
MySQL jdbc:mysql://HOST_NAME:PORT/DATABASE_NAME
Oracle jdbc:oracle:thin:@HOST_NAME:PORT:SERVICE_NAME
Sybase jdbc:Sybase:Tds:HOSTNAME:PORT/DATABASE_NAME
MS Access jdbc:ucanaccess://DATABASE_PATH

Example-

Connection con =
DriverManager.getConnection(jdbc:oracle:thin:@localhost:
●1521:xe,System,Pass123)
thin - refers to the Driver type. ● System – User name to connect to the

● localhost - is where the Oracle database is running. Oracle Database.

● 1521 - is the port number to connect to DB. ● Pass123 – Password

● xe – SID
19
Cont.
Step 4 - Create Statement and Execute SQL queries
● Once the connection has established, we can interact with the connected Database.
First, we need to create the statement to perform the SQL query and then execute the
statement.
I. Create Statement
● In order to send the SQL commands to database from our java program, we need
Statement object.
● We can get the Statement object by calling the createStatement() method
on connection.
There are 3 Statement interfaces are available in the java.sql package.
a. Statement
● This interface is used to implement simple SQL statements with no parameter.

● It returns the ResultSet object.

Statement st = conn.createStatement();
20
Cont.
b. PreparedStatement
● This interface extends the Statement interface. So, it has more features than the
Statement interface.
● It is used to implement parameterized and precompiled SQL statements.
Example –
String s_query = “Select * from states where sid = 1”;
PreparedStatement pst = conn.prepareStatement(s_query);
c. CallableStatement
● This interface extends the PreparedStatement interface.
● So, it has more features than the PreparedStatement interface.
● It is used to implement a parameterized SQL statement that invokes procedure or
function in the database.
● A stored procedure works like a method or function in a class.

21
Cont.
● It supports the IN and OUT parameters.
● The CallableStatement instance is created by calling the prepareCall method of the
Connection object.
Example -

CallableStatement cs = con.prepareCall("{call
procedures(?,?)}");
II. Execute the SQL Query
There are 4 important methods to execute the query in Statement interface.
a. ResultSet executeQuery(String sql)
● The executeQuery() method in Statement interface is used to execute the SQL query
and retrieve the values from DB.
● It returns the ResultSet object, that can be used to get all the records of a table.

● Normally, we will use this method for the SELECT query.

22
Cont.
b. executeUpdate(String sql)
● The executeUpdate() method is used to execute value specified queries like INSERT,
UPDATE, DELETE (DML statements), or DDL statements that return nothing.
● Mostly, we will use this method for inserting and updating.
c. execute(String sql)
● The execute() method is used to execute the SQL query.
● It returns true if it executes the SELECT query. And, it returns false if it executes
INSERT or UPDATE query.
d. executeBatch()
● This method is used to execute a batch of SQL queries to the Database and if all the
queries get executed successfully, it returns an array of update counts.
● We will use this method to insert/update the bulk of records.
Step 5 - Retrieve Results
● When we execute the queries using the executeQuery() method, the result will be
stored in the ResultSet object.

23
Cont.
● The returned ResultSet object will never be null even if there is no matching record in
the table.
● ResultSet object is used to access the data retrieved from the Database.
ResultSet rs = st.executeQuery(QUERY);
● We can use the executeQuery() method for the SELECT query.
● When someone tries to execute the insert/update query, it will throw SQLExecption
with the message “executeQuery method can not be used for update”.
● A ResultSet object points to the current row in the Resultset.
● To iterate the data in the ResultSet object, call the next() method in a while loop. If
there is no more record to read, it will return FALSE.
● ResultSet can also be used to update data in DB.
● We can get the data from ResultSet using getter methods such as getInt(),
getString(), getDate().
● We need to pass the column index or column name as the parameter to get the values
using Getter methods.
24
Cont.
Step 6 - Close Connection
● Finally, we need to make sure that we have closed the resource after we have used it.
● If we don’t close them properly we may end up out of connections.
● When we close the Connection object, Statement and ResultSet objects will be closed
automatically.
conn.close();

25
Java Database Connectivity with MySQL
In this example we are using MySQL as the database. So we need to know following
information's for the MySQL database:
● Driver class: The driver class for the MySQL database is com.mysql.cj.jdbc.Driver.
● Connection URL: The connection URL for the MySQL database
is jdbc:mysql://localhost:3306/alliance
● Username: The default username for the MySQL database is root.
● Password: It is the password given by the user at the time of installing the MySQL
database. In this example, we are going to use root as the password.
Let's first create a table in the MySQL database, but before creating table, we need
to create database first.
create database alliance;
use alliance;
create table stud(id int(10),name varchar(40),age int(3));
Following is example to Connect Java Application with MySQL database

26
Cont.
import java.sql.*;
class MysqlCon{
public static void main(String args[]){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql:
//localhost:3306/alliance","root","root");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from stud");
while(rs.next())
System.out.println(rs.getInt(1)+“ "+rs.getString(2)+"
"+rs.getString(3));
con.close();
}catch(Exception e){ System.out.println(e);}
}
}

27
Java Database Connectivity with Oracle
In this example we are using Oracle 10g as the database. So we need to know
following information's for the MySQL database:
● Driver class: The driver class for the oracle database
is oracle.jdbc.driver.OracleDriver.
● Connection URL: The connection URL for the oracle10G database
is jdbc:oracle:thin:@localhost:1521:xe.
● Username: The default username for the oracle database is system.
● Password: It is the password given by the user at the time of installing the oracle
database.
Let's first create a table in the Oracle 10g database Following is the SQL query to
create a table.
create table stud(id int(10),name varchar(40),age int(3));
Following is example to Connect Java Application with Oracle 10g database

28
Cont.
import java.sql.*;
class MysqlCon{
public static void main(String args[]){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:
thin:@localhost:1521:xe","system",“root");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from stud");
while(rs.next())
System.out.println(rs.getInt(1)+“ "+rs.getString(2)+"
"+rs.getString(3));
con.close();
}catch(Exception e){ System.out.println(e);}
}
}
29