Using Java to Connect to your Database: JDBC API

One of the topics being covered in my database management class right now is JDBC, which stands for Java DataBase Connectivity. Essentially the API is used to manage how the client-side of our app accesses the backend. More specifically, it allows us to use SQL directly in our Java code. I figured it would be a good topic to walk through and demo the use of this API.

This API is included in more recent versions of JDK, which allows us to use it without having to install any library files (though we still have to install driver files, but we’ll get to that). Please refer to the JDBC Documentation for a more exhaustive look at the nuances and functionalities of this API.

For this brief example, I will be using a local MySQL community database instance. If you are following along, please make sure to install this as JDBC uses a specific drivers for different SQL variants.

Let’s start off my making a new directory with two folders called src and lib. In src, we will create a new file called ExampleQuery.java. Install the MySQL official connector driver, extract the entire folder and place it into the lib folder.

Your final folder hierarchy should look like this:

Inside our ExampleQuery.java file, be sure to import the JDBC api using this line:

import java.sql.*;

It’s not best practice to import the entire library, but until you work with this long enough to the point you are sure of which components you will need, it’s better to have everything available at the start and go back to change it after we are done with the rest of the code.

We will be compiling and running this code using our command line, so we can just keep all of it inside our main function. We will be invoking this small app with the following command:

java ExampleQuery <DBURL> <DBUsername> <DBPassword> <DBDriver> <SQL Query>

We will be utilizing Connection, Statement, and ResultSet from the JDBC API. All instantiation of these classes must be done within a try-catch block. The entire process can be broken down into 5 steps:

Step 1) Load the driver

This step is pretty simple, you just need to find the name of the driver package. For our MySQL driver, the name is com.mysql.cj.jdbc.Driver. In my code I have the Driver name as the fourth command line argument, as that lends to more flexibility when using different types of databases or drivers.

Load the driver name in with:

Class.forName(args[3]);

Step 2) Make the Connection to the DB

Another simple step, though this will require that you have your db instance’s URI, admin username, and password. The java.sql package contains a class called DriverManager, with a static method getConnection(), which takes a URI string, User name string, and a password string as arguments, and returns a Connection object.

Make the connection with:

Connection conn = DriverManager.getConnection(args[0],args[1],args[2]);

Step 3) Create the Statement object

The Connection object has an instance method called createStatement(). This essentially just creates a Statement object that is associated with the database via our Connection object.

Create the Statement object with:

Statement stmt = conn.createStatement();

Step 4) Query our database

In turn, the Statement object has an instance method called executeQuery(), which takes a String SQL query as an argument. It returns a ResultSet object.

To make the query (and retrieve the result):

ResultSet rs = stmt.executeQuery(args[4]);

Step 5) Display our result

The ResultSet object has several instance methods, one of which is next(). In order to iterate through our results, we will be looping this method call in a while loop:

while (rs.next()) { 
//Display results here (see below)
}

Inside this while loop, we then display the data using print lines (or we can use this data for some other purpose in our app). In order to retrieve the data, we must use the getter instance methods from the ResultSet object.

One important thing to note, is that while in most of our experience with arrays and other indexed data, we are 0-indexed (starting at index 0), the columns from our resulting table are 1-indexed (starting at index 1).

For example, if we wanted to retrieve the first column, which we know is the ID of the row, then we would write something like this inside our while loop:

System.out.println("ID: "+rs.getInt(1));

Next, make sure to catch any exceptions that are needed (I just went with a catch-all:

catch (Exception e) { e.printStackTrace(); }

and in our finally block, we must close all of our objects in reverse order of ‘opening’.

if (rs!=null) rs.close();
if (stmt!=null) stmt.close();
if (conn!=null) conn.close();

Compiling and Making Queries

Back into our command line, navigate to the root directory of this project. Compile this app with:

javac src/ExampleQuery.java

And run with:

java -cp "lib/mysql-connector-java-<YOUR VERSION NUM>.jar; ./src" ExampleQuery <DBURL> <DBUsername> <DBPassword> <DBDriver> <SQL Query>