关于JDBC的PreparedStatement,Statement和Statement Cache

下边这两篇文章写的比较深入。里边的例子运行了一下，都正确。

https://blog.csdn.net/Marvel__Dead/article/details/69486947

https://www.iteye.com/blog/cs-css-1847772

想补充的是：

对于mysql看起来开启预编译与不开启效率差不多，这样在mysql上使用PreparedStatement（带有占位符的sql）唯一好处就是防止sql注入了。

而对于预编译的sql的缓存，Oracle数据库做法不一样。Oracle数据库有共享池(Share Pool)，编译好的（或者说解析，包括生成执行计划等）SQL语句会放在Share Pool中，会在多个Session(即JDBC的Connection，数据库那边叫session)中共享，而MySql通过上边的实验发现只是在单个的Connection，并且是同一个PreparedStatement对象中共享。如果PreparedStatement 调用close()方法关闭，MySQL数据库会Deallocate这个prepare 语句。(这跟MySQL的官方文档说法有点出入，MySQL官方文档说的是，prepared的语句，是在connection范围内共享的，只要connection不关，prepared的语句在服务器端就会保留。如果connection关了，prepared语句会被自动deallocated. 具体见下边的原文链接：)

另外之前跟Oracle数据库的专家聊过，他说客户端尽量使用变量绑定，会大大提高效率。对于使用变量绑定的语句，Oracle数据库会缓存。(有待验证)

Oracle JDBC文档

https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Overview of Prepared Statements

Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement, that you already know.

If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.

MySQL文档：

https://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html

A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically.

A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends.

To guard against too many prepared statements being created simultaneously, set the max_prepared_stmt_count system variable. To prevent the use of prepared statements, set the value to 0.