Thursday, May 24, 2012

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 see this question recently but it was very popular during 2006 - 2008 period and still asked mostly on Junior programmer level interviews. There are mainly 4 types of JDBC drivers in Java, those are referred as type 1 to type 4 jdbc drivers. I agree its easy to remember them by type rather than with there actual name, Which I have yet to get in memory except plain old JDBC-ODBC bridge driver. By the way here are there full names :

Type 1 JDBC Driver is called JDBC-ODBC Bridge driver (bridge driver)
Type 2 JDBC Driver is referred as Native-API/partly Java driver (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, JDBC-ODBC Bridge driver is most common for connecting SQL Server, MS Access and mostly on training and development. here are 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 Java In 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 callas and send them to ODBC driver for passing to database. Since type 1 driver act as 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  advantage in terms of of availability and can be your last choice.

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

This is also called type 2 driver and its slightly better than type 1 JDBC driver. type 2 JDBC driver convert JDBC calls into database calls by using native API provided by 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 JDBC-ODBC bridge driver since communication layer is reduced. type 2 JDBC driver requires database native library to be available on client but it poses several version and compatibility issue. This was liked by Database vendors though because they can reuse there existing native libraries.

AllJava/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 issue. 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 database. type 3 JDBC driver never get popular among database vendors as it was costly for them to rewrite there existing native database library which was mainly on C and C++.

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

type 4 JDBC driver is most popular among all four types of JDBC driver. it has not only implemented in Java but also incorporates all database call in single driver. It was pretty easy to use and deploy as well just include driver's jar in 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 database upgrade themselves, though some of them still upgrade native database library or type 2 driver.

That's all on quick overview of different types of JDBC drivers in Java. JDBC drivers has 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 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 

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

Post a Comment