127 lines
8.0 KiB
HTML
127 lines
8.0 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="concept" />
|
||
|
|
<meta name="DC.Title" content="Create and use PreparedStatements" />
|
||
|
|
<meta name="abstract" content="The prepareStatement method is used to create new PreparedStatement objects. Unlike the createStatement method, the SQL statement must be supplied when the PreparedStatement object is created. At that time, the SQL statement is precompiled for use." />
|
||
|
|
<meta name="description" content="The prepareStatement method is used to create new PreparedStatement objects. Unlike the createStatement method, the SQL statement must be supplied when the PreparedStatement object is created. At that time, the SQL statement is precompiled for use." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="prepstat.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="prepproc.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="prepex.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="pmd.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="prepcreate" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Create and use PreparedStatements</title>
|
||
|
|
</head>
|
||
|
|
<body id="prepcreate"><a name="prepcreate"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Create and use PreparedStatements</h1>
|
||
|
|
<div><p>The prepareStatement method is used to create new PreparedStatement
|
||
|
|
objects. Unlike the createStatement method, the SQL statement must be supplied
|
||
|
|
when the PreparedStatement object is created. At that time, the SQL statement
|
||
|
|
is precompiled for use.</p>
|
||
|
|
<p>For example, assuming a Connection object named conn already exists, the
|
||
|
|
following example creates a PreparedStatement object and prepares the SQL
|
||
|
|
statement for processing within the database.</p>
|
||
|
|
<pre> PreparedStatement ps = conn.prepareStatement("SELECT * FROM EMPLOYEE_TABLE
|
||
|
|
WHERE LASTNAME = ?");</pre>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Specify ResultSet characteristics and auto-generated key support</h4><p>As
|
||
|
|
with the createStatement method, the prepareStatement method is overloaded
|
||
|
|
to provide support for specifying ResultSet characteristics. The prepareStatement
|
||
|
|
method also has variations for working with auto-generated keys. The following
|
||
|
|
are some examples of valid calls to the prepareStatement method:</p>
|
||
|
|
<p><strong>Example:</strong> The
|
||
|
|
prepareStatement method</p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> Read the <a href="codedisclaimer.htm">Code
|
||
|
|
example disclaimer</a> for important legal information.</div>
|
||
|
|
<pre> // New in JDBC 2.0
|
||
|
|
|
||
|
|
PreparedStatement ps2 = conn.prepareStatement("SELECT * FROM
|
||
|
|
EMPLOYEE_TABLE WHERE LASTNAME = ?",
|
||
|
|
|
||
|
|
ResultSet.TYPE_SCROLL_INSENSITIVE,
|
||
|
|
ResultSet.CONCUR_UPDATEABLE);
|
||
|
|
|
||
|
|
// New in JDBC 3.0
|
||
|
|
|
||
|
|
PreparedStatement ps3 = conn.prepareStatement("SELECT * FROM
|
||
|
|
EMPLOYEE_TABLE WHERE LASTNAME = ?",
|
||
|
|
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATEABLE,
|
||
|
|
ResultSet.HOLD_CURSOR_OVER_COMMIT);
|
||
|
|
|
||
|
|
PreparedStatement ps4 = conn.prepareStatement("SELECT * FROM
|
||
|
|
EMPLOYEE_TABLE WHERE LASTNAME = ?", Statement.RETURN_GENERATED_KEYS);</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Handle parameters</h4><p>Before a PreparedStatement object
|
||
|
|
can be processed, each of the parameter markers must be set to some value.
|
||
|
|
The PreparedStatement object provides a number of methods for setting parameters.
|
||
|
|
All methods are of the form set<Type>, where <Type> is a Java™ data
|
||
|
|
type. Some examples of these methods include setInt, setLong, setString, setTimestamp,
|
||
|
|
setNull, and setBlob. Nearly all of these methods take two parameters:</p>
|
||
|
|
<ul><li>The first parameter is the index of the parameter within the statement.
|
||
|
|
Parameter markers are numbered, starting with 1.</li>
|
||
|
|
<li>The second parameter is the value to set the parameter to. There are a
|
||
|
|
couple set<Type> methods that have additional parameters such as the
|
||
|
|
length parameter on setBinaryStream.</li>
|
||
|
|
</ul>
|
||
|
|
<p>Consult the Javadoc for the <a href="javaapi/api/java/sql/package-summary.html" target="_blank">java.sql package</a> for more information. Given the prepared
|
||
|
|
SQL statement in the previous examples for the ps object, the following code
|
||
|
|
illustrates how the parameter value is specified before processing:</p>
|
||
|
|
<blockquote><pre>ps.setString(1,'Dettinger');</pre>
|
||
|
|
</blockquote>
|
||
|
|
<p>If
|
||
|
|
an attempt is made to process a PreparedStatement with parameter markers that
|
||
|
|
have not been set, an SQLException is thrown.</p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> Once set, parameter
|
||
|
|
markers hold the same value between processes unless the following situations
|
||
|
|
occur:<ul><li>The value is changed by another call to a set method.</li>
|
||
|
|
<li>The value is removed when the clearParameters method is called. </li>
|
||
|
|
</ul>
|
||
|
|
<p>The clearParameters method flags all parameters as being unset. After
|
||
|
|
the call to clearParameters has been made, all the parameters must have the
|
||
|
|
set method called again before the next process.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">ParameterMetaData support</h4><p>A new ParameterMetaData
|
||
|
|
interface allows you to retrieve information about a parameter. This support
|
||
|
|
is the compliment to ResultSetMetaData and is similar. Information such as
|
||
|
|
the precision, scale, data type, data type name, and whether the parameter
|
||
|
|
allows the null value are all provided.</p>
|
||
|
|
<p>See <a href="pmd.htm">Example:
|
||
|
|
ParameterMetaData</a> on how to use this new support in an application
|
||
|
|
program. </p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="pmd.htm">Example: ParameterMetaData</a></strong><br />
|
||
|
|
This is an example of using the ParameterMetaData interface to retrieve information about parameters.</li>
|
||
|
|
</ul>
|
||
|
|
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="prepstat.htm" title="PreparedStatements extend the Statement interface and provide support for adding parameters to SQL statements.">PreparedStatements</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="prepproc.htm" title="Processing SQL statements with a PreparedStatement object is accomplished with the executeQuery, executeUpdate, and execute methods like Statement objects are processed. Unlike Statement versions, no parameters are passed on these methods because the SQL statement was already provided when the object was created. Because PreparedStatement extends Statement, applications can attempt to call versions of executeQuery, executeUpdate, and execute methods that take a SQL statement. Doing so results in an SQLException being thrown.">Process PreparedStatements</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="prepex.htm" title="This is an example of using a PreparedStatement object's executeQuery method to obtain a ResultSet.">Example: Use PreparedStatement to obtain a ResultSet</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|