本篇文章涉及内容比较多，单就Hibernate来讲就很大，再加上数据库驱动和数据库相关，非一篇文章或一篇专题就能说得完。本文从使用入手在【Spring实战】----Spring4.3.2集成Hibernate5.2.5基础上继续深入研究。本文包含以下内容：SQL语句在数据库中的执行过程、JDBC、PreparedStatement、Hibernate参数绑定
代码托管地址：https://github.com/honghailiang/SpringMango
MySQL数据库的使用（本文用到了操作日志的开启和查看）可参考https://dev.mysql.com/doc/refman/5.7/en/，MySQL数据库驱动预编译的配置可参考https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html
1、SQL语句在数据库中的执行过程（大概）
在一般关系型数据库系统架构下（如：Oracle、MySQL等），SQL语句由用户进程产生，然后传到相对应的服务端进程，之后由服务器进程执行该SQL语句。服务器进程处理SQL语句的基本阶段是：解析、参数绑定、执行、返回结果。
1）解析
服务器进程接收到一个SQL语句时，首先要将其转换成执行这个SQL语句的最有效步骤，这些步骤被称为执行计划。
Step 1：检查共享池中是否有之前解析相同的SQL语句后所存储的SQL文本、解析树和执行计划。如果能从共享池的缓存库中找到之前解析过生成的执行计划，则SQL语句则不需要再次解析，便可以直接由库缓存得到之前所产生的执行计划，从而直接跳到绑定或执行阶段，这种解析称作软解析。
但是如果在共享池的库缓存中找不到对应的执行计划，则必须继续解析SQL、生成执行计划，这种解析称作硬解析
Step 2：语法分析，分析SQL语句的语法是否符合规范，衡量语句中各表达式的意义
Step 3：检查是否存在语义错误和权限。语义分析，检查语句中设计的所有数据库对象是否存在，且用户有相应的权限。
Step 4：视图转换和表达式转换 将涉及视图的查询语句转换为相应的对基表查询语句。将复杂表达式转化较为简单的等效连接表达式。
Step 5：决定最佳执行计划。优化器会生成多个执行计划，在按统计信息带入，找出执行成本最小的执行计划，作为执行此SQL语句的执行计划
Step 6：将SQL文本、解析树、执行计划缓存到库缓存，存放地址以及SQL语句的哈希值。
2）参数绑定
如果SQL语句中使用了绑定变量，扫描绑定变量的声明，给绑定变量赋值。则此时将变量值带入执行计划。
3）执行
此阶段按照执行计划执行SQL，产生执行结果。不同类型的SQL语句，执行过程也不同。
SELECT查询
检查所需的数据块是否已经在缓冲区缓存中，如果已经在缓冲区缓存中，直接读取器内容即可。这种读取方式称为逻辑读取。如果所需数据不在缓冲区缓存中，则服务器进程需要先扫描数据块，读取相应数据块到缓冲区缓存，这种读取方式称为物理读。和逻辑读相比较，它更加耗费CPU和IO资源。
修改操作（INSERT、UPDATE、DELETE）
将需要修改或删除的行锁住，以便在事务结束之前相同的行不会被其他进程修改。
4）返回结果
对于select语句，在执行阶段，要将查询到的结果（或被标示的行）返回给用户进程。加入查询结果需要排序，还要利用共享池的排序区，甚至临时表空间的临时段来排序。查询结果总是以列表格式显示。根据查询结果的大小不同，可以一次全部返回，也可以分多次逐步返回。对于其他DML语句，将执行是否成功等状态细心返回给用户进程。
对于select语句，在执行阶段，要将查询到的结果（或被标示的行）返回给用户进程。加入查询结果需要排序，还要利用共享池的排序区，甚至临时表空间的临时段来排序。查询结果总是以列表格式显示。根据查询结果的大小不同，可以一次全部返回，也可以分多次逐步返回。对于其他DML语句，将执行是否成功等状态细心返回给用户进程。
2、JDBC
怎么用Java程序连接数据库，并执行sql操作。JDBC粉墨登场，JDBC是Java数据库连接（Java Database Connectivity）的缩写，而现在是指一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成，其具体实现由各数据库驱动实现。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。其典型应用如下：
public static void JDBCExample(){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/hhl?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=2048&characterEncoding=utf8&useSSL=false",
"root", "123456");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT p.productId FROM product p WHERE p.productName='Mango'");
while (resultSet.next()){
System.out.println(resultSet.getString(1));
}
resultSet.close();
statement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
可见，Java访问数据库，是要依赖数据库驱动的，这里是mysql Driver。JDBC只是提供了如Connettion、Statement、ResultSet等接口，其具体实现是由mysql Driver实现的。其DriverManager.getConnection()也是最终调用的driver的connect()。如下;java.sql.DriverManager.java
// Worker method called by the public getConnection() methods.
private static Connection getConnection(
String url, java.util.Properties info, Class<?> caller) throws SQLException {
/*
* When callerCl is null, we should check the application's
* (which is invoking this class indirectly)
* classloader, so that the JDBC driver class outside rt.jar
* can be loaded from here.
*/
ClassLoader callerCL = caller != null ? caller.getClassLoader() : null;
synchronized(DriverManager.class) {
// synchronize loading of the correct classloader.
if (callerCL == null) {
callerCL = Thread.currentThread().getContextClassLoader();
}
}
if(url == null) {
throw new SQLException("The url cannot be null", "08001");
}
println("DriverManager.getConnection(\"" + url + "\")");
// Walk through the loaded registeredDrivers attempting to make a connection.
// Remember the first exception that gets raised so we can reraise it.
SQLException reason = null;
for(DriverInfo aDriver : registeredDrivers) {
// If the caller does not have permission to load the driver then
// skip it.
if(isDriverAllowed(aDriver.driver, callerCL)) {
try {
println(" trying " + aDriver.driver.getClass().getName());
Connection con = aDriver.driver.connect(url, info); //最终connect
if (con != null) {
// Success!
println("getConnection returning " + aDriver.driver.getClass().getName());
return (con);
}
} catch (SQLException ex) {
if (reason == null) {
reason = ex;
}
}
} else {
println(" skipping: " + aDriver.getClass().getName());
}
}
// if we got here nobody could connect.
if (reason != null) {
println("getConnection failed: " + reason);
throw reason;
}
println("getConnection: no suitable driver found for "+ url);
throw new SQLException("No suitable driver found for "+ url, "08001");
}
那么这里的driver什么时候注册的呢，就是在执行Class.forName("com.mysql.jdbc.Driver");时(从JDBC4开始支持spi，不用显式调用Class.forName("")，直接丢啊哦用DriverManager.getConnection(url)即可，加载DriverManager时会执行静态代码块，加载驱动并注册)，
java.sql.DriverManager.java
/**
* Load the initial JDBC drivers by checking the System property
* jdbc.properties and then use the {@code ServiceLoader} mechanism
*/
static {
loadInitialDrivers();
println("JDBC DriverManager initialized");
}
加载Driver时，会向DriverManager注册Driver,com.mysql.jdbc.Driver.java
// Register ourselves with the DriverManager
//
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
尽管JDBC将接口统一化了，但是如果用JDBC操作数据库，还是需要写sql语句，sql语句针对不同的数据库也会不同，为了更好地实现跨数据库操作，于是诞生了Hibernate项目，Hibernate是对JDBC的再封装，实现了对数据库操作更宽泛的统一和更好的可移植性。因此Hibernate也是建立在JDBC的基础上的，JDBC又是通过数据库driver操作数据库的。
为了更好地实现跨数据库操作，于是诞生了Hibernate项目，Hibernate是对JDBC的再封装，实现了对数据库操作更宽泛的统一和更好的可移植性。
3、PreparedStatement
PreparedStatement也是JDBC提供的接口，其实现也是在driver中的，典型用法如下：
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM product p WHERE p.productName=?");
preparedStatement.setString(1,"Mango");
ResultSet resultSet = preparedStatement.executeQuery();
String productId;
while (resultSet.next()){
productId = (String) resultSet.getObject(1);
System.out.println(productId);
}
resultSet.close();
preparedStatement.close();
connection.close();
通过调用connection.preparedStatement(sql)方法可以获得PreparedStatment对象。数据库系统会对sql语句进行预编译处理（如果JDBC驱动支持的话），预处理语句将被预先编译好，这条预编译的sql查询语句能在将来的操作中重用，这样一来，它比Statement对象生成的查询速度更快。这里所说的预编译就是1中的sql执行过程中的解析。
wiki中对使用preparedstatement的流程如下说明：https://en.wikipedia.org/wiki/Prepared_statement
In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution. The typical workflow of using a prepared statement is as follows: 1.Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below): INSERT INTO PRODUCT (name, price) VALUES (?, ?) 2.The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it. 3.Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and '1.00' for the second parameter.
可以看出，使用preparedStatement的典型工作流程有三步：1）准备：应用将有占位符或绑定变量的sql statement发送给数据库管理系统。2）数据库管理系统解析、编译、和优化sql statement并将结果（执行计划）缓存。3）执行：应用提供绑定参数的值，由DBMS执行。
以上三步中的1）和3）的提供绑定参数的值都是由JDBC做的，代码操作就是如下
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM product p WHERE p.productName=?"); //prepare
preparedStatement.setString(1,"Mango");
ResultSet resultSet = preparedStatement.executeQuery();
而JDBC只是提供接口，实现是在数据库驱动中做的，而数据库驱动必须支持预编译才行（MySql需要开启）。不支持预编译SQL查询的JDBC驱动，在调用connection.prepareStatement(sql)的时候，它不会把SQL查询语句发送给数据库做预处理，而是等到执行查询动作的时候（调用executeQuery()方法时）才把查询语句发送个数据库，这种情况和使用Statement是一样的。
4、MySQL驱动开启预编译
就以mysql driver分析原代码：
com.mysql.jdbc.ConnectionImpl.java
/**
* A SQL statement with or without IN parameters can be pre-compiled and
* stored in a PreparedStatement 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. In
* this case, the statement is not sent to the database until the PreparedStatement is executed. This has no direct effect on users; however it does affect
* which method throws certain java.sql.SQLExceptions
* </p>
* <p>
* MySQL does not support precompilation of statements, so they are handled by the driver.
* </p>
*
* @param sql
* a SQL statement that may contain one or more '?' IN parameter
* placeholders
* @return a new PreparedStatement object containing the pre-compiled
* statement.
* @exception SQLException
* if a database access error occurs.
*/
public java.sql.PreparedStatement prepareStatement(String sql) throws SQLException {
return prepareStatement(sql, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY);
}
从注释可以看出预编译的意义，wiki给出预编译的优点
As compared to executing SQL statements directly, prepared statements offer two main advantages:[1] 1）The overhead of compiling and optimizing the statement is incurred only once, although the statement is executed multiple times. Not all optimization can be performed at the time the prepared statement is compiled, for two reasons: the best plan may depend on the specific values of the parameters, and the best plan may change as tables and indexes change over time.[2] 2）Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.
可以看出最主要的有两点1）一次预编译，多次执行。2）防止sql注入。
继续看prepareStatement
public java.sql.PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
synchronized (getConnectionMutex()) {
checkClosed();
//
// FIXME: Create warnings if can't create results of the given type or concurrency
//
PreparedStatement pStmt = null;
boolean canServerPrepare = true;
String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql) : sql;
if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {
canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
}
if (this.useServerPreparedStmts && canServerPrepare) {
if (this.getCachePreparedStatements()) {
synchronized (this.serverSideStatementCache) {
pStmt = (com.mysql.jdbc.ServerPreparedStatement) this.serverSideStatementCache.remove(sql);
if (pStmt != null) {
((com.mysql.jdbc.ServerPreparedStatement) pStmt).setClosed(false);
pStmt.clearParameters();
}
if (pStmt == null) {
try {
pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType,
resultSetConcurrency);
if (sql.length() < getPreparedStatementCacheSqlLimit()) {
((com.mysql.jdbc.ServerPreparedStatement) pStmt).isCached = true;
}
pStmt.setResultSetType(resultSetType);
pStmt.setResultSetConcurrency(resultSetConcurrency);
} catch (SQLException sqlEx) {
// Punt, if necessary
if (getEmulateUnsupportedPstmts()) {
pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
if (sql.length() < getPreparedStatementCacheSqlLimit()) {
this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
}
} else {
throw sqlEx;
}
}
}
}
} else {
try {
pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);
pStmt.setResultSetType(resultSetType);
pStmt.setResultSetConcurrency(resultSetConcurrency);
} catch (SQLException sqlEx) {
// Punt, if necessary
if (getEmulateUnsupportedPstmts()) {
pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
} else {
throw sqlEx;
}
}
}
} else {
pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
}
return pStmt;
}
}
首先根据是否开启预编译创建ServerPreparedStatement还是clientPrepareStatement判定逻辑是基于“useServerPreparedStmts”、“canServerPrepare”这两个参数决定的，而“useServerPreparedStmts”我们可以将对应的参数设置为true即可。但是还需要取决于canHandleAsServerPreparedStatement(String sql)，可以看出并不是所有的sql都会预编译，首先只考虑“SELECT、UPDATE、DELETE、INSERT、REPLACE”几种语法规则，也就是如果不是这几种就直接返回false了。另外会对参数Limit后面7位做一个判定是否有逗号、?这些符号，如果有这些就返回false了。
对于本例中的sql在url中加入
jdbc.url=jdbc:mysql://${host}:3306/hhl?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=2048&characterEncoding=utf8&useSSL=false
实际上开启预编译返回的PreparedStatement是com.mysql.jdbc.JDBC42ServerPreparedStatement，而未开启预编译返回的是com.mysql.jdbc.JDBC42PreparedStatement。
其中还有缓存个数及缓存sql的长度，默认25个及256（本例设置2048）长，超过这些个数或长度就不会缓存预编译statement，还需要再预编译一次。对比如下：
不开启预编译的mysql中的操作日志：
2017-05-09T01:03:44.863573Z 2 Query SELECT * FROM product p WHERE p.productName='Mango'
开启预编译的mysql中的操作日志
2017-05-09T08:32:27.518405Z 17 Prepare SELECT * FROM product p WHERE p.productName=? 2017-05-09T08:32:27.538406Z 17 Execute SELECT * FROM product p WHERE p.productName='Mango' 2017-05-09T08:33:22.339540Z 17 Execute SELECT * FROM product p WHERE p.productName='Mango'
private void serverPrepare(String sql) throws SQLException {
synchronized (checkClosed().getConnectionMutex()) {
MysqlIO mysql = this.connection.getIO();
if (this.connection.getAutoGenerateTestcaseScript()) {
dumpPrepareForTestcase();
}
try {
long begin = 0;
if (StringUtils.startsWithIgnoreCaseAndWs(sql, "LOAD DATA")) {
this.isLoadDataQuery = true;
} else {
this.isLoadDataQuery = false;
}
if (this.connection.getProfileSql()) {
begin = System.currentTimeMillis();
}
String characterEncoding = null;
String connectionEncoding = this.connection.getEncoding();
if (!this.isLoadDataQuery && this.connection.getUseUnicode() && (connectionEncoding != null)) {
characterEncoding = connectionEncoding;
}
Buffer prepareResultPacket = mysql.sendCommand(MysqlDefs.COM_PREPARE, sql, null, false, characterEncoding, 0);
if (this.connection.versionMeetsMinimum(4, 1, 1)) {
// 4.1.1 and newer use the first byte as an 'ok' or 'error' flag, so move the buffer pointer past it to start reading the statement id.
prepareResultPacket.setPosition(1);
} else {
// 4.1.0 doesn't use the first byte as an 'ok' or 'error' flag
prepareResultPacket.setPosition(0);
}
this.serverStatementId = prepareResultPacket.readLong();
this.fieldCount = prepareResultPacket.readInt();
this.parameterCount = prepareResultPacket.readInt();
this.parameterBindings = new BindValue[this.parameterCount];
for (int i = 0; i < this.parameterCount; i++) {
this.parameterBindings[i] = new BindValue();
}
this.connection.incrementNumberOfPrepares();
if (this.profileSQL) {
this.eventSink.consumeEvent(new ProfilerEvent(ProfilerEvent.TYPE_PREPARE, "", this.currentCatalog, this.connectionId, this.statementId, -1,
System.currentTimeMillis(), mysql.getCurrentTimeNanosOrMillis() - begin, mysql.getQueryTimingUnits(), null, LogUtils
.findCallingClassAndMethod(new Throwable()), truncateQueryToLog(sql)));
}
if (this.parameterCount > 0) {
if (this.connection.versionMeetsMinimum(4, 1, 2) && !mysql.isVersion(5, 0, 0)) {
this.parameterFields = new Field[this.parameterCount];
Buffer metaDataPacket = mysql.readPacket();
int i = 0;
while (!metaDataPacket.isLastDataPacket() && (i < this.parameterCount)) {
this.parameterFields[i++] = mysql.unpackField(metaDataPacket, false);
metaDataPacket = mysql.readPacket();
}
}
}
if (this.fieldCount > 0) {
this.resultFields = new Field[this.fieldCount];
Buffer fieldPacket = mysql.readPacket();
int i = 0;
// Read in the result set column information
while (!fieldPacket.isLastDataPacket() && (i < this.fieldCount)) {
this.resultFields[i++] = mysql.unpackField(fieldPacket, false);
fieldPacket = mysql.readPacket();
}
}
} catch (SQLException sqlEx) {
if (this.connection.getDumpQueriesOnException()) {
StringBuilder messageBuf = new StringBuilder(this.originalSql.length() + 32);
messageBuf.append("\n\nQuery being prepared when exception was thrown:\n\n");
messageBuf.append(this.originalSql);
sqlEx = ConnectionImpl.appendMessageToException(sqlEx, messageBuf.toString(), getExceptionInterceptor());
}
throw sqlEx;
} finally {
// Leave the I/O channel in a known state...there might be packets out there that we're not interested in
this.connection.getIO().clearInputStream();
}
}
}
最终将sql发送给数据库管理系统进行prepare操作。
小结：1）根据是否开启预编译和对该sql是否预编译决定不同的preparedstatement操作，预编译并保存到缓存中（没有超过缓存个数和长度） 2）后面操作类似sql时会从缓存中取出preparedstatement，如果缓存中没有则再发送给数据库管理系统预编译，也就是会执行多个prepare（数据库日志中可以看出）
2017-05-10T06:22:21.552185Z 28 Prepare SELECT * FROM product p WHERE p.productName=? 2017-05-10T06:25:18.132574Z 28 Execute SELECT * FROM product p WHERE p.productName='Mango' 2017-05-10T06:25:19.222636Z 26 Prepare SELECT * FROM product p WHERE p.productName=? 2017-05-10T06:25:21.599731Z 26 Execute SELECT * FROM product p WHERE p.productName='Mango'
什么时候缓存的，就是在调用preparedstatement.close()的时候com.mysql.jdbc.ServerPreparedStatement.java
/**
* @see java.sql.Statement#close()
*/
@Override
public void close() throws SQLException {
MySQLConnection locallyScopedConn = this.connection;
if (locallyScopedConn == null) {
return; // already closed
}
synchronized (locallyScopedConn.getConnectionMutex()) {
if (this.isCached && !this.isClosed) {
clearParameters();
this.isClosed = true;
this.connection.recachePreparedStatement(this); //缓存
return;
}
realClose(true, true);
}
}
可见缓存是缓存到连接缓存中的。如果连接关闭或者重新建立数据库连接，那么缓存失效，比如下面操作：
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/hhl?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=2048&characterEncoding=utf8&useSSL=false",
"root", "123456");
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM product p WHERE p.productName=?");
preparedStatement.setString(1,"'Mango'");
ResultSet resultSet = preparedStatement.executeQuery();
String productId;
while (resultSet.next()){
productId = (String) resultSet.getObject(1);
System.out.println(productId);
}
resultSet.close();
preparedStatement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
每次都会重新建立数据库连接，那么每次都会预编译，日志如下：
2017-05-11T02:03:11.344672Z 8 Connect root@localhost on hhl using TCP/IP 2017-05-11T02:03:11.344672Z 8 Query /* mysql-connector-java-5.1.38 ( Revision: fe541c166cec739c74cc727c5da96c1028b4834a ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout 2017-05-11T02:03:11.344672Z 8 Query SET NAMES utf8 2017-05-11T02:03:11.344672Z 8 Query SET character_set_results = NULL 2017-05-11T02:03:11.344672Z 8 Query SET autocommit=1 2017-05-11T02:03:14.495877Z 8 Prepare SELECT * FROM product p WHERE p.productName=? 2017-05-11T02:03:17.116682Z 8 Execute SELECT * FROM product p WHERE p.productName='\'Mango\'' 2017-05-11T02:03:18.349084Z 8 Quit 2017-05-11T02:03:25.841572Z 9 Connect root@localhost on hhl using TCP/IP 2017-05-11T02:03:25.842572Z 9 Query /* mysql-connector-java-5.1.38 ( Revision: fe541c166cec739c74cc727c5da96c1028b4834a ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout 2017-05-11T02:03:25.843572Z 9 Query SET NAMES utf8 2017-05-11T02:03:25.844572Z 9 Query SET character_set_results = NULL 2017-05-11T02:03:25.845572Z 9 Query SET autocommit=1 2017-05-11T02:03:32.180935Z 9 Prepare SELECT * FROM product p WHERE p.productName=? 2017-05-11T02:03:36.316171Z 9 Execute SELECT * FROM product p WHERE p.productName='\'Mango\'' 2017-05-11T02:03:37.224223Z 9 Quit
而采用连接池可以一定程度上避免出现这种情况。
2）可见如果不采用预编译则驱动会将sql拼装起来一次（preparedStatement.executeQuery()时）发送给数据库（由对应的mysql驱动com.mysql.jdbc.JDBC42PreparedStatement实现，发送的是SELECT * FROM product p WHERE p.productName='\'Mango\''包），而采用预编译的会和数据库交互两次，第一次是（connection.prepareStatement("SELECT * FROM product p WHERE p.productName=?")时）发送sql进行预编译（发送的是SELECT * FROM product p WHERE p.productName=?包），第二次执行的时候会（preparedStatement.executeQuery()）发送绑定参数（由对应的mysql驱动com.mysql.jdbc.JDBC42ServerPreparedStatement实现，发送的是'Mango'包，而没有进行转义的，这个转义是在数据库管理系统中去做的。），如果单纯的一次查询则不预编译的效率要高，而且如果预编译不缓存的话下次还需要预编译。因此对于一次查询的建议不开启预编译，对已多次查询开启预编译的同时也要开启缓存。
预编译时那么数据库是怎么知道要执行哪条sql语句的，就在于发送包中的
/** The ID that the server uses to identify this PreparedStatement */
private long serverStatementId;
该值是发送预编译语句时数据库返回的
Buffer prepareResultPacket = mysql.sendCommand(MysqlDefs.COM_PREPARE, sql, null, false, characterEncoding, 0);
this.serverStatementId = prepareResultPacket.readLong();
看完预编译，看下不采用预编译的prepared.setString()，
**
* Set a parameter to a Java String value. The driver converts this to a SQL
* VARCHAR or LONGVARCHAR value (depending on the arguments size relative to
* the driver's limits on VARCHARs) when it sends it to the database.
*
* @param parameterIndex
* the first parameter is 1...
* @param x
* the parameter value
*
* @exception SQLException
* if a database access error occurs
*/
public void setString(int parameterIndex, String x) throws SQLException {
synchronized (checkClosed().getConnectionMutex()) {
// if the passed string is null, then set this column to null
if (x == null) {
setNull(parameterIndex, Types.CHAR);
} else {
checkClosed();
int stringLength = x.length();
if (this.connection.isNoBackslashEscapesSet()) {
// Scan for any nasty chars
boolean needsHexEscape = isEscapeNeededForString(x, stringLength);
if (!needsHexEscape) {
byte[] parameterAsBytes = null;
StringBuilder quotedString = new StringBuilder(x.length() + 2);
quotedString.append('\'');
quotedString.append(x);
quotedString.append('\'');
if (!this.isLoadDataQuery) {
parameterAsBytes = StringUtils.getBytes(quotedString.toString(), this.charConverter, this.charEncoding,
this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
} else {
// Send with platform character encoding
parameterAsBytes = StringUtils.getBytes(quotedString.toString());
}
setInternal(parameterIndex, parameterAsBytes);
} else {
byte[] parameterAsBytes = null;
if (!this.isLoadDataQuery) {
parameterAsBytes = StringUtils.getBytes(x, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
this.connection.parserKnowsUnicode(), getExceptionInterceptor());
} else {
// Send with platform character encoding
parameterAsBytes = StringUtils.getBytes(x);
}
setBytes(parameterIndex, parameterAsBytes);
}
return;
}
String parameterAsString = x;
boolean needsQuoted = true;
if (this.isLoadDataQuery || isEscapeNeededForString(x, stringLength)) {
needsQuoted = false; // saves an allocation later
StringBuilder buf = new StringBuilder((int) (x.length() * 1.1));
buf.append('\'');
//
// Note: buf.append(char) is _faster_ than appending in blocks, because the block append requires a System.arraycopy().... go figure...
//
for (int i = 0; i < stringLength; ++i) {
char c = x.charAt(i);
switch (c) {
case 0: /* Must be escaped for 'mysql' */
buf.append('\\');
buf.append('0');
break;
case '\n': /* Must be escaped for logs */
buf.append('\\');
buf.append('n');
break;
case '\r':
buf.append('\\');
buf.append('r');
break;
case '\\':
buf.append('\\');
buf.append('\\');
break;
case '\'':
buf.append('\\');
buf.append('\'');
break;
case '"': /* Better safe than sorry */
if (this.usingAnsiMode) {
buf.append('\\');
}
buf.append('"');
break;
case '\032': /* This gives problems on Win32 */
buf.append('\\');
buf.append('Z');
break;
case '\u00a5':
case '\u20a9':
// escape characters interpreted as backslash by mysql
if (this.charsetEncoder != null) {
CharBuffer cbuf = CharBuffer.allocate(1);
ByteBuffer bbuf = ByteBuffer.allocate(1);
cbuf.put(c);
cbuf.position(0);
this.charsetEncoder.encode(cbuf, bbuf, true);
if (bbuf.get(0) == '\\') {
buf.append('\\');
}
}
// fall through
default:
buf.append(c);
}
}
buf.append('\'');
parameterAsString = buf.toString();
}
byte[] parameterAsBytes = null;
if (!this.isLoadDataQuery) {
if (needsQuoted) {
parameterAsBytes = StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charConverter, this.charEncoding,
this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
} else {
parameterAsBytes = StringUtils.getBytes(parameterAsString, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
this.connection.parserKnowsUnicode(), getExceptionInterceptor());
}
} else {
// Send with platform character encoding
parameterAsBytes = StringUtils.getBytes(parameterAsString);
}
setInternal(parameterIndex, parameterAsBytes);
this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.VARCHAR;
}
}
}
会对特殊字符进行转义，从而防止sql注入，如preparedStatement.setString(1,"'Mango'");经转义后变为\'Mango\'。转码工作是有JDBC驱动或者数据库进行的，而且无论是否开启预编译，只要使用PreparedStatement设置参数的形式，都会对sql进行转义，都会防止sql注入。
5、SQL注入
假如不用PreparedStatement进行参数设置，采用拼接的形式，如下
String sql = "SELECT * FROM product p WHERE p.productName='" + productName + "'";
如果用户没有输入一个名字而是输入
Mango' or 'Y' = 'Y
产生的语句就变成了
SELECT * FROM product p WHERE p.productName='Mango' or 'Y' = 'Y'
这样就改变了查询的本意，会返回整个product关系。更甚者可以编写输入值以输出更多的数据，使用preparedStatement可以防止这类问题，被转义后的语句变为如下：
SELECT * FROM product p WHERE p.productName='Mango\' or \'Y\' = \'Y'
这样就变成一个无害的语句
6、Hibernate参数绑定
Hibernate的实现也是基于JDBC的，而且采用的是PreparedStatement的方式，因此Hibernate的参数绑定具有PreparedStatement的所有优点。Hibernate参数绑定的方式有两种：利用具名或者是利用定位
1）具名方式
/* (non-Javadoc)
* @see com.mango.jtt.dao.MangoDao#list(java.lang.String)
*/
@Override
public List list(String querySql, Map<String, Object> map) {
Query<?> query = currentSession().createQuery(querySql);
if (map != null) {
for (String key : map.keySet()) {
if (querySql.indexOf(":" + key) != -1) {
query.setParameter(key, map.get(key));
}
}
}
return query.getResultList();
}
使用
/* (non-Javadoc)
* @see com.mango.jtt.service.ProductService#getProductList()
*/
@Override
public List<Product> getProductList() {
String sql = "from Product p where p.productName=:productName ";
Map<String, Object> map = new HashMap<String, Object>();
map.put("productName", "Mango");
return dao.list(sql, map);
}
2）定位参数方式
String sql = "from product p where p.productName=?"; Query query = session.creatQuery(sql).setparameter(0, "Mango");
同样参数绑定具有防止SQL注入的作用。
总结：
1）一般关系数据库（Oracle、MySQL）是支持预编译的，预编译sql操作意味着数据库系统不用再进行分析，直接从缓存中取出执行计划执行即可，提高效率
2）本文中的MySql是需要驱动端开启预编译功能的，否则默认不进行预编译，是否预编译由驱动控制，语句的预编译时在数据库系统中做的，并将预编译语句缓存在数据库缓存中；而应用中缓存的是对应的PrepareStatement，是缓存在数据库连接中的。
3）是否采用预编译对SQL注入没有影响，只要使用PreparedStatement设置参数方式操作数据库，则均能防止SQL注入