AVAJAVA Web Tutorials

If you're using the MySQLW JDBCW driver and you're using prepared statements, it's very easy to display the SQLW of your prepared statement that includes the values of your bind variables. All you need to do is call toString() on your PreparedStatement object.

As an example, suppose we have the following simple project. It has the MySQL JDBC driver library in its build path (in this example, mysql-connector-java-5.0.4-bin.jar). It features one class, the MySqlBindVariable class.

The MySqlBindVariableTest class is shown below. It connects to a local MySQL databaseW that I set up in other tutorials. It features a SQL statement variable called 'sqlStmt' that features two bind variables represented as question marks in the SQL statement. The prepared statement (prepStmt) variable uses this SQL statement for its query, and then it binds in the two bind variables, setting the first (using setString) to "Doe" and the second (using setString) to "%n%". It displays the toString() value of the prepared statement both before and after the bindings. After that, it displays the results of the query.

MySqlBindVariableTest.java

package test;

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

public class MySqlBindVariableTest {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String connectionUrl = "jdbc:mysql://localhost:3306/testdatabase";
			String connectionUser = "testuser";
			String connectionPassword = "testpassword";
			conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
			String sqlStmt = "SELECT * FROM employees where last_name=? and first_name like ?";
			System.out.println("SQL Statement:\n\t" + sqlStmt);
			prepStmt = conn.prepareStatement(sqlStmt);
			System.out.println("Prepared Statement before bind variables set:\n\t" + prepStmt.toString());
			prepStmt.setString(1, "Doe");
			prepStmt.setString(2, "%n%");
			System.out.println("Prepared Statement after bind variables set:\n\t" + prepStmt.toString());
			rs = prepStmt.executeQuery();
			while (rs.next()) {
				String id = rs.getString("id");
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				System.out.println("ID: " + id + ", First Name: " + firstName + ", Last Name: " + lastName);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (prepStmt != null)
					prepStmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

(Continued on page 2)