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

Product name version
1 Java 8
2 MySQL JDBC Driver 5.1.44
3 PostgreSQL JDBC Driver 42.1.4

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 () __.

Recommended Posts

How to get the query string to actually issue when using PreparedStatement with JDBC
How to get started with JDBC using PostgresSQL on MacOS
[Java] How to get to the front of a specific string using the String class
[Java] How to easily get the longest character string of ArrayList using stream
Whether to enable SSL when using JDBC with MySQL.
How to get the class name of the argument of LoggerFactory.getLogger when using SLF4J in Java
[Rails] How to solve the error "undefined method` visit'" when using Capybara with Rspec
How to set environment variables when using Payjp with Rails
How to get the log when install4j does not start
How to write query option when using gem ruby-firebase (memorial)
How to get started with slim
How to get along with Rails
How to get the contents of Map using for statement Memorandum
How to solve the unknown error when using slf4j in Java
[Java] How to convert from String to Path type and get the path
[Rails 5] How to display the password change screen when using devise
[Note] How to get started with Rspec
[Java] How to get the current directory
How to get the current date as a string in yyyyMMdd format
[Rails] How to get the user information currently logged in with devise
How to get the date in java
graphql-ruby: How to get the name of query or mutation in controller Note
How to get the ID of a user authenticated with Firebase in Swift
I tried to get the distance from the address string to the nearest station with ruby
How to implement the breadcrumb function using gretel
[Swift] How to link the app with Firebase
How to get today's day of the week
How to delete the database when recreating the application
How to get started with Eclipse Micro Profile
How to get resource files out with spring-boot
[Java] How to get the redirected final URL
[Java] How to get the authority of the folder
How to delete the tweet associated with the user when you delete it at the same time
How to request by passing an array to the query with HTTP Client of Ruby
[Swift] How to display the entered characters in Widget via UserDefaults when using WidgetKit
How to fix the problem that the upper half is cut off when using UITabBar
Summary of how to use the proxy set in IE when connecting with Java
[Java] How to get the URL of the transition source
How to change the action with multiple submit buttons
How to unit test with JVM with source using RxAndroid
How to add characters to display when using link_to method
[Java] How to get the maximum value of HashMap
Organized how to interact with the JDK in stages
[Android] How to get the setting language of the terminal
How to query Array in jsonb with Rails + postgres
[Rails] How to get the contents of strong parameters
[Ruby] How to get the tens place and the ones place
[Swift] How to get the document ID of Firebase
How to get started with creating a Rails app
How to get jdk etc from oracle with cli
[Ruby] Get in the habit of using the dup method when making a copy of a string variable
Check how to set the timeout when connecting with Spring + HikariCP + MySQL and executing SQL
How to reduce the load on the program even a little when combining characters with JAVA
How to automatically generate ER diagram when migrating with Rails6
How to get the class name / method name running in Java
How to take a screenshot with the Android Studio emulator
How to test including images when using ActiveStorage and Faker
How to set chrony when the time shifts in CentOS7
[IOS] How to get the table name from AWS DynamoDB
Import the date string automatically converted to Excel with java.time.LocalDate
How to get values in real time with TextWatcher (Android)