Java Reference
In-Depth Information
// query
String mySQL = "SELECT id, pw FROM Users WHERE name = ?";
PreparedStatement stmt = conn.prepareStatement(mySQL);
stmt.setString(1, args[0]);
If you're at all familiar with SQL then you'll recognize the SQL syntax
within the
String mySQL
. Whatever you want your query to be, just build it
as literal text. The query is “parameterized” by using the “
?
” character. Wher-
ever a “
?
” appears in the query string, you can substitute a value with the
setString()
method on the
PreparedStatement
class.
There are a variety of
set
XXXX
()
methods where
XXXX
stands for different
data types. Besides
setString()
, the most common ones are
setInt()
,
setBigDecimal()
,
setDouble()
, and
setTimestamp()
which set the
parameter from an
int
,
BigDecimal
,
Double
, and
Timestamp
classes, respec-
tively. The
java.sql.Timestamp
class is basically a
java.util.Date
aug-
mented for compatibility with SQL's notion of
TIMESTAMP
. Read more
about it on the Javadoc page for
java.sql.Timestamp
, or read the
java.sql.PreparedStatement
page for more on the other
set
methods
available.
The two arguments to each of these
set
methods are the index and the
value that you want to substitute. The index is simply the count of which
question mark gets substituted, starting with
1
for the first one. Caution: The
parameters start at one, even though most other things in Java, such as
Array
s,
ArrayList
s, and so on, are zero-based. So it's not uncommon in code that
uses JDBC to see something like this:
setInt(i+1, args[i]);
NOTE
Building SQL queries out of
String
literals is made easier in Java by a conve-
nient mismatch between the two languages. In Java,
String
s are delimited
by double quotes (
"
) whereas in SQL literals are bounded by single quotes
(
'
). Thus in Java, you can construct SQL queries that contain literal string
references without much trouble, as in:
String clause = "WHERE name != 'Admin'"