十年网站开发经验 + 多家企业客户 + 靠谱的建站团队
量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决
本篇文章给大家分享的是有关java-jdbc-prepared-statement的示例分析,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。
创新互联公司成都网站建设定制网站开发,是成都网站设计公司,为服务器托管提供网站建设服务,有成熟的网站定制合作流程,提供网站定制设计服务:原型图制作、网站创意设计、前端HTML5制作、后台程序开发等。成都网站制作热线:18982081108
在写neo4j和orientdb的通用方法时,忽然想到jdbc,然后就想试试MySQL neo4j orientdb几个数据库jdbc连接方式里的 prepartdStatement一不一样。
问题的来源来自以下代码
List
/** * @param sql 查询语句 * @param params 占位符 参数 * @param conn 连接 * @return */ @Override public Iterator
然后查看对应的源代码 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; } }
从代码里可以看到,服务(代码里)缓存了解析编译的语句,如果有直接拿来用。
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语句,没有做预编译处理
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的示例分析,小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注创新互联行业资讯频道。