Dolt is the world's first version-controlled SQL database – you can branch, merge, diff, fork, revert, rebase, and clone your relational data using the same version control model and syntax as Git. Dolt is open source and built from the ground up to leverage a novel storage engine that allows Dolt to efficiently diff data, merge data, and support structural sharing of stored data. Although Dolt's internals are quite unique, the supported SQL dialect and the wire protocol for communicating with a Dolt database is fully compatible with MySQL, which means you can use Dolt with any tool or library from the MySQL ecosystem.

We've written previous blog posts showing how Dolt works with other libraries, ORMs, and tools in the MySQL ecosystem, such as Debezium, Diesel, ASP.NET Core, and many more. Today, we're taking a closer look at how Dolt works with the venerable Java Database Connectivity (JDBC) API.

If you just want to jump straight into sample code for this blog post, go ahead and clone the dolthub/dolt-jdbc-sample repository on GitHub and follow the README instructions to get it running.

JDBC

JDBC is the default API that Java applications use to connect to a database. It provides a standard interface for connecting to any type of database and for executing queries and reading result sets.

JDBC has been around for almost three decades now, since it was originally introduced in JDK 1.1 in 1997. It is extremely widely used in Java applications, as well as higher-level database access frameworks, such as Hibernate and the Java Persistence API (JPA), which leverage JDBC for the lower-level database connectivity.

A JDBC driver implements the JDBC API for a specific database. Since Dolt is compatible with MySQL, we'll be using the MySQL Connector/J JDBC driver to connect to Dolt in our sample application today.

Dolt JDBC Sample

The rest of this blog walks through a simple example of building an application to connect to a Dolt database using JDBC. You can find the full source code for this application in the dolthub/dolt-jdbc-sample repository on GitHub.

Start a Dolt SQL Server

Before we start creating the project, let's go ahead and get a Dolt SQL server running for our application to connect to. If you don't already have Dolt installed, head over to the Dolt installation docs and follow the instructions to install Dolt.

Once Dolt is installed, create a directory for your new Dolt database and initialize it as a Dolt database directory:

mkdir doltdb && cd doltdb
dolt init

After you run dolt init, you'll have a working Dolt database directory. To launch a Dolt SQL server that serves the data in this directory, run:

dolt sql-server --port 11229

Note that by default, Dolt's SQL server will start up on port 3306, the same port used by MySQL. In case you already have a MySQL server running on your machine, we've added the --port 11229 flag to start the server on a different port. Keep this terminal window open so you can see any log statements emitted from the server.

Next, we'll open up a SQL shell to our Dolt database and create some sample data. In a new terminal window, run:

mysql --port 11229 --protocol TCP -u root 

This will open up a SQL shell connected to your Dolt database. There isn't any data in the database yet, so let's create some sample data to query:

-- the database name comes from the directory where we ran dolt init
use doltdb;

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

-- Insert some sample data
INSERT INTO users (name, email) VALUES ("Mickey", "themick@dolthub.com"), ("Minnie", "minnie@dolthub.com"), ("Goofy", "goofy@dolthub.com");

-- Create a Dolt commit
CALL dolt_commit('-Am', 'Adding sample data for users table');

Now that we have some sample data in our Dolt database, we can start building our Java application to connect to it.

Create a New Java Project

Jump over to your favorite IDE and create a new Java project. We'll be using Maven in this sample to help manage our dependencies and our build process, but you can use whatever build tool you prefer. One of the most important details to include in your Maven pom.xml file is the dependency on the MySQL Connector/J JDBC driver. Make sure your pom.xml includes the following dependencies section:

    <dependencies>
        <!-- MySQL JDBC Driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version> <!-- Ensure you use the latest version -->
        </dependency>
    </dependencies>

You can check out the complete Maven pom.xml file below.

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>dolt-jdbc-sample</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>Dolt JDBC Sample</name>
    <url>https://doltdb.com</url>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>  <!-- Specify Java version -->
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <dependencies>
        <!-- MySQL JDBC Driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version> <!-- Ensure you use the latest version -->
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <!-- Maven Compiler Plugin for compiling Java code -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>11</source>  <!-- Use Java 11 or the version you prefer -->
                    <target>11</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Open a JDBC Connection

Now that we've got a Java project set up with a dependency on the MySQL JDBC driver, let's write some Java code to connect to our database!

I've created a Java file named Main.java at the root of my new project. At the top of this new class, I've added some constants that we'll use to tell our JDBC connection where our database is, and I've also added a variable to hold the JDBC connection.

    // JDBC URL, username, and password of Dolt server
    private static final String URL = "jdbc:mysql://localhost:11229/doltdb";
    private static final String USER = "root";
    private static final String PASSWORD = "";

    // JDBC variables for opening and managing connection
    private static Connection connection;

Inside the main method, I've added code to open a JDBC connection:

    // Establish a connection to the database
    connection = DriverManager.getConnection(URL, USER, PASSWORD);

    System.out.println("Connection to Dolt database successful!");

Now that we have a JDBC connection object, we can use that to prepare and execute queries against our Dolt database.

Execute Queries

We're ready to start querying some data now! The code below shows how we create a PreparedStatement from a query and how we execute that prepared statement to get a ResultSet:

    // Perform a sample query (SELECT)
    String query = "SELECT id, name, email FROM users";
    PreparedStatement statement = connection.prepareStatement(query);

    // Execute query and get the result
    ResultSet resultSet = statement.executeQuery();

Once we have a ResultSet, we can iterate over the rows and print out our data:

    // Process the result set
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String email = resultSet.getString("email");
        System.out.printf("User [ID=%d, Name=%s, Email=%s]%n", id, name, email);
    }

You should see the three rows we inserted earlier printed out to the console:

    User [ID=1, Name=Mickey, Email=themick@dolthub.com]
    User [ID=2, Name=Minnie, Email=minnie@dolthub.com]
    User [ID=3, Name=Goofy, Email=goofy@dolthub.com]

Last, but not least, don't forget to close the ResultSet, Statement, and Connection objects when you're done with them:

    // Close the result set, statement, and connection
    resultSet.close();
    statement.close();
    connection.close();

You can find the full source code for this example in GitHub, or in the expandable section below.

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

public class Main {
    // JDBC URL, username, and password of Dolt server
    private static final String URL = "jdbc:mysql://localhost:11229/doltdb";
    private static final String USER = "root";
    private static final String PASSWORD = "";

    // JDBC variables for opening and managing connection
    private static Connection connection;

    public static void main(String[] args) throws Exception {
        try {
            // Establish a connection to the database
            connection = DriverManager.getConnection(URL, USER, PASSWORD);

            System.out.println("Connection to Dolt database successful!");

            // Perform a sample query (SELECT)
            String query = "SELECT id, name, email FROM users";
            PreparedStatement statement = connection.prepareStatement(query);

            // Execute query and get the result
            ResultSet resultSet = statement.executeQuery();

            // Process the result set
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                System.out.printf("User [ID=%d, Name=%s, Email=%s]%n", id, name, email);
            }

            // Close the statement and result set
            resultSet.close();
            statement.close();

        } catch (SQLException e) {
            // Handle SQL exceptions
            System.err.println("SQL error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // Always close the connection to avoid database connection leakage
            if (connection != null) {
                try {
                    connection.close();
                    System.out.println("Connection closed.");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Query Dolt Version Control Data

Now that we've seen the basics of connecting to a Dolt database via JDBC and running a query, let's look at an example of accessing some of the unique version control features of Dolt, such as querying Dolt's system tables, calling Dolt's stored procedures, and using Dolt's functions. These are the main ways of accessing Dolt's version control features.

In this example, we're going to call the dolt_diff() table function. This function allows you to see how every row in a table has changed between those two commits. For example, you could use this function to find the differences between the main branch and a feature dev branch to quickly see how a table's data differs between two branches. In our example below, we'll use ancestry syntax to compare HEAD (i.e. the current tip of the current branch) with HEAD~ (the commit immediately preceding the tip of the current branch).

The schema of the returned result set for dolt_diff() consists of the schema from the underlying table prepended with from_ to show the value of all fields in a row at the starting point, and the schema from the underlying table prepended with to_ to show the value of all fields in a row at the ending point. A few additional fields such as diff_type provide extra metadata about the change.

Just like before, we'll create a new PreparedStatement and execute it to get a ResultSet and then pull out each field we want to access by referencing its name:

    // Query the dolt_diff() table function to see what changed in the most recent commit
    statement = connection.prepareStatement("select * from dolt_diff('HEAD~', 'HEAD', 'users');");
    resultSet = statement.executeQuery();
    while (resultSet.next()) {
        int toId = resultSet.getInt("to_id");
        String toName = resultSet.getString("to_name");
        String toEmail = resultSet.getString("to_email");
        int fromId = resultSet.getInt("from_id");
        String fromName = resultSet.getString("from_name");
        String fromEmail = resultSet.getString("from_email");
        String diffType = resultSet.getString("diff_type");

        System.out.printf("%s: From (%d, %s, %s), To (%d, %s, %s)%n", diffType, fromId, fromName, fromEmail, toId, toName, toEmail);
    }

When we run this code, we should see results showing that three new rows were added to the users table in the most recent commit:

    added: From (0, null, null), To (1, Mickey, themick@dolthub.com)
    added: From (0, null, null), To (2, Minnie, minnie@dolthub.com)
    added: From (0, null, null), To (3, Goofy, goofy@dolthub.com)

Connect to a Specific Branch

One of the most powerful features of Dolt is the ability to branch your data and then merge changes back together. For example, you might configure your application to use a dev branch while you test in development and a prod branch in production. You could periodically merge data from the prod branch to the dev branch so that you have a development environment with data that closely resembles real production data. This also allows you to use a single Dolt SQL server, while still keeping all the data in the two environments separate and isolated.

Branches can be used in any way that makes sense for your application and your development process. Some customers use a customer-per-branch data model, where each customer's data is isolated on its own branch. Other customers actually expose Dolt's branching capabilities directly to their customers as another feature of their application.

When working with branches in a Java application, there are two main ways to check out the branch you want to work with. In the first approach, you call the dolt_checkout() stored procedure to switch to the branch you want to work with. This should be done as soon as you get the JDBC connection, either from directly creating the connection, or by grabbing it from a connection pool. This is the most flexible approach, since you can work with any branch you want, and you can switch branches at any time, but it requires explicit branch management in your application code. For example, the following snippet shows how to create a connection and then immediately call dolt_checkout() to switch to a branch named myBranch:

    String url = "jdbc:mysql://localhost:11229/doltdb";
    connection = DriverManager.getConnection(url, "root", "");
    PreparedStatement statement = connection.prepareStatement("call dolt_checkout('myBranch');");
    statement.execute();

In the second approach, you specify the branch name in the JDBC connection string. You can include the branch name directly in the JDBC connection string and Dolt will automatically connect you to the specific branch. This approach doesn't require you to add branch management statements to your application code, but it is slightly less flexible than the first approach, since you can't dynamically select different branches. This approach works very well when you want all connections in an application to use the same branch and you want to be able to control it from a central location. For example, the following code snippet shows how to create a connection that will automatically be connected to the myBranch branch:

    String urlWithBranch = "jdbc:mysql://localhost:11229/doltdb/myBranch";
    connection = DriverManager.getConnection(urlWithBranch, "root", "");

Summary

It's easy to connect to a Dolt database from Java applications via JDBC. The JDBC API gives you a standard interface for executing SQL queries and reading results from a Dolt SQL server. Dolt is fully compatible with the standard MySQL Connector/J JDBC Driver. JDBC is a lower level interface than ORMs or persistence frameworks, and primarily models database connections, statements, and results. You can access all the version-control features of Dolt through JDBC. The dolthub/dolt-jdbc-sample project on GitHub provides a simple example and good starting point for working with Dolt via JDBC.

If you want to talk about database development with Java, version control, or anything else, come by and join us, and other Dolt customers, on the DoltHub Discord server.