JDBC Tutorial Part 1: How to Connect 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 , , , , or any other for Java. JPA Hibernate MyBatis jOOQ database framework 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. The source code is available on with each part in independent Maven projects. Note: GitHub Here’s a version of this article, in case you want to see the concepts in action: video Creating a Demo Database Before we start, we need a database to play with. I assume you have on your computer or have a account with a database instance running in the Cloud (you can for free). In my case, I have it running on my computer, so I can connect to the server using the client tool using the database user that I created beforehand: installed MariaDB SkySQL create an account mariadb 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 and a table with the name as follows: jdbc_demo programming_language CREATE DATABASE jdbc_demo; USE jdbc_demo; CREATE TABLE programming_language( name VARCHAR(50) NOT NULL UNIQUE, Rating INT ); Use the command to exit the client tool. quit What Is a JDBC Driver? JDBC is an API : 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. specification For example, in the case of (a multi-purpose database), the JDBC driver is . 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 to the 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. MariaDB MariaDB Connector/J dependency pom.xml Creating a New Maven Project Let’s create a new Java application with a simple pom.xml file that includes the MariaDB JDBC driver: 4.0.0 org.example jdbc-demo 1.0-SNAPSHOT 17 17 org.mariadb.jdbc mariadb-java-client 2.7.4 maven-shade-plugin 3.2.4 package shade com.example.Application As you can see, the JDBC driver is called . We have also added the to be able to generate an executable JAR that includes the JDBC driver (Uber JAR). mariadb-java-client Maven Shade Plugin You can place this file in an empty directory and run to download the MariaDB Connector/J dependency and check that the project builds correctly: pom.xml mvn package 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 ( ) 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: main If you are wondering what that little creature is, it’s a seal. MariaDB’s is a . Since I wasn’t able to find a sea lion emoji, instead. Close enough, I guess. logo sea lion I went for a seal Back to the tutorial... Here’s the structure of the Java app (add this into a new file in the directory we created): Application.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 interface or the 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). DataSource DriverManager 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: : The part identifies the kind of database. The 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 online and in the official documentation of the JDBC drivers. connection URLs for all databases 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 to the class. This interface contains all the methods to interact with the database. Second, we used the 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. Connection DriverManager Notice that we added a clause to the method signatures. Most JDBC operations throw an 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. throws SQLException 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 and 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 block and closing the connection in a block. The block always gets executed even if a problem occurs and an exception is thrown. Here’s the final result with the problem fixed: openDatabaseConnection() closeDatabaseConnection() try finally finally 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 to build the final JAR and run it from the command line as follows: mvn clean package 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 object to send SQL statements to the database. connection Also Published Here