Java Database Connectivity (JDBC)

— Husein Motiwala, Path Mukadam, Eshaan Menkar, Gaurav Mekhe & Neel Malwatkar

Introduction

Java is one of the powerful languages used in the IT industry to develop several software projects.

To be a successful Java programmer, a developer needs to understand one of the important concepts of Java i.e. JDBC — Java Database Connectivity or JDBC.

What does JDBC mean?

JDBC stands for Java Database Connectivity. It is a step-by-step procedure to communicate with the database from the Java application to perform database operations. API (Application programming interface) is a document that contains a description of all the features of a product or software. It represents classes and interfaces that software programs can follow to communicate with each other. An API can be created for applications, libraries, operating systems, etc.

JDBC is an API that makes it possible to standardize and simplify the process of connecting Java applications to a database. DBC is considered a part of the Java Platform, Standard Edition (Java SE) uses the Structured Query Languages (SQL) to perform different database queries like access, storage, update or delete. Relational Database Management System (RDBMS) supports Structured Query Language (SQL). Since Java is a platform-independent programming language that runs on most platforms, JDBC helps to write a single database application that can run on different platforms and interact with different Database Management Systems.

The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database usage.

• Making a connection to a database.
• Creating SQL or MySQL statements.
• Executing SQL or MySQL queries in the database.
• Viewing & Modifying the resulting records.

Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to an underlying database. Java can be used to write different types of executables, such as −

• Java Applications
• Java Applets
• Java Servlets
• Java ServerPages (JSPs)
• Enterprise JavaBeans (EJBs).

All of these different executables can use a JDBC driver to access a database, and take advantage of the stored data.

JDBC provides the same capabilities as ODBC, allowing Java programs to contain database-independent code.

The Need for JDBC

It is important to understand why we need Java Database connectivity. Java applications are required to connect with the database. Java applications are written in Java programming language, but the database only understands Structured Query Language (SQL). To establish a connection between Java application and database, JDBC is used. JDBC contains a set of interfaces and classes which helps to connect Java applications to the database.

Before JDBC, ODBC (Open Database Connectivity) API was the database API to connect and execute the query with the database. But, ODBC API uses ODBC driver which is written in C language (i.e. platform-dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language).

Key differences between JDBC and ODBC

JDBC Drivers and Types

As we have understood, JDBC is used to connect Java applications with the database. We should also know that JDBC uses different JDBC drivers to perform this task. A JDBC driver is a software component that enables Java applications to interact with the database.

There are 4 different types of JDBC drivers. These are:

• JDBC-ODBC Bridge Driver
• Native-API Driver (partially Java driver)
• Network Protocol Driver (fully Java driver)
• Thin Driver (fully Java driver).

WORKING

JDBC establishes a connection with a data source, sending queries, updating statements, and processing results. JDBC helps developers with the following aspects:

• It helps to establish a connection with a data source
• It allows to send queries and updates statements
• It helps to fetch the data from the database and process the fetched results.

Here, the Java application calls the JDBC to submit the SQL statements queries and get the results. The JDBC driver which constitutes a set of classes helps to implement the JDBC API. The database stores all the data that is retrieved by the JDBC driver.

ARCHITECTURE

The JDBC API supports 2 different types of model to access the database they are:

1.Two-tier model.

In the two-tier model, a Java application directly communicates with the database. It requires a JDBC driver to establishes communication with the particular database. When the user sends the query to the database, results for those queries are sent back to the user. The database may be present on the same machine or it may be located in any remote machine connected via a network. This type of architecture is called client-server architecture.

2.Three-tier model.

In the three-tier model, the user queries are sent to the middle-tier services, from there they are sent to the database. Then, the database processes the queries, and results are sent back to the middle tier, and from there they are sent to the user. This type of architecture increases the performance and simplifies the application deployment.

The JDBC API supports both two-tier and three-tier processing models for database access but in general, JDBC Architecture consists of two layers −

• JDBC API − This provides the application-to-JDBC Manager connection.
• JDBC Driver API − This supports the JDBC Manager-to-Driver Connection.

The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases.

The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases.

Following is the architectural diagram, which shows the location of the driver manager concerning the JDBC drivers and the Java application -

JDBC Components

The JDBC API provides the following interfaces and classes −

• Driver Manager − This class manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication subprotocol. The first driver that recognizes a certain subprotocol under JDBC will be used to establish a database connection.
• Driver − This interface handles the communications with the database server. You will interact directly with Driver objects very rarely. Instead, you use DriverManager objects, which manage objects of this type. It also abstracts the details associated with working with Driver objects.
• Connection − This interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with the database is through the connection object only.
• Statement − You use objects created from this interface to submit the SQL statements to the database. Some derived interfaces accept parameters in addition to executing stored procedures.
• ResultSet − These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data.
• SQLException − This class handles any errors that occur in a database application.

JDBC Packages

JDBC packages need to be imported at the beginning of the program. Java Database Connectivity API consists of two packages:

• java.sql
• javax.sql

Database best suited for Java

As there are several databases available that help to develop software projects on Java, it is difficult to say which is the best database among all of them. There are various factors involved while choosing the right database to develop a software application. For example, you have to consider whether you are looking for an open-source database or a paid version of the database, or if you want to use cloud-based database matters.

Some of the databases which can be used for Java are:

1. Oracle
2. MySQL
3. PostgreSQL
4. IBM-DB2
5. MS-SQL

JDBC Advantages

Java Database Connectivity comes up with several advantages, some of them are:

• JDBC itself creates XML format of data from the database automatically
• JDBC supports modules
• JDBC provides better security
• JDBC completely supports query and stored procedure
• JDBC supports both types of processing i.e. Synchronous and Asynchronous processing
• JDBC does not require content conversion.

Creating a simple Database connection

The programming involved to establish a JDBC connection is simple. Here are these simple four steps −

· Import JDBC Packages − Add import statements to your Java program to import required classes in your Java code.

Code Snippet for importing JDBC Packages

· Register JDBC Driver − This step causes the JVM to load the desired driver implementation into memory so it can fulfil your JDBC requests.

Code snippet for registering JDBC Driver

· Database URL Formulation − This is to create a properly formatted address that points to the database to which you wish to connect. After we load the driver, we can establish a connection using the DriverManager.getConnection() method the three overloaded DriverManager.getConnection() methods:

• getConnection(String url)
• getConnection(String url, Properties prop)
• getConnection(String url, String user, String password)

Here each form requires a database URL. A database URL is an address that points to your database. In MySQL for the driver — com.mysql.jdbc.Driver the url format should be — jdbc:mysql://hostname/ databaseName

· Create Connection Object − Finally, code a call to the DriverManager object’s getConnection() method to establish actual database connection.

Code snippet for creating Connection object

Conclusion

The blog has elaborated upon relevant knowledge and understanding pertaining to Java Database Connectivity that one may require. JDBC is a very important concept which every Java developer needs to understand to develop projects. We believe this blog has helped you to understand various concepts related to JDBC. Do let us know which concept from this informative discussion you found most interesting!