Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

More Java Pitfalls Share Reactor - Daconta M,C.

Daconta M,C. More Java Pitfalls Share Reactor - Wiley publishing, 2003. - 476 p.
ISBN: 0-471-23751-5
Download (direct link): morejavapitfallssharereactor2003.pdf
Previous << 1 .. 130 131 132 133 134 135 < 136 > 137 138 139 140 141 142 .. 166 >> Next

01 public void doPost
02 (HttpServletRequest request, HttpServletResponse response)
03 throws ServletException, IOException {
04 response.setContentType(CONTENT_TYPE);
05
06 String salary = request.getParameter("salary");
07
08 try {
09
10 PreparedStatement statement
Listing 44.2 Using prepared statements (continued)
376 Item 44
11 = connection.prepareStatement("SELECT ename FROM emp WHERE 2
sal > ?");
12
13 statement.setFloat(1, Float.parseFloat(salary));
14
15 ResultSet results =
16 statement.executeQuery();
17
// ... remaining code Identical to Listing 44.1 ...
Listing 44.2 (continued)
This example shows precompiling the statement and accepting parameters instead of any string. This is helpful for controlling data types and escape sequences. For example, calling setDate() or setString() handles the appropriate escape characters for the specific database being used, and it also prevents mismatching typesólike specifying a String for a long field.
However, while this is a common practice among many developers, the Prepared-Statement is not reused. This means that the statement is recompiled each time; thus, no advantage is seen in the precompiling, aside from data typing. So, the Prepared-Statement should persist between invocations to receive the most effective and effi-
cient results. Listing 44.3 is an example of the servlet rewritten to reuse the
PreparedStatement.
01 import javax.servlet.*;
02 import javax.servlet.http.*;
03 import java.io.*;
04 import java.util.*;
05 import java.sql.*;
06
07 public class SalaryServlet extends HttpServlet {
08
09 Connection connection; // Shouldn't be member variable under
normal conditions.
10 PreparedStatement statement;
11
12 private static final String CONTENT_TYPE = "text/html"
13
14 public void init(ServletConfig config) throws ServletException {
15 super.init(config);
16
17 // Database config information
18 String driver = "oracle.jdbc.driver.OracleDriver";
19 String url = "jdbc:oracle:thin:@joemama:1521:ORACLE"
Listing 44.3 Best use of prepared statements
The Unprepared PreparedStatement 377
20 String username = "scott";
21 String password = "tiger";
22
23 // Establish connection to database
24 try {
25 Class.forName(driver);
26 connection =
27 DriverManager.getConnection(url, username, password);
28
29 PreparedStatement statement
30 = connection.prepareStatement("SELECT mp e M O R F me na e 2
WHERE sal > ?");
31
32 } catch(ClassNotFoundException cnfe) {
33 System.err.println("Error loading driver: " + cnfe);
34
35 } catch(SQLException sqle) {
36 sqle.printStackTrace();
37
38 }
39 }
40 /**Process the HTTP Post request*/
41 public void doPost(HttpServletRequest request, 2
HttpServletResponse response) throws ServletException, IOException {
42 response.setContentType(CONTENT_TYPE);
43
44 String salary = request.getParameter("salary") ;
45
46 try {
47
48 statement.setFloat(1, Float.parseFloat(salary));
49
50 ResultSet results =
51 statement.executeQuery();
52
// ... remaining code Identical to listing 44.1 ...
85 }
86
Listing 44.3 (continued)
Notice in this example that the parameter is set and the PreparedStatement is executed. This allows the reuse of the PreparedStatement between requests to the servlet. This improves the responsiveness, since it does not require the repetitive and time-consuming step of recompiling the statement on every invocation of the servlet.
It seems logical that there would be a performance improvement in reusing rather than recompiling statements. Testing was conducted to determine how much better the reuse option was. The three previously mentioned scenarios were tested. All used
378 Item 45
an Oracle database over a local area network. A test class was built to connect to the database and sequentially call a method representing each scenario, using the same connection for each. The same query was executed 100 times using each methodology. Here is the output of the tester class:
Executing prepared statement 100 times took 0.791 seconds.
Executing raw query 100 times took 1.472 seconds.
Executing repeated prepared statements 100 times took 1.622 seconds.
This class was executed several times with very similar results. Notice two things: First, the reused prepared statement was approximately 40 to 50 percent faster than the raw query statement, and second, the repeated compiling of prepared statements actually gave about a 10 percent performance hit.
This example shows a classic pitfall in the use of JDBC. When building desktop applications or when in the development phase of Web and enterprise applications, many developers fail to notice the performance hit that comes from not effectively using the PreparedStatement. A 50 percent hit in response time can get very significant in Web applications when loads can go up tremendously without much notice.
Previous << 1 .. 130 131 132 133 134 135 < 136 > 137 138 139 140 141 142 .. 166 >> Next