java-jdbc-prepared-statement的示例分析

本篇文章给大家分享的是有关java-jdbc-prepared-statement的示例分析，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

在写neo4j和orientdb的通用方法时，忽然想到jdbc，然后就想试试mysql neo4j orientdb几个数据库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 占位符 参数     * @param conn   连接     * @return     */    @Override    public Iterator> query(String sql, Map params,                                               Connection conn) throws SQLException {        //        final PreparedStatement statement = conn.prepareStatement(sql);        // 设置参数        setParameters(statement, params);        // 执行查询并获得结果        final ResultSet result = statement.executeQuery();        // 封装返回        return new Iterator>() {            boolean hasNext = result.next();            // 所有字段            public List columns;            // 字段个数            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

在命令行执行以下语句

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 hypotenuse2019-08-14T12:24:02.934762Z      1042 Query   SET @a = 32019-08-14T12:24:02.935089Z      1042 Query   SET @b = 42019-08-14T12:24:02.935404Z      1042 Query   EXECUTE stmt1 USING @a, @b2019-08-14T12:24:02.935449Z      1042 Execute SELECT SQRT(POW(3,2) + POW(4,2)) AS hypotenuse2019-08-14T12:24:02.935949Z      1042 Query   EXECUTE stmt1 USING @a, @b2019-08-14T12:24:02.935994Z      1042 Execute SELECT SQRT(POW(3,2) + POW(4,2)) AS hypotenuse2019-08-14T12:24:02.936388Z      1042 Query   SET @a = 62019-08-14T12:24:02.936938Z      1042 Query   SET @b = 82019-08-14T12:24:02.937319Z      1042 Query   EXECUTE stmt1 USING @a, @b2019-08-14T12:24:02.937358Z      1042 Execute SELECT SQRT(POW(6,2) + POW(8,2)) AS hypotenuse2019-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 @s2019-08-14T12:24:02.938187Z      1042 Prepare SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse2019-08-14T12:24:02.938518Z      1042 Query   SET @a = 62019-08-14T12:24:02.938804Z      1042 Query   SET @b = 82019-08-14T12:24:02.939095Z      1042 Query   EXECUTE stmt2 USING @a, @b2019-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.         * 
         * 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的示例分析，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。