PreparedStatement是如何在Java中进行SQL的编译和缓存工作的?

https://stackoverflow.com/questions/41758436/how-preparedstatement-works-internally-in-java-how-compilation-caching-of-sql

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

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.)

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.)

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?

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

Possible duplicate of Difference between Statement and PreparedStatement – walen Jan 20 '17 at 7:47

答案1:

preparedStatement need the support from DBMS.

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 has three advantages:

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

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

  3. 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

    and someone give it a userid value like

    userid = "1;delete users;";

    the statement will execute the sql as

    "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.

if we use preparestatement

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

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. 

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;

The sql generated by your example should be "select * from users where userid=1=1;" and "delete users;" – Adrian Shum Jan 20 '17 at 8:26

  • @AdrianShum thanks. I've changed it. – stackoverflow Jan 20 '17 at 8:40

  • I know the sql injection part. Actually I am facing some performance issue using it & I solved it using sendStringParametersAsUnicode=false. Now I am trying to dig deeper to understand what is happening underline. But thanks anyway for the explanation :) – Sumit Jan 20 '17 at 8:42

  • Suppose I am writing PreparedStatement ps = connection.prepare("SOME SQL"); Is the sql compilation happening in connection.prepare("SOME SQL")` or it happens in DBMS or it depends on the jdbc driver – Sumit Jan 20 '17 at 8:53

  • @SumitPal it depends – stackoverflow Jan 20 '17 at 9:05

  • @SumitPal I have added much more details in the interface java.sql.Connection, it is the note for the method prepareStatement(String sql) – stackoverflow Jan 20 '17 at 9:14

  • Thanks a lot @stackoverflow. It is very helpful :) – Sumit Jan 20 '17 at 9:59

  • 1

    Note that for a lot of database systems prepared statements don't actually work like this: the values used on execute are sent separately from the query with placeholders. – Mark Rotteveel Jan 20 '17 at 10:58 

  • --------------- 

 

答案2

java.sql.PreparedStatement is API defined by JRE.

Actual internal behavior of execute() depends on JDBC driver implementation provided by DBMS vendor.

For example, there are embedded databases (SQLite, H2) where sending statement to servermakes no sense.

You should consult with documentation of JDBC driver you are using.

shareimprove this answer

answered Jan 20 '17 at 7:47

rkosegi

9,15655 gold badges3939 silver badges6565 bronze badges

答案3

Adding to rkosegi's answer above, I want to describe a bit more about how this works with the PostgreSQL driver because it highlights some of the difficulties here.

With the PostgreSQL driver (see documentation), the behavior is actually configurable and quite complex.

The PostgreSQL query protocol allows queries and parameters to be sent separately and so the PreparedStatement API does not need to go through the process of asking the database to prepare a statement and then execute it later. The tradeoff is that there is extra overhead with server-side preparations in the initial preparation and problems of when to re-use query plans, but this is off-set against shorter start-up time for query re-use. So if you are optimizing and you are repeatedly looking up a single record you want to prepare, while if you are running on variable ranges or not re-using statements, then you probably don't. This is configured on the database connection. This is usually set as a threshold regarding when you start caching plans after a certain amount of re-use.

When server-side prepare is not used the driver sends to the database the parameterized query and arguments separately in the same command. The server then parses the query, inserts the variables into the appropriate places, and plans. The plan is then discarded after it is run.

Then server-side prepare is used, it works as you describe, however.

So here is a hard example of why you cannot ask the question and get a definite answer. JDBC provides a programming interface and the drivers provide the details in how it works. prepareStatement does NOT define whether the query plan is cached or not. That is a decision up to the driver.

shareimprove this answer

answered Jan 20 '17 at 8:11

Chris Travers

19.9k66 gold badges4646 silver badges148148 bronze badges

  • Suppose I am writing PreparedStatement ps = connection.prepare("SOME SQL"); Is the sql compilation happening in connection.prepare("SOME SQL")` or it happens in DBMS or it depends on the jdbc driver. – Sumit Jan 20 '17 at 8:52

  • Well, if you are using the jdbc driver for csv files, I would assume it would happen in the jdbc driver. So yes, depends on the driver. – Chris Travers Jan 20 '17 at 9:00

 

 

 

 

转载于:https://my.oschina.net/u/244278/blog/3096097

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值