java-jdbc-prepared-statement
在写neo4j和orientdb的通用方法时，忽然想到jdbc，然后就想试试mysql neo4j orientdb几个数据库jdbc连接方式里的 prepartdStatement一不一样。
问题的来源来自以下代码
List<Map<String, Object>> list = new ArrayList();
try (PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();) {
List<String> fields = new ArrayList<>();
while (rs.next()) {
if (fields.isEmpty()) {
ResultSetMetaData metaData = rs.getMetaData();
// 查询出的字段
int count = metaData.getColumnCount();
for (int i = 1; i <= count; i++) {
fields.add(metaData.getColumnName(i));
}
}
Map<String, Object> map = new HashMap<>();
for (String field : fields) {
map.put(field, rs.getObject(field));
}
// T r = JSONObject.parseObject(JSON.toString(map), Object.class);
list.add(map);
}
} catch (SQLException e) {
throw new SQLException(e);
}/**
* @param sql 查询语句
* @param params 占位符 参数
* @param conn 连接
* @return
*/
@Override
public Iterator<Map<String, Object>> query(String sql, Map<Integer, Object> params,
Connection conn) throws SQLException {
//
final PreparedStatement statement = conn.prepareStatement(sql);
// 设置参数
setParameters(statement, params);
// 执行查询并获得结果
final ResultSet result = statement.executeQuery();
// 封装返回
return new Iterator<Map<String, Object>>() {
boolean hasNext = result.next();
// 所有字段
public List<String> columns;
// 字段个数
public int columnsCount;
/**
*
*
* @return
*/
@Override
public boolean hasNext() {
return hasNext;
}
/**
* 获得所有字段<br>
* 第一次会查询出所有字段，第二 第三次 直接用columns
*
* @return
* @throws SQLException
*/
private List<String> getColumns() throws SQLException {
if (columns != null) {
return columns;
}
ResultSetMetaData metaData = result.getMetaData();
// 查询出的字段
int count = metaData.getColumnCount();
List<String> cols = new ArrayList<>(count);
for (int i = 1; i <= count; i++) {
cols.add(metaData.getColumnName(i));
}
columnsCount = cols.size();
return columns = cols;
}
/**
*
* @return
*/
@Override
public Map<String, Object> next() {
try {
if (hasNext) {
//
Map<String, Object> map = new LinkedHashMap<>(columnsCount);
for (String col : getColumns()) {
map.put(col, result.getObject(col));
}
hasNext = result.next();
if (!hasNext) {
result.close();
statement.close();
}
return map;
} else {
throw new NoSuchElementException();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
*
*/
@Override
public void remove() {
}
};
} 然后查看对应的源代码
mysql-connector-java-5.1.40.jar
neo4j-jdbc-3.4.0.jar
orientdb-jdbc-3.0.22.jar
MySQL prepart 测试
MySQL client server prepart 测试
MySQL server 配置开启 all_query log
在命令行执行以下语句
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;
EXECUTE stmt1 USING @a, @b;
SET @a = 6;
SET @b = 8;
EXECUTE stmt1 USING @a, @b;
SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
PREPARE stmt2 FROM @s;
SET @a = 6;
SET @b = 8;
EXECUTE stmt2 USING @a, @b;all_query.log输出如下
2019-08-14T12:24:02.934322Z 1042 Query PREPARE stmt1 FROM ...
2019-08-14T12:24:02.934412Z 1042 Prepare SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse
2019-08-14T12:24:02.934762Z 1042 Query SET @a = 3
2019-08-14T12:24:02.935089Z 1042 Query SET @b = 4
2019-08-14T12:24:02.935404Z 1042 Query EXECUTE stmt1 USING @a, @b
2019-08-14T12:24:02.935449Z 1042 Execute SELECT SQRT(POW(3,2) + POW(4,2)) AS hypotenuse
2019-08-14T12:24:02.935949Z 1042 Query EXECUTE stmt1 USING @a, @b
2019-08-14T12:24:02.935994Z 1042 Execute SELECT SQRT(POW(3,2) + POW(4,2)) AS hypotenuse
2019-08-14T12:24:02.936388Z 1042 Query SET @a = 6
2019-08-14T12:24:02.936938Z 1042 Query SET @b = 8
2019-08-14T12:24:02.937319Z 1042 Query EXECUTE stmt1 USING @a, @b
2019-08-14T12:24:02.937358Z 1042 Execute SELECT SQRT(POW(6,2) + POW(8,2)) AS hypotenuse
2019-08-14T12:24:02.937791Z 1042 Query SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'
2019-08-14T12:24:02.938083Z 1042 Query PREPARE stmt2 FROM @s
2019-08-14T12:24:02.938187Z 1042 Prepare SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse
2019-08-14T12:24:02.938518Z 1042 Query SET @a = 6
2019-08-14T12:24:02.938804Z 1042 Query SET @b = 8
2019-08-14T12:24:02.939095Z 1042 Query EXECUTE stmt2 USING @a, @b
2019-08-14T12:24:02.939130Z 1042 Execute SELECT SQRT(POW(6,2) + POW(8,2)) AS hypotenuse 确实是使用了Prepare
不过从这个结果看不出Prepare提高了多少性能
通过程序测试Prepare大概提高了30%的性能，语句不同，参数不通，测试结果会有差异。
jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&useServerPrepStmts=true
com.mysql.jdbc.ConnectionImpl.java
public PreparedStatement prepareStatement(String sql) throws SQLException {
return this.prepareStatement(sql, 1003, 1007);
}
public PreparedStatement prepareStatement(String sql, int autoGenKeyIndex) throws SQLException {
PreparedStatement pStmt = this.prepareStatement(sql);
((com.mysql.jdbc.PreparedStatement)pStmt).setRetrieveGeneratedKeys(autoGenKeyIndex == 1);
return pStmt;
}
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
synchronized(this.getConnectionMutex()) {
this.checkClosed();
com.mysql.jdbc.PreparedStatement pStmt = null;
boolean canServerPrepare = true;
String nativeSql = this.getProcessEscapeCodesForPrepStmts() ? this.nativeSQL(sql) : sql;
if (this.useServerPreparedStmts && this.getEmulateUnsupportedPstmts()) {
canServerPrepare = this.canHandleAsServerPreparedStatement(nativeSql);
}
if (this.useServerPreparedStmts && canServerPrepare) {
// // 从缓存中获取 pst
if (this.getCachePreparedStatements()) {
synchronized(this.serverSideStatementCache) {
pStmt = (ServerPreparedStatement)this.serverSideStatementCache.remove(sql);
if (pStmt != null) {
((ServerPreparedStatement)pStmt).setClosed(false);
// 清理上次留下的参数
((com.mysql.jdbc.PreparedStatement)pStmt).clearParameters();
}
if (pStmt == null) {
// 向 Server 提交 SQL 预编译
try {
pStmt = ServerPreparedStatement.getInstance(this.getMultiHostSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);
if (sql.length() < this.getPreparedStatementCacheSqlLimit()) {
((ServerPreparedStatement)pStmt).isCached = true;
}
((com.mysql.jdbc.PreparedStatement)pStmt).setResultSetType(resultSetType);
((com.mysql.jdbc.PreparedStatement)pStmt).setResultSetConcurrency(resultSetConcurrency);
} catch (SQLException var13) {
if (!this.getEmulateUnsupportedPstmts()) {
throw var13;
}
pStmt = (com.mysql.jdbc.PreparedStatement)this.clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
if (sql.length() < this.getPreparedStatementCacheSqlLimit()) {
this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
}
}
}
}
} else {
// // 向 Server 提交 SQL 预编译
try {
pStmt = ServerPreparedStatement.getInstance(this.getMultiHostSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);
((com.mysql.jdbc.PreparedStatement)pStmt).setResultSetType(resultSetType);
((com.mysql.jdbc.PreparedStatement)pStmt).setResultSetConcurrency(resultSetConcurrency);
} catch (SQLException var12) {
if (!this.getEmulateUnsupportedPstmts()) {
throw var12;
}
pStmt = (com.mysql.jdbc.PreparedStatement)this.clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
}
}
} else {
pStmt = (com.mysql.jdbc.PreparedStatement)this.clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
}
return (PreparedStatement)pStmt;
}
}从代码里可以看到，服务(代码里)缓存了解析编译的语句，如果有直接拿来用。
Neo4j
Neo4j 连接方式 有 neo4j-jdbc-driver neo4j-jdbc-bolt neo4j-jdbc-http
org.neo4j.jdbc.Neo4jPreparedStatement.java
/**
* Default constructor with connection and statement.
*
* @param connection The JDBC connection
* @param rawStatement The prepared statement
*/
protected Neo4jPreparedStatement(Neo4jConnection connection, String rawStatement) {
super(connection);
this.statement = PreparedStatementBuilder.replacePlaceholders(rawStatement);
this.parametersNumber = PreparedStatementBuilder.namedParameterCount(statement);
this.parameters = new HashMap<>(this.parametersNumber);
this.batchParameters = new ArrayList<>();
}org.neo4j.jdbc.utils.PreparedStatementBuilder
/**
* This method return a String that is the original raw string with all valid placeholders replaced with neo4j curly brackets notation for parameters.
* <br>
* i.e. MATCH n RETURN n WHERE n.name = ? is transformed in MATCH n RETURN n WHERE n.name = {1}
*
* @param raw The string to be translated.
* @return The string with the placeholders replaced.
*/
public static String replacePlaceholders(String raw) {
int index = 1;
String digested = raw;
String regex = "\\?(?=[^\"]*(?:\"[^\"]*\"[^\"]*)*$)";
Matcher matcher = Pattern.compile(regex).matcher(digested);
while (matcher.find()) {
digested = digested.replaceFirst(regex, "{" + index + "}");
index++;
}
return digested;
}neo4j-jdbc 里对PreparedStatement里的语句仅仅是把占位符组装成一个cypher语句，没有做预编译处理
Orientdb
com.orientechnologies.orient.jdbc.OrientJdbcPreparedStatement.java
public OrientJdbcPreparedStatement(OrientJdbcConnection iConnection, String sql) {
this(iConnection, 1003, 1007, 1, sql);
}
public OrientJdbcPreparedStatement(OrientJdbcConnection iConnection, int resultSetType, int resultSetConcurrency, String sql) throws SQLException {
this(iConnection, resultSetType, resultSetConcurrency, 1, sql);
}
public OrientJdbcPreparedStatement(OrientJdbcConnection iConnection, int resultSetType, int resultSetConcurrency, int resultSetHoldability, String sql) {
super(iConnection, resultSetType, resultSetConcurrency, resultSetHoldability);
this.sql = sql;
this.params = new HashMap();
}
orientdb-jdbc jar包里没有对PreparedStatement的语句做预编译处理
References
[1] JDBC PreparedStatement 实现原理【推荐阅读】
[2] mysql-JDBC源码解析
[3] mysql sql-syntax-prepared-statements
[4] github mysql-connector-j
[5] github neo4j-jdbc
[6] neo4j-jdbc doc
[7] github orientdb-jdbc
[8] orientdb-docs