java-jdbc-prepared-statement

在写neo4j和orientdb的通用方法时,忽然想到jdbc,然后就想试试mysql neo4j orientdb几个数据库jdbc连接方式里的 prepartdStatement一不一样。

问题的来源来自以下代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
/**
* @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

在命令行执行以下语句

1
2
3
4
5
6
7
8
9
10
11
12
13
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输出如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

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

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
* 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
* 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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