Java Database Connectivity (JDBC)

JDBC interfaces, classes and components

The JDBC API is composed of a number of interfaces and classes that represent a connection to the database, provide facilities for sending SQL queries to a database and help Java developer process the results of relational database interactions.

JDBC and SQL compared

Structured Query Language (SQL) is an ISO specification that defines how applications can query, update and just generally interact with a relational database. The JDBC API does not perform any functions that could otherwise be performed through a SQL query. The goal of the JDBC API is to provide a connection to a relational database through with SQL queries can be performed, and the results from those queries can be processed within a Java program. JDBC is a connectivity API. SQL remains the language used to actually talk to the database.

JDBC architecture

JDBC is designed to make Java applications database agnostic. That is, a program written using JDBC will work with any JDBC compliant database. That was a Java application that is tested with Apache Derby can confidently be deployed against an IBM DB2 database in production. However, there are differences between database vendors, and these differences must be abstracted away. The tool for abstracting away these differences is known as a JDBC driver.

When a Java wants to connect to a database, it calls upon a JDBC interface known as the DriverManager, which loads a driver that has been written specifically by the vendor of the database to which the Java program is connecting. This driver contains all of the information required to connect the Java program to the underlying database. The JDBC driver is vendor specific, so the MySQL JDBC driver is different from the Apache Derby JDBC driver. The job of these drivers is to address differences between databases at the technical level, abstract them away from the application, and allow Java developers to be confidence that the JDBC API they interact with will work with any JDBC compliant database.

How to connect to a database with JDBC

The basic steps to connect to a JDBC database are:

1. Load the right JDBC driver
2. Obtain the database URL
3. Use the JDBC DriverManger to connect to the database
4. Create a SQL based Statement or PreparedStatement object
5. Execute the statement against the database
6. Process the results and handle any SQL exceptions
7. Close any database connections or resources no longer in use

JDBC vs. ODBC

JDBC and Open Database Connectivity (ODBC) are very similar in nature. The ODBC specification defines a set of criteria for connecting to ODBC compliant databases, just as JDBC does for JDBC compliant databases. The big distinction between the two is that JDBC is only available to languages written and Java and applications deployed to the JDK, while ODBC can be used with a variety of different languages including Visual Basic, C#, FORTRAN and pascal.

The Java Development Kit (JDK) does provide something called a JDBC-ODBC bridge driver that allows developers to write applications that use the JDBC API but connect to ODBC compliant databases such as Microsoft Access. So it is possible to access an ODBC database using JDBC.

Types of JDBC drivers

The JDBC-ODBC bridge is referred to as the Type 1 JDBC driver. There are four types, with the distinctions being:

1. The Type 4 JDBC driver is written purely in Java and works over a network connection. This is often referred to as the JDBC thin driver.
2. The Type 3 JDBC driver which first interacts with a middleware server such as IBM WebSphere, which then in turn communicates with the relational database. This is sometimes referred to as the JDBC proxy driver.
3. The Type 2 JDBC driver which may or may not be written in Java. These drivers typically include proprietary code written specifically for a given database to optimize performance and throughput. This driver is often referred to as the thick JDBC driver.
4. The Type 1 JDBC driver is the aforementioned JDBC-ODBC bridge.

Object-Relational Impedance Mismatch

The philosophies behind object-oriented development and relational database systems are very different, which often leads to discussions about the object-relational impedance mismatch. Object-oriented systems are designed much differently than relational systems, and pulling data from a relational database and bringing that data into an object oriented system is not a straight forward process. The goal of the JDBC API is to define classes, methods and interfaces that minimize the impedance mismatch and make it easier to marshal data back and forth between a Java program and an external database.

JDBC vs. Hibernate and JPA

To help address the object-relational impedance mismatch, a number of frameworks exist that simplify the task of moving data between a relational database and a Java program. Popular object-relational mapping (ORM) frameworks include Hibernate, TopLink and DataNucleus. While each framework has its own set of unique capabilities, all of them comply with the Java Persistence API standard, which is now part of the Java EE/Jakarta EE specification.

JDBC and NoSQL databases

The JDBC API is solely focused on interactions between Java programs and relational databases. While NoSQL databases are rising in popularity, there are no build-in functions in the JDBC API to facilitate interactions with NoSQL databases such as Redis or CouchDB. JDBC is solely focused on interactions with relational systems.

Criticisms of JDBC

JDBC is often criticized for burdening the developer by requiring them to write a significant amount of boilerplate code in order to perform basic operation. In addition to this, interactions with the JDBC API throw a large number of potential exceptions that must be handled, despite the fact that there is often very little a developer can do when these JDBC errors occur. As a result, operations that might require only a few lines of code in languages such as C# or GoLang can be five or six times in length when performing the same database operation in Java.

Spring JDBC Framework

There have been many efforts by members of the Java community to address some of the shortcomings of JDBC, with the most popular being the Spring JDBC Framework.

The Spring JDBC Framework is built entirely on top of the JDBC API, so its underpinnings are completely standards based. However, the framework provides various helper classes such as the Spring JdbcTemplate class and the SQLExceptionTranslator JDBC interface, which are designed to assist in the handling of JDBC exceptions while also minimizing the amount of boilerplate code that is required to perform simple database operations.

The true meaning of JDBC

On a final note, it’s worth mentioning that in legal terms, JDBC actually does not stand for Java Database Connectivity. The term JDBC is itself a trademarked word, and while it acts as an umbrella under which all of the various Java database and SQL related APIs reside, Oracle’s trademark application itself does not indicate anywhere that JDBC means Java database connectivity. In fact, doing so would actually dilute Oracle’s right to the trademark. If a user were to download and read the current JDBC specification, nowhere in that JSR would the words Java database connectivity be found.