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

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

创新互联公司成都网站建设定制网站开发，是成都网站设计公司,为服务器托管提供网站建设服务,有成熟的网站定制合作流程，提供网站定制设计服务：原型图制作、网站创意设计、前端HTML5制作、后台程序开发等。成都网站制作热线：18982081108

在写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 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 连接方式 有 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的示例分析，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注创新互联行业资讯频道。