How to get the query string to actually issue when using PreparedStatement with JDBC

Introduction

When using PreparedStatement, you may want to know the query string that is actually issued after binding the value to the placeholder (output to log, etc.). When using the framework, you can get the issued query history from the framework, but I don't see much how to get it when using plain JDBC, so I wrote it. The details differ depending on the JDBC driver, so MySQL and PostgreSQL are described below.

environment

Sample code

This is a sample to actually get and display the query string after replacing the placeholder. Gets and displays the query string when accessing the column [name] of the database [sample] and table [ACCOUNT] in the local environment.

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

/**
 *Sample to get query string after placeholder replacement from PreparedStatement
 */
public class JdbcTest {

	public static void main(String... args) {
		useMySQL();
		System.out.println("=====================");
		usePostgres();
	}

	/**
	 *MySQL version
	 */
	private static void useMySQL() {
		try (Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1/sample", "username", "password");
				PreparedStatement statement = connection.prepareStatement( "select * from ACCOUNT where (name like ?)");) {

			statement.setString(1, "%hoge%");
			//Get / display query string here
			System.out.println("mysql    > " + statement.toString());

			if (statement instanceof com.mysql.jdbc.PreparedStatement) {
				//Cast to PreparedStatement for MySQL to take advantage of MySQL-specific features
				com.mysql.jdbc.PreparedStatement mysqlStatement = (com.mysql.jdbc.PreparedStatement) statement;
				System.out.println("No reference version> " + mysqlStatement.asSql());
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	/**
	 *PostgreSQL version
	 */
	private static void usePostgres() {
		try (Connection connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/sample", "username", "password");
				PreparedStatement statement = connection.prepareStatement("select * from ACCOUNT where name = ?");) {

			statement.setString(1, "hogehoge");
			//Get / display query string here
			System.out.println("postgres     > " + statement.toString());

			if (statement instanceof org.postgresql.jdbc.PgStatement) {
				//There is no asSql method in PreparedStatement for PostgreSQL
				org.postgresql.jdbc.PgStatement postgresStatement = (org.postgresql.jdbc.PgStatement) statement;
				System.out.println("This is unchanged> " + postgresStatement.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
}

output

mysql    > com.mysql.jdbc.JDBC42PreparedStatement@5ef04b5: select * from ACCOUNT where (name like '%hoge%')
No reference version> select * from ACCOUNT where (name like '%hoge%')
=====================
postgres     > select * from ACCOUNT where name = 'hogehoge'
This is unchanged> select * from ACCOUNT where name = 'hogehoge'

Summary

As you can see from the sample code, you can basically get the query string after placeholder replacement by executing __java.util.PreparedStatement # toString () __. However, in the case of MySQL, the reference information is also included in the character string that can be obtained, so if you want only the query string, you need to execute __com.mysql.jdbc.PreparedStatement # asSql () __.