Module 3

Back End Development

Java Database Connectivity
● JDBC stands for Java Database Connectivity. JDBC is a Java API to connect
and execute the query with the database.
● It is a specification from Sun Microsystems that provides a standard
abstraction(API or Protocol) for Java applications to communicate with
various databases.
● It provides the language with Java database connectivity standards. It is
used to write programs required to access databases.
● JDBC, along with the database driver, can access databases and
spreadsheets.
● The enterprise data stored in a relational database(RDB) can be accessed
with the help of JDBC APIs.
Components of JDBC
1. JDBC API
2. JDBC Driver manager
3. JDBC Test suite
4. JDBC-ODBC Bridge Drivers
Architecture of JDBC
JDBC API
● It provides various methods and interfaces for easy
communication with the database.
● It provides two packages as follows, which contain the java SE
and Java EE platforms to exhibit WORA(write once run
anywhere) capabilities.
● The java.sql package contains interfaces and classes of JDBC
API.
JDBC Driver manager
● It loads a database-specific driver in an application to
establish a connection with a database.
● It is used to make a database-specific call to the database to
process the user request.
JDBC Test suite
● It is used to test the operation(such as insertion, deletion, updation) being
performed by JDBC Drivers.

JDBC-ODBC Bridge Drivers

● It connects database drivers to the database. This bridge translates the
JDBC method call to the ODBC function call.
● It makes use of the sun.jdbc.odbc package which includes a native library
to access ODBC characteristics.
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.
● JDBC drivers are the software components which implements interfaces
in JDBC APIs to enable java application to interact with the database.
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
JDBC-ODBC bridge driver – Type 1 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.
JDBC-ODBC bridge driver – Type 1 driver
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.
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.
Native-API driver – Type 2 driver ( Partially Java driver)
Advantage

● Native-API driver gives better performance than JDBC-ODBC bridge 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.
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.
Network Protocol driver – Type 3 driver (fully Java driver)

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.
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.
Thin driver – Type 4 driver (fully Java 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 varies, then the driver will carry because it is database
dependent.
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.
Steps to Connect Java Application with Database
Step 1 – Import the Packages

Step 2 – Load the drivers using the forName() method

Step 3 – Register the drivers using DriverManager

Step 4 – Establish a connection using the Connection class object

Step 5 – Create a statement

Step 6 – Execute the query

Step 7 – Close the connections

Step 1-Import the packages:
● This includes uploading all the packages containing the JDBC classes,
interfaces, and subclasses used during the database programming.
● More often than not, using import java.sql.* is enough. However, other
classes can be imported if needed in the program.
Step 2 – Load the drivers using the forName() method
In order to begin with, you first need to load the driver or register it before using it in the program.

Registration is to be done once in your program. You can register a driver in one of two ways mentioned below
as follows:

2-A Class.forName()

Here we load the driver’s class file into memory at the runtime. No need of using new or create objects. The
following example uses Class.forName() to load the Oracle driver as shown below as follows:

Class.forName(“oracle.jdbc.driver.OracleDriver”);
2-B DriverManager.registerDriver()

DriverManager is a Java inbuilt class with a static member register. Here we call the constructor of the driver
class at compile time. The following example uses DriverManager.registerDriver()to register the Oracle driver as
shown below:

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver())
Step 3: Establish a connection using the Connection class
object
After loading the driver, establish connections as shown below as follows:
Connection con = DriverManager.getConnection(url,user,password)

● user: Username from which your SQL command prompt can be accessed.
● password: password from which the SQL command prompt can be accessed.
● con: It is a reference to the Connection interface.
● Url: Uniform Resource Locator which is created as shown below:
String url = “ jdbc:oracle:thin:@localhost:1521:xe”

1. oracle is the database used,

2. thin is the driver used,
3. @localhost is the IP Address where a database is stored,
4. 1521 is the port number
5. xe is the service provider
Step 4: Create a statement
Once a connection is established you can interact with the database. The JDBCStatement,
CallableStatement, and PreparedStatement interfaces define the methods that enable you to
send SQL commands and receive data from your database.
Use of JDBC Statement is as follows:
Statement st = con.createStatement();

Note: Here, con is a reference to Connection interface used in previous step .

 Step 5: Execute the query
import java.io.*; System.out.println(
import java.sql.*; "Connection Established
successfully" );
class GFG {
public static void main(String[] args) throws Statement st = con.createStatement();
Exception
ResultSet rs
{
String url
= st.executeQuery(query); //
= Execute query
"jdbc:mysql://localhost:3306/table_name" ; // rs.next();
table details String name
String username = "rootgfg" ; // MySQL = rs.getString( "name"); // Retrieve
credentials name from db
String password = "gfg123" ;
String query System.out.println(name); // Print
= "select *from students" ; // query result on console
to be run st.close(); // close statement
Class.forName( con.close(); // close connection
"com.mysql.cj.jdbc.Driver" ); //
System.out.println( "Connection
Driver name
Closed...." );
Connection con =
DriverManager.getConnection( }
url, username, password); }
Step 6: Closing the connections
● So finally we have sent the data to the specified location and now we are on the verge of
completing our task.
● By closing the connection, objects of Statement and ResultSet will be closed automatically.
The close() method of the Connection interface is used to close the connection. It is shown
below as follows:

con.close();
Example
import java.sql.*; // Username and password to access DB
// Importing required classes // Custom initialization
import java.util.*; String user = "system" ;
String pass = "12345";
// Main class
class Main { // Entering the data
Scanner k = new Scanner(System.in);
// Main driver method
public static void main(String a[]) System.out.println( "enter name" );
{ String name = k.next();

// Creating the connection using System.out.println( "enter roll no" );

Oracle DB int roll = k.nextInt();
// Note: url syntax is standard, so
do grasp System.out.println( "enter class" );
String url = String cls = k.next();
"jdbc:oracle:thin:@localhost:1521:xe" ;
// Inserting data using SQL query // Executing query
String sql = "insert into student1 int m = st.executeUpdate(sql);
values('" + name if (m == 1)
+ "'," + roll + ",'" +
System.out.println(
cls + "')";
"inserted successfully : " +
// Connection class object sql);
Connection con = null; else
System.out.println("insertion
// Try block to check for exceptions
failed");
try {

// Registering drivers // Closing the connections

DriverManager.registerDriver( con.close();
new }
oracle.jdbc.OracleDriver());
// Catch block to handle exceptions
// Reference to connection
interface catch (Exception ex) {
con = // Display message when exceptions occurs
DriverManager.getConnection(url, user, System.err.println(ex);
}
pass); }
// Creating a statement
Statement st = con.createStatement(); }
Output