Java JDBC - JDBC Drivers

JDBC Drivers helps in interacting with databases. JDBC driver manager makes sure that correct driver is used to access each data source. There are 4 types of JDBC drivers :

1. Type 1 : JDBC-ODBC Bridge Driver
2. Type 2 : Native API Driver (partially java driver)
3. Type 3 : Network Protocol or Net Pure Driver (fully java driver)
4. Type 4 : Thin Driver (100% pure java driver)

Now we will discuss all of these 4 types in detail :

Type 1 : JDBC-ODBC Bridge Driver

This driver uses ODBC driver installed on client machine to make connection to data source. In order to use this driver, we need to configure Data Source Name (DSN) on our machines which represents target database. This is the oldest driver we have and it is not much in use. Moreover, support for this driver has been stopped from Java 8.

There are few disadvantages of using this driver :

1. Performance of the applications gets degraded as the JDBC calls gets converted into ODBC class for executing SQL statements.
2. ODBC driver needs to be installed on client machine.
3. Support for this driver is ended in Java 8

Type 2 : Native API Driver

When we use this driver JDBC calls are converted into native C/C++ calls. This driver is not entirely written in Java. These drivers are used in same manner as JDBC-ODBC Bridge. These drivers are vendor specific and needs to be installed on each client machine. If we change the database then we need to change the native API library as well.

Performance with these drivers are upgraded as compared to JDBC-ODBC Bridge. There are some disadvantages as well :

1. These needs to be installed on each client machine.
2. In addition to drivers vendor specific API also needs to be installed on client machine. 

Type 3 : Network Protocol or Net Pure Driver

These drivers are purely written in Java language. These drivers used a 3-tier architecture for communication with databases. Most of DB related tasks are handled by a middleware server which converts JDBC calls directly or indirectly into vendor specific database protocol.
These drivers are very flexible as compared to previous drivers because it does not require any code to be installed on client machine.

.

Type 4 : Thin Driver (100% Pure Java Driver)

This is a pure Java-based driver, which communicates directly with vendor's database through socket connection. Performance wise this is the best driver to choose. This driver doesn't require any native database library for interaction with database. Moreover, we don't need to install any kind of special software on client or server machine.

Which driver to should be used and when?

If we are accessing one type of database, such as Oracle, MySQL, Sybase etc. then we should use Type 4 driver.
If our Java Application is accessing multiple types of databases at same time then, we should use Type 3 driver.
If neither Type 4 nor Type 3 is available for the database used by our application then we should go for Type 2 driver.
We should not use Type 1 for deployment purpose, it should be only used for development and testing purposes only.

Share:

Read More

Java JDBC - Introduction

What is JDBC?

JDBC stands for Java Database Connectivity, it is an API for database independent connectivity between Java programs and databases. It helps in connecting wide range of databases and execute the query with the database.

The java.sql package contains classes and interfaces for working with databases. Below is the list of  commonly used classes and interfaces available in java.sql  package :

• DriverManager class : A list of database drivers is managed by this class, which matches connection requests from java application with the proper database driver using communication sub protocol.   
• Driver interface : Communication with database is managed by this interface but we will rarely use objects of this interface rather we will be using Objects of DriverManager class for communication with database. 
• Connection interface : Objects of this interface represents the communication context. Methods available in this interface helps in contacting database. 
• Statement interface : SQL statements are submitted using objects of this interface.
• PreparedStatement interface : Parameterized SQL statements are submitted through objects of this interface.  
• ResultSet interface : Object of this interface will hold the data returned from SQL query execution.
• ResultSetMetaData interface : Object of this interface holds metadata information about the resultset returned from SQL query execution. 

The JDBC library includes classes and interfaces for performing below tasks :

1. Making connection to database.
2. Creating SQL Statements
3. Executing SQL queries
4. Viewing and modifying the result set. 

JDBC API uses JDBC drivers to connect with database. There are four types of drivers :

1. JDBC-ODBC Bridge Driver
2. Native Driver
3. Network Protocol Driver
4. Thin Driver

JDBC Architecture 

JDBC API supports Two-Tier and Three-Tier processing models :

Two-Tier Architecture for Data Access :

In this architecture a Java Applet or Application talks directly to data source. This requires a JDBC driver that can communicate with particular database. A user's command are delivered to the database and result of those commands are send back to the user. The database in this situation may be located on another machine to which user is connected via network. This is referred to as client/server configuration, where user's machine is client and the machine hosting database is called server. 

Three-Tier Architecture for Data Access :

In three tier architecture commands are send to a middle tier of services which then send those commands to database server. The database server processes those commands and sends results back to middle tier, which then sends those results back to users. 

Share:

Read More