onjava_com database connections and sqlj statements.htm

一个applet与oracle进行连接的例子(大家知道applet与数据库是比较难通讯的

<dt>
<strong>driver_specific_information</strong>
</dt>
<dd>
<p>Specifies any driver-specific information required to connect to the database. This is dependent on the driver being used.
In the case of the Oracle JDBC Thin driver, the driver-specific information may be specified in the following format:</p>

   <pre><code>host_name:port:database_SID</code></pre>

<p>For all the Oracle JDBC drivers -- including the various OCI drivers -- the driver-specific information may be specified using an Oracle Net8 (or above) keyword-value pair, which may be specified in the following format:</p>

<p><code>(description=(address=(host=host_name)(protocol=tcp)(port=port))
(connect_data=(sid=database_sid)))</code></p>

<p>The syntax elements are as follows:</p>

    <dl><dt>
<code>host_name</code>
</dt>
    <dd>
    Specifies the name of the machine on which the database is running.
    </dd>
    <dt>
    <code>port</code>
    </dt>
    <dd>
    Specifies the port number on which the Net8 database listener 
	waits for requests. 1521 is the default port number. Check 
	with your Database Administrator (DBA) to ensure that this is 
	correct value in your environment.
    </dd>
    <dt>
    <code>database_SID</code>
    </dt>
    <dd>
    Specifies the system identifier (SID) of the database instance 
	to which you want to connect. Your DBA will be able to provide 
	you with the correct database SID to use.<br /><br />
    You may also use an Oracle Net8 (or above) <code>TNSNAMES</code> string -- 
	for more information on this, speak with your DBA or consult 
	the Oracle documentation.
    </dd>
    </dl>
</dd>
</dl>

<p>The following example shows the <code>connect()</code> method being used to connect to a database using the Oracle OCI8 driver:</p>

<pre><code>Oracle.connect(
  "jdbc:oracle:oci8:@(description=(address=(host=localhost)" +
    "(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))",
  "scott",
  "tiger"
);</code></pre>

<p>The Oracle JDBC Thin driver has the least amount of system resource requirements, and is generally used in lightweight, client-based programs such as Java applets. The Oracle JDBC Thin driver may be used to access Oracle7 databases  and above.</p>

<p>The various Oracle JDBC OCI drivers require more system resources than the thin driver, but they are faster and are suitable for middle tier programs. The OCI driver is used to access Oracle9<i>i</i> databases, and above. The OCI8 driver is used for accessing Oracle8<i>i</i> and Oracle8 databases. The OCI7 driver is used for accessing Oracle7 databases.</p>

<p>Once you've made a connection to the database using the <code>connect()</code> method, you may execute SQLJ statements that contain embedded SQL operations.</p>

<h3>Simple SQLJ Statements</h3>

<p>In this section you will learn how to write simple SQLJ statements that use embedded SQL Data Manipulation Language (DML) statements. DML consists of the following types of statements:</p>

<ul><li><code>SELECT</code></li>
<li><code>INSERT</code></li>
<li><code>UPDATE</code></li>
<li><code>DELETE</code></li></ul>

<p>You will also learn how SQLJ statements can share data with other Java statements in the program, through the use of host variables.</p>

<h4>The Form of a SQLJ Statement</h4>

<p>A SQLJ executable statement is a program line that contains an embedded SQL statement. There are two possible types of executable statements; the statement type is determined by whether or not the embedded SQL statement returns a value.</p>

<p>If an embedded SQL statement does not return a value, the syntax of the SQLJ executable statement is as follows:</p>

<pre><code>#sql { SQL_statement };</code></pre>

<p>The syntax element is as follows:</p>

<pre><code>SQL_statement
     Specifies any valid SQL statement.</code></pre>
	 
<p>The following SQLJ executable statement invokes a SQL <code>INSERT</code> statement to add a row to the <code>customers</code> table:</p>

<pre><code>#sql {
  INSERT INTO
    customers (id, first_name, last_name, dob, phone)
  VALUES
    (1, 'John', 'Smith', '13-NOV-1970', '650-555-1212')
};</code></pre>

<p>Everything to the right of the <code>#sql</code> token in the syntax is the executable part of the SQLJ statement, and is known as the <i>SQLJ clause</i>. There are two types of SQLJ clauses. Because this SQLJ clause does not include a result expression, it is known as a <i>statement clause</i>.</p>

<p>If an embedded SQL statement does return a result, then you need a way to specify where that result should be placed. SQLJ syntax accommodates this need. When a value is returned, the syntax for a SQLJ executable statement is as follows:</p>

<pre><code>#sql host_variable = { SQL_statement };</code></pre>

<p>The syntax elements are as follows:</p>

<dl>
<dt>
<code>host_variable</code>
</dt>
<dd>
Is a variable, declared in the Java program, which is used to store the value returned by the SQL statement. This may also be a Java array element or object attribute.
</dd>
<dt>
<code>SQL_statement</code>
</dt>
<dd>
Is any valid SQL statement that returns a value.
</dd>
</dl>

<p>An example of the type of SQL statement that returns a value is a call to a PL/SQL function. The following example uses an assignment clause to store the result returned by a call to a PL/SQL function (you will learn more about using PL/SQL in SQLJ in a future column):</p>

<pre><code>int result;
#sql result = { VALUES update_product_price_func(1, 2) };</code></pre>

<p>The PL/SQL function <code>update_product_price_func()</code> is created by the <i>fundamental_user_schema.sql</i> script. It attempts to update the <code>price</code> column of the row in the <code>products</code> table, the ID of which is equal to the first parameter in the function call; the <code>price</code> column is multiplied by the second parameter in the function call. The function will return 0 if the product was found; otherwise, the function will return 1. The value returned from this function is assigned by the SQLJ statement to the Java variable named <code>result</code>.</p>

<p>Recall that everything to the right of <code>#sql</code> is known as a SQLJ clause. A SQLJ clause that contains a result expression, such as the one shown here, is known as an <i>assignment clause</i>. Assignment clauses are fine for storing the results of PL/SQL function calls, but one question I'm sure you're thinking is, "How does an SQLJ program retrieve the values stored in table columns into Java variables?" The answer is by using host variables and expressions.</p>

<table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td><p class="secondary">
Pages: <a href="/pub/a/onjava/2002/01/02/learning_sqlj.html?page=1">1</a>, <b>2</b>, <a href="/pub/a/onjava/2002/01/02/learning_sqlj.html?page=3">3</a>
</p></td><td><p class="secondary" align="right">
<a href="/pub/a/onjava/2002/01/02/learning_sqlj.html?page=3">
Next Page<img src="/images/arrows/arrows-66.gif" border="0" alt="arrow" width="12" height="13" hspace="2" vspace="0" align="baseline"></a></p></td></tr></table>




<!--  end content  -->
<img src="/images/trans.gif" alt=" " width="490" height="1" /><br />

<!--  <csperl file="childarticles">  -->


<a name="thread"></a>

<br /><br >


<img src="/images/trans.gif" alt=" " width="450" height="12" border="0" /><br />
</td>
<td width="20" valign="top"><!--  space between news and sponsors  -->
<img src="/images/trans.gif" alt=" " width="20" height="1" /></td>
<td width="130" bgcolor="#330066" valign="top" align="center" class="secondary" style="border-right: 1px #FFF solid;">
<!--  Sponsors  -->
<img src="/images/trans.gif" alt=" " width="140" height="3"><br />
<span class="header">Sponsored by:</span><br /><br />
<table border="0" cellspacing="0" width="120"><tr><td><!-- dy -->
				<iframe src="http://ad.doubleclick.net/adi/onjava.ds/jdbcart;pos=r_jdbcart;sz=120x600;ord=1923818595?" width="120" height="600" frameborder="0" marginwidth="0" marginheight="0" scrolling="no">
				<script type="text/javascript" language="JavaScript1.1" src="http://ad.doubleclick.net/adj/onjava.ds/jdbcart;abr=!ie;pos=r_jdbcart;sz=120x600;ord=1923818595?"></script>
				</iframe>
				<noscript>
				<a href="http://ad.doubleclick.net/jump/onjava.ds/jdbcart;abr=!ie;pos=r_jdbcart;sz=120x600;ord=1923818595?"><img src="http://ad.doubleclick.net/ad/onjava.ds/jdbcart;abr=!ie;kw=pos=r_jdbcart;sz=120x600;ord=1923818595?" border="0" width="120" height="600" alt="Advertisement" /></a>
				</noscript></td></tr></table><p></p>

</center>
<img src="/images/trans.gif" alt=" " width="140" height="1">
</td>
</tr>
</table>
<table width="100%"  border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="9" bgcolor="#cccccc"><img src="/images/trans.gif" alt=" " border="0" height="1" width="1"></td>
</tr>
<tr>
<td>
<!--  Creative for 728x90 format  -->
<div style="text-align:center; width:100%; padding:6px 0; background:#fff;"> <script type="text/javascript"><!--
google_ad_client = "oreilly_728x90";
google_ad_width = "728";
google_ad_height = "90";
google_ad_format = "728x90_pas_abgnc";
google_safe = "high";
google_color_border = "CCCCCC";
google_color_bg = "FFFFFF";
google_color_link = "000066";
google_color_url = "000066";
google_color_text = "000000";
//--></script>
<script type="text/javascript" language="JavaScript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<noscript>
<img height="1" width="1" border="0"
src="http://pagead2.googlesyndication.com/pagead/imp.gif?client=ca-oreilly_728x90&amp;event=noscript" alt=""/>
</noscript>
 </div>
<!--  End Creative  --> <br />
<br />
<p class="secondary" align="center">
<a href="/pub/a/mediakit/contact.html">Contact Us</a> | 
<a href="/pub/a/mediakit/adcontact.html">Advertise with Us</a> |
<a href="/pub/a/mediakit/privacy.html">Privacy Policy</a> |
<a href="http://press.oreilly.com/">Press Center</a> |
<a href="http://jobs.oreilly.com/">Jobs</a>
</p>
<p class="tiny" align="center">Copyright &copy; 2000-2004 O扲eilly Media, Inc. All Rights Reserved.<br />
All trademarks and registered trademarks appearing on the O'Reilly Network are the property of their respective owners.<br /><br />
For problems or assistance with this site, email <script language="JavaScript" type="text/javascript">
<!--
var name = "help";
var domain = "oreillynet.com";
var display = "";
var subject = "";
var ending = "";
document.write('<a href="mailto:' + name + '&#64;' + domain + '?subject=' + subject + '">');
if (display) {
 document.write(display);
} else {
 document.write(name + '&#64;' + domain);
}
document.write('</a>' + ending);
document.write('<!-- mailmunge bit -->');

// -->
</script>

</p>
<br />
</td>
</tr>
</table></body>

</html>