How to Retrieve the SQL of a PreparedStatement in Java

In this tutorial, we will explore how to retrieve the SQL query from a PreparedStatement object in Java. This can be useful for debugging and logging purposes. The standard JDBC API does not provide a direct method to fetch the complete SQL statement with parameters filled in, but we can achieve this through some workarounds.

Table of Contents

1. Introduction
2. Method 1: Manual Construction
3. Method 2: Using a Wrapper Library (p6spy)
4. Complete Example with Manual Construction
5. Complete Example with p6spy
6. Conclusion

Introduction

A PreparedStatement in Java is used to execute parameterized SQL queries. While it provides various advantages such as improved performance and protection against SQL injection, it does not offer a straightforward way to retrieve the complete SQL query with parameters filled in. This tutorial will show you two methods to achieve this.

Method 1: Manual Construction

One way to get the SQL query is by manually constructing it with the parameter values set on the PreparedStatement. This involves creating a utility function to replace the placeholders (?) with the actual parameter values.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class PreparedStatementSQL {
    public static void main(String[] args) {
        String jdbcURL = "jdbc:mysql://localhost:3306/library";
        String username = "root";
        String password = "root";

        try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
            String sql = "INSERT INTO books (title, author, price) VALUES (?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, "Effective Java");
            preparedStatement.setString(2, "Joshua Bloch");
            preparedStatement.setBigDecimal(3, new BigDecimal("45.00"));

            String filledSql = getFilledSQL(preparedStatement, sql);
            System.out.println(filledSql);

            preparedStatement.executeUpdate();

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

    private static String getFilledSQL(PreparedStatement preparedStatement, String sql) throws SQLException {
        String[] parts = sql.split("\\?");
        StringBuilder filledSql = new StringBuilder();

        int paramIndex = 1;
        List<String> params = new ArrayList<>();

        for (int i = 0; i < preparedStatement.getParameterMetaData().getParameterCount(); i++) {
            Object param = preparedStatement.getParameterMetaData().getParameterType(paramIndex);
            if (param instanceof String) {
                params.add("'" + preparedStatement.getString(paramIndex) + "'");
            } else if (param instanceof BigDecimal) {
                params.add(preparedStatement.getBigDecimal(paramIndex).toString());
            }
            // Handle other types similarly
            paramIndex++;
        }

        for (int i = 0; i < parts.length - 1; i++) {
            filledSql.append(parts[i]).append(params.get(i));
        }
        filledSql.append(parts[parts.length - 1]);

        return filledSql.toString();
    }
}

Explanation

1. Connection Setup: Establish a connection to the MySQL database.
2. Prepare SQL Statement: Create a PreparedStatement with placeholders (?).
3. Set Parameters: Set the values of the parameters.
4. Construct SQL String: Manually construct the SQL string with parameter values filled in.
5. Print SQL: Print the constructed SQL string.

Method 2: Using a Wrapper Library (p6spy)

p6spy is a third-party library that intercepts and logs SQL statements, including those created by PreparedStatement.

Example with p6spy

1. Add Dependency: Add p6spy to your project dependencies.

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.1</version>
</dependency>

2. Configuration: Configure p6spy by adding a spy.properties file to your classpath with appropriate settings.

modulelist=com.p6spy.engine.spy.P6SpyFactory
# other configuration settings

3. Usage: Use your JDBC code as usual, and p6spy will log the SQL statements.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.math.BigDecimal;

public class PreparedStatementSQLWithP6Spy {
    public static void main(String[] args) {
        String jdbcURL = "jdbc:p6spy:mysql://localhost:3306/library";
        String username = "root";
        String password = "root";

        try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
            String sql = "INSERT INTO books (title, author, price) VALUES (?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, "Effective Java");
            preparedStatement.setString(2, "Joshua Bloch");
            preparedStatement.setBigDecimal(3, new BigDecimal("45.00"));

            preparedStatement.executeUpdate();

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

Explanation

1. Connection Setup: Establish a connection to the MySQL database using the p6spy driver.
2. Prepare and Execute SQL: Prepare and execute the SQL statement as usual.
3. Logging: p6spy will automatically log the SQL statements with parameter values.

Conclusion

Using the methods described above, you can effectively retrieve the SQL of a PreparedStatement in Java. The manual method provides fine-grained control and customization, while p6spy offers an automated and comprehensive logging solution. Both methods can be useful depending on your specific needs and the complexity of your application.

By using these approaches, you can debug and log your SQL queries more effectively, which can significantly help in troubleshooting and optimizing your database interactions.