java-jdbc-prepared-statement

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);
}

/**
 * @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() {
        }
    };

}

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;

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

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;
    }
}

/**
 * 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<>();
}

/**
 * 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;
}

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();
}