Preparedstatement and statement

One realm of SQL optimization is not only to preparedstatement, but also to minimize the number of SQL executions. Big Data is usually formed through loop nesting in applications, the other is formed by the Cartesian set of the table. reducing SQL Execution times can be achieved through clever use of the database's Cartesian set.

Preparedstatement:

1. the time saved by preparedstatement is the network overhead time. The actual SQL Execution time accounts for about 10%, and the network connection time accounts for about 90%.

For batch insert operations, the DB server is stillInsert one by one(I used to think that the DB server only executes the prestatedstatement SQL statement once. This is an incorrect understanding and is still performed multiple times, but this is implemented on the basis of a connection multiple times, the statement SQL statement is a connection each time. multiple executions)

2. Another saving time is that the pre-compiled SQL statement is cached and only one execution plan is generated. Each SQL statement of statement generates one execution plan.

3. preparedstatement sends all parameters and SQL statements with placeholders to the database at one time. note that multiple messages may be sent. If the packet is too large, the Protocol below the application layer protocol will send packets in batches.

4. The main problem with prestatestatement is that SQL debugging is not satisfactory. can only see? But this can be solved through the jdbclogger tool. Through jdbclogger, we can see that there are multiple pre-compiled SQL statements to be executed. If you do not configure tools such as jdbclogger, only one tape can be seen in the log file? Point operator SQL, will mistakenly think that only one execution.

An extreme example of using preparestatement:

/* Formatted on 2011-8-2 18:54:00 (qp5 v5.114.809.3010 )*/
Merge into CPC. cpcpartneraudit CPA
Using (select5011as opid, 1011as accountid, 2011as groupid, 3013as ideaid, 4011as keyid, 0as checkstatus, to_date ('2016/07/21 14:22:50 ', 'Mm/DD/YYYY hh24: MI: SS ') as createdate,' contains the elements being reviewed 'As refusereason, 0as adminuserid, 'automatic review' as adminusername,' contains the elements being reviewed 'As auditreason, nullas backupideaid from dual) cpai
On (CPA. keyid = cpai. keyid and CPA. ideaid = cpai. ideaid)
Whennot matched then
Insert
Values (cpai. opid, cpai. accountid, cpai. groupid, cpai. ideaid, cpai. keyid,
Cpai. checkstatus, cpai. createdate, cpai. refusereason, cpai. adminuserid, cpai. adminusername,
Cpai. auditreason, cpai. backupideaid)

 

For applications, there is no need for two SQL statements, one being duplicated and one being inserted. You only need to deal with one preparedstatement SQL statement.

/* Formatted on 2011-8-2 18:54:00 (qp5 v5.114.809.3010 )*/
Merge into CPC. cpcpartneraudit CPA
Using (select
? As opid ,? As accountid ,? As groupid ,? As ideaid ,? As keyid ,? As
Checkstatus ,? As createdate ,? As refusereason ,? As adminuserid ,? As
Adminusername ,? As auditreason ,? As backupideaid from dual) cpai
On (CPA. keyid = cpai. keyid and CPA. ideaid = cpai. ideaid)
Whennot matched then
Insert
Values (cpai. opid, cpai. accountid, cpai. groupid, cpai. ideaid, cpai. keyid,
Cpai. checkstatus, cpai. createdate, cpai. refusereason, cpai. adminuserid, cpai. adminusername,
Cpai. auditreason, cpai. backupideaid)

 

 

 

Statement:

1. This network overhead and execution plan overhead of statement exist.

db_conn = DriverManager.getConnection("jdbc:oracle:thin:" + s1);

db_stmt = db_conn.createStatement();

db_rset = db_stmt.executeQuery(s1);

db_stmt.close();

db_rset.close();

db_conn.close();

 

2. Statement may be SQL injection.

Example: You have a query condition string query = "select * From t_location where id = '" + user_wanna_see_this_location + "'"; where user_wanna_see_this_location is submitted by user. http://daodao.com/example? User_wanna_see_this_location = 2011
Someone submitted comment 'or true; drop table t_location; select * From t_location where ''='
Always use parameterized SQL statement statements string query = "select * From t_location where id =? ";

 

3. The prestatedstatement execution is slower than the one-time execution. If the statement is executed only once, the overhead of the statement is smaller than that of the preparedstatement operation.

4. statement the SQL statement is printed every time for debugging.

 

-------------------------------------------

Appendix: SQL Injection:
When processing data from users on a public web site, security issues become extremely important. The string parameter passed to preparedstatement is automatically ignored by the drive. In the simplest case, this means that when your program tries to insert the string "D 'Angelo" into varchar2, this statement will not recognize the first ",", this leads to a miserable failure. There is almost no need to create your own strings to ignore the code.

In the web environment, malicious users use applications that are poorly designed and cannot correctly process strings. Especially on public websites, all user input should not be passed to SQL statements without preparedstatement object processing. In addition, SQL statements should not be displayed when users have the opportunity to modify SQL statements, such as HTML hidden areas or a query string.
When executing SQL commands, we have two options: You can use the preparedstatement object or the statement object. No matter how many times you use the same SQL command, preparedstatement only parses and compiles it once. When a statement object is used, it is parsed and compiled every time an SQL command is executed.