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.

How do I use DataSource instead of DriverManager?

By I Wayan Saryada in JNDI 0 Comment

Switching from DriverManager to DataSource is a best practice in modern Java applications because it supports connection pooling, is more configurable, and decouples your code from the specific database driver implementation.

While DriverManager creates a physical connection every time you call getConnection(), a DataSource (specifically a pooling one) maintains a set of open connections that can be reused, significantly improving performance.

1. The DriverManager Approach (Old way)

You are likely used to this pattern:

// Hardcoded driver details and physical connection creation
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/db", "user", "pass");

2. The DataSource Approach (Modern way)

With DataSource, you configure the object once and then use it to get connections throughout your application.

Using Apache Commons DBCP (Connection Pooling)

To use a DataSource with pooling, you can use a library like Apache Commons DBCP’s BasicDataSource.

package org.kodejava.jdbc;

import org.apache.commons.dbcp2.BasicDataSource;
import javax.sql.DataSource;

public class DatabaseConfig {
    private static final BasicDataSource dataSource;

    static {
        dataSource = new BasicDataSource();
        dataSource.setUrl("jdbc:mysql://localhost/musicdb");
        dataSource.setUsername("music");
        dataSource.setPassword("s3cr*t");

        // Optional: Configure pooling parameters
        dataSource.setInitialSize(5);
        dataSource.setMaxTotal(10);
    }

    public static DataSource getDataSource() {
        return dataSource;
    }
}

3. Using the Connection in your Code

Once you have the DataSource instance, getting a connection is consistent regardless of the underlying implementation:

public void fetchData() {
    DataSource ds = DatabaseConfig.getDataSource();

    // The try-with-resources ensures the connection is "closed" 
    // (returned to the pool) automatically.
    try (Connection conn = ds.getConnection()) {
        // Use the connection as usual
        var stmt = conn.prepareStatement("SELECT * FROM record");
        var rs = stmt.executeQuery();
        // ... process results
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Why use DataSource?

  • Connection Pooling: Reusing connections is much faster than opening/closing them for every request.
  • Decoupling: Your business logic only knows about the javax.sql.DataSource interface. You can switch from BasicDataSource to HikariCP (another popular pool) without changing your data-access code.
  • JNDI Support: In Jakarta EE environments, you can look up a DataSource configured in the application server via JNDI, keeping credentials out of your source code.
  • Spring Integration: If you use Spring Framework, JdbcTemplate is designed to work directly with a DataSource.

Maven Dependency

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.13.0</version>
</dependency>