PreparedStatement如何在Java内部运行:编译和amp;缓存SQL作品 [英] How PreparedStatement Works Internally in Java: How compilation & caching of SQL works

问题描述

我在互联网上阅读了有关此内容的信息，但无法获得某些查询的答案.

I read about it in the internet but I am not able to get answer for some queries.

Q1.据说，当执行PreparedStatement时，DBMS可以只运行PreparedStatement SQL语句而不必先对其进行编译.我的问题是何时调用prepareStetement.execute()会发生什么? (我的理解是:不带任何参数的sql发送到DBMS，由DBMS编译并缓存以供将来使用.然后将参数发送并由DBMS替换为占位符并执行.)

Q1. It is said that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first. My question is when preparedStetement.execute() is called what happens? (My understanding: The sql without any parameter is sent to DBMS which is compiled & cached by the DBMS for future use. Then the parameters are sent & DBMS replace them with the place holder & execute.)

第二季度.如果下一次我使用相同的sql执行prepareStetement.execute()，那会发生什么? (我的理解:DBMS将sql与先前的sql比较，如果匹配则采用已编译的sql，请替换参数& execute.)

Q2. If next time I execute preparedStetement.execute() with the same sql then what happens? (My understanding: The DBMS compares the sql with the previous sql & if it matches it takes the compiled sql, replace parameters & execute.)

第3季度.如果我偶尔调用数据库，那么由于在此期间将清除数据库缓存，因此prepared语句对性能提高没有帮助.因此，sql将每次都被编译.对吧?

Q3. If I call database occasionally then prepared statement won't help me in performance improvement because the database cache will be cleared during that time. So the sql will be compiled every-time. Right?

推荐答案

preparedStatement需要DBMS的支持.

preparedStatement need the support from DBMS.

如果已编译sql，则数据库将对其进行缓存.当同一个再次出现时，只需发送参数即可完成缓存的sql.

if the sql has been compiled, the DB will cache it. When the same one appears again, just send the parameters to complete the cached sql.

prepareStatement具有三个优点:

1. 使代码更清晰，以便您更轻松地阅读.

1. make the code more clearly so you can read it easier.

尽可能提高性能.缩短编译时间.

improve the performance as far as possible. redue the compile time.

最重要的是，它使sql更安全.如果您的sql如下所示:

most important, it makes the sql more secure. if your sql is like this below:

String sql = "select * from users where userid = " + userid; // use statement

有人给它一个

userid = "1;delete users;";

该语句将以

"select * from users where userid=1;"
"delete users;"

如果操作员确实有权这样做，对数据库来说是非常危险的操作.

it is a very dangerouse operation for a database if the operator really has the right to do this.

如果我们使用preparestatement

String sql = "select * from users where userid = ?"; // use preparestatement

数据库将把sql编译为"select * from users where userid = '?'"，然后等待参数?"，这意味着sql将像这样被执行

the database will compile the sql as "select * from users where userid = '?'" and wait for the parameter "?" which means the sql will be execute like this

"select * from users where userid = '1;delete users;'  ;" // of course, it will select 0 column. 

像对待字符串一样处理参数. 这是Interface java.sql.Connection类中的注释.阅读

treat the parameter just like a string. This is the Note in the Interface java.sql.Connection Class. read it

/**
 * Creates a <code>PreparedStatement</code> object for sending
 * parameterized SQL statements to the database.
 * <P>
 * A SQL statement with or without IN parameters can be
 * pre-compiled and stored in a <code>PreparedStatement</code> object. This
 * object can then be used to efficiently execute this statement
 * multiple times.
 *
 * <P><B>Note:</B> This method is optimized for handling
 * parametric SQL statements that benefit from precompilation. If
 * the driver supports precompilation,
 * the method <code>prepareStatement</code> will send
 * the statement to the database for precompilation. Some drivers
 * may not support precompilation. In this case, the statement may
 * not be sent to the database until the <code>PreparedStatement</code>
 * object is executed.  This has no direct effect on users; however, it does
 * affect which methods throw certain <code>SQLException</code> objects.
 * <P>
 * Result sets created using the returned <code>PreparedStatement</code>
 * object will by default be type <code>TYPE_FORWARD_ONLY</code>
 * and have a concurrency level of <code>CONCUR_READ_ONLY</code>.
 * The holdability of the created result sets can be determined by
 * calling {@link #getHoldability}.
 *
 * @param sql an SQL statement that may contain one or more '?' IN
 * parameter placeholders
 * @return a new default <code>PreparedStatement</code> object containing the
 * pre-compiled SQL statement
 * @exception SQLException if a database access error occurs
 * or this method is called on a closed connection
 */
PreparedStatement prepareStatement(String sql)
    throws SQLException;

这篇关于PreparedStatement如何在Java内部运行:编译和amp;缓存SQL作品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！