java - 在哪里使用 JDBC 创建准备好的语句？

Question

考虑以下方法，该方法从某些数据结构 (InteractionNetwork) 读取数据并使用 SQLite-JDBC dirver 将它们写入 SQLite 数据库中的表中:

private void loadAnnotations(InteractionNetwork network) throws SQLException {
    PreparedStatement insertAnnotationsQuery = 
        connection.prepareStatement(
        "INSERT INTO Annotations(GOId, ProteinId, OnthologyId) VALUES(?, ?, ?)");
    PreparedStatement getProteinIdQuery = 
        connection.prepareStatement(
        "SELECT Id FROM Proteins WHERE PrimaryUniProtKBAccessionNumber = ?");
    connection.setAutoCommit(false);
    for(common.Protein protein : network.get_protein_vector()) {
        /* Get ProteinId for the current protein from another table and
           insert the value into the prepared statement. */
        getProteinIdQuery.setString(1, protein.get_primary_id());
        ResultSet result = getProteinIdQuery.executeQuery();
        result.next();
        insertAnnotationsQuery.setLong(2, result.getLong(1));
        /* Extract all the other data and add all the tuples to the batch. */
    }
    insertAnnotationsQuery.executeBatch();
    connection.commit();
    connection.setAutoCommit(true);
}

这段代码运行良好，程序运行大约 30 秒，平均占用 80m 堆空间。因为代码看起来很难看，所以想重构一下。我做的第一件事是将 getProteinIdQuery 的声明移到循环中:

private void loadAnnotations(InteractionNetwork network) throws SQLException {
    PreparedStatement insertAnnotationsQuery = 
        connection.prepareStatement(
        "INSERT INTO Annotations(GOId, ProteinId, OnthologyId) VALUES(?, ?, ?)");
    connection.setAutoCommit(false);
    for(common.Protein protein : network.get_protein_vector()) {
        /* Get ProteinId for the current protein from another table and
           insert the value into the prepared statement. */
        PreparedStatement getProteinIdQuery = // <--- moved declaration of statement here
            connection.prepareStatement(
            "SELECT Id FROM Proteins WHERE PrimaryUniProtKBAccessionNumber = ?");
        getProteinIdQuery.setString(1, protein.get_primary_id());
        ResultSet result = getProteinIdQuery.executeQuery();
        result.next();
        insertAnnotationsQuery.setLong(2, result.getLong(1));
        /* Extract all the other data and add all the tuples to the batch. */
    }
    insertAnnotationsQuery.executeBatch();
    connection.commit();
    connection.setAutoCommit(true);
}

当我现在运行代码时会发生什么，它需要大约 130m 的堆空间并且需要一个永恒的时间来运行。谁能解释这种奇怪的行为？

Answer 1

正如您所发现的，准备声明需要时间。无论代码是否丑陋，速度的下降也非常丑陋，因此您需要使用更快的形式。

但是您可以做的是使用内部类来保存细节并提供更好的界面:

private class DatabaseInterface {
    private PreparedStatement insertAnnotation, getProteinId;
    public DatabaseInterface() {
        // This is an inner class; 'connection' is variable in outer class
        insertAnnotation = connection.prepareStatement(
            "INSERT INTO Annotations(GOId, ProteinId, OnthologyId) VALUES(?, ?, ?)");
        getProteinId = connection.prepareStatement(
            "SELECT Id FROM Proteins WHERE PrimaryUniProtKBAccessionNumber = ?");
    }
    public long getId(Protein protein) { // Exceptions omitted...
        getProteinId.setString(1, protein.get_primary_id());
        ResultSet result = getProteinId.executeQuery();
        try {
            result.next();
            return result.getLong(1);
        } finally {
            result.close();
        }
    }
    public void insertAnnotation(int GOId, long proteinId, String ontologyId) {
        insertAnnotation.setInt(1, GOId);          // type may be wrong
        insertAnnotation.setLong(2, proteinId);
        insertAnnotation.setString(3, ontologyId); // type may be wrong
        insertAnnotation.executeUpdate();
    }
}
private void loadAnnotations(InteractionNetwork network) throws SQLException {
    connection.setAutoCommit(false);
    DatabaseInterface dbi = new DatabaseInterface();
    for(common.Protein protein : network.get_protein_vector()) {
        dbi.insertAnnotation(..., dbi.getId(protein), ...);
    }
    connection.commit();
    connection.setAutoCommit(true);
}

目标是让您有一段代码知道如何将事物整合到 SQL 中(如果您转到不同的数据库，它很容易适应)和另一段代码知道如何将这些事物协调在一起。