How do I fetch results with ResultSet?

By I Wayan Saryada in JDBC 0 Comment

To fetch results using a ResultSet in Java JDBC, you follow a standard pattern of executing a query, iterating through the rows, and extracting data using “getter” methods.

Basic Fetching Pattern

  1. Execute the Query: Use stmt.executeQuery(sql) (for Statement) or pstmt.executeQuery() (for PreparedStatement).
  2. Iterate through Rows: Use a while (rs.next()) loop. The next() method moves the cursor to the next row and returns false when there are no more rows.
  3. Retrieve Values: Use methods like getString(), getInt(), getLong(), or getBigDecimal() based on the column type.

Example

try (Connection conn = DriverManager.getConnection(url, user, pass);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT id, name, created_at FROM users")) {

    while (rs.next()) {
        // 1. Fetch by Column Name (Recommended for readability)
        int id = rs.getInt("id");
        String name = rs.getString("name");

        // 2. Fetch by Column Index (1-based, often faster)
        java.sql.Timestamp date = rs.getTimestamp(3);

        System.out.println("User: " + id + " | Name: " + name);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Key Considerations

  • 1-Based Indexing: If you use column indices (e.g., rs.getString(1)), remember that JDBC indices start at 1, not 0.
  • Column Names vs. Indices:
    • Names: More maintainable and easier to read.
    • Indices: Slightly better performance as the driver doesn’t have to look up the column mapping.
  • Try-with-Resources: Always wrap the ResultSet in a try-with-resources block (or ensure it is closed in a finally block) to prevent database cursor leaks.
  • Data Types: Use the getter that matches your database schema:
    • rs.getInt() for INTEGER
    • rs.getString() for VARCHAR/TEXT
    • rs.getBigDecimal() for DECIMAL/NUMERIC (best for currency)
    • rs.getBoolean() for BIT/BOOLEAN
  • Handling Nulls: If a column allows NULL, primitive getters (like getInt) will return 0. To check if the value was actually null in the database, call rs.wasNull() immediately after the getter.

How do I use CallableStatement for stored procedures?

By I Wayan Saryada in JDBC 0 Comment

Using a CallableStatement in JDBC is the standard way to execute stored procedures. It allows you to handle input parameters (IN), output parameters (OUT), and even result sets returned by the database.

Here is a guide on how to use it for different scenarios.

1. Basic Syntax

The syntax for calling a stored procedure uses the escape sequence {call procedure_name(?, ?)}.

// Standard format for calling a procedure with parameters
String query = "{call get_user_details(?, ?)}";
CallableStatement stmt = connection.prepareCall(query);

2. Working with Input Parameters (IN)

If your procedure requires data, you set them just like a PreparedStatement using index-based setters (starting at 1).

try (CallableStatement stmt = conn.prepareCall("{call get_product_by_id(?)}")) {
    stmt.setInt(1, 101); // Set the first parameter

    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.println("Product: " + rs.getString("name"));
        }
    }
}

3. Working with Output Parameters (OUT)

If the procedure returns a value via an OUT parameter, you must register its SQL type before execution and retrieve it afterward.

String sql = "{call get_employee_count_by_dept(?, ?)}";
try (CallableStatement stmt = conn.prepareCall(sql)) {
    // 1. Set the IN parameter
    stmt.setString(1, "Engineering");

    // 2. Register the OUT parameter type
    stmt.registerOutParameter(2, java.sql.Types.INTEGER);

    // 3. Execute
    stmt.execute();

    // 4. Retrieve the value from the OUT parameter
    int count = stmt.getInt(2);
    System.out.println("Total employees: " + count);
}

4. Handling Stored Functions

If you are calling a database function that returns a value directly, the syntax changes slightly to include a return placeholder at the beginning.

// The first '?' is the return value
String sql = "{? = call calculate_tax(?)}";
try (CallableStatement stmt = conn.prepareCall(sql)) {
    stmt.registerOutParameter(1, java.sql.Types.DOUBLE);
    stmt.setDouble(2, 5000.00);

    stmt.execute();
    double tax = stmt.getDouble(1);
    System.out.println("Tax: " + tax);
}

Key Best Practices:

  • Try-with-resources: Always wrap your Connection and CallableStatement in a try-with-resources block to ensure they are closed automatically.
  • Parameter Indexing: Remember that JDBC parameters are 1-indexed.
  • SQL Types: Use java.sql.Types when registering OUT parameters to match your database schema.
  • Performance: CallableStatement objects are often pre-compiled by the database driver, making them more efficient than raw SQL strings for repeated calls.

How do I use PreparedStatement to prevent SQL injection?

By I Wayan Saryada in JDBC 0 Comment

Using PreparedStatement is one of the most effective ways to prevent SQL injection in Java. It works by separating the SQL query structure from the data, ensuring that user input is treated strictly as data and never as part of the executable SQL command.

Here is how you use it:

1. The Key Concept: Placeholders

Instead of concatenating strings (which is where the danger lies), you use a question mark (?) as a placeholder for every dynamic value.

2. Implementation Example

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SecureQueryExample {
    public void getUserDetails(String username) {
        // 1. Define SQL with placeholders (?)
        String sql = "SELECT id, email, status FROM users WHERE username = ?";

        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "user", "pass");
             // 2. Prepare the statement
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 3. Bind the values (index starts at 1)
            pstmt.setString(1, username);

            // 4. Execute the query
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.println("User ID: " + rs.getInt("id"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Why this prevents SQL Injection

Imagine a malicious user provides this as a “username”: ' OR '1'='1.

  • Vulnerable (String Concatenation):
    SELECT * FROM users WHERE username = '' OR '1'='1' — This changes the logic to return all users.
  • Secure (PreparedStatement):
    The database receives the query structure first. When the input is sent, the database looks literally for a user whose name is the string ' OR '1'='1. Since no such user exists, the attack fails, and the query remains safe.

Best Practices

  • Use setXXX methods: Always use the specific setter for your data type (e.g., setInt(), setString(), setTimestamp()). This adds an extra layer of type validation.
  • Never Concatenate: Even if you use a PreparedStatement, if you build the SQL string using + or StringBuilder before passing it to prepareStatement(), you are still vulnerable.
  • Try-with-resources: As shown above, use try-with-resources to ensure the Connection and PreparedStatement are closed automatically, preventing resource leaks.

How do I execute a simple SQL query with Statement?

By I Wayan Saryada in JDBC 0 Comment

Executing a simple SQL query using a Statement object in JDBC follows a straightforward pattern: establish a connection, create the statement, execute the query, and process the results.

Here is a clean example of how to perform a SELECT query:

Simple SQL Query Example

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SimpleQueryExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "username";
        String password = "password";

        String sql = "SELECT id, username, email FROM users";

        // Use try-with-resources to ensure resources are closed automatically
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // Iterate through the result set
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("username");
                String email = rs.getString("email");

                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Key Methods to Know

The Statement interface provides different methods depending on the type of SQL you are running:

  1. executeQuery(String sql): Used for SELECT statements. It returns a ResultSet containing the data.
  2. executeUpdate(String sql): Used for INSERT, UPDATE, or DELETE statements. It returns an int representing the number of rows affected.
  3. execute(String sql): A general-purpose method that can execute any SQL statement. It returns true if the result is a ResultSet (query) and false if it is an update count or there are no results.

Important Tips

  • Try-with-Resources: Always use the try-with-resources block (shown above) for Connection, Statement, and ResultSet. This prevents memory leaks by ensuring the database handles are closed even if an exception occurs.
  • Security: While Statement is great for simple or static queries, use PreparedStatement if your query includes variables provided by a user. This prevents SQL Injection attacks.
  • Indices vs. Names: When reading from a ResultSet, you can use column names (e.g., rs.getString("username")) or 1-based indices (e.g., rs.getString(1)). Names are generally more readable and maintainable.

How do I close JDBC resources properly?

By I Wayan Saryada in JDBC 0 Comment

Properly closing JDBC resources is crucial to prevent memory leaks and database connection exhaustion. In modern Java, the absolute best way to do this is by using the try-with-resources statement.

The Best Practice: Try-with-Resources

Introduced in Java 7, this approach automatically closes any resource that implements java.lang.AutoCloseable (which Connection, Statement, and ResultSet all do) at the end of the block, even if an exception occurs.

You should declare your resources in the parentheses of the try block in the order they are created:

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcResourceManagement {
    private static final String URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "user";
    private static final String PASSWORD = "password";

    public void fetchData() {
        String query = "SELECT id, name FROM users";

        // Resources are closed in reverse order of their creation
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        // No finally block needed for closing!
    }
}

Why this is the “Proper” Way:

  1. Reverse Closure: Resources are closed in the reverse order of their initialization (ResultSet → Statement → Connection).
  2. Exception Safety: If an exception occurs while closing a resource, it doesn’t prevent the others from closing. If multiple exceptions occur, the primary exception is thrown, and the “close” exceptions are attached as “suppressed” exceptions.
  3. Readability: It eliminates the “pyramid of doom” found in older finally blocks where you had to wrap every .close() call in its own try-catch to handle potential nulls and nested SQLExceptions.

Important Note on Connection Pools

If you are using a DataSource (like HikariCP or Apache Commons DBCP), calling conn.close() inside a try-with-resources block does not actually shut down the physical connection to the database. Instead, it “returns” the connection to the pool so it can be reused by another part of your application. This is why closing resources is just as important when using pools—forgetting to “close” a pooled connection will eventually lead to a “Pool Exhausted” error.