PreparedStatement, ResultSet & Connection Pooling in Java — Advanced Java Episode 20 (Updated June 2026) (Updated June 2026)

Here's a mistake I see constantly from Java developers who learned the basics but skipped the details: they use Statement instead of PreparedStatement, forget to close their connections, and then wonder why their application is slow and vulnerable. Episode 20 of our Advanced Java series dives into the three things that separate a working JDBC implementation from a production-ready one — PreparedStatement with proper parameterisation, correct ResultSet iteration patterns, and connection pooling with HikariCP. With NASSCOM-Deloitte projecting 1.25 million tech professionals needed in India by 2027, Java developers who write secure and scalable data access code command significantly better offers at companies like TCS, Infosys, and Wipro.

PreparedStatement — Parameterised SQL That Prevents Injection

PreparedStatement is the correct way to execute any SQL query that includes variable data. Instead of building SQL strings by concatenating user input (which creates SQL injection vulnerabilities), PreparedStatement uses ? placeholders that the JDBC driver sends to the database engine as separate, typed parameters — never as executable SQL code. This means even if a user types a malicious SQL fragment into a form field, it's treated as a plain string value, not code. Beyond security, PreparedStatement pre-compiles the SQL on the database side, allowing the database engine to reuse the query execution plan on subsequent calls — typically 20–40% faster than Statement for repeated queries, which matters enormously in high-traffic applications at companies like TCS and Infosys.

Binding Parameters and Executing PreparedStatements

Binding parameters to a PreparedStatement uses typed setter methods: ps.setString(1, username), ps.setInt(2, userId), ps.setDate(3, sqlDate). The first argument is the parameter index (1-based, matching the ? placeholders in order), the second is the value. For null values, use ps.setNull(index, Types.VARCHAR) — don't pass a Java null directly. Once all parameters are bound, call ps.executeQuery() for SELECT statements (returns a ResultSet) or ps.executeUpdate() for INSERT, UPDATE, and DELETE (returns the number of rows affected). You can reuse a PreparedStatement by calling ps.clearParameters(), rebinding new values, and executing again — the query is only compiled once on the database side.

JDBC API	SQL Operation	Execute Method	Returns
PreparedStatement	SELECT	executeQuery()	ResultSet
PreparedStatement	INSERT / UPDATE / DELETE	executeUpdate()	int (rows affected)
PreparedStatement	Any	execute()	boolean
CallableStatement	Stored procedure	execute()	boolean + OUT params

ResultSet Iteration — Reading Query Results Correctly

A ResultSet is a cursor positioned before the first row of the query result. Call rs.next() to advance the cursor — it returns true if there's a row to read and false when the results are exhausted. Inside the while loop, retrieve values with rs.getString("column_name"), rs.getInt("column_name"), rs.getDouble("amount"), etc. Using column names instead of numeric indices makes your code resistant to SQL query changes that reorder columns. Always check rs.wasNull() after retrieving a numeric value that could be NULL in the database — JDBC returns 0 for null integers by default, which is indistinguishable from a legitimate zero without the wasNull() check. This is a subtle bug that only surfaces with real data.

try-with-resources — Closing JDBC Resources Safely

Java 7's try-with-resources syntax is the correct way to manage JDBC resources. Declare Connection, PreparedStatement, and ResultSet in the try parentheses: try (Connection conn = pool.getConnection(); PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery()) { ... }. Java automatically calls close() on each resource when the try block exits, whether normally or via an exception — eliminating an entire class of resource leak bugs. Closing order matters: ResultSet closes first, then Statement, then Connection. try-with-resources handles this automatically since resources are closed in reverse declaration order. Without this pattern, a single uncaught exception in your data access code can permanently consume a database connection from your pool.

Connection Pooling with HikariCP — Scaling Java Database Access

A connection pool maintains a set of pre-opened database connections and loans them out to application threads on demand, returning them to the pool when released. HikariCP is the fastest, most reliable connection pool for Java applications — it's the default in Spring Boot and widely used at enterprise shops. Configure it with a maximum pool size (typically 10–20 connections per application instance), a connection timeout, and idle connection eviction. The performance difference between pooled and non-pooled connections on a high-traffic application is dramatic — without pooling, each database call waits 20–100ms just to open a connection; with pooling, that overhead drops to microseconds. For Java developers at companies in Pune's Wagholi corridor or Sambhajinagar CIDCO building SaaS applications, connection pooling is non-negotiable production hygiene.

Maharashtra's CMYKPY (Chief Minister Yuva Karya Prashikshan Yojana) offers apprenticeship stipends of ₹6,000–₹10,000 per month for IT students learning Java, database connectivity, and web development. ABC Trainings assists eligible candidates in enrolling and connects them with IT companies in Pune, Sambhajinagar, and Sangli that hire CMYKPY apprentices.

FAQs

Why should I always use PreparedStatement instead of Statement in Java?

PreparedStatement prevents SQL injection by sending SQL and parameters separately to the database — user input is never treated as executable code. It also pre-compiles the query, making repeated executions faster. Statement concatenates user input directly into SQL strings, creating injection vulnerabilities. Any reputable Java shop will reject Statement usage with user input in a code review. Always use PreparedStatement for any query that includes variable data.

How do I properly iterate through a ResultSet in Java?

Declare a ResultSet, then loop with while (rs.next()). Each call to rs.next() advances the cursor to the next row and returns true; it returns false when there are no more rows. Inside the loop, retrieve values using typed getters by column name: rs.getString("username"), rs.getInt("age"), rs.getDouble("salary"). Always use try-with-resources to ensure the ResultSet is closed automatically when done, even if an exception occurs mid-iteration.

What happens if I don't close a JDBC Connection?

If you don't close a JDBC Connection, it remains open on both the Java side and the database server side, consuming resources indefinitely. If your application uses a connection pool, unclosed connections are never returned to the pool — the pool exhausts its available connections, new requests start timing out, and the application becomes unresponsive. This is one of the most common production outage causes in Java web applications. Always use try-with-resources or call close() in a finally block.

What is HikariCP and why is it the best connection pool for Java?

HikariCP is a high-performance JDBC connection pool that's the default in Spring Boot. It's significantly faster than alternatives like Apache DBCP and C3P0 because of its minimal lock overhead and optimised byte-code. It handles connection validation, eviction of stale connections, and leak detection out of the box. Configure maximum pool size (typically 10–20), connection timeout, and idle timeout. For any Java application serving more than a few concurrent users, HikariCP is the standard choice.