[EE] about PreparedStatement

Today, colleagues have a problem, in short, is how PreparedStatement's pre-compilation actually works ...

Hey, ashamed to say, I used to only know preparedstatement than statement better, to prevent SQL injection, it is necessary to use PS.

But, it's pre-compiled, but I didn't pay attention. Now after some research, I dare not say that I have "understood", but it is always a clear idea,

I put my understanding of the hope for the people also help, there is no place to ask you to correct.

• Pre-compilation

The first thing to say is "precompilation". We know that when the database executes the SQL statement, the first thing to parse the SQL statement is to have an "execution plan" concept (that is, to look for an optimal path to execute the SQL statement). For frequently executed SQL, the database caches the execution plan, and the corresponding key is the SQL. Then, when the same SQL is executed again, the database is no longer parsed and the corresponding execution plan is returned directly from key. This saves a lot of time.

• PreparedStatement (hereafter referred to as PS)

When we talk about PS, we may be concerned with a few questions:

1. When does precompilation occur
2. When PS is cached

Answer the first question:

" An SQL statement was specified when the PreparedStatement object was created, and the statement is immediately sent to the DBMS for compilation ."

Answer a second question:

is when we execute the close () method of PS ~ ~ ~

• TEST

I did a test for the PS cache, the idea is for the same SQL, if I fetch the PS is the same, the description is really taking the cache ....

The code is as follows:

1 Importjava.sql.Connection;2 ImportJava.sql.DriverManager;3 Importjava.sql.PreparedStatement;4 Importjava.sql.SQLException;5 6 Importoracle.jdbc.OracleConnection;7 8 9  Public classTestTen { One      StaticConnection con =NULL; A      Public Static voidMain (String args[])throwsClassNotFoundException, SQLException -     { -Con =( (Oracle.jdbc.OracleConnection) getconnection ()); thePreparedStatement PS0 =Getps (con); - ps0.close (); -PreparedStatement PS1 =Getps (con); - ps1.close (); +System.out.println (PS0==PS1);//If True, indicates that the cache has a role -          +          A con.close (); atCon =NULL; -Con =( (Oracle.jdbc.OracleConnection) getconnection ()); -PreparedStatement PS2 =Getps (con); - ps2.close (); - con.close (); -System.out.println (PS1==PS2);//If False, the connection will be re-cached when it is closed in      -     } to      +     //Get PS Instance -      Public StaticPreparedStatement Getps (Connection con)throwsClassNotFoundException, sqlexception{ the            *String sql = "SELECT * from Cms_turbine t where t.mapping like?"; $PreparedStatement PS1 =con.preparestatement (SQL);Panax NotoginsengPs1.setstring (1, "Hello"); -           returnPS1; the     } +      A     //Establish a connection the      Public StaticConnection getconnection () { +         if(Con = =NULL){ -         Try{ $Class.forName ("Oracle.jdbc.driver.OracleDriver"); $con = drivermanager.getconnection ("jdbc:oracle:thin:@127.0.0.1:1523:xxx", xx,xx); -  - //the next two sentences are to open the cache . the((Oracle.jdbc.OracleConnection) con). setimplicitcachingenabled (true); -((Oracle.jdbc.OracleConnection) con). Setstatementcachesize (10);Wuyi}Catch(Exception e) { the e.printstacktrace (); -        } Wu         } -         returncon; About     } $}

The result of the execution is: true

False

Note that the same connection two times the PS is the same instance, different connection do not share PS.

Reference:

Http://www.linuxidc.com/Linux/2011-07/38776.htm

http://docs.oracle.com/cd/E11882_01/java.112/e10589/stmtcach.htm#JJDBC28649

[EE] about PreparedStatement