本篇文章給大家分享的是有關(guān)java-jdbc-prepared-statement的示例分析，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。
創(chuàng)新互聯(lián)公司成都網(wǎng)站建設定制網(wǎng)站開發(fā)，是成都網(wǎng)站設計公司,為服務器托管提供網(wǎng)站建設服務,有成熟的網(wǎng)站定制合作流程，提供網(wǎng)站定制設計服務：原型圖制作、網(wǎng)站創(chuàng)意設計、前端HTML5制作、后臺程序開發(fā)等。成都網(wǎng)站制作熱線：18982081108
在寫neo4j和orientdb的通用方法時，忽然想到jdbc，然后就想試試MySQL neo4j orientdb幾個數(shù)據(jù)庫jdbc連接方式里的 prepartdStatement一不一樣。
問題的來源來自以下代碼
List> list = new ArrayList();
try (PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();) {
List 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 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 占位符 參數(shù)
* @param conn 連接
* @return
*/
@Override
public Iterator> query(String sql, Map params,
Connection conn) throws SQLException {
//
final PreparedStatement statement = conn.prepareStatement(sql);
// 設置參數(shù)
setParameters(statement, params);
// 執(zhí)行查詢并獲得結(jié)果
final ResultSet result = statement.executeQuery();
// 封裝返回
return new Iterator>() {
boolean hasNext = result.next();
// 所有字段
public List columns;
// 字段個數(shù)
public int columnsCount;
/**
*
*
* @return
*/
@Override
public boolean hasNext() {
return hasNext;
}
/**
* 獲得所有字段
* 第一次會查詢出所有字段，第二 第三次 直接用columns
*
* @return
* @throws SQLException
*/
private List getColumns() throws SQLException {
if (columns != null) {
return columns;
}
ResultSetMetaData metaData = result.getMetaData();
// 查詢出的字段
int count = metaData.getColumnCount();
List 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 next() {
try {
if (hasNext) {
//
Map 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
在命令行執(zhí)行以下語句
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 不過從這個結(jié)果看不出Prepare提高了多少性能 通過程序測試Prepare大概提高了30%的性能，語句不同，參數(shù)不通，測試結(jié)果會有差異。
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);
// 清理上次留下的參數(shù)
((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.
*
* 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的語句做預編譯處理
以上就是java-jdbc-prepared-statement的示例分析，小編相信有部分知識點可能是我們?nèi)粘９ぷ鲿姷交蛴玫降?。希望你能通過這篇文章學到更多知識。更多詳情敬請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
新聞名稱：java-jdbc-prepared-statement的示例分析
文章源于：
http://weahome.cn/article/pedcoi.html
重慶分公司
重慶分公司
