What is JDBC?

Intro

So, you want to connect your Java application to a database. It actually doesn’t matter what kind of application, if server-side or frontend GUI. And it also doesn’t matter what database, if MySQL, Postgres or SQLite (or any other).

The way every Java application connects to a database is through the JDBC API.

Even better, you do not need to install the JDBC API explicitly or include it as a third-party library in your project, because, by default, it comes with every JDK/JRE.

The only thing you need to get started with JDBC is a driver for your specific database.

JDBC Drivers

What is a JDBC driver?

To connect to your database in Java, you need a so-called JDBC driver. Every database (MySQL, Oracle, PostgreSQL etc.) comes with their own JDBC driver, usually built by the database vendor and found on the database’s website.

Drivers do a fair amount of work, from the basics of opening socket connections from your Java application to the database, submitting your SQL queries, to more advanced features like offering abilities to receive events from the database (Oracle).

So, to connect to, for example, a MySQL database, you will need to go to the MySQL website, download the MySQL JDBC driver .jar file (also called: Connector/J) and add it to your project.

Where do I find the latest JDBC driver for my database?

Here’s a list of driver locations for the most popular databases for your reference:

• Postgres

• MySQL

• SQL Server

• DB2

• Oracle

• H2

JDBC drivers & Maven repositories

If you are using Maven or Gradle in your project, then you would add the JDBC driver as a dependency to your project instead of adding the .jar file manually.

You’ll find the Maven coordinates (=XML) tags for every JDBC driver in this great list by Vlad Mihalcea:

https://vladmihalcea.com/jdbc-driver-maven-dependency/

So, in the case of MySQL, you would add this tag to your pom.xml file.

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.20</version>
    </dependency>

JDBC Connections

Opening database connections with JDBC

As soon as you added the driver to your project, you are ready to open up JDBC connections to your database.

We added the mysql-connector-java driver, so we are going to connect to a MySQL database. If you have ever used a database before, this will essentially be the same as opening up a terminal window and executing the 'mysql' command to use MySQL’s command-line interface.

In short, you will end up with an active database connection and can then run your SQL statements.

So, to access a MySQL database called test, running on your local machine and valid username/password, you would execute the following code:

package com.marcobehler;

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

public class MyMainClass {

    public static void main(String[] args) {
        try (Connection conn = DriverManager
                .getConnection("jdbc:mysql://localhost/test?serverTimezone=UTC",
                        "myUsername", "myPassword")) {

            // "0" means disabling the timeout, when doing isValid checks
            boolean isValid = conn.isValid(0);
            System.out.println("Do we have a valid db connection? = " + isValid);

            // Do something with the Connection, run some SQL statements
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Let’s break this down:

try (Connection conn = DriverManager
        .getConnection("jdbc:mysql://localhost/test?serverTimezone=UTC",
                "myUsername", "myPassword")) {

The simplest way to open a database connection is by calling DriverManager.getConnection, which will automatically detect the JDBC driver you added to your project, earlier.

It takes three parameters:

• [0] URL: A valid JDBC URL. You are going to see what these have to look like in the next section.

• [1] Username: Self-explanatory

• [2] Password: Self-explanatory
  

// "0" means disabling the timeout, when doing isValid checks
boolean isValid = conn.isValid(0);
System.out.println("Do we have a valid db connection? = " + isValid);

As soon as you have a connection, you can execute SQL queries with it. As we don’t make this example too complicated, we’re just checking if the connection is alive and kicking (and hasn’t been canceled by the database server or something similar).

There’s one last thing to note. Let’s take a step back again:

try (Connection conn = DriverManager
        .getConnection("jdbc:mysql://localhost/test?serverTimezone=UTC",
                "myUsername", "myPassword")) {
}

Notice the try block around our getConnection call? It is a try-with-resources statement and makes sure that your connection automatically gets closed at the end of the block, without you having to close it manually. That’s a handy convenience function.

Alright, you now know how to open database connections. Let’s have a look at these JDBC connection strings, now.

JDBC Connection Strings - Explained

The string passed to the DriverManager.getConnection() method above is what is called the JDBC connection string and is database specific. More specifically a connection string:

• Always starts with jdbc, followed by a colon (:).

• Then has an identifier for the database, like mysql or oracle, followed by a colon (:).

• Followed by a database specific connection string.

Let’s check out our MySQL example from above again:

.getConnection("jdbc:mysql://localhost/test?serverTimezone=UTC",

1. It starts with jdbc:

2. Then mysql:

3. Then a MySQL specific string: localhost/test?serverTimezone=UTC. ServerTimezone is a property that only a MySQL server understands, so this won’t work for e.g. PostgreSQL.

Here’s a couple examples of what those connection strings can look like for other databases:

• MySQL → jdbc:mysql://localhost/mydb

• Postgres → dbc:postgresql://localhost/test

• Oracle → jdbc:oracle:thin:@prodHost:1521:ORCLE

• SQL Server → jdbc:sqlserver://localhost;instance=SQLEXPRESS;databaseName=myDb

The gist: Do not freak out if your connection string looks complex. Always consult the database’s documentation to decipher it.

A good cheat-sheet for finding out what your connection string has to look like can be found here: JDBC Driver Strings.

JDBC Queries & ResultSets

As soon as you have an open database connection, it is time to fire your SQL queries against the database. But how?

How to execute JDBC queries (SQL queries)

Let’s have a look at how you would execute your typical CRUD SQL queries:

• select * from…​

• insert into…​

• update…​

(Assume the connection comes from a DataSource, just like in our examples above.)

Select queries & ResultSets

// jdbc select from
PreparedStatement selectStatement = connection.prepareStatement("select * from users where first_name =  ?");
selectStatement.setString(1, "John");

// this will return a ResultSet of all users with said name
ResultSet rs = selectStatement.executeQuery();

// will traverse through all found rows
while (rs.next()) {
    String firstName = rs.getString("first_name");
    String lastName = rs.getString("last_name");
    System.out.println("firstName = " + firstName + "," + "lastName= " + lastName );
}

There are two parts to selecting data with JDBC:

First, you’ll need to create a PreparedStatement, which takes your SQL as input, with any placeholders marked as ?. Here we are selecting everything from an imaginary users database table, with a where condition on the first_name column.

You’ll want to use ? placeholders for any variable data (like the first name) to be safe from SQL Injection. You’ll need to set them on your PreparedStatement with the correct setter (setString for strings, setInt for ints etc). Also, keep in mind that the placeholders are numbered, starting with 1. So if you have just one question mark, you’ll need to call setString(1, yourString). For the second one setString(2, someOtherString) etc.

Second, executing the query will return you a ResultSet, which is basically a list of all the rows that your database found for a given query. And you’ll have to traverse through that ResultSet with a while-loop, calling rs.next().

In our example above we simply print out the first_name and last_name of every user that we found. Note that the ResultSet offers different get methods, like getString or getInt or getDate, that you need to call depending on the column types of your returned database records. FirstName and LastName are string columns, so we simply call getString.

Simple, right?

Insert queries & ExecuteUpdate

// jdbc insert into
PreparedStatement statement = connection.prepareStatement("insert into users (first_name, last_name) values (?,?)");
statement.setString(1, "John");
statement.setString(2, "Rambo");
int insertedRows = statement.executeUpdate();
System.out.println("I just inserted " + insertedRows + " users");

Inserting rows into the database is somewhat similar to selecting rows. Again, you create a PreparedStatement, with the only difference that you now call the executeUpdate method on the statement (don’t get thrown off by the name, there is no executeInsert).

Once more, you should use a PreparedStatement with ? placeholders to safeguard against SQL injection.

You won’t have to traverse through a ResultSet with inserts, instead, executeUpdate will return you the actual number of inserted rows (1 in our case if everything worked correctly).

Update queries & ExecuteUpdate

// jdbc update
PreparedStatement updateStatement = connection.prepareStatement("update users set first_name = 'unknown' where id = ?");
updateStatement.setInt(1, 1);
int updatedRows = updateStatement.executeUpdate();
System.out.println("I just updated " + updatedRows + " users");

Updating rows is basically identical to inserting rows. You create a PreparedStatement and call executeUpdate on it.

Once more, you should use a PreparedStatement with ? placeholders to safeguard against SQL injection. ExecuteUpdate will now return you the actual number of updated rows.

Delete queries & ExecuteUpdate

They are the same as the update queries above, so we’ll skip them for now.

JDBC Connection Pooling

What is JDBC connection pooling?

Opening and closing database connections (think: tcp sockets and connections) like you did above with the DriverManager.getConnection method takes some time.

Especially in web applications, you do not want to open up a fresh database connection for every single user action, rather you want to have a small pool of connections that are always open, available shared between users.

That’s what JDBC connection pools are for. A connection pool keeps open a small number of database connections (think: 10) and instead of opening up database connections yourself, you’ll ask the connection pool instead.

What is the best JDBC connection pool?

Unfortunately, In Java land you are hammered with a plethora of options when it comes to connection pools:

• Older, legacy pools: Apache Commons DBCP and C3P0.

• HikariCP (recommended)

• Vibur-dbcp

• Oracle’s UCP

• Tomcat JDBC Connection Pool

The issue with the older connection pools (DBCP / C3P0) is that they lack proper, sane configuration defaults, have trouble with performance and handling error cases and are - on top - often misconfigured.

Therefore, for newer projects I would recommend the following pools:

HikariCP or Vibur-dbcp as a great default choice (HikariCP is Spring Boot’s default choice). Use Oracle’s UCP, if you are working with Oracle databases.

All of these connection pools are rock solid, performant, and offer sane defaults & error handling.

How to use a JDBC connection pool

Independent of the option you choose, you will then, in your JDBC code, not open up connections yourself through the DriverManager, but rather you will construct a connection pool, represented by the DataSource interface, and ask it to give you one of its connections.

Let’s see some code. It is exactly the same code as above (connecting to MySQL), online re-written to use a DataSource, instead of the DriverManager.

package com.marcobehler;

import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class MyHikariCpClass {

    public static void main(String[] args) {
        DataSource dataSource = createDataSource();

        try (Connection conn = dataSource.getConnection()) {

            // "0" means disabling the timeout, when doing isValid checks
            boolean isValid = conn.isValid(0);
            System.out.println("Do we have a valid db connection? = " + isValid);

            // Do something with the Connection, run some SQL statements
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static DataSource createDataSource() {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl("jdbc:mysql://localhost/test?serverTimezone=UTC");
        ds.setUsername("myUsername");
        ds.setPassword("myPassword");
        return ds;
    }
}

Let’s break it down.

DataSource dataSource = createDataSource();

You need to create a connection pool data source somewhere in your application. Here, we extracted the creation code to another method.

private static DataSource createDataSource() {
    HikariDataSource ds = new HikariDataSource();
    ds.setJdbcUrl("jdbc:mysql://localhost/test?serverTimezone=UTC");
    ds.setUsername("myUsername");
    ds.setPassword("myPassword");
    return ds;
}

This is HikariCP specific configuration code. In the end, though, we simply set the same parameters we would have set on the DriverManager.getConnection() call.

try (Connection conn = dataSource.getConnection()) {

As you can see, you don’t directly open connections anymore through the DriverManager, but you ask the DataSource instead. The first time we ask our HikariDataSource for a database connection, it will initialize a pool behind the scenes - which means opening up (by default) 10 database connections and giving you one of these ten.

Great! Now, you don’t have to worry about the performance penalties anymore of opening and closing database connections.

Fin

This article covered the basics of JDBC, from drivers to handling connections and SQL queries, to connection pooling.

The gist is: When using JDBC, you are working with bare metal. You have the full power and speed of SQL and JDBC at your hand, but JDBC comes with no convenience features (think about how much manual work traversing a ResultSet is).

That’s where other Java database frameworks & libraries come in. From light-weight JDBC wrappers, to full-blown ORM solutions like Hibernate/JPA.

You can get a great overview of all these frameworks in the Java & Databases: Frameworks & Libraries article.

That’s it for today. Thanks for reading.