Saturday, July 31, 2021

Different Types of JDBC Drivers in Java - Quick Overview

How many types of JDBC drivers in Java is a classical JDBC interview question, though I have not seen this question recently it was very popular during the last decade and still asked mostly on Junior programmer level interviews. There are mainly 4 types of JDBC drivers in Java, those are referred to as type 1 to type 4 jdbc drivers. I agree it's easy to remember them by type rather than with their actual name, Which I have yet to get in-memory except for plain old JDBC-ODBC bridge driver.


By the way here are their full names :

Type 1 JDBC Driver is called JDBC-ODBC Bridge driver (bridge driver)
Type 2 JDBC Driver is referred to as Native-API/partly Java driver (the native driver)
Type 3 JDBC Driver is called AllJava/Net-protocol driver (middleware driver)
Type 4 JDBC Driver is called All Java/Native-protocol driver (Pure java driver)


This JDBC tutorial is in continuation of my earlier tutorials in JDBC like How to connect to Oracle database using JDBC and  4 tips to improve performance of JDBC applications. If you are new here and haven't read them already, Its worth looking.  

Anyway out of all those 4 types, the JDBC-ODBC Bridge driver is most common for connecting SQL Server, MS Access and mostly on training and development. here are a quick review of all these four types of JDBC drivers. Also there has been some speculation of type 5 JDBC driver, I have to yet to see it.





JDBC ODBC Bridge Driver or Type 1 JDBC driver


types of JDBC drivers in JavaIn case of JDBC ODBC bridge driver all JDBC calls doesn't directly goes to database instead they go via ODBC driver. JDBC-ODBC driver translates JDBC calls into ODBC calls and sends them to the ODBC driver for passing to database. Since type 1 driver act as a bridge between JDBC and ODBC and that's why its called JDBC-ODBC bridge driver. 

This driver is not fast and good for production use mainly because of several layer of translation on back and fourth database traffic but it has an advantage in terms of of availability and can be your last choice.


1. Native-API/partly Java driver or Type 2 JDBC driver

This is also called type 2 driver and it's slightly better than type 1 JDBC driver. type 2 JDBC driver converts JDBC calls into database calls by using native API provided by the database. This driver is database-specific so once you switch from one database to another you need to change type 2 JDBC driver. performance is better than the JDBC-ODBC bridge driver since the communication layer is reduced. type 2 JDBC driver requires database native library to be available on the client but it poses several versions and compatibility issues. This was liked by Database vendors though because they can reuse there existing native libraries.


2. All Java/Net-protocol driver or Type 3 JDBC driver

both type 1 and type 2 JDBC drivers were not written in Java so there was need for pure Java JDBC driver to resolve portability issues. type 3 JDBC driver comes with pure java implementation (that's why All Java word ) but it uses 3 tier architecture where you have a Java client and Java Server which talk with Net protocol and Server speaking to the database. type 3 JDBC driver never get popular among database vendors as it was costly for them to rewrite their existing native database library which was mainly on C and C++.

3. All Java/Native-protocol driver or Type 4 JDBC driver

type 4 JDBC driver is most popular among all four types of JDBC drivers. it has not only been implemented in Java but also incorporates all database call in a single driver. It was pretty easy to use and deploy as well just include driver's jar in the classpath and you are ready. 

It also removes 3 tier architecture of type 3 JDBC driver which makes it faster than type 3.  Major development happens on type 4 JDBC driver when databases upgrade themselves, though some of them still upgrade native database library or type 2 driver.

That's all on a quick overview of different types of JDBC drivers in Java. JDBC drivers have evolved from JDBC ODBC bridge driver to type 4 JDBC driver, which is clean and portable. There has been some buzz around JDBC driver 5 on the Java community which may include some advanced functionality. let us know if you come across some news on JDBC 5 driver.


Other Java and SQL tutorials you may  like
10 Object-oriented design principles in Java 

9 comments :

Jirka Pinkas said...

Man, isn't this a little bit old? I use JDBC 4 drivers for quite a while (several years) and I have never met the older version. Have you recently used older version?

javin paul said...

you are correct Jirka, I used JDBC-ODBC when I started learning Java with MS-Access but I never met with any other type of driver. I used type-4 for mysql, oracle and sybase. This is mostly theoretical question which some guys prefer to ask(I am not from them) and one of my reader asked this question so I thought to just summarize it. By the way there was some buzz about JDBC 5, let see.

Anonymous said...

I was asked this questions yesterday and this is 2013. The in interview was not satisfied with Type1 , type2 , type3 , type4 and he wanted actual names. I am a mid-level software engineer.

Anonymous said...

I was once asked, Why you have four type of JDBC driver in Java? I couldn't answer because I wasn't aware of motivation. After some reasearch I found that main reason of so many differnet types of driver in Java is portability. In Java, using native libraries is considered bad and as last resort, that's why we have different types of JDBC driver from type 1 (least portable) to type 4 (pure Java driver). I hope this help you to reason why JDBC has so many types of driver.

Anonymous said...

You should always use type 4 JDBC driver, there is no need for using type1, typ2 or even type 3. Three main reason I recommend type 4 driver :

1) They are fastest
2) They are portable because implemented in pure 100% Java.
3) They don't require any setup, just include respective JAR in your class file from whichever vendor you are connecting e.g. for Oracle use ojdbc6.jar for SQL Server use SQLJDBC4.jar

Anonymous said...

Yesterday, one of my friend ask about difference between JDBC Type 3 and Type 4 driver? When he says performance and portability, interviewer was quite impressed. Which means this question is still valuable and shows how do you understand motivation around certain features. Yes, you would hardly use anything but other than type 4 JDBC driver e.g. mysq-connector.jar, ojdbc_6.jar or sqjdbc4.jar because they are easy to use, just drop their JAR in classpath and you are done, but knowing bit of history behind it not bad at all.

ndtdb2 said...

Hello,
It is possible to have 2 releases of the same jdbc driver on the same machine ?
Thanks

javin paul said...

@dt nguen, yes it's possible because ultimately they are JAR files, you can put both old and new driver JAR file and CLASSPATH will load the class from the JAR which comes first in CLASSPATH, but beware of any issue which comes because on incompatibility and new features.

Unknown said...

What are the similarities between type 1 or type 2 driver of JDBC....??? Please answer it

Post a Comment