JDBC in Java
JDBC (Java Database Connectivity) is the standard API for connecting Java applications to relational databases. It provides a uniform interface that works ...
Connecting to Databases with JDBC
JDBC (Java Database Connectivity) is the standard API for connecting Java applications to relational databases. It provides a uniform interface that works with any database that has a JDBC driver, including MySQL, PostgreSQL, Oracle, and SQLite. The driver translates JDBC API calls into database-specific wire protocol messages, so your application code does not need to change when you switch databases.
The JDBC connection process begins with loading a driver and obtaining a Connection object. Since Java 6, drivers are auto-discovered through the Service Provider Interface, so you no longer need to call Class.forName() explicitly. Instead, call DriverManager.getConnection() with the JDBC URL and credentials:
String url = "jdbc:mysql://localhost:3306/mydb"; Connection conn = DriverManager.getConnection(url, "user", "password");
The JDBC URL format is driver-specific. For PostgreSQL it is jdbc:postgresql://host/db, for SQLite it is jdbc:sqlite:/path/to/file.db. Always store credentials in environment variables or a secrets manager rather than hardcoding them in source code.
Connection objects are expensive to create because they involve network handshakes and authentication. Production applications use connection pools such as HikariCP or Apache DBCP to maintain a pool of ready connections and loan them to application code on demand:
HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:postgresql://localhost/mydb"); config.setUsername(System.getenv("DB_USER")); config.setPassword(System.getenv("DB_PASS")); config.setMaximumPoolSize(10); HikariDataSource dataSource = new HikariDataSource(config); Connection conn = dataSource.getConnection();
Always close Connection, Statement, and ResultSet objects in finally blocks or use try-with-resources. Failing to close them leaks database connections, which are a limited resource, and can eventually prevent the application from getting new connections.
The Connection object represents a session with the database and maintains transaction state. By default, JDBC operates in auto-commit mode, where each statement is immediately committed. For multi-statement transactions, disable auto-commit and manually commit or rollback.
Interview tip: Explain why connection pooling is essential in a web application. Each HTTP request acquires a connection, executes queries, and releases it. Creating a new physical connection for every request takes 10-100ms. A pool reduces this to sub-millisecond acquisition from a reused connection, making it a critical performance optimization.
Executing Queries and Preventing SQL Injection
Once you have a database connection, you execute SQL using Statement or PreparedStatement objects. Choosing the right one is not just a performance decision; it is a critical security decision. Using raw Statement with string concatenation to build SQL is one of the most dangerous things you can do in a Java application.
SQL injection occurs when user-supplied data is concatenated directly into a SQL string. An attacker can supply input that changes the SQL's meaning, bypassing authentication, extracting sensitive data, or destroying tables. For example:
// DANGEROUS - do not do this String sql = "SELECT * FROM users WHERE name = '" + userInput + "'"; Statement stmt = conn.createStatement();
If userInput is O'Brien, the SQL breaks. If it is ' OR '1'='1, the WHERE clause becomes always true and returns all rows.
PreparedStatement prevents SQL injection by separating the SQL template from the data. The database compiles the query with placeholders (?), then the data is bound separately and is never interpreted as SQL:
String sql = "SELECT * FROM users WHERE name = ?"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, userInput); ResultSet rs = pstmt.executeQuery();
PreparedStatements also improve performance when the same query is executed repeatedly, because the database can cache the execution plan after the first compilation.
Reading results from a ResultSet is straightforward. Call next() to advance to the next row and use typed getters to retrieve column values:
while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println(id + ": " + name); }
Always iterate to completion or close the ResultSet explicitly. Open ResultSets hold a cursor on the server side, consuming resources. Use try-with-resources to ensure all JDBC objects are closed even if an exception occurs.
Interview tip: When asked to differentiate Statement from PreparedStatement, lead with security before performance. Emphasize that SQL injection is one of the most common and severe web vulnerabilities, and PreparedStatement completely eliminates it for parameterized queries.
Transactions and Batch Operations
Database transactions allow you to group multiple SQL statements into an atomic unit of work. Either all statements succeed and the changes are committed, or any failure causes all changes to be rolled back, leaving the database in its original state. This is essential for maintaining data integrity in operations that span multiple tables or rows.
To use transactions in JDBC, disable auto-commit mode, execute your statements, and then commit or rollback explicitly:
conn.setAutoCommit(false); try { PreparedStatement debit = conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?"); PreparedStatement credit = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?"); debit.setInt(1, 100); debit.setInt(2, fromId); debit.executeUpdate(); credit.setInt(1, 100); credit.setInt(2, toId); credit.executeUpdate(); conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; } finally { conn.setAutoCommit(true); }
JDBC supports savepoints within a transaction, allowing you to roll back to a specific point rather than the entire transaction. This is useful for long transactions with optional steps that you want to undo independently.
Batch operations dramatically improve performance when you need to execute the same statement many times with different parameters, such as inserting thousands of rows. Instead of sending each statement to the database individually, you add them to a batch and execute the whole batch in one network round trip:
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO logs (message) VALUES (?)"); for (String msg : messages) { pstmt.setString(1, msg); pstmt.addBatch(); } int[] results = pstmt.executeBatch();
The executeBatch() return array contains the row count affected by each statement. Batch sizes of 100 to 1000 rows typically provide the best performance trade-off between network overhead and database transaction log pressure.
Transaction isolation levels control how much one transaction can see of another's uncommitted changes. The four standard levels are READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE. Higher isolation levels prevent more concurrency anomalies (dirty reads, non-repeatable reads, phantom reads) but reduce concurrency. Most applications use READ_COMMITTED, which is also the default in PostgreSQL.
Interview tip: Explain what a deadlock looks like in a database context and how it differs from a Java thread deadlock. In a database, two transactions each hold a lock that the other needs, causing neither to progress. The database detects this cycle and automatically kills one transaction, returning an error to the application. The application should catch this error and retry the transaction.