创建statment主在循环内外对比

class DemoParse {
	public static void main(String args[]) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rset = null;
		ResultSet rset1 = null;
		// first parameter: database name
		try {
			// get connection - auto commit is off
			conn = (Connection) JDBCUtil
					.getConnection("fuyou", "fuyou", "orcl");
			JDBCUtil.startTrace(conn);
			JDBCUtil.getTraceName(conn);
			String stmtString = "select /*+ prepareStatement() within loop */ empno, job from emp where ename = ?";
			for (int i = 0; i < 5; i++) {
				pstmt = conn.prepareStatement(stmtString);
				pstmt.setString(1, "SCOTT");
				rset = pstmt.executeQuery();
				while (rset.next()) {
				}
			}
			stmtString = "select /*+ prepareStatement() outside loop */ empno, job from emp where ename = ?";
			pstmt1 = conn.prepareStatement(stmtString);
			for (int i = 0; i < 5; i++) {
				pstmt1.setString(1, "SCOTT");
				rset1 = pstmt1.executeQuery();
				while (rset1.next()) {
				}
			}
		} finally {
			// release resources associated with JDBC in the finally clause.
			JDBCUtil.close(rset);
			JDBCUtil.close(pstmt);
			JDBCUtil.close(rset1);
			JDBCUtil.close(pstmt1);
			JDBCUtil.close(conn);
		}
	}
}

利于tkprof 查看结果 ：

select /*+ prepareStatement() within loop */ empno, job 
from
 emp where ename = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.01       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         35          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.01       0.00          0         35          0           5

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 62  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=62 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      10        0.00          0.00
  SQL*Net message from client                    10        0.01          0.01
********************************************************************************

select /*+ prepareStatement() outside loop */ empno, job 
from
 emp where ename = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0         35          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.00       0.00          0         35          0           5

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 62  

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  TABLE ACCESS FULL EMP (cr=35 pr=0 pw=0 time=189 us)