溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇文章給大家分享的是有關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);
}<!--more-->
/**
* @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 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;
}
}從代碼里可以看到，服務(代碼里)緩存了解析編譯的語句，如果有直接拿來用。
<!-- jdbc的代碼里有解析sql并且編譯，然后把編譯好的發給服務端，以后每次發送對應的id以及占位符的參數，這樣免得服務器每次解析sql，而且不用每次把整個語句以及后面的參數發送給服務端，只需要發送預編譯后的預計的id以及占位參數即可，減少了服務端編譯，減少了網絡傳輸，提高了效率。 -->
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語句，沒有做預編譯處理
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的示例分析，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
