Database Connectivity – PreparedStatement and CallableStatement

Introduction to Prepared Statements

When working with databases in Java, you often need to execute SQL queries. The conventional way of executing SQL queries is by using a Statement. However, Java provides more efficient alternatives called PreparedStatement and CallableStatement for executing parameterized SQL queries and stored procedures, respectively. In this section, we’ll explore the benefits and usage of PreparedStatement and CallableStatement in Java database connectivity.

PreparedStatement – Benefits and Usage

A PreparedStatement is a precompiled SQL statement that allows you to efficiently execute parameterized queries. It offers several advantages over using simple Statement objects:

1. Efficiency: PreparedStatements are precompiled by the database, which improves execution speed and performance.
2. Security: They help prevent SQL injection attacks by automatically escaping input values.
3. Reusability: You can reuse the PreparedStatement with different parameter values, reducing database load.

Here’s how to use a PreparedStatement in Java:

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

public class PreparedStatementExample {

    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "yourUsername";
        String password = "yourPassword";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Create a PreparedStatement
            String selectQuery = "SELECT * FROM products WHERE category = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(selectQuery);

            // Set parameter values
            preparedStatement.setString(1, "Electronics");

            // Execute the query
            ResultSet resultSet = preparedStatement.executeQuery();

            // Process the results
            while (resultSet.next()) {
                int productId = resultSet.getInt("product_id");
                String productName = resultSet.getString("product_name");
                double price = resultSet.getDouble("price");

                System.out.println("Product ID: " + productId);
                System.out.println("Product Name: " + productName);
                System.out.println("Price: " + price);
            }

            // Close resources
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

CallableStatement – Benefits and Usage

A CallableStatement is used to execute stored procedures in a database. It offers similar benefits to PreparedStatement, such as security and reusability. To use CallableStatement, you need to prepare the stored procedure in the database and then call it from Java. Here’s an example:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.Types;
import java.sql.SQLException;

public class CallableStatementExample {

    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "yourUsername";
        String password = "yourPassword";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Prepare the stored procedure call
            String callProcedure = "{call get_product_price(?, ?)}";
            CallableStatement callableStatement = connection.prepareCall(callProcedure);

            // Set input parameters
            callableStatement.setInt(1, 123);

            // Register the output parameter
            callableStatement.registerOutParameter(2, Types.DOUBLE);

            // Execute the stored procedure
            callableStatement.execute();

            // Retrieve the output parameter
            double productPrice = callableStatement.getDouble(2);

            System.out.println("Product Price: " + productPrice);

            // Close resources
            callableStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Conclusion

PreparedStatement and CallableStatement provide efficient and secure ways to work with parameterized SQL queries and stored procedures in Java database applications. By using these features, you can improve performance, enhance security, and create reusable code for database connectivity in your Java projects.