Java Reference
In-Depth Information
Listing 13-1: Using a PreparedStatement
Class.forName("com.inet.pool.PoolDriver");
com.inet.tds.TdsDataSource tds = new com.inet.tds.TdsDataSource();
tds.setServerName( "JUPITER" );
tds.setDatabaseName( "MEMBERS" );
tds.setUser( dbUserName );
tds.setPassword( dbPassword );
DataSource ds = tds;
Connection con = ds.getConnection(dbUserName,dbPassword);
String SQLQuery = "SELECT * FROM LOGIN WHERE UserName = ?;";
PreparedStatement pstmt = con.prepareStatement(SQLQuery);
pstmt.setString(1, username);
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
id = rs.getInt("MemberID");
memberPwd = rs.getString("Password");
}
con.close();
The main difference between the
PreparedStatement
used in this example and the
Statement
object used in
Chapter 12
lies in the form of the SQL command. In this example, a "?" is used as a
place holder for the variable UserName, which is set using the
pstmt.setString()
method. There
are corresponding setter methods in the
PreparedStatement
for all SQL data types.
You need to supply values to be used in place of all placeholders before you can execute a
PreparedStatement
. Once a
PreparedStatement
parameter has been set to a given value, it
retains that value until it is reset to another value or until the method
clearParameters
is called.
Using PreparedStatement in a Loop
The real efficiency gain in using
PreparedStatement
objects occurs when you use them repeatedly
(for example, when you need to execute a SQL command in a loop). If you need to use the same SQL
command frequently from different instances of the Java class, a better alternative is the use of a
CallableStatement
.
An example of using a
PreparedStatement
in a loop is shown in
Listing 13-2
. A simple
for
loop sets
the parameters of the
PreparedStatement
from the Orders array. The data is then inserted into the
Ordered_Items Table, which is similar to the table of the same name used in the examples of
Part II
.
Listing 13-2: Using a PreparedStatement in a loop
package JavaDatabaseBible.ch13;
import java.sql.*;
import javax.sql.*;
