快上网专注成都网站设计 成都网站制作 成都网站建设
成都网站建设公司服务热线:028-86922220

网站建设知识

十年网站开发经验 + 多家企业客户 + 靠谱的建站团队

量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决

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


文章标题:java-jdbc-prepared-statement的示例分析
网站链接:http://6mz.cn/article/pedcoi.html

其他资讯