JDBC · Lesson 3
Connecting to a Database
Establishing Database Connections
A database connection is a communication channel between your Java application and the database server. Connections are resource-intensive and must be carefully managed. To establish a connection, you need: the driver (for your specific database), the connection URL (where the database is), and credentials (username/password). Modern applications use connection pools to reuse connections efficiently instead of creating new ones for each operation.
JDBC Connection URLs and Configuration
Each database has a specific JDBC URL format that specifies the driver, host, port, and database name. For example, MySQL uses jdbc:mysql://hostname:port/dbname. The connection URL is crucial — it must match your database setup exactly. You can also configure connection properties like charset, timeouts, and SSL encryption.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionExample {
// Different database connection URLs
public static void demonstrateConnections() {
// MySQL
String mysqlUrl = "jdbc:mysql://localhost:3306/myapp?useSSL=false&serverTimezone=UTC";
String mysqlUser = "root";
String mysqlPassword = "password";
// PostgreSQL
String postgresUrl = "jdbc:postgresql://localhost:5432/myapp";
String postgresUser = "postgres";
String postgresPassword = "password";
// Oracle
String oracleUrl = "jdbc:oracle:thin:@localhost:1521:myapp";
// SQL Server
String sqlServerUrl = "jdbc:sqlserver://localhost:1433;databaseName=myapp";
try {
// MySQL connection example
Class.forName("com.mysql.cj.jdbc.Driver");
Connection mysqlConn = DriverManager.getConnection(mysqlUrl, mysqlUser, mysqlPassword);
System.out.println("Connected to MySQL!");
// PostgreSQL connection example
Class.forName("org.postgresql.Driver");
Connection postgresConn = DriverManager.getConnection(postgresUrl, postgresUser, postgresPassword);
System.out.println("Connected to PostgreSQL!");
// Test connection
if (!mysqlConn.isClosed()) {
System.out.println("MySQL connection is open");
}
// Always close when done
mysqlConn.close();
postgresConn.close();
} catch (ClassNotFoundException e) {
System.err.println("Driver not found: " + e.getMessage());
} catch (SQLException e) {
System.err.println("Connection failed: " + e.getMessage());
}
}
}
Using Connection Pools for Scalability
Creating a new database connection for each request is slow and resource-intensive. Connection pools maintain a set of open connections, reusing them across requests. When you need a connection, you "borrow" one from the pool; when done, you "return" it without closing it. This dramatically improves performance and scalability. Libraries like Apache Commons DBCP and HikariCP provide connection pooling.
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class ConnectionPoolExample {
private static BasicDataSource dataSource;
// Initialize connection pool (call once at app startup)
public static void initializePool() {
dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/myapp?useSSL=false");
dataSource.setUsername("root");
dataSource.setPassword("password");
// Pool configuration
dataSource.setInitialSize(5); // Create 5 connections at startup
dataSource.setMaxTotal(20); // Maximum 20 connections
dataSource.setMaxIdle(10); // Keep up to 10 idle connections
dataSource.setMinIdle(2); // Maintain at least 2 idle
dataSource.setMaxWaitMillis(30000); // Wait up to 30 seconds for connection
}
// Get connection from pool (reuses existing if possible)
public static java.sql.Connection getConnection() throws java.sql.SQLException {
return dataSource.getConnection();
}
public static void example() {
initializePool();
try (java.sql.Connection conn = getConnection()) {
// Use connection for queries
System.out.println("Got connection from pool!");
// Connection is automatically returned to pool when try-with-resources closes
} catch (java.sql.SQLException e) {
System.err.println("Error getting connection: " + e.getMessage());
}
}
// Close pool on application shutdown
public static void closePool() {
try {
if (dataSource != null) {
dataSource.close();
}
} catch (Exception e) {
System.err.println("Error closing pool: " + e.getMessage());
}
}
}
Testing Connections and Handling Connection Failures
Database connections can fail due to network issues, database restarts, or authentication problems. Test connections to verify they work before use. Implement retry logic to handle transient failures. Set timeouts to prevent indefinite hangs if the database is unresponsive.
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
public class ConnectionTesting {
// Test if a connection is valid
public static boolean testConnection(Connection conn) {
try {
// Method 1: Execute a simple query
if (!conn.createStatement().execute("SELECT 1")) {
System.out.println("Connection test passed!");
return true;
}
} catch (SQLException e) {
System.err.println("Connection test failed: " + e.getMessage());
return false;
}
return false;
}
// Get database metadata
public static void getConnectionInfo(Connection conn) {
try {
DatabaseMetaData metadata = conn.getMetaData();
System.out.println("Database: " + metadata.getDatabaseProductName());
System.out.println("Version: " + metadata.getDatabaseProductVersion());
System.out.println("Driver: " + metadata.getDriverName());
System.out.println("Is Read-Only: " + conn.isReadOnly());
System.out.println("Transaction Isolation: " + conn.getTransactionIsolation());
} catch (SQLException e) {
System.err.println("Error getting metadata: " + e.getMessage());
}
}
// Retry connection with exponential backoff
public static Connection connectWithRetry(String url, String user, String pass) {
int maxAttempts = 3;
int delay = 1000; // Start with 1 second
for (int attempt = 1; attempt <= maxAttempts; attempt++) {
try {
Connection conn = java.sql.DriverManager.getConnection(url, user, pass);
System.out.println("Connected on attempt " + attempt);
return conn;
} catch (SQLException e) {
System.err.println("Attempt " + attempt + " failed: " + e.getMessage());
if (attempt < maxAttempts) {
try {
System.out.println("Retrying in " + delay + "ms...");
Thread.sleep(delay);
delay *= 2; // Exponential backoff
} catch (InterruptedException ie) {
Thread.currentThread().interrupt();
break;
}
}
}
}
System.err.println("Failed to connect after " + maxAttempts + " attempts");
return null;
}
}
Connection Configuration and Properties
JDBC connections support various configuration options passed in the connection URL. These control behavior like SSL encryption, charset, connection timeouts, and auto-commit mode. Different databases support different properties — check your driver documentation.
public class ConnectionProperties {
public static void main(String[] args) throws Exception {
// MySQL connection with properties
String url = "jdbc:mysql://localhost:3306/myapp?" +
"useSSL=false&" + // Disable SSL for development
"serverTimezone=UTC&" + // Set timezone
"characterEncoding=utf8mb4&" + // Use UTF-8 encoding
"autoReconnect=true&" + // Reconnect if connection lost
"connectTimeout=10000&" + // 10 second connection timeout
"socketTimeout=30000&" + // 30 second socket timeout
"useCompression=true"; // Use compression
java.sql.Connection conn = java.sql.DriverManager.getConnection(url, "root", "password");
// Check connection properties
java.sql.DatabaseMetaData meta = conn.getMetaData();
System.out.println("Auto-commit: " + conn.getAutoCommit());
System.out.println("Read-only: " + conn.isReadOnly());
System.out.println("Catalog: " + conn.getCatalog());
conn.close();
}
}
| Connection Property | Purpose | Example |
|---|---|---|
| useSSL | Enable/disable SSL encryption | useSSL=true |
| serverTimezone | Database timezone | serverTimezone=UTC |
| characterEncoding | Character set encoding | characterEncoding=utf8mb4 |
| autoReconnect | Automatically reconnect if connection lost | autoReconnect=true |
| connectTimeout | Time to wait for connection (ms) | connectTimeout=10000 |
| socketTimeout | Time to wait for queries (ms) | socketTimeout=30000 |
Common Mistake: Using DriverManager.getConnection() directly in production. This creates new connections for every operation, exhausting database resources. Always use a connection pool (DataSource). Also, never hardcode database credentials in code — use environment variables or configuration files instead. Credentials in source code are a security risk.
Best Practices for Database Connections
- Always use connection pools in production (DataSource, HikariCP, Commons DBCP)
- Set appropriate timeouts (connectTimeout, socketTimeout) to prevent hangs
- Close connections promptly using try-with-resources
- Test connections at startup to catch configuration errors early
- Implement retry logic for transient connection failures
- Monitor pool statistics: active connections, wait times, errors
- Use SSL/TLS encryption for database connections over networks
- Store credentials securely: environment variables, configuration servers, never in code
Real-World Example: Containerized Application Configuration
A containerized Java application reads database configuration from environment variables set by Docker or Kubernetes. It initializes a HikariCP connection pool at startup, setting pool size based on expected load. If the database is initially unavailable, it retries with exponential backoff, allowing containers to start in any order. Connection failures are logged with full context. The connection pool is gracefully shut down on application termination. This approach is resilient, scalable, and follows cloud-native best practices.
Quiz Check