JDBC, PreparedStatement, pooling

Why Raw JDBC Still Breaks Production

In production systems, database access is rarely the bottleneck — misusing it is. Teams still open raw connections per request, concatenate SQL strings, forget to close resources, and then blame the database when latency explodes. Common production symptoms: - CPU spikes on DB server - Too many connections errors - Random slow queries - Intermittent timeouts under load This is rarely a database problem. It is usually bad JDBC usage.

Incident Scenario

A service running at 400 RPS suddenly fails during traffic spike. Error: Too many connections Root cause: Each request created a new DriverManager connection. Connections were never pooled. Under load, DB max_connections was exhausted. This is not scaling. This is resource abuse.

Anti-Pattern: Raw Statement + String Concatenation

Connection conn = DriverManager.getConnection(url, user, pass);
Statement stmt = conn.createStatement();

String sql = "SELECT * FROM users WHERE email =  + email + ";
ResultSet rs = stmt.executeQuery(sql);

Why this is dangerous: - SQL injection risk - No statement reuse - No connection pooling - No timeout configuration - Manual lifecycle management In production, this WILL break.

Correct Pattern: PreparedStatement

String sql = "SELECT id, email FROM users WHERE email = ?";
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setString(1, email);
    ps.setQueryTimeout(5);

    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            return rs.getLong("id");
        }
    }
}

Why this is production-safe: - Parameter binding prevents injection - Query plan reuse - Automatic resource closing - Timeout defined

Connection Pooling: Non-Negotiable

Opening a DB connection costs: - TCP handshake - Authentication - Thread allocation - DB memory allocation Without pooling, you are doing this per request. Use HikariCP. It is fast and stable. Example configuration:

HikariConfig config = new HikariConfig();
config.setJdbcUrl(url);
config.setUsername(user);
config.setPassword(pass);
config.setMaximumPoolSize(20);
config.setConnectionTimeout(3000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);

HikariDataSource dataSource = new HikariDataSource(config);

Production notes: - MaximumPoolSize must match DB capacity - Too large pool = DB thrash - Too small pool = thread blocking - Monitor pool usage metrics

Timeouts Are Mandatory

If you do not set timeouts: - Slow query can block thread forever - Thread pool saturates - Service becomes unresponsive You must define: - Connection timeout - Query timeout - Socket timeout (driver level) No timeout = hidden outage.

Isolation Awareness Even in JDBC

Default isolation is usually READ_COMMITTED. Never assume. Always log or explicitly configure:

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

Wrong isolation level can: - Create phantom reads - Cause inconsistent financial data - Break reporting logic

Performance Considerations

- Fetch only required columns - Always use indexes - Avoid SELECT * - Use batch updates for bulk inserts - Monitor slow query log Example batch:

String sql = "INSERT INTO events(type, payload) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (Event e : events) {
        ps.setString(1, e.getType());
        ps.setString(2, e.getPayload());
        ps.addBatch();
    }
    ps.executeBatch();
}

Checklist

- Use DataSource, never DriverManager directly in production - Use PreparedStatement only - Always close resources via try-with-resources - Configure connection pool properly - Define query and connection timeouts - Monitor pool metrics - Avoid SELECT * - Validate isolation level If your JDBC layer is naive, scaling will not fix it. It will just fail faster.