lib0085.html

java外企软件工程师就业班 J2EE方向 《J2EE架构师手册》 电子书

</table>
</div>
</div>
<p class="para">
<b class="bold">Consolidate formation of SQL statement strings.</b> As a former database administrator, I spend a substantial portion of my time reading the code others have written and suggesting ways to improve performance. As you might expect, I am particularly interested in the SQL statements. I find it especially hard to follow SQL statements constructed by string manipulation scattered over several methods. It greatly enhances readability if you consolidate the logic that forms the SQL statement.</p>
<p class="para">
<a class="internaljump" href="#ch12list10a">Listing 12.10a</a> is a good illustration of this point. Note that the string manipulation to form the SQL statement is located in one place. The SQL string is also defined statically to reduce the amount of string concatenation.</p>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 12.10a: </span>Using a String Host Variable for a Date Field</span><a name="403"></a><a name="ch12list10a"></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">
Select sum(sale_price)
       From purchase_order
       Where to_char(sale_dt,'YYYY-MM-DD') &gt;= ?
<a name="404"></a><a name="IDX-167"></a>
</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">
<b class="bold">Limit use of column functions.</b> Try to limit your use of column functions to the select lists of select statements. Moreover, use only aggregate functions (e.g., count, sum, and average) needed for select statements that use a "group by" clause. There are two reasons for this recommendation: performance and portability.</p>
<p class="para">When you limit the use of a function to a select list (and keep it out of where clauses), you can use the function without blocking the use of an index. In the same way that the <span class="fixed">to_char</span> function prohibited the database from using an index in <a class="internaljump" href="#ch12list10a">listing 12.10a</a>, column functions in where clauses likely will prohibit the database from using an index. This results in slower query performance. Rewriting the SQL statement as shown in <a class="internaljump" href="#ch12list10b">Listing 12.10b</a> allows most databases to use indices.</p>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 12.10b: </span>Query with java.sql.Timestamp as a Host Variable</span><a name="405"></a><a name="ch12list10b"></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">
Select sum(sale_price)
       From purchase_order
       Where sale_dt &gt;= ?
</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">In addition, many of the operations for which developers use SQL column functions (data type conversion, value formatting, etc.) are faster in Java than if the database did them. I've had 5&#8211;20 percent performance improvement in many applications by avoiding some column functions and implementing the logic in Java instead. Another way to look at it is that you cannot tune column functions because you cannot control the source code. By implementing that logic in Java, you create code that you can tune if necessary.</p>
<p class="para">Moreover, using non-ANSI standard column functions can also cause portability problems. Not all database vendors implement the same column functions. For instance, one of my favorite Oracle column functions, decode, which allows you to translate one set of values into another, is not implemented in many of the other major database platforms. In general, using column functions like decode has the potential to become a portability issue.</p>
<p class="para">
<b class="bold">Always specify a column list with a select statement (avoid </b><b class="bold"><span class="fixed">Select</span></b> <b class="bold">*).</b> A common shortcut is to use the * in select statements to avoid having to type out a column list. <a class="internaljump" href="#ch12list11a">Listing 12.11a</a> illustrates this shortcut, and <a class="internaljump" href="#ch12list11b">listing 12.11b</a> illustrates the alternative of explicitly listing desired columns.</p>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 12.11a: </span>Select Statement with *</span><a name="406"></a><a name="ch12list11a"></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">
Select * from customer
</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>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 12.11b: </span>Full Select Statement with Column List</span><a name="407"></a><a name="ch12list11b"></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">
Select last_nm, first_nm, address, city, state, customer_nbr from
customer
</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>
<a name="408"></a><a name="IDX-168"></a>
<p class="para">I recommend explicitly listing columns in select statements, as illustrated in <a class="internaljump" href="#ch12list11b">listing 12.11b</a>. The reason is that if someone reorders the columns in any of the tables in the select or adds new columns, the results obtained with the * shortcut will change, and you must modify the class. For example, suppose a database administrator changes the order of the columns, putting column <span class="fixed">CUSTOMER_NBR</span> first (there are valid reasons why a database administrator would reorder columns) and adds a column called <span class="fixed">COUNTRY</span>. The developer who used the * shortcut will have to change code: all the offset references used in processing the <span class="fixed">ResultSet</span> will change. On the other hand, the developer who explicitly listed all the columns will be oblivious to the change because the code will still work.</p>
<p class="para">Explicitly listing columns in a select statement is a best practice because it eliminates the need for maintenance in some cases.</p>
<p class="para">
<b class="bold">Always specify a column list with an insert statement.</b> Many developers use the shortcut of omitting the column list in an insert statement to avoid having to type out a column list. By default, the column order is the same as physically defined in the table. <a class="internaljump" href="#ch12list12a">Listing 12.12a</a> illustrates this shortcut, and <a class="internaljump" href="#ch12list12b">listing 12.12b</a> illustrates the alternative of explicitly listing desired columns.</p>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 12.12a: </span>Insert Statement Without Column List</span><a name="409"></a><a name="ch12list12a"></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">
Insert into customer
       Values ('Ashmore','Derek','3023 N.Clark','Chicago',
               'IL', 555555)
</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>
<div class="example">
<span class="example-title"><span class="example-titlelabel">Listing 12.12b: </span>Full Insert Statement with Column List</span><a name="410"></a><a name="ch12list12b"></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">
Insert into customer
        (last_nm, first_nm, address, city, state, customer_nbr)
        Values (?,?,?,?,?,?)
</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">I recommend explicitly listing columns in insert statements, as illustrated in <a class="internaljump" href="#ch12list12b">listing 12.12b</a>, for the same reason you should explicitly list columns in <a name="411"></a><a name="IDX-169"></a>select statements. If someone reorders the columns in any of the tables in the insert or adds new columns, the insert could generate an exception, and you must modify the insert statement. For example, suppose that database administrator again changes the order of the columns, putting column <span class="fixed">CUSTOMER_NBR</span> first, and adds a column called <span class="fixed">COUNTRY</span>. The developer who used the shortcut will have to change code The developer who explicitly listed all columns will be oblivious to the change because the code will still work. In addition, note that <a class="internaljump" href="#ch12list12b">listing 12.12b</a> uses host variables, so the same <span class="fixed">PreparedStatement</span> can be used for all inserts if there are multiple inserts.</p>
<p class="para">As in select statements, explicitly listing columns in an insert statement is a best practice because it can eliminate the need for maintenance. Further, allowing reuse of the <span class="fixed">PreparedStatement</span> improves performance, especially for inserts of large numbers of rows.</p>
<p class="last-para">
<b class="bold">Code test cases for all DAO methods and put them in the test suite.</b> You should be able to run a regression test for all objects in the data access layer at any time. This improves product quality by automating a reasonable test process.</p>
</div>
</div><br>
<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>
</body></html>