JDBC Tutorial Part 1: Connecting to a Database
Tags: java, sql, programming, database
Published: 2022-01-03 20:01:48

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](https://en.wikipedia.org/wiki/Jakarta_Persistence), [Hibernate](https://en.wikipedia.org/wiki/Hibernate_(framework)), [MyBatis](https://en.wikipedia.org/wiki/MyBatis), [jOOQ](https://en.wikipedia.org/wiki/JOOQ_Object_Oriented_Querying), or any other [database framework](https://en.wikipedia.org/wiki/List_of_object%E2%80%93relational_mapping_software#Java) 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](https://github.com/mariadb-developers/java-quickstart/tree/main/jdbc) with each part in independent Maven projects.

Here’s a [video](https://youtu.be/nWhZl4cMjRw) version of this article, in case you want to see the concepts in action:

[![JDBC Tutorial part 1/3 – Database connections](https://img.youtube.com/vi/ceAev_93p3s/hqdefault.jpg)](https://www.youtube.com/watch?v=ceAev_93p3s)

Creating a Demo Database
------------------------

Before we start, we need a database to play with. I assume you have [installed MariaDB](https://mariadb.com/kb/en/getting-installing-and-upgrading-mariadb/) on your computer or have a [SkySQL](https://mariadb.com/products/skysql) account with a database instance running in the Cloud (you can [create an account](https://mariadb.com/products/skysql/get-started) 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:

```SQL
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](https://jcp.org/aboutJava/communityprocess/mrel/jsr221/index3.html): 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](https://mariadb.com) (a multi-purpose database), the JDBC driver is [MariaDB Connector/J](https://mariadb.com/docs/clients/mariadb-connectors/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](https://search.maven.org/artifact/org.mariadb.jdbc/mariadb-java-client) 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
<?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](https://maven.apache.org/plugins/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:

![JDBC Demo](https://dz2cdn1.dzone.com/storage/temp/15510531-1-jdbc-maven-package.png)

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:

![JDBC on Linux-like systems](https://dz2cdn1.dzone.com/storage/temp/15510532-2-jdbc-mkdir.png)

If you are wondering what that little creature is, it’s a seal. MariaDB’s [logo](https://mariadb.com/about-us/logos/) is a [sea lion](https://mariadb.org/sea-lion). Since I wasn’t able to find a sea lion emoji, [I went for a seal](https://gist.github.com/alejandro-du/d3ba8788651ca7728797593d4d447bc2) 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):

```Java
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](https://vladmihalcea.com/jdbc-driver-connection-url-strings) online and in the official documentation of the JDBC drivers.

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

```Java
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:

```Java
    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:

```Java
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:

![JDBC Demo Output](https://dz2cdn1.dzone.com/storage/temp/15510536-3-jdbc-output.png)

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

```Java
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.