What is JDBC? Introduction to Java Database Connectivity

Get to know Java's low-level API for making database connections and handling SQL queries and responses

JavaWorld > Persistence [series] > coding / programming / software development
Alpesh Ambalal Patel / Getty Images

JDBC (Java Database Connectivity) is the Java API that manages connecting to a database, issuing queries and commands, and handling result sets obtained from the database. Released as part of JDK 1.1 in 1997, JDBC was one of the first components developed for the Java persistence layer.

JDBC was initially conceived as a client-side API, enabling a Java client to interact with a data source. That changed with JDCB 2.0, which included an optional package supporting server-side JDBC connections. Every new JDBC release since then has featured updates to both the client-side package (java.sql) and the server-side package (javax.sql). JDBC 4.3, the most current version as of this writing, was released as part of Java SE 9 in September 2017.

This article presents an overview of JDBC, followed by a hands-on introduction to using the JDBC API to connect a Java client with SQLite, a lightweight relational database.

How JDBC works

Developed as an alternative to the C-based ODBC (Open Database Connectivity) API, JDBC offers a programming-level interface that handles the mechanics of Java applications communicating with a database or RDBMS. The JDBC interface consists of two layers:

  1. The JDBC API supports communication between the Java application and the JDBC manager.
  2. The JDBC driver supports communication between the JDBC manager and the database driver.

JDBC is the common API that your application code interacts with. Beneath that is the JDBC-compliant driver for the database you are using.

Figure 1 is an architectural overview of JDBC in the Java persistence layer.

JavaWorld > Persistence [series] > diagram > Overview of JDBC in the Java persistence layer JavaWorld / IDG

Figure 1. JDBC in the Java persistence layer

Using JDBC to connect to a database

One of the fortunate facts of programming in the Java ecosystem is that you will likely find a stable JDBC database connector for whatever database you choose. In this tutorial we'll use SQLite to get to know JDBC, mainly because it's so easy to use.

The steps for connecting to a database with JDBC are as follows:

  1. Install or locate the database you want to access.
  2. Include the JDBC library.
  3. Ensure the JDBC driver you need is on your classpath.
  4. Use the JDBC library to obtain a connection to the database.
  5. Use the connection to issue SQL commands.
  6. Close the connection when you're finished.

We'll go through these steps together.

Step 1. Download and install SQLite

SQLite is a very compact database. It isn't intended for production use, but is a great choice for quickly trying things out. SQLite uses a file as its functional database, without requiring any service or daemon installations.

To get started with this demo, go ahead and download the SQLite sample database. Unzip the .db file and save it somewhere you won't forget.

This file contains both a functional file-based database and sample schema and data we can use.

Step 2. Import JDBC Into your Java application

We could do our coding in an IDE, but coding directly in a text editor will better demonstrate JDBC's simplicity. To begin, you will need to have a compatible JDK installation for your operating system.

Assuming you have the Java platform developer tools installed, we can start by creating a simple Java program. In your text editor, paste in the code shown in Listing 1. Call this file WhatIsJdbc.java.

Listing 1. A simple Java program


class WhatIsJdbc{
  public static void main(String args[]){
    System.out.println("Hello JavaWorld");
  }
}

Now compile the code by entering the command: javac WhatIsJdbc.java. Compiling will output the WhatIsJdbc.class file. Execute this file from the command line with the call: java WhatIsJdbc.

[ See "What is the JDK? Introduction to the Java Developer Kit" for more about interacting with the JDK on the command line.]

Once you have a basic Java program, you can include the JDBC libraries. Paste in the code from Listing 2 at the head of your simple Java program.

Listing 2. JDBC imports


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;

Each of these imports provides access to a class that facilitates the standard Java database connection:

  • Connection represents the connection to the database.
  • DriverManager obtains the connection to the database. (Another option is DataSource, used for connection pooling. )
  • SQLException handles SQL errors between the Java application and the database.
  • ResultSet and Statement model the data result sets and SQL statements.

We'll see each of these in action shortly.

Step 3. Add the JDBC driver to your classpath

Next, you'll add the SQLite driver to your classpath. A JDBC driver is a class that implements the JDBC API for a specific database.

Download the SQLite driver from GitHub. Be sure to get the most recent .jar file and store it somewhere you'll remember.

The next time you execute your Java program, you will pull that .jar file in via the classpath. There are several ways to set the classpath. Listing 3 shows how to do it using a command-line switch.

Listing 3. Executing SQLite driver on the Java classpath


java.exe -classpath /path-to-driver/sqlite-jdbc-3.23.1.jar:. WhatIsJdbc

Notice that we've set the classpath to point at the driver and the local directory; this way Java will still find our class file.

Step 4. Obtain a database connection

The classpath now has access to the driver. Now, change your simple Java application file to look like the program in Listing 4.

Listing 4. Using the JDBC Connection class to connect to SQLite


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;

class WhatIsJdbc{
  public static void main(String[] args) {
    Connection conn = null;
    try {
      String url = "jdbc:sqlite:path-to-db/chinook/chinook.db";
      conn = DriverManager.getConnection(url);

      System.out.println("Got it!");

    } catch (SQLException e) {
        throw new Error("Problem", e);
    } finally {
      try {
        if (conn != null) {
            conn.close();
        }
      } catch (SQLException ex) {
          System.out.println(ex.getMessage());
      }
    }
  }
}

Compile and execute this code. Assuming all goes well, you will get an affirming message.

Now we're ready for some SQL commands.

Step 5. Query the database

With the live connection object in hand, we can do something useful, like querying the database. Listing 5 shows how to query SQLite using the JDBC Connection and Statement objects.

Listing 5. Querying the database with JDBC


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;

class WhatIsJdbc{
  public static void main(String[] args) {
    Connection conn = null;
    try {
      String url = "jdbc:sqlite:path-to-db-file/chinook/chinook.db";
      conn = DriverManager.getConnection(url);

      Statement stmt = null;
      String query = "select * from albums";
      try {
          stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(query);
          while (rs.next()) {
              String name = rs.getString("title");
              System.out.println(name);
          }
      } catch (SQLException e ) {
          throw new Error("Problem", e);
      } finally {
          if (stmt != null) { stmt.close(); }
      }

    } catch (SQLException e) {
        throw new Error("Problem", e);
    } finally {
      try {
        if (conn != null) {
            conn.close();
        }
      } catch (SQLException ex) {
          System.out.println(ex.getMessage());
      }
    }
  }
}

In Listing 5 we use our Connection object to obtain a Statement object: conn.createStatement(). We then use this object to execute an SQL query: stmt.executeQuery(query).

The executeQuery command returns a ResultSet object, which we then use to iterate over the data with while (rs.next()). In this example, you should see the album titles we've queried on as output.

Notice that we also closed the connection, via a call to conn.close().

Doing more with JDBC

So far we've covered the basics of using JDBC to connect to a database and issue SQL commands. While Statementss and ResultSets work well for common scenarios, you'll likely need additional options for larger or more complex applications. Fortunately, the JDBC library continues evolving to meet most database access needs.

PreparedStatements

One easy way to increase the flexibility of your code is to replace the Statement class with PreparedStatement, as shown in Listing 6.

Listing 6. Using JDBC PreparedStatements


String prepState = "insert into albums values (?, ?);";

PreparedStatement  prepState  =
        connection.prepareStatement(sql);

prepState.setString(1, "Uprising");
prepState.setString(2, "Bob Marley and the Wailers  ");

int rowsAffected = preparedStatement.executeUpdate();

PreparedStatement replaces Statement's hard-coded values with question marks (?). Using PreparedStatements optimizes your code for reuse: a PreparedStatement is compiled only once, and can then be reused with a variety of parameters. As your code base grows, you simply insert new values into the statement, instead of hacking the string object itself.

Batch updates

Whenever an application has several updates to issue, doing them in batches can greatly benefit performance. The essence of batching is to take the multiple updates and collect them together, then issue them all at once. Listing 7 uses JDBC's batch methods to perform a batch update of several PreparedStatements.

Listing 7. Batching with PreparedStatement


prepState.setString(1, "Uprising");
prepState.setString(2, "Bob Marley and the Wailers");
    preparedStatement.addBatch();

prepState.setString(1, "Wildflowers");
prepState.setString(2, "Tom Petty and the Heartbreakers");
    preparedStatement.addBatch();

    int[]  rowsAffected   = preparedStatement.executeBatch();

JDBC transactions

Transactions in relational databases allow for a set of updates to be wrapped in an interaction that either succeeds or fails altogether. The basics of using a transaction via JDBC are to tell the system to turn off auto-commit, and then manually tell the system to commit when you are done. By default, auto-commit is on, which means whenever an executeUpdate or executeInsert is run, the command is committed.

Listing 8 shows a small slice of a JDBC transaction.

Listing 8. JDBC transactions


connection.setAutoCommit(false);
// Use executeUpdate multiple times
connection.commit();

When connection.commit() is encountered, all the updates wrapped inside will be attempted, and if any fail, they all will be rolled back.

There are many more features in JDBC 4.3 worth exploring, including using CallableStatement for stored procedures, using DataSource objects for improved application performance (especially via connection pooling), and converting a JDBC ResultSet to a Java Stream.

Database-specific features

Although every JDBC-compliant database offers the same core features for connecting and interacting with a database via SQL, some databases do more than others. As an example, Oracle DB offers result caching, which is not required by the JDBC specification. Here's an example:


conn.prepareStatement ("select /*+ result_cache */ * from employees where employee_id < : 1");

This example is taken from the documentation for Oracle's JDBC OCI Driver.

Conclusion

JDBC is one of Java's oldest APIs, providing an easy-to-use solution for one of the perennial needs of Java application development. Knowing just the few JDBC calls demonstrated in this article will get you started using JDBC to connect to virtually any database. Once you've got those commands down, you can begin to explore some of the more sophisticated options that have been built into JDBC.

While JDBC is sufficient for simpler applications, most developers will eventually look to the Java Persistence API (JPA) in order to develop a more formal data access layer. JPA requires more up-front work and a more sophisticated understanding of the application architecture, but it nets you a more consistent, isolated, and well-defined data access layer. See the companion to this article, "What is JPA? Introduction to the Java Persistence API" for more about developing the data persistence layer for your Java applications.

This story, "What is JDBC? Introduction to Java Database Connectivity" was originally published by JavaWorld.

ITWorld DealPost: The best in tech deals and discounts.
  
Shop Tech Products at Amazon