📄 lib0085.html
字号:
<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>JDBC Best Practices</title>
<link rel="STYLESHEET" type="text/css" href="images/xpolecat.css">
<link rel="STYLESHEET" type="text/css" href="images/ie.content.css">
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;"><a href="toc.html"><img src="images/teamlib.gif" width="62" height="15" border="0" align="absmiddle" alt="Team LiB"></a></div></td>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href="LiB0084.html"><img src="images/previous.gif" width="62" height="15" border="0" align="absmiddle" alt="Previous Section"></a>
<a href="LiB0086.html"><img src="images/next.gif" width="41" height="15" border="0" align="absmiddle" alt="Next Section"></a>
</div></td></tr></table>
<br>
<div class="chapter">
<a name="ch12"></a>
<div class="section">
<h2 class="first-section-title"><a name="396"></a><a name="ch12lev1sec4"></a>JDBC Best Practices</h2><p class="first-para">Most J2EE applications manage their own persistence via JDBC, so a few tips and guidelines for JDBC usage are appropriate here. I assume that you already know the basics of JDBC programming. Readers wanting a good reference for JDBC basics as well as other programming topics should see <a href="LiB0088.html#428" target="_parent" class="chapterjump">Horstmann and Cornell (2001)</a>.</p>
<p class="para">
<b class="bold">Use host variables in SQL statements instead of hard-coding literals in</b> <b class="bold">SQL strings.</b> As a convenience, many developers embed literals in SQL statements instead. <a class="internaljump" href="#ch12list07">Listing 12.7</a> is an example of the bad practice of embedding literals. Notice that this example places a user ID directly in the SQL statement. Notice, too, that this example uses the + operator for string concatenation. Although using + is convenient, you can concatenate strings faster using <span class="fixed">StringBuffers</span> and the <span class="fixed">StringBuffer.append()</span> method.</p>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 12.7: </span>Embedding Literals in SQL Statements (Bad Practice)</span><a name="397"></a><a name="ch12list07"></a>
<div class="formalbody">
<table class="BlueLine" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td bgcolor="000080" class="bluecell"><font size="2" face="Arial" color="010100"><b><img src="_.gif" width="1" height="2" alt="Start example" border="0"></b></font></td>
</tr>
</table>
<pre class="literallayout">
1: Statement stmt;
2: ResultSet rst;
3: Connection dbconnection;
4:
5: // some code omitted
6:
7: stmt = dbconnection.createStatement();
8: rst = stmt.executeQuery(
9: "select count(*) "+
10: "from portfolio_info "+
11: "where USER_ID = "+
12: userID);
13: if(rst.next())
14: {
15: count = rst.getInt(1);
16: }
</pre>
<table class="BlueLine" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td bgcolor="000080" class="bluecell"><font size="2" face="Arial" color="010100"><b><img src="_.gif" width="1" height="2" alt="End example" border="0"></b></font></td>
</tr>
</table>
<table class="BlankSpace" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td height="16"></td>
</tr>
</table>
</div>
</div>
<p class="para">The problem with the code in <a class="internaljump" href="#ch12list07">listing 12.7</a> is that it circumvents database optimizations provided by Oracle, DB2/UDB, and many others. To get the benefit of database software optimizations, you need to use <span class="fixed">PreparedStatement</span> objects instead of <span class="fixed">Statement</span> objects for SQL that <a name="398"></a><a name="IDX-164"></a>will be executed multiple times. Further, you need to use host variables instead of literals for literals that will change between executions. With <a class="internaljump" href="#ch12list07">listing 12.7</a>, the SQL statement for user ID 1 will be different than for user ID 2 (<span class="fixed">"where USER_ID = 1"</span> is different from <span class="fixed">"where USER_ID = 2"</span>). A better way to approach this SQL statement is presented in <a class="internaljump" href="#ch12list08">listing 12.8</a>.</p>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 12.8: </span>Using a Host Variable in a SQL Statement (Listing 12.7 Rewritten)</span><a name="399"></a><a name="ch12list08"></a>
<div class="formalbody">
<table class="BlueLine" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td bgcolor="000080" class="bluecell"><font size="2" face="Arial" color="010100"><b><img src="_.gif" width="1" height="2" alt="Start example" border="0"></b></font></td>
</tr>
</table>
<pre class="literallayout">
ResultSet rst;
PreparedStatement pstmt;
Connection dbconnection;
...
pstmt = dbconnection.prepareStatement ("select count(*) from
portfolio_info where USER_ID = ? ");
pstmt.setDouble(1,userID);
rst = pstmt.executeQuery();
if(rst.next())
{
count = rst.getInt(1);
}
</pre>
<table class="BlueLine" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td bgcolor="000080" class="bluecell"><font size="2" face="Arial" color="010100"><b><img src="_.gif" width="1" height="2" alt="End example" border="0"></b></font></td>
</tr>
</table>
<table class="BlankSpace" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td height="16"></td>
</tr>
</table>
</div>
</div>
<p class="para">Notice that because <a class="internaljump" href="#ch12list08">listing 12.8</a> uses host variables instead of literals, the SQL statement is identical no matter what the qualifying user ID is. Further, a <span class="fixed">PreparedStatement</span> is used instead of a <span class="fixed">Statement</span>.</p>
<p class="para">To better understand the database optimizations possible when using <span class="fixed">PreparedStatement</span> objects, consider how Oracle processes SQL statements. When executing SQL statements, Oracle goes through the following steps:</p>
<ol class="orderedlist">
<li class="first-listitem">
<p class="first-para">Look up the statement in the shared pool to see if it has already been parsed or interpreted. If yes, go directly to step 4.</p>
</li>
<li class="listitem">
<p class="first-para">Parse (or interpret) the statement.</p>
</li>
<li class="listitem">
<p class="first-para">Figure out how to get the desired data and record the information in a portion of memory called the shared pool.</p>
</li>
<li class="listitem">
<p class="first-para">Get the data.</p>
</li>
</ol>
<p class="para">When Oracle looks up a SQL statement to see if it has already been executed (step 1), it attempts a character-by-character match of the SQL statement. If the program finds a match, it can use the parse information already in the shared pool and does not have to do steps 2 and 3 because it has done the work already. If you hard-code literals in SQL statements, the probability of finding a match is low (<span class="fixed">"where USER_ID = 1"</span> is not the <a name="400"></a><a name="IDX-165"></a>same as <span class="fixed">"where USER_ID = 2"</span>). This means that Oracle will have to reparse <a class="internaljump" href="#ch12list07">listing 12.7</a> for each portfolio selected. Had <a class="internaljump" href="#ch12list07">listing 12.7</a> used host variables and a <span class="fixed">PreparedStatement</span>, the SQL statement (which would look something like <span class="fixed">"where USER_ID =:1"</span> in the shared pool) would have been parsed once and only once.</p>
<p class="para">DB2/UDB uses different terminology but a similar algorithm for dynamic SQL statements. Use of the <span class="fixed">PreparedStatement</span> over the <span class="fixed">Statement</span> is recommended for DB2/UDB as well.</p>
<p class="para">
<b class="bold">Always close Statement, </b><b class="bold"><span class="fixed">PreparedStatement</span></b>, <b class="bold"><span class="fixed">CallableStatement</span></b> <b class="bold">and </b><b class="bold"><span class="fixed">Connection</span></b> <b class="bold">variables with a finally block.</b> Many database platforms allocate resources to servicing these classes, and many continue to allocate those resources for a period if the objects aren't closed after use. Closing the variables improves time and resources spent on maintenance to keep errors from happening.</p>
<p class="para">In the example shown in <a class="internaljump" href="#ch12list09">listing 12.9</a>, a finally block closes <span class="fixed">PreparedStatement</span>. The connection in the example method remains open because it is used elsewhere in the application. Also notice that the call to <span class="fixed">DatabaseUtility</span> from CementJ closes the <span class="fixed">PreparedStatement</span>. Using a utility to do the close makes it a one-liner. To execute <span class="fixed">close()</span> on <span class="fixed">ResultSet</span> and <span class="fixed">PreparedStatement</span> directly, you need to use a try/catch to handle the <span class="fixed">SQLException</span>.</p>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 12.9: </span>Using a Finally Block to Close JDBC Resources</span><a name="401"></a><a name="ch12list09"></a>
<div class="formalbody">
<table class="BlueLine" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td bgcolor="000080" class="bluecell"><font size="2" face="Arial" color="010100"><b><img src="_.gif" width="1" height="2" alt="Start example" border="0"></b></font></td>
</tr>
</table>
<pre class="literallayout">
1:import org.cementj.util.DatabaseUtility;
2:
3:// Some code omitted..
4:private static final String GET_DISPLAY_SQL =
5: "select t_ddlb_itm_dspl_val "+
6: "from nasdb..nmr_parm_ddlb_val where "+
7: "i_templ_parm = ? and c_ddlb_itm = ? ";
8:
9:public String getDisplayValue(int parmId,
10: String parmValue)
11: {
12: String displayValue = null;
13:
14: PreparedStatement pStmt = null;
15: ResultSet results = null;
16:
17: try
18: {
19: pStmt = _dbConnection.prepareStatement(<a name="402"></a><a name="IDX-166"></a>
20: GET_DISPLAY_SQL);
21:
22: pStmt.setInt(1, parmId);
23: pStmt.setString(2, parmValue.trim());
24: results = pStmt.executeQuery();
25:
26: if (results.next())
27: {
28: displayValue =
29: results.getString("t_ddlb_itm_dspl_val");
30: if (displayValue != null) displayValue =
31: displayValue.trim();
32: }
33: }
34: catch (Throwable t)
35: {
36: throw new MyApplicationRuntimeException(
37: "Error selecting parm value::> parmId=" +
38: parmId +
39: "; parmValue=" + parmValue, t);
40: }
41: finally
42: {
43: DatabaseUtility.close(results, pStmt);
44: }
45:
46: return displayValue;
47: }
</pre>
<table class="BlueLine" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td bgcolor="000080" class="bluecell"><font size="2" face="Arial" color="010100"><b><img src="_.gif" width="1" height="2" alt="End example" border="0"></b></font></td>
</tr>
</table>
<table class="BlankSpace" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td height="16"></td>
</tr>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -