JDBC Tutorial Part 1: Connecting to a Database

In this series of articles (and videos), you’ll learn the basics of Java Database Connectivity, most frequently abbreviated as JDBC. All of the most popular persistence frameworks use JDBC behind the scenes, so having a solid understanding of the key concepts in JDBC is key when using JPA, Hibernate, MyBatis, jOOQ, or any other database framework for Java.

The most important concepts that you need to know are:

JDBC driver

Connection

Connection pool

In addition to these concepts, you need to understand how to make calls to the database: specifically, how to run an SQL query from a Java program, how to process the results, and how to insert, update, and delete data.

This article focuses on what a JDBC driver is and how to open and safely close database connections. The next articles talk about executing SQL statements and using connection pools.

Note: The source code is available on GitHub with each part in independent Maven projects.

Here’s a video version of this article, in case you want to see the concepts in action:

Creating a Demo Database

Before we start, we need a database to play with. I assume you have installed MariaDB on your computer or have a SkySQL account with a database instance running in the Cloud (you can create an account for free). In my case, I have it running on my computer, so I can connect to the server using the mariadb client tool using the database user that I created beforehand:

mariadb -u user -p

You might have to specify the host, port, and database username for your own instance. For example, in the case of SkySQL, you can use something like the following:

mariadb --host example.skysql.net --port 5001 \
    --user db_user --password \
    --ssl-verify-server-cert \
    --ssl-ca /path/to/skysql_chain.pem

Let’s create a new database with the name jdbc_demo and a table with the name programming_language as follows:

CREATE DATABASE jdbc_demo;
USE jdbc_demo;

CREATE TABLE programming_language(
    name VARCHAR(50) NOT NULL UNIQUE,
    Rating INT
);

Use the quit command to exit the client tool.

What Is a JDBC Driver?

JDBC is an API specification: a set of interfaces that define what the technology can do. It doesn’t implement the details of how to connect to a specific database. Instead, it lets database vendors implement the logic required to “talk” to their databases. Each database has a different way to “speak” through the network (the database client/server protocol), so each database needs custom Java code compatible with JDBC. This code is packaged as a JAR file called JDBC driver.

For example, in the case of MariaDB (a multi-purpose database), the JDBC driver is MariaDB Connector/J. You have to download the JAR file that contains the driver and add it to your Java project. If you are using Maven, you can add the dependency to the pom.xml file. When you search for the JDBC driver of the database you want, use the latest version of the driver if possible. This way you’ll get the latest security patches, performance improvements, and features.

Creating a New Maven Project

Let’s create a new Java application with a simple pom.xml file that includes the MariaDB JDBC driver:

<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>jdbc-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>2.7.4</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-shade-plugin</artifactId>
                <version>3.2.4</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <transformers>
                                <transformer
                                        implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <mainClass>com.example.Application</mainClass>
                                </transformer>
                            </transformers>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

As you can see, the JDBC driver is called mariadb-java-client. We have also added the Maven Shade Plugin to be able to generate an executable JAR that includes the JDBC driver (Uber JAR).

You can place this pom.xml file in an empty directory and run mvn package to download the MariaDB Connector/J dependency and check that the project builds correctly:

We are going to code a simple Java application that opens a connection and closes it: no data manipulation for now. That’s covered in the next article of this series. The application includes a Java entry point (main) and a couple of methods. You can use any IDE to create the class or we can manually add a new subdirectory for the Java package where our code will reside. On Linux-like systems, this will look like the following:

If you are wondering what that little creature is, it’s a seal. MariaDB’s logo is a sea lion. Since I wasn’t able to find a sea lion emoji, I went for a seal instead. Close enough I guess.

Back to the tutorial... Here’s the structure of the Java app (add this into a new Application.java file in the directory we created):

package com.example;

public class Application {

    public static void main(String[] args) throws SQLException {
        openDatabaseConnection();
        closeDatabaseConnection();
    }

    private static void openDatabaseConnection() {
    }

    private static void closeDatabaseConnection() {
    }

}

Opening Database Connections From Java

With JDBC, Java applications establish database connections through the DataSource interface or the DriverManager class. For simplicity, we are going to use the latter in this step of the tutorial, but most serious applications should use a connection pool (we’ll learn this in the third article of this series).

To connect to a database using JDBC, we need something called a connection URL (or JDBC connection string). Depending on your specific database the connection URL will look different, but in general, a connection string looks like this:

jdbc:<subprotocol>:<subname>

The <subprotocol> part identifies the kind of database. The <subname> part is database-specific and typically contains information on the location of the database instance and configuration parameters. For example, in the case of a MariaDB server running locally, the connection URL looks something like this:

jdbc:mariadb://localhost:3306/jdbc_demo

Or, if you are using SkySQL:

jdbc:mariadb://example.skysql.net:5001/jdbc_demo?useSsl=true&serverSslCert=/path/to/skysql_chain.pem

You can find examples of connection URLs for all databases online and in the official documentation of the JDBC drivers.

Here’s how we can connect to the database from the Java application:

package com.example;

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

public class Application {

    private static Connection connection;

    public static void main(String[] args) throws SQLException {
        openDatabaseConnection();
        closeDatabaseConnection();
    }

    private static void openDatabaseConnection() throws SQLException{
        System.out.println("Opening database connection...");

        connection = DriverManager.getConnection(
                "jdbc:mariadb://localhost:3306/jdbc_demo",
                "user", "password"
        );

        System.out.println("Connection valid: " + connection.isValid(0));
    }

    private static void closeDatabaseConnection() {
    }

}

First, we added a Java field of type Connection to the class. This interface contains all the methods to interact with the database. Second, we used the DriverManager class to get a new connection to the database using the connection URL and the database username and password. Third, we showed a message confirming that the connection is valid.

Notice that we added a throws clause to the method signatures. Most JDBC operations throw an SQLException in case of errors. We can handle it in a catch block or in the case we want to show an error message to the user. In this demo, we won’t deal with exception handling.

Closing Database Connections

Before the application finishes, we need to close the database connection. A connection consumes resources that should be released: both operating-system-level resources and database resources such as cursors or handles. Here’s how we can close the database connection:

private static void closeDatabaseConnection() throws SQLException {
        connection.close();
    }

That’s it really. But, wait! There’s a catch here. Since later in this series, we will be adding functionality between the openDatabaseConnection() and closeDatabaseConnection() methods, things could go wrong at any point and we might not be able to close the database connection properly. Hopefully, this can be easily solved by enclosing the code in a try block and closing the connection in a finally block. The finally block always gets executed even if a problem occurs and an exception is thrown. Here’s the final result with the problem fixed:

package com.example;

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

public class Application {

    private static Connection connection;

    public static void main(String[] args) throws SQLException {
        try {
            openDatabaseConnection();
        } finally {
            closeDatabaseConnection();
        }
    }

    private static void openDatabaseConnection() throws SQLException{
        System.out.println("Opening database connection...");

        connection = DriverManager.getConnection(
                "jdbc:mariadb://localhost:3306/jdbc_demo",
                "user", "password"
        );

        System.out.println("Connection valid: " + connection.isValid(0));
    }

    private static void closeDatabaseConnection() throws SQLException {
        connection.close();
        System.out.println("Connection valid: " + connection.isValid(0));
    }

}

Run mvn clean package to build the final JAR and run it from the command line as follows:

java -jar target/jdbc-demo-1.0-SNAPSHOT.jar

Here’s a screenshot of the output:

Try manually throwing an exception somewhere before closing the connection to simulate an error and see how the connection is closed regardless:

if (true) throw new RuntimeException("Simulated error!");

In the next article, we’ll learn how to use the connection object to send SQL statements to the database.

Categories:

Java Sql Database

Tags:

Java Sql Programming Database

Websites To Learn Programming For Free

PHP __construct (🧙‍♂️Lesson 1: PHP Magic Methods)

MySQL shell: the best tool for your logical backups

Abstract class vs interfaces. When to use them?

C++ is awesome, here's why...

Arctype connects to YugabyteDB

Solving the Cash Register challenge in Free Code Camp

Consultando API de CEP com Fetch

JDBC Tutorial Part 1: Connecting to a Database

Categories:

Tags:

Related Posts