Java Database Connection
A Java database connection is an open session between your program and a database, represented by a java.sql.Connection object. Every SQL statement you run travels over a connection, so opening it correctly, configuring it and closing it on time are the foundations of all JDBC code.
In this lesson you will open connections with DriverManager, write connection URLs for popular databases, pass settings with Properties, control timeouts, keep passwords out of the code, use a DataSource, and build a reusable connection helper that retries when the database is briefly unavailable.
How to Open a Java Database Connection
DriverManager.getConnection() is the standard way to open a connection. It has three versions:
| Method | When to use it |
|---|---|
getConnection(String url) | User and password are inside the URL, or not needed |
getConnection(String url, String user, String password) | The most common form |
getConnection(String url, Properties info) | You need to pass several settings, including user and password |
All three return a Connection or throw SQLException. The examples use the H2 driver, which you added in Java JDBC Setup.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class ThreeWaysToConnect {
public static void main(String[] args) throws SQLException {
// 1. URL only (H2 accepts the user and password as URL settings)
try (Connection first = DriverManager.getConnection("jdbc:h2:mem:demo;USER=sa;PASSWORD=")) {
System.out.println("URL only: connected as " + first.getMetaData().getUserName());
}
// 2. URL + user + password
try (Connection second = DriverManager.getConnection("jdbc:h2:mem:demo", "sa", "")) {
System.out.println("URL, user, pass: connected as " + second.getMetaData().getUserName());
}
// 3. URL + Properties
Properties settings = new Properties();
settings.setProperty("user", "sa");
settings.setProperty("password", "");
try (Connection third = DriverManager.getConnection("jdbc:h2:mem:demo", settings)) {
System.out.println("URL + Properties: connected as " + third.getMetaData().getUserName());
}
}
}
Output:
URL only: connected as SA
URL, user, pass: connected as SA
URL + Properties: connected as SA
How it works:
- All three calls reach the same in-memory database named
demo. - In the
Propertiesversion, the keysuserandpasswordare standard JDBC names that every driver understands. - H2 stores unquoted names in upper case, so the user name comes back as
SA. Other databases keep the case you typed.
JDBC Connection URL Formats
The URL is different for every database. It always starts with jdbc:, then the driver name, then the location of the database and optional settings.
| Database | URL format | Example |
|---|---|---|
| MySQL | jdbc:mysql://host:port/database?options | jdbc:mysql://localhost:3306/shop |
| PostgreSQL | jdbc:postgresql://host:port/database?options | jdbc:postgresql://localhost:5432/shop |
| H2 in memory | jdbc:h2:mem:name;options | jdbc:h2:mem:shop |
| H2 file | jdbc:h2:path;options | jdbc:h2:./data/shop |
| SQLite | jdbc:sqlite:file | jdbc:sqlite:shop.db |
| SQL Server | jdbc:sqlserver://host:port;databaseName=name | jdbc:sqlserver://localhost:1433;databaseName=shop |
| Oracle | jdbc:oracle:thin:@//host:port/service | jdbc:oracle:thin:@//localhost:1521/FREEPDB1 |
Common Connection URL Options
Options are added after ? and separated with & for MySQL and PostgreSQL, or after ; for H2.
| Database | Option | Meaning |
|---|---|---|
| MySQL | connectTimeout=5000 | Give up connecting after 5000 milliseconds |
| MySQL | sslMode=REQUIRED | Refuse to connect without TLS encryption |
| MySQL | serverTimezone=Asia/Kolkata | Time zone used to convert dates and times |
| PostgreSQL | connectTimeout=5 | Give up connecting after 5 seconds |
| PostgreSQL | sslmode=require | Require an encrypted connection |
| H2 | DB_CLOSE_DELAY=-1 | Keep an in-memory database alive until the JVM exits |
| H2 | MODE=MySQL | Accept MySQL-style SQL (useful for tests) |
A complete MySQL URL with options looks like this:
jdbc:mysql://db.example.com:3306/shop?sslMode=REQUIRED&connectTimeout=5000&serverTimezone=Asia/Kolkata
Note: The same word can mean different units in different drivers. MySQL's
connectTimeoutis in milliseconds, PostgreSQL's is in seconds. Always check the driver's documentation.
Connection Lifetime: In-Memory Databases and DB_CLOSE_DELAY
A connection's lifetime can affect your data. An H2 in-memory database exists only while at least one connection to it is open. When the last connection closes, the database and all its tables are deleted. This example shows the difference the DB_CLOSE_DELAY=-1 option makes.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnectionLifetime {
static void createTable(String url) throws SQLException {
try (Connection connection = DriverManager.getConnection(url, "sa", "");
Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE notes (text VARCHAR(50))");
statement.executeUpdate("INSERT INTO notes VALUES ('remember me')");
} // the connection closes here
}
static void readTable(String url) {
try (Connection connection = DriverManager.getConnection(url, "sa", "");
Statement statement = connection.createStatement();
ResultSet rows = statement.executeQuery("SELECT text FROM notes")) {
rows.next();
System.out.println(" Found note: " + rows.getString("text"));
} catch (SQLException e) {
System.out.println(" Table is gone: the database was deleted with its last connection");
}
}
public static void main(String[] args) throws SQLException {
String shortLived = "jdbc:h2:mem:temp";
String longLived = "jdbc:h2:mem:kept;DB_CLOSE_DELAY=-1";
System.out.println("Without DB_CLOSE_DELAY:");
createTable(shortLived);
readTable(shortLived);
System.out.println("With DB_CLOSE_DELAY=-1:");
createTable(longLived);
readTable(longLived);
}
}
Output:
Without DB_CLOSE_DELAY:
Table is gone: the database was deleted with its last connection
With DB_CLOSE_DELAY=-1:
Found note: remember me
How it works:
createTable()opens a connection, creates and fills a table, then closes the connection.- For
jdbc:h2:mem:temp, that was the only connection, so H2 deletes the database.readTable()then opens a brand-new, empty database and theSELECTfails. - For
jdbc:h2:mem:kept;DB_CLOSE_DELAY=-1, H2 keeps the database until the program ends, so the second connection finds the row. - Server databases such as MySQL and PostgreSQL keep their data on disk, so closing connections never deletes anything there.
Connection Timeouts and Checking a Connection
A connection attempt to a server that is down or blocked by a firewall can hang for a long time. JDBC gives you two standard tools:
| Method | Purpose |
|---|---|
DriverManager.setLoginTimeout(seconds) | Maximum time to wait while opening a connection (applies to all drivers that support it) |
connection.isValid(seconds) | Asks the driver to check that an open connection still works |
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class TimeoutsAndValidity {
public static void main(String[] args) throws SQLException {
DriverManager.setLoginTimeout(5); // wait at most 5 seconds when connecting
System.out.println("Login timeout: " + DriverManager.getLoginTimeout() + " s");
Connection connection = DriverManager.getConnection("jdbc:h2:mem:check", "sa", "");
System.out.println("Valid while open: " + connection.isValid(2));
connection.close();
System.out.println("Valid after close: " + connection.isValid(2));
System.out.println("isClosed(): " + connection.isClosed());
}
}
Output:
Login timeout: 5 s
Valid while open: true
Valid after close: false
isClosed(): true
How it works:
setLoginTimeout(5)is a global setting. Every latergetConnection()call waits at most 5 seconds for the database to respond.isValid(2)returnstrueif the connection works, waiting at most 2 seconds for the answer. It never throws for a broken connection; it returnsfalse.- After
close(),isValid()returnsfalseandisClosed()returnstrue.
Note:
isClosed()only tells you whether you closed the connection. A connection can still be broken by a network failure whileisClosed()isfalse. UseisValid()to test that it really works.
Keep Database Passwords Out of Your Code
A password typed into Java source ends up in Git history, build artifacts and screenshots. Read connection details from environment variables (or a secrets manager) instead, and fail with a clear message when one is missing.
import java.util.Map;
public class ConnectionSettings {
record DbSettings(String url, String user, String password) {
@Override
public String toString() { // never print the real password
return "DbSettings[url=" + url + ", user=" + user + ", password=****]";
}
}
static String require(Map<String, String> env, String name) {
String value = env.get(name);
if (value == null || value.isBlank()) {
throw new IllegalStateException("Missing environment variable " + name);
}
return value;
}
static DbSettings fromEnvironment(Map<String, String> env) {
return new DbSettings(require(env, "DB_URL"), require(env, "DB_USER"), require(env, "DB_PASSWORD"));
}
public static void main(String[] args) {
// In a real program: fromEnvironment(System.getenv())
Map<String, String> production = Map.of(
"DB_URL", "jdbc:mysql://db.internal:3306/shop",
"DB_USER", "shop_app",
"DB_PASSWORD", "s3cr3t-value");
System.out.println(fromEnvironment(production));
Map<String, String> broken = Map.of("DB_URL", "jdbc:mysql://db.internal:3306/shop");
try {
fromEnvironment(broken);
} catch (IllegalStateException e) {
System.out.println("Startup failed: " + e.getMessage());
}
}
}
Output:
DbSettings[url=jdbc:mysql://db.internal:3306/shop, user=shop_app, password=****]
Startup failed: Missing environment variable DB_USER
How it works:
fromEnvironment()takes aMap, so the real program passesSystem.getenv()while the example passes test maps.require()throws immediately when a variable is missing. Failing at startup with a clear message is far better than a confusingSQLExceptionlater.- The record overrides
toString()so logging the settings never reveals the password.
Using a DataSource Instead of DriverManager
javax.sql.DataSource is an interface for an object that hands out connections. You configure it once, then call getConnection() with no arguments. Application servers, Spring and connection pools all work with DataSource, so code written against it is easy to move to a pool later.
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.h2.jdbcx.JdbcDataSource;
public class DataSourceExample {
static DataSource createDataSource() {
JdbcDataSource dataSource = new JdbcDataSource(); // H2's DataSource implementation
dataSource.setURL("jdbc:h2:mem:pooled;DB_CLOSE_DELAY=-1");
dataSource.setUser("sa");
dataSource.setPassword("");
return dataSource;
}
static void printSchema(DataSource dataSource) throws SQLException {
try (Connection connection = dataSource.getConnection()) { // no URL or password here
System.out.println("Connected to schema " + connection.getSchema());
}
}
public static void main(String[] args) throws SQLException {
DataSource dataSource = createDataSource();
printSchema(dataSource);
printSchema(dataSource);
}
}
Output:
Connected to schema PUBLIC
Connected to schema PUBLIC
How it works:
createDataSource()is the only place that knows the URL and credentials. The rest of the code just receives aDataSource.printSchema()depends only on thejavax.sql.DataSourceinterface, so you can later pass a pooledDataSource(such as HikariCP) without changing it.getSchema()returns the current schema, which isPUBLICby default in H2.
DriverManager | DataSource | |
|---|---|---|
| Configuration | URL and credentials on every call | Configured once |
| Connection pooling | No | Yes, with a pooling implementation |
| Used by frameworks | Rarely | Spring, Jakarta EE, connection pools |
| Best for | Small programs, scripts, learning | Applications and servers |
Real-World Example: A Reusable Connection Helper with Retry
Databases restart, containers start in the wrong order and networks have short failures. A production application should retry a connection a few times for connection errors only (SQLState class 08), wait a little longer each time, and give up with a clear error.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Database {
private final String url;
private final String user;
private final String password;
private final int maxAttempts;
private final long firstDelayMillis;
public Database(String url, String user, String password, int maxAttempts, long firstDelayMillis) {
this.url = url;
this.user = user;
this.password = password;
this.maxAttempts = maxAttempts;
this.firstDelayMillis = firstDelayMillis;
}
static boolean isConnectionProblem(SQLException e) {
return e.getSQLState() != null && e.getSQLState().startsWith("08");
}
public Connection connect() throws SQLException {
long delay = firstDelayMillis;
for (int attempt = 1; ; attempt++) {
try {
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("Attempt " + attempt + ": connected");
return connection;
} catch (SQLException e) {
if (!isConnectionProblem(e) || attempt == maxAttempts) {
System.out.println("Attempt " + attempt + ": giving up");
throw e;
}
System.out.println("Attempt " + attempt + ": failed (" + e.getSQLState() + "), retrying in " + delay + " ms");
try {
Thread.sleep(delay);
} catch (InterruptedException interrupted) {
Thread.currentThread().interrupt();
throw new SQLException("Interrupted while waiting to reconnect", "08000", interrupted);
}
delay *= 2; // exponential backoff: 100, 200, 400 ...
}
}
}
public static void main(String[] args) {
Database working = new Database("jdbc:h2:mem:app", "sa", "", 3, 100);
try (Connection connection = working.connect();
Statement statement = connection.createStatement();
ResultSet rows = statement.executeQuery("SELECT 40 + 2")) {
rows.next();
System.out.println("Query result: " + rows.getInt(1));
} catch (SQLException e) {
System.out.println("Error: " + e.getMessage());
}
System.out.println();
Database unreachable = new Database("jdbc:missingdriver://db.internal/app", "app", "secret", 3, 100);
try (Connection connection = unreachable.connect()) {
System.out.println("Unexpected success");
} catch (SQLException e) {
System.out.println("Error: " + e.getMessage());
}
}
}
Output:
Attempt 1: connected
Query result: 42
Attempt 1: failed (08001), retrying in 100 ms
Attempt 2: failed (08001), retrying in 200 ms
Attempt 3: giving up
Error: No suitable driver found for jdbc:missingdriver://db.internal/app
How it works:
- One place for connection details. The
Databaseclass stores the URL, credentials and retry policy. The rest of the application only callsconnect(). - Retry only connection errors.
isConnectionProblem()checks for SQLState class08. A wrong password (class28) or bad SQL (class42) will not fix itself, so those fail immediately. - Exponential backoff. The wait doubles after each failure (100 ms, then 200 ms), giving a restarting database time to come up without flooding it with attempts.
- A clear limit. After
maxAttemptsthe original exception is re-thrown, so the caller sees the real cause. - Interrupt-safe waiting. If the thread is interrupted during
Thread.sleep(), the helper restores the interrupt flag and stops retrying. - The caller still closes the connection.
connect()returns a freshConnection, andmain()closes it with try-with-resources.
The second run uses a URL that no driver accepts, which produces SQLState 08001 every time, so you can watch the retry logic without a real outage. Next, Java JDBC Connection explores the methods of the Connection object itself, and Java JDBC Basics recaps how connections fit into the whole JDBC workflow.