Java Database Connectivity Tutorial

Java Database Connectivity Tutorial shows usage of JDBC APIs and java.sql package classes and interfaces such as Connection, Statement, PreparedStatement, CallableStatement, ResultSet, Batch Processing, Transactions, StoredProcedure etc with an example.


In this tutorial, we use the MySQL Connector/J driver. It is the official JDBC driver for MySQL.
All the source code examples in this tutorial are developed using JDK 8+ with the latest release of JDBC 4.2 API.

What is JDBC API?

Java Database Connectivity or JDBC API provides industry-standard and database-independent connectivity between the Java applications and relational database servers(relational databases, spreadsheets, and flat files).

Using the JDBC API, we can access virtually any data source, from relational databases to spreadsheets and flat files. JDBC technology also provides a common base on which tools and alternate interfaces can be built.


The JDBC API is comprised of two packages:
We automatically get both packages when you download the Java Platform Standard Edition (Java SE) 8.
JDBC API consists of two parts – the first part is the JDBC API to be used by the application programmers. The second part is the low-level API to connect to a database server(JDBC Driver).
  1. The first part of JDBC API is part of standard java packages in java.sql package. We use java.sql package API for accessing and processing data stored in a data source (usually a relational database) using the Java programming language. For example Connection, Statement, ResultSet, etc are part of java.sql package.
  2. For the second part is the JDBC driver (there are four different types of JDBC drivers) A JDBC driver is a set of Java classes that implement the JDBC interfaces, targeting a specific database. The JDBC interfaces come with standard Java, but the implementation of these interfaces is specific to the database you need to connect to. Such an implementation is called a JDBC driver. For example, mysql-connector-java.jarpostgresql.7.4.jar, etc.

JDBC Driver Types

To use the JDBC API with a particular database management system(MySQL, Oracle, etc), we need a JDBC technology-based driver to mediate between JDBC technology and the database (oracle.jdbc.driver.OracleDriver). Depending on various factors, a driver might be written purely in the Java programming language or in a mixture of the Java programming language and Java Native Interface (JNI) native methods.
Install a JDBC driver from the vendor of your database. A JDBC driver is a set of Java classes that implement the JDBC interfaces, targeting a specific database. The JDBC interfaces come with standard Java, but the implementation of these interfaces is specific to the database you need to connect to. Such an implementation is called a JDBC driver.
There are 4 different types of JDBC drivers:
  1. Type 1: JDBC-ODBC bridge driver
  2. Type 2: Java + Native code driver
  3. Type 3: All Java + Middleware translation driver
  4. Type 4: All Java driver.
If you are using Java DB ( Apache Derby database), it already comes with a JDBC driver. If you are using MySQL, install the latest version of Connector/J.

Type 1: JDBC-ODBC bridge driver

Drivers that implement the JDBC API as a mapping to another data access API, such as ODBC (Open Database Connectivity). Drivers of this type are generally dependent on a native library, which limits their portability.
Note: The JDBC-ODBC Bridge should be considered a transitional solution. It is not supported by Oracle. Consider using this only if your DBMS does not offer a Java-only JDBC driver.

Type 2: Java + Native code driver

Drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited. Oracle's OCI (Oracle Call Interface) client-side driver is an example of a Type 2 driver.

Type 3: All Java + Middleware translation driver

Type 3: Drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client's requests to the data source.

Type 4: All Java driver

Type 4: Drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.
MySQL Connector/J is a Type 4 driver.

How to install a JDBC driver?

Installing a JDBC driver generally consists of copying the driver to your computer, then add the location of it to your classpath. In addition, many JDBC drivers other than Type 4 drivers require you to install a client-side API. No other special configuration is usually needed.
This tutorial is divided into a number of separate posts as refer below.

JDBC API - Statement Interface Examples

In these posts, you will learn JDBC Statement interface APIs/Methods with examples in detail.
          Example to create a table using Statement interface.
          Example to insert multiple records in a table using Statement interface.
          Example to update a record in a table using Statement interface.
          Example to retrieve records from a table using Statement interface.
          Example to delete a record from a table using Statement interface.
          Example to insert records in a batch process via Statement interface.
          Example to update records in a batch process via Statement interface.

JDBC API - PreparedStatement Examples

In these posts, you will learn JDBC PreparedStatement interface APIs/Methods with examples in detail.
         Example to insert a record in a table using PreparedStatement interface.
         Example to update a record in a table using PreparedStatement interface.
          Example to retrieve records from a table using PreparedStatement interface.
          Example to pass a list of values to IN clause using PreparedStatement interface.
          Example to insert records in a batch process via PreparedStatement interface.
          Example to update records in a batch process via PreparedStatement interface.

JDBC API - CallableStatement Examples

In these posts, you will learn JDBC CallableStatement interface APIs/Methods with examples in detail.
          Create and use Stored Procedure examples using CallableStatement interface.

JDBC API - Transactions

          How to use JDBC transactions with examples.

JDBC API - SQLExceptions Handling

          In this article, we will learn how to handle SQLExceptions while working with JDBC.

JDBC API - java.sql Package

          In this article, we will learn commonly used methods of Connection interface with examples.
         In this article, we will learn commonly used methods of Statement interface with examples.
          In this article, we will learn commonly used methods of PreparedStatement interface.
          In this article, we will learn commonly used methods of CallableStatement interface.
          In this article, we will learn commonly used methods of ResultSet interface with examples.
          In this article, we will learn commonly used methods of ResultSetMetaData interface.
          In this article, we will learn commonly used methods of DatabaseMetadata interface.
          In this article, we will learn commonly used methods of DriverManager class with examples.

JDBC API - Batch Processing

         Example to update records in a batch process using Statement and PreparedStatement interfaces.
         Example to insert records in a batch process using Statement and PreparedStatement interfaces.
          Example to insert records in a batch process via Statement interface.
         Example to update records in a batch process via Statement interface.
         Example to insert records in a batch process via PreparedStatement interface.
          Example to update records in a batch process via PreparedStatement interface.

JSP + JDBC

  • JSP + JDBC + MySQL Example -  In this article, we will build a simple Employee Registration module using JSPJDBC, and MySQL database.

JDBC API FAQ

         Example to dynamically insert rows using StringBuilder and PreparedStatement placeholders ?.
         This article provides how to retrieve the column names of a table using getMetaData() method.
          Example of how to use DataSource to connect with MySQL database.

Resource and Useful Links

Comments