Using an SQL Statement with Parameters
To work with data in a SQL Server database by using an SQL statement that contains IN parameters, you can use the executeQuery method of the SQLServerPreparedStatement class to return a SQLServerResultSet that will contain the requested data. To do this, you must first create a SQLServerPreparedStatement object by using the prepareStatement method of the SQLServerConnection class.
When you construct your SQL statement, the IN parameters are specified by using the ? (question mark) character, which acts as a placeholder for the parameter values that will later be passed into the SQL statement. To specify a value for a parameter, you can use one of the setter methods of the SQLServerPreparedStatement class. The setter method that you use is determined by the data type of the value that you want to pass into the SQL statement.
When you pass a value to the setter method, you must specify not only the actual value to be used in the SQL statement, but also the parameter's ordinal placement in the SQL statement. For example, if your SQL statement contains a single parameter, its ordinal value will be 1. If the statement contains two parameters, the first ordinal value will be 1, while the second ordinal value will be 2.
In the following example, an open connection to the SQL Server 2005 AdventureWorks sample database is passed in to the function, an SQL prepared statement is constructed and run with a single String parameter value, and then the results are read from the result set.
public static void executeStatement(Connection con) {
try {
String SQL = "SELECT LastName, FirstName FROM Person.Contact WHERE LastName = ?";
PreparedStatement pstmt = con.prepareStatement(SQL);
pstmt.setString(1, "Smith");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("LastName") + ", " + rs.getString("FirstName"));
}
rs.close();
pstmt.close();
}
catch (Exception e) {
e.printStackTrace();
}
}