Different Types of JDBC Drivers in Java

JDBC Tutorial & Program

JDBC Basic
➤ Intro to JDBC
➤ JDBC vs ODBC
➤ JDBC API
➤ JDBC Driver
Connect to Database
➤ Oracle Connection
➤ MySQL Connection
➤ Using Eclipse IDE
➤ Coding Standards
➤ Close JDBC Object
➤ Java.sql Package
➤ DriverManager Class
➤ Get Connection
JDBC Statement
➤ Statement Interface
➤ Statement Example
➤ Create a database
PreparedStatement
➤ Types of statements
➤ PreparedStatement
➤ Statement vs PreParedStatement
PreparedStatement Example
➤ Insert record
➤ Update record
➤ Select record
➤ Create a table
➤ More Examples
➤ Surrogate Key
Others
➤ DatabaseMetaData
➤ ResultSetMetaData & ParameterMetaData

Officially JDBC driver in Java is divided into four types or levels. Each type defines a JDBC driver implementation with increasingly higher levels of platform independence, performance, and deployment administration.

There are 4+1 mechanism/architectures/methodologies to create JDBC driver software for different software based on the rules and guidelines of JDBC technology.

JDBC Driver software	Technical name
Type1 driver	JDBC-ODBC bridge driver
Type2 driver	Native API/partly JDBC driver
Type3 driver	Net protocol/fully java driver
Type4 driver	Native protocol/fully java driver
Type5 driver	It is not the official driver so, it hasn’t any technical name.

The type of driver depends on quite a few parameters like whether the application is internet-based, whether it needs to support heterogeneous databases, the number of concurrent users, and so on.

JDBC-ODBC Bridge Driver or Type1 JDBC Driver

In JDBC-ODBC bridge driver or Type1 JDBC driver, Firstly, the JDBC code interacts with DriverManager Service. Then it interacts with the ODBC driver software and later interacts with the Vendor Database library and finally, interacts with Database software.

The Type1 JDBC driver in Java is an old driver, nowadays no one is using it because we have better alternatives. This type of JDBC driver was given by SUN Microsystem.

Up to Java 1.7 version, SUN Microsystem provided the Type1 JDBC driver along with JDK. But from Java8 onwards SUN Microsystem stop giving JDBC drivers because no one is using type1 JDBC drivers in the software industry.

Type1 JDBC driver is synchronized which means it is single-threaded so it allows only one user/application at a time to utilize the JDBC driver. The other JDBC drivers are multi-threaded i.e. they allow multiple users or applications simultaneously to utilize the JDBC driver.

Vendor Database Library:- It is a software that contains logic to locate and interact with local or remote database software. It is specific to each database software and comes along with database software installation.

In the Oracle database, it comes as oci.dll along with complete server database software or client database software installation. All ODBC drivers and some JDBC drivers (type2) use vendor database libraries to locate and interact with local and remote database software.

Advantages and Disadvantage

Advantages of Type1 JDBC driver

Only a single type1 driver can be used to interact with multiple Database software by using appropriate ODBC drivers and vendor database libraries.
Since ODBC drivers and vendor Database libraries are available almost for all database software so we can use type1 driver to interact with any database software.
Up to JDK1.7, it was built-in JDBC driver of Java. Therefore we don’t need to download it separately.
It is suitable for the testing environment.
The JDBC-ODBC bridge driver is a thread-safe.

Disadvantage of Type1 JDBC driver

Type1 JDBC driver doesn’t directly interact with Database software. It needs the support of the ODBC driver and Vendor Database library. As a result, while working with the type1 JDBC driver we need to arrange multiple components at the client-side. Due to multiple layers of components, the type1 driver gives poor performance.
The performance of the type1 driver is not suitable for real-time projects.
It isn’t the software industry-standard JDBC driver.
From JDK1.8 onwards Type1 JDBC driver is not provided.
Due to Thread safety, the JDBC-ODBC bridge driver gives a very bad performance.

Type2 JDBC driver

The Type2 JDBC driver is designed to use the vendor Database library directly to interact with Database software.

With respect to the JDBC-ODBC bridge driver or type1 JDBC driver, the type2 JDBC driver doesn’t use an ODBC driver.

Type2 JDBC driver uses native code internally to interact with the native Database library directly. The code which is written in other than Java language, but used with Java code is called native code (generally written in C/C++).

Advantages of Type 2 JDBC driver

There is no need for arranging ODBC drivers.
It gives quite good performance when compared to the JDBC-ODBC bridge driver or type 1 JDBC driver.

Disadvantage of Type 2 JDBC driver

The type2 JDBC driver performance is not suitable for medium, and large scale applications/projects. Therefore, it isn’t an industry-standard JDBC driver.
It must be arranged separately, either from Database software vendors or from third-party vendors.
A separate type2 JDBC driver is given for every Database software. For example, the Oracle database needs its own type2 JDBC driver, MYSQL database needs its own type2 JDBC driver, and e.t.c.
The type2 JDBC driver doesn’t come along with JDK installation.
Type2 JDBC drivers can’t be used for the internet application purpose.
It shows lower performance than type3 and type4 drivers.

Type4 JDBC driver software in Java

The Type 4 JDBC driver is designed to interact with Database software directly without taking the support of ODBC drivers and vendor Database libraries.

Advantagesof Type4 JDBC driver

There is no need of arranging vendor Database libraries, and ODBC drivers at the client-side.
The type4 JDBC driver can be used for the untrusted applet (now outdated) to Database software communication.
It gives better performance when compared to type1, and type2 drivers. Therefore, it can be used in real projects for small scale, medium scale projects.
The type4 JDBC driver is an industry-standard JDBC driver.
The type4 JDBC driver is completely developed in Java so it is platform-independent.
It can be easily downloaded from the internet directly.

Disadvantages of Type4 JDBC driver

To use Type4 JDBC driver we must arrange it separately either from Database vendors or from third-party vendors.
For every Database software, we need a separate type4 JDBC driver.

Note:-

=> Since more components are required between Java application and Database software while working with type1, type2 driver, So these JDBC drivers are also called as Thick JDBC Driver.
=> Since type4 driver can interact with Database software directly without taking the support of any other components, So the type4 JDBC driver is also called as Thin JDBC driver.

Type3 JDBC driver

The Type3 JDBC driver is completely different from Type1/2/4 JDBC drivers. To understand the Type3 driver first we need to understand the JDBC connection pool.

There are two types of JDBC connections are available,

1) Direct JDBC connection:- It is the JDBC connection object that is created by the programmer manually. Type1/2/4 drivers are using a direct JDBC connection.

2) Pooled JDBC connection:- The JDBC connection object that is collected from the JDBC connection pool is called pooled JDBC connection object.

JDBC Connection Pool

The pool is a set of readily available same items.
The JDBC connection pool is a factory that contains a set of readily available JDBC connection object before actually being used.
All JDBC connection objects of connection pool represent connectivity with the same Database software. For example:- JDBC connection pool for Oracle database means all JDBC connection objects in that connection pool represent connectivity with the same Oracle database software.

JDBC Connection Pool Advantages

It gives the reusability of JDBC connection objects.
By using minimum JDBC connection objects we can make max clients talking with Database software.
Creating JDBC connection objects, managing JDBC connection objects. Destroying JDBC connection objects e.t.c will be taken care of by the connection pool itself. We don’t need to write code explicitly.

Web server like tomcat, proxy server like IDS server maintains JDBC connection pool. Now lets discuss about Type3 JDBC driver.

Type3 driver

The type3 JDBC driver is completely different from other JDBC drivers. It isn’t given to talk to database software directly and to establish the connection. It is designed to talk with a web-server/proxy server and to get a JDBC connection object from the JDBC connection pool.

The web server or proxy server internally uses the given details in the program and interacts with database software to create connection objects. This connection object is given back to the Java application using the Type3 JDBC driver. On the behalf of Java application, this proxy server interacts with database software, creates a JDBC connection, uses that connection object, and gives that connection object back to the Java class.

Java application passes DSN along with JDBC URL to the proxy server. The proxy server uses this DSN based ODBC driver to establish a connection with the database. The proxy server returns that connection object back to Java application and Java application uses that connection object to create other JDBC objects and to develop the persistence logic.

The JDBC type3 driver follows a three-tiered approach whereby the JDBC database requests are passed through the network to the middle-tier server. The middle-tier server then translates the request (directly or indirectly) to the database-specific native-connectivity interface to further the request to the database server. If the middle-tier server is written in Java, it can use a type 1 or type2 JDBC driver to do this.

Advantages and Disadvantages

Advantages

The type3 driver is server-based, so there is no need for any vendor database library to be present on client machines. Further, there are many opportunities to optimize portability, performance, and scalability. Moreover, the net protocol can be designed to make the client JDBC driver very small and fast to load. Additionally, a type3 driver typically provides support for features such as caching (connections, query results, and so on), load balancing, and advanced system administration such as auditing.

Disadvantages

It requires database-specific coding to be done in the middle tier. Additionally, traversing recordset may take longer, since the data comes through the backend server.

How type3 JDBC driver is used?

The Server uses a type1/2/4 driver to interact with database software to create connection objects in the connection. The Client application uses a type3 driver to get one JDBC connection objects from the JDBC connection pool by implementing it with a web server or proxy server. Client applications use the received connection object to create JDBC objects and to develop persistence logic. By calling con.close() method client applications releases JDBC connection object back to JDBC connection pool.

The type3 driver isn’t used along, it always used with type4/type2/type2 JDBC driver. You will learn it in detail in Servlet, and JSP technologies or in Spring framework, so while learning JDBC technology don’t worry about it.

Type5 JDBC driver

The Type5 JDBC driver is not official architecture given by Sun microsystem or Oracle Corporation. Some third-party vendors have created this architecture based on type4 architecture addressing a few problems of Type4 JDBC driver. See more:- Type 5 JDBC Drivers

Conclusion

In Java, when to use which JDBC driver?

If the Java project is a standalone project and not using the support of the web-server then use the type4 JDBC driver. Example:- Office automation applications

If the Java project is a web application, 3 tier-application, n tier-application running in web server then we should use type3 along with type4 JDBC driver. Here we should use type4 JDBC to create JDBC connection objects in the JDBC connection pool and use the type3 JDBC driver to get JDBC connection objects from the JDBC con pool. Example:- Website, banking project, insurance project.

Oracle thin driver details

Oracle database supplied JDBC driver softwares

The oracle corporation supplied type2 mechanism-based JDBC driver for the Oracle database is also called as oracle oci driver.
The oracle corporation supplied type4 mechanism-based JDBC driver for the Oracle database is also called as oracle thin driver.
We can get both these JDBC drivers in a single jar file along with oracle software installation.

The details of Oracle supplied Type4 JDBC driver are:-

JDBC driver class name:: oracle.jdbc.driver.OracleDriver (or) oracle.jdbc.OracleDriver
URL:: jdbc:oracle:thin:@::<host>:<port>:<sid>
Jar file:: ojdbc6.jar (Oracle 11g), ojdbc7.jar (Oracle 12g)

Q) Why Type4 JDBC driver URL is longer than Type1/Type2 JDBC driver URLs?

The Type1/Type2 drivers locate and interact with database software through ODBC, vendor database libraries. So, URL information is just required to locate that ODBC, and vendor database libraries because they take the responsibility of locating database software. Whereas the type4 driver must locate database software directly. So we need to provide a hostname and port number of database software in the URL. That makes JDBC URL quite lengthy in the case of Type4 drivers.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!