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

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

                 

What is type 1 driver in JDBC?

• This is the oldest JDBC driver, mostly used to connect database like MS Access from Microsoft Windows operating system. Type 1 JDBC driver actually translate JDBC calls into ODBC (Object Database connectivity) calls, which in turn connects to database. 
• 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. 
• If native library e.g. ocijdbc11.dll, which is required to connect Oracle 11g database is not present in client machine then you will get java.lang.UnsatisfiedLinkError: no dll in java.library.path error.  Performance of type 2 driver is slightly better than type 1 JDBC driver.

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

*Summary of above all story ...

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

Convert Numeric To Varchar in SQL

RIGHT('000000' + CONVERT(VARCHAR(6), BranchMaster.Pincode), 6)