Preparing for Java and Spring Boot Interview?

Join my Newsletter, its FREE

Difference between Type 1, 2, 3 and 4 JDBC Driver in Java? Example

One of the oldest Java interview questions is what is the difference between different types of JDBC drivers e.g. what is the difference between type 1, type 2, type 3, or type 4 drivers? Sometimes also asked as to how do you choose between different JDBC drivers? When to use type 3 over type 4 driver etc. It's 2015 now and I doubt anyone is using JDBC driver other than type 4 for connecting to the database, but let's see how to answer this question when you face it during the interview. The difference between different types of JDBC drivers comes from the fact how they work, which is basically driven by two factors, 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 the best performance. You will learn more differences between different JDBC drivers as we go along.

Since the database is very important and almost all Java application uses the database in some form or other, it's important to learn JDBC well. If you are a beginner who started to learn Java and struggling with JDBC then I suggest you take a look at these best JDBC books. It's one of the best resources, 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 a Java program. JDBC is just an API, which Java has designed and the onus to implement this API lies on different vendor because different database works in a different way, they internally use different protocols.

So MySQL gives its own implementation of JDBC, we call it MySQL JDBC driver and we use it when we want to connect to MySQL database from Java program.

 Similarly, Oracle, SQL SERVER, Sybase, and PostgreSQL have provided their own implementation of JDBC API to connect them. Since the Java program uses JDBC API, they are portable across different databases, all you need to do is change the JDBC driver, which is just a JAR file if you are using a type 4 JDBC driver.

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

How many drivers are there in JDBC?

There is a total of 4 types of JDBC drivers that exist in Java. They are known as type 1, 2, 3, and 4 drivers. In order to understand the difference between different JDBC drivers, the 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 a type 1 JDBC driver and the most recent one is known as a 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, the type 4 JDBC driver is still the latest one.

Difference between different JDBC drivers in Java

What is a Type 1 driver in JDBC?

This is the oldest JDBC driver, mostly used to connect databases like MS Access from Microsoft Windows operating system. Type 1 JDBC driver actually translates JDBC calls into ODBC (Object Database connectivity) calls, which in turn connects to the database. 

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

It has also less portable because it relies on an ODBC driver to connect to the 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 a Type 2 driver in JDBC?

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

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

If the native library e.g. ocijdbc11.dll, which is required to connect to Oracle 11g database is not present in the client machine then you will get java.lang.UnsatisfiedLinkError: no dll in java.library.path error.  The performance of the type 2 driver is slightly better than the type 1 JDBC driver.

What is a 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 drivers in the sense that it was completely written in Java as opposed to the previous two drivers which were not written in Java. 

That's why this is also known as all Java drivers. 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 the 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 the database vendor was reluctant to rewrite their existing native library which was mainly in C and C++

What is a Type 4 JDBC driver?

This is the driver you are most likely using to connect to modern databases like Oracle, SQL Server, MySQL, SQLLite, and PostgreSQL. This driver is implemented in Java and directly speaks to the 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 the Java program is to include the JAR file of the relevant JDBC driver. 

Because of being lightweight, this is also known as a thin JDBC driver. Since this driver is also written in pure Java, it's portable across all platforms, which means you can use the same JAR file to connect to MySQL database 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 vendors liked this type and all enhancements they make they also port for type 4 drivers.

Difference between Type 1 and Type 2 JDBC drivers?

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 the type 1 JDBC driver, in which JDBC calls are translated into ODBC calls before they go to the database, type 2 JDBC driver directly connects to DB client using the native library.

Difference between Type 2 and Type 3 JDBC driver?

The main difference between type 2 and type 3 JDBC drivers is that as opposed to type 2 drivers, 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 the client-side to connect to database. In terms of architecture, this was 3 tier architecture and uses a net protocol for client-server communication.

Difference between type 3 and type 4 JDBC drivers?

The main difference between type 3 and type 4 JDBC drivers was the removal of 3 tier architecture. Type 4 JDBC drivers directly connect to the database using their native protocol as opposed to the network protocol used by type 3 drivers. Though both type 3 and type 4 driver is written in Java. 

Another key difference is the ease of use, type 4 drivers just require one JAR file into classpath in order to connect to DB. The performance of the Type 4 JDBC driver is also better than the type 3 driver because of direct connectivity to the database as opposed to 3 tier architecture of the type 3 driver.

When to use different types of JDBC drivers?

You should always use a type 4 JDBC driver, there is hardly any situation when you need to go to the previous version of the 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 a thick JDBC driver. 

That requires a database native client library e.g. ocijdbc11.dll and if that's not present in the machine then your Java program will throw java.lang.unsatisfiedlinkerror no ocijdbc11 in java.library.path error at run time. 

That's all about the difference between type 1, 2, 3, and type 3 JDBC drivers 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 the real world, you will be most likely using a 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 the database from the Java program.

1 comment:

  1. ODBC is platform independent.


Feel free to comment, ask questions if you have any doubt.