Java JDBC API Overview

In this article, I will provide an overview of Java JDBC.

1. What is JDBC?


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


To keep it simple, JDBC allows a Java application to connect to a relational database. The major databases are supported such as Oracle, Microsoft SQL Server, DB2 and many others.

2. JDBC Flow


JDBC helps you to write Java applications that manage these three programming activities:
  1. Connect to a data source, like a database
  2. Send queries and update statements to the database
  3. Retrieve and process the results received from the database in answer to your query

3. JDBC API

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

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

5. Fundamental Steps in JDBC

The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:
  1. Import JDBC packages 
  2. Load and register the JDBC driver // This step is not required in Java 6 and in JDBC 4.0
  3. Open a connection to the database.
  4. Create a statement object to perform a query.
  5. Execute the statement object and return a query resultset.
  6. Process the resultset.
  7. Close the resultset and statement objects. // This step is not required because we use a try-with-resource statement to auto-close the resources
  8. Close the connection. // This step is not required because we use a try-with-resource statement to auto-close the resources
From the above steps, we actually require below five steps to connect a Java application to the database (example: MySQL):
  1. Import JDBC packages 
  2. Open a connection to the database.
  3. Create a statement object to perform a query.
  4. Execute the statement object and return a query resultset.
  5. Process the resultset.

Key points

  1. From JDBC 4.0, we don't need to include 'Class.forName()' in our code to load JDBC driver. JDBC 4.0 drivers that are found in your classpath are automatically loaded.
  2. We have used try-with-resources statements to automatically close JDBC resources.

Step 1. Import JDBC packages 

This is for making the JDBC API classes immediately available to the application program. The following import statement should be included in the program irrespective of the JDBC driver being used:
import java.sql.*;
For individual JDBC API classes imports:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

Step 2. Open a connection to the database

We use the getConnection() method of the DriverManager class to connect to the database.

Syntax: Here are overloaded getConnection() methods available:
Connection java.sql.DriverManager.getConnection(String url) throws SQLException
Connection java.sql.DriverManager.getConnection(String url, String username, String password) throws SQLException
Connection java.sql.DriverManager.getConnection(String url, Properties info) throws SQLException
Example: The following lines of code illustrate using the getConnection() method to connect to a MySQL database:
try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

Step 3. Create a statement object to perform a query

We can use the createStatement() method is invoked on the current Connection object to create a SQL Statement.
Syntax:
public Statement createStatement() throws SQLException
Example: Let's create a Statement object using a connection.createStatement() method:
// Step 3:Create a statement using connection object
 Statement stmt = connection.createStatement();

Step 4. Execute the statement object and return a query resultset

Let's use the executeQuery() method of Statement interface is used to execute SQL statements.
Syntax:

public ResultSet executeQuery(String query) throws SQLException
Example:
// Step 4: Execute the query or update query
ResultSet rs = stmt.executeQuery(QUERY));

Step 5. Process the resultset

Below snippet shows how to process ResultSet object using while loop:
// Step 4: Process the ResultSet object.
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + "," + name + "," + email + "," + country + "," + password);
            }

Complete Code

Let's put all the steps together and here is the complete example with output:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Select Statement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class SelectStatementExample {
    private static final String QUERY = "select id,name,email,country,password from Users";

    public static void main(String[] args) {

        // using try-with-resources to avoid closing resources (boilerplate code)

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            Statement stmt = connection.createStatement();

            // Step 3: Execute the query or update query
            ResultSet rs = stmt.executeQuery(QUERY)) {

            // Step 4: Process the ResultSet object.
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + "," + name + "," + email + "," + country + "," + password);
            }
        } catch (SQLException e) {
            printSQLException(e);
        }
        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Output:
1,Ram,tony@gmail.com,US,secret
3,Pramod,pramod@gmail.com,India,123
4,Deepa,deepa@gmail.com,India,123
5,Tom,top@gmail.com,India,123

Comments