Friday, 24 July 2015

One of the oldest Java interview question
is what is difference between different types of JDBC drivers e.g. what
is difference between type 1, type 2, type 3 or type 4 drivers?
Sometime also asked as how do you choose between different JDBC driver?

When to use type 3 over type 4 driver
etc. Its 2015 now and I doubt anyone is using JDBC driver other than
type 4 for connecting to database, but let's see how to answer this question when you face it during interview.

Difference between different types of JDBC driver comes from the fact
how they work, which is basically driven by two factor, portability and
performance.

Type 1 JDBC driver is the poorest in terms of portability
and performance, while type 4 JDBC driver is highly portable and gives
best performance.

You will learn more differences between different JDBC
drivers as we go along. Since database is very important and almost all
Java application uses database in some form or other, its important to
learn JDBC well.

If you are a beginner started to learn Java and
struggling with JDBC then I suggest you to take a look at Practical Database Programming with Java By Ying Bai. It's one of the rarest book, which covers Java database connectivity well,

What is JDBC Driver in Java?

A driver is nothing but software required to connect to a database from
Java program. JDBC is just an API, which Java has designed and onus to
implement this API lies on different vendor because different database
works in different way, they internally use different protocols.

Similarly Oracle, SQL SERVER, Sybase and PostgreSQL has provided their
own implementation of JDBC API to connect them.

Since Java program uses
JDBC API, they are portable across different database, all you need to
do is change the JDBC driver, which is just a JAR file
if you are using type 4 JDBC driver.

By the way, migrating to database
is not as easy, especially if you are using any proprietary feature of
database, but if you ANSI SQL and not using any database specific
feature, its easy.

How many drivers are there in JDBC?

There are total 4 types of JDBC drivers exists in Java. They are known
as type 1, 2, 3, and 4 drivers. In order to understand difference
between different JDBC drivers, first and most important thing to
understand is why Java has so many types of JDBC drivers?

Why not
just one? the answer lies in portability and performance. The first
JDBC driver is known as type 1 JDBC driver and the most recent one is
known as type 4 JDBC driver.

There has been some talk about type 5 JDBC
driver but I have not heard anything concrete about it from Oracle or
any other reliable source. So, type 4 JDBC driver is still the latest
one.

Since it acts as bridge between JDBC and ODBC, it is also
known as JDBC ODBC bridge driver. This driver had very poor
performance because of several layers of translation which took place
before your program connects to database.

It has also less portable
because it relies on ODBC driver to connect to database which is
platform dependent. It is now obsolete and only used for development and
testing, I guess Java 7 even removed this driver from JDK.

What is type 2 driver in JDBC?

This was the second JDBC driver introduced by Java after Type 1, hence
it known as type 2. In this driver, performance was improved by reducing
communication layer. Instead of talking to ODBC driver, JDBC driver
directly talks to DB client using native API.

That's why its also known
as native API or partly Java driver. Since it required native API to
connect to DB client it is also less portable and platform dependent.

What is type 3 driver in JDBC?

This was the third JDBC driver introduced by Java, hence known as type
3. It was very different than type 1 and type 2 JDBC driver in sense
that it was completely written in Java as opposed to previous two
drivers which were not written in Java.

That's why this is also known as
all Java driver. This driver uses 3 tier approach i.e. client, server
and database. So you have a Java client talking to a Java server and
Java Server talking to database.

Java client and server talk to each
other using net protocol hence this type of JDBC driver is also known as Net protocol
JDBC driver. This driver never gained popularity because database
vendor was reluctant to rewrite their existing native library which was
mainly in C and C++

What is type 4 JDBC driver?

This is the driver you are most likely using to connect to modern database like Oracle, SQL Server, MySQL, SQLLite and PostgreSQL.

This driver is implemented in Java and directly speaks to database using its native protocol.

This driver includes all database call in one JAR file, which makes it very easy to use. All you need to do to connect a database from Java program is to include JAR file
of relevant JDBC driver. Because of light weight, this is also known as
thin JDBC driver.

Since this driver is also written in pure Java, its
portable across all platform, which means you can use same JAR file to
connect to MySQL even if your Java program is running on Windows, Linux
or Solaris. Performance of this type of JDBC driver is also best among
all of them because database vendor liked this type and all enhancement
they make they also port for type 4 drivers.

Difference between type 1 and type 2 JDBC driver?

Though both type 1 and type 2 drivers are not written in Java, there was some significant
difference between them. Type 2 driver has better performance than type
1 driver because of less layer of communication and translation.

As
opposed to type 1 JDBC driver, in which JDBC calls are translated into
ODBC calls before they go to database, type 2 JDBC driver directly
connect to db client using native library.

Difference between type 2 and type 3 JDBC driver?

Main difference between type 2 and type 3 JDBC driver is that as opposed
to type 2 driver, type 3 is completely written in Java.

Another
difference which comes from this fact is that type 3 driver is more
portable than type 1 and type 2 drivers because it doesn't require any
native library on client side to connect to database. In terms of
architecture, this was 3 tier architecture and uses net protocol for client server communication.

Difference between type 3 and type 4 JDBC driver?

Main difference between type 3 and type 4 JDBC driver was removal of 3
tier architecture. Type 4 JDBC driver directly connect to database using
their native protocol as opposed to net protocol used by type 3 driver.

Though both type 3 and type 4 driver is written in Java. Another key
difference is ease of use, type 4 drivers just require one JAR file into
classpath in order to connect to db. Performance of type 4 JDBC driver
is also better than type 3 driver because of direct connectivity to
database as opposed to 3 tier architecture of type 3 driver.

When to use different types of JDBC driver?

You should always use type 4 JDBC driver, there is hardly any situation
when you need to go to previous version of JDBC driver. Though, if you
want to connect to Oracle database using TNS name using OCI client, you
need to use type 2 JDBC driver also known as thick JDBC driver.

That's all about difference between type 1, 2, 3, and type 3 JDBC driver in Java.
JDBC drivers are evolved in Java from less portable to most portable
and from low performance to high performance.

Type 1 JDBC driver is the
oldest while type 4 JDBC driver is the latest. In real world, you will
be mostly likely using type 4 JDBC driver, which is bundled in a JAR file. Just make sure to put them into your Java application's classpath when you connect to database from Java program.