Types of JDBC Drivers

 JDBC Drivers
 JDBC-ODBC bridge driver
 Native-API driver
 Network Protocol driver
 Thin driver

JDBC Driver is a software component that enables java application to interact with the database.
There are 4 types of JDBC drivers:

 JDBC-ODBC bridge driver

 Native-API driver (partially java driver)
 Network Protocol driver (fully java driver)
 Thin driver (fully java driver)

1) JDBC-ODBC bridge driver

The 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. This is now discouraged
because of thin driver.

Note: In Java 8, the JDBC-ODBC Bridge has been removed.

 Oracle does not support the JDBC-ODBC Bridge from Java 8. Oracle
recommends that you use JDBC drivers provided by the vendor of your
database instead of the JDBC-ODBC Bridge.

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.

2) Native-API driver

The Native API driver uses the client-side libraries of the database. The driver converts JDBC
method calls into native calls of the database API. It is not written entirely in java.

A
dvantage:

 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.

3) Network Protocol driver

The Network Protocol driver uses middleware (application server) that converts
JDBC calls directly or indirectly into the vendor-specific database protocol. It is
fully written in java.

Advantage:

 No client side library is required because of application server that can

perform many tasks like auditing, load balancing, logging etc.

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.

4) Thin driver

The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is
why it is known as thin driver. It is fully written in Java language.

Advantages:

 Better performance than all other drivers.

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

Disadvantage:

 Drivers depend on the Database.

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:

 Register the Driver class

 Create connection
 Create statement
 Execute queries
 Close connection
 1) 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.

Syntax of forName() method

public static void forName(String className)throws ClassNotFoundExceptio

n

Example to register the OracleDriver class

Here, Java program is loading oracle driver to establish database connection.

Class.forName("oracle.jdbc.driver.OracleDriver");

2) Create the connection object

The getConnection() method of DriverManager class is used to establish connection with the
database.

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException

 2) public static Connection getConnection(String url,String name,String passw
ord) throws SQLException

Example to establish connection with the Oracle database

Connection con=DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe","system","password");

3) Create the Statement object

The createStatement() method of Connection interface is used to create statement. The object of
statement is responsible to execute queries with the database.

Syntax of createStatement() method

public Statement createStatement()throws SQLException

Example to create the statement object

Statement stmt=con.createStatement();

4) Execute the query

The executeQuery() method of Statement interface is used to execute queries to the database. This
method returns the object of ResultSet that can be used to get all the records of a table.

Syntax of executeQuery() method

public ResultSet executeQuery(String sql)throws SQLException

Example to execute query

ResultSet rs=stmt.executeQuery("select * from emp");

while(rs.next()){

System.out.println(rs.getInt(1)+" "+rs.getString(2));

5) Close the connection object

By closing connection object statement and ResultSet will be closed automatically. The close()
method of Connection interface is used to close the connection.

Syntax of close() method

public void close()throws SQLException

Example to close connection

con.close();

It avoids explicit connection closing step.

Java Database Connectivity with Oracle

To connect java application with the oracle database, we need to follow 5

following steps. In this example, we are using Oracle 10g as the database. So
we need to know following information for the oracle 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 where jdbc is the API, oracle is the
database, thin is the driver, localhost is the server name on which oracle is
running, we may also use IP address, 1521 is the port number and XE is the
Oracle service name. You may get all these information from the
tnsnames.ora file.
 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.

Create a Table

Before establishing connection, let's first create a table in oracle database.

Following is the SQL query to create a table.

create table emp(id number(10),name varchar2(40),age number(3));

Example to Connect Java Application with Oracle database

we are connecting to an Oracle database and getting data from emp table. Here,
system and oracle are the username and password of the Oracle database.

import java.sql.*;
class OracleCon
{
public static void main(String args[])
{
Try
{
//step1 load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");

//step2 create the connection object

Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

//step3 create the statement object

Statement stmt=con.createStatement();

//step4 execute query

ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));

//step5 close the connection object

con.close();

}
catch(Exception e)
{
System.out.println(e);
}
}
}
Two-Tier Client/Server Model and Three-Tier Client/Server Model:

A ‘tier’ commonly refers to the logical or functional separation of software into

layers on different physical locations or hardware. Multi-tier architecture is a
pattern of splitting software into several definite domains that handle particular
aspects of the software such as presentation, logic or data management. The
main areas of focus here are two-tier and three-tier architecture
 Fig: Two-Tier Client/Server Architecture

The two-tier architecture is based on the Client-Server model. It consists of

Client-Application tier and Database tier. The Client-Application server
communicates directly with the Database server. Data or information transfer
between the two components is fast due to the absence of a middleware.

The Client-Application contains the codes for interfacing with the user and also
for saving data in the database server. The Client-Application sends the request
to the server and it processes the request and sends back with data. This means
client Application handles both the Presentation layer (application interface)
and the Application layer (logical operations). The client-Application layer can
be build using languages such as C, C++, Java, Python, PHP, Rails, .NET.

On the other hand, the database server handles the data management layer. Data
management layer consists of data storage(database or file system) and methods
for storing and retrieving data from the data storage. Commonly used databases
include MySQL, MongoDB, PostgreSQL, SQLite. Hosting is possible on-
premises or in the cloud.

Two-tiered application examples include desktop applications, games, and

music players.

Advantages:

 It is fast and easy to implement

 Communication is faster
 It is suitable in an environment where business rules or logic operations are
static
Disadvantages:

 It is not easily scalable, thus performance degrades as users scale.

 Data integrity issue may arise due to the server responding to multiple
requests at the same time.

Three-Tier Client/Server Model:

Fig: Three-Tier Client/Server Architecture

The three-tier architecture is similar to two-tier architecture with the Client-

Application tier being divided into two. In other words, it is a modular client-
server architecture that consists of a presentation (client) tier, an application tier,
and a data tier. A good example is modern web applications.

The Presentational tier: it is the topmost layer of an application. This interface

layer translates tasks and information to something the user can understand. It
also takes in user request, sends it to the application layer where it is processed
and the result sends back to the user. This layer can be built with HTML, CSS,
VBScript, and Javascript frameworks such as React, Angular, Vue.js.
Moreover, deployment is possible via web browsers such as chrome, firefox.

The Application tier: it is the middle layer. This layer contains business rules
and logical operations for performing tasks like processing commands,
validation, logical evaluations, decision making, database interactions, and web
applications structuring. It can be implemented using frameworks such as
Django, Rails, Spring, Laravel, .NET. Javascript too can be used when executed
on Node.js. Deployment is possible on distributed or dedicated in-house servers
such as Nginx, Apache, Puma, Microsoft’s Internet Information Server.

The Data tier: it is the back-end layer. It is similar to the database server on
two-tier architecture.

Advantages:

 It scales horizontally well due to the distributed deployment of application

servers. For instance, when there are more web requests, the presentational
layer can load-balanced among the existing servers.
 It has better reusability of the various components as a result of the
separation of concerns(interface, logic and data management).
 Data integrity is improved due to the existence of middleware between the
client and the server.
 Maintenance is simplified because each layer is treated as an independent
entity.
 Performance is improved due to minimized network utilization as a result of
cashing requests at the presentational layer.

Disadvantage:

 Complexity is increased.