How to connect to a database with JDBC

Short answer

Add the database driver to the project and open a connection with DriverManager.getConnection() inside try with resources. Here it is an in-memory SQLite database; run it with java --enable-native-access=ALL-UNNAMED -cp sqlite-jdbc-3.53.4.0.jar Main.java:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        IO.println("Connected to " + conn.getMetaData().getDatabaseProductName());
    }
}

Output:

Connected to SQLite

The driver

The driver is a separate library for each database system. In Maven it is added as a dependency, for example for PostgreSQL:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.4</version>
</dependency>

For MySQL it is com.mysql:mysql-connector-j, for SQLite org.xerial:sqlite-jdbc. The driver registers itself; there is no need to call Class.forName().

URL, user and password

The URL starts with jdbc: and the database name. Take the password from an environment variable rather than writing it in the code:

// src/main/java/shop/Database.java
package shop;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class Database {
    private static final String URL = "jdbc:postgresql://localhost:5432/shop";
    // MySQL:  jdbc:mysql://localhost:3306/shop
    // SQLite: jdbc:sqlite:shop.db
 
    public static Connection connect() throws SQLException {
        return DriverManager.getConnection(URL, "app", System.getenv("DB_PASSWORD"));
    }
}

The first query

Create a Statement from the connection and run SQL:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
         Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
        st.executeUpdate("INSERT INTO users (name) VALUES ('Anna'), ('Oleg')");
 
        try (ResultSet rs = st.executeQuery("SELECT id, name FROM users")) {
            while (rs.next()) {
                IO.println(rs.getInt("id") + " " + rs.getString("name"));
            }
        }
    }
}

Output:

1 Anna
2 Oleg

Values from user input are never concatenated into SQL — use a PreparedStatement for them.

Common mistake

Forgetting to add the driver. The program compiles but fails when connecting:

import java.sql.*;
 
void main() throws SQLException {
    DriverManager.getConnection("jdbc:postgresql://localhost:5432/shop", "app", "secret");
}

Output:

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:postgresql://localhost:5432/shop

Another mistake is not closing connections. Each one holds database resources, and after a while new connections stop opening. Web applications use a connection pool such as HikariCP.

Thanks for reading!

Short answer

The driver

URL, user and password

The first query

Common mistake

Functions used in this recipe

Thanks for reading!