DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • Keep Your Application Secrets Secret
  • Getting Started With JPA/Hibernate
  • Realistic Test Data Generation for Java Apps

Trending

  • Rethinking Recruitment: A Journey Through Hiring Practices
  • From Zero to Production: Best Practices for Scaling LLMs in the Enterprise
  • AI’s Role in Everyday Development
  • Performance Optimization Techniques for Snowflake on AWS
  1. DZone
  2. Data Engineering
  3. Databases
  4. JDBC Tutorial Part 1: Connecting to a Database

JDBC Tutorial Part 1: Connecting to a Database

In this series, learn the basics of Java Database Connectivity. In Part 1, learn what a JDBC driver is and how to open and safely close database connections.

By 
Alejandro Duarte user avatar
Alejandro Duarte
DZone Core CORE ·
Updated Jan. 06, 22 · Tutorial
Likes (8)
Comment
Save
Tweet
Share
36.5K Views

Join the DZone community and get the full member experience.

Join For Free

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:

Plain Text
 
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:

MariaDB 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: 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
 
<?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:

JDBC Demo

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

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):

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 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

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. 

Database connection Java (programming language) Java Database Connectivity Driver (software) application MariaDB

Opinions expressed by DZone contributors are their own.

Related

  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • Keep Your Application Secrets Secret
  • Getting Started With JPA/Hibernate
  • Realistic Test Data Generation for Java Apps

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!