Java PreparedStatement.executeBatch()在SQLite中不工作

nxowjjhe  于 2023-10-14  发布在  Java
关注(0)|答案(2)|浏览(132)

我有以下方法。注解的方法saveOrUpdateToDatabase执行得很好,但我想使用executeBatch。我错过了什么?接收executeBatch()结果的int[]r总是空的.

public boolean saveOrUpdate(MonitoredData mData) {
        try {
            PreparedStatement prep;
            String timeID = this.getTimeLastRowID();

            for (CpuData o : mData.getCpu()) {
                prep = this.conn.prepareStatement(String.format(
                        INSERT_CPU_USAGE, this.nodeID, timeID, o.toString()));
                prep.addBatch();
                // saveOrUpdateToDatabase(String.format(INSERT_CPU_USAGE,
                // this.nodeID, timeID, o.toString()));
            }
            for (DiskData o : mData.getDisk()) {
                prep = this.conn.prepareStatement(String.format(
                        INSERT_DISK_USAGE, this.nodeID, timeID, o.toString()));
                prep.addBatch();
                // saveOrUpdateToDatabase(String.format(INSERT_DISK_USAGE,
                // this.nodeID, timeID, o.toString()));

            }
            for (NetworkData o : mData.getNet()) {
                prep = this.conn.prepareStatement(String
                        .format(INSERT_NETWORK_USAGE, this.nodeID, timeID, o
                                .toString()));
                prep.addBatch();
                // saveOrUpdateToDatabase(String.format(INSERT_NETWORK_USAGE,
                // this.nodeID, timeID, o.toString()));

            }

            prep = this.conn.prepareStatement(String.format(
                    INSERT_MEMORY_USAGE, this.nodeID, timeID, mData.getMem()
                            .toString()));
            // saveOrUpdateToDatabase(String.format(INSERT_MEMORY_USAGE,
            // this.nodeID, timeID, mData.getMem().toString()));

            conn.setAutoCommit(false);
            int[] r = prep.executeBatch();
            conn.setAutoCommit(true);

            return true;

        } catch (SQLException ex) {
            Logger.getLogger(HistoricalDatabase.class.getName()).log(
                    Level.SEVERE, null, ex);
        }
        return false;
    }
nukf8bse

nukf8bse1#

我错过了什么?接收executeBatch()结果的int[]r总是空的.
您错误地使用了addBatch()方法。在你的代码中,你正在做:

for (CpuData o : mData.getCpu()) {
    // WRONG!! you can not prepare a new query each time
    prep = this.conn.prepareStatement(INSERT_CPU_USAGE);
    prep.setObject(1, this.nodeID);
    prep.addBatch();
}

这每次都会 * 替换 * 准备好的查询。每个批处理只应该调用prepareStatement(...)一次。你应该做一些像下面这样的事情。你需要修改insert语句,使其具有?参数:

PreparedStatement prep = conn.prepareStatement(INSERT_CPU_USAGE);
for (CpuData o : mData.getCpu()) {
    prep.setObject(1, this.nodeID);
    prep.setObject(2, timeID);
    prep.setObject(3, o);
    prep.addBatch();
}
prep.executeBatch();

注意,只使用了一个prepareStatement()调用。这里面有? SQL参数实体,然后在循环中用prep.setString(1, ...)赋值。当批处理准备好执行时,您调用prep.executeBatch(),但这 * 没有 * 替换您正在执行的prep准备语句。
如果你试图在同一批中的一行中执行一系列 * 不同 * 的insert语句,那么你应该考虑关闭自动提交,执行你的语句,调用commit,然后重新打开自动提交。例如:

conn.setAutoCommit(false);
// statements prepared and executed here
// maybe no need for batch operations
...
conn.commit();
conn.setAutoCommit(true);
eh57zj3b

eh57zj3b2#

除了我写的评论,我还要冒险给出一个答案。如果你尝试这样做:

public boolean saveOrUpdate(MonitoredData mData) {
    try {
        PreparedStatement prep;
        String timeID = this.getTimeLastRowID();
        conn.setAutoCommit(false);
        for (CpuData o : mData.getCpu()) {
            prep = this.conn.prepareStatement(INSERT_CPU_USAGE);
            prep.setObject(1, this.nodeID);
            prep.setObject(2, timeID);
            prep.setObject(3, o);
            prep.addBatch();
            // saveOrUpdateToDatabase(String.format(INSERT_CPU_USAGE,
            // this.nodeID, timeID, o.toString()));
        }
        prep.executeBatch();
        for (DiskData o : mData.getDisk()) {
            prep = this.conn.prepareStatement(INSERT_DISK_USAGE);
            prep.setObject(1, this.nodeID);
            prep.setObject(2, timeID);
            prep.setObject(3, o);
            prep.addBatch();
            // saveOrUpdateToDatabase(String.format(INSERT_DISK_USAGE,
            // this.nodeID, timeID, o.toString()));
        }
        prep.executeBatch();
        for (NetworkData o : mData.getNet()) {
            prep = this.conn.prepareStatement(INSERT_NETWORK_USAGE);
            prep.setObject(1, this.nodeID);
            prep.setObject(2, timeID);
            prep.setObject(3, o);
            prep.addBatch();
            // saveOrUpdateToDatabase(String.format(INSERT_NETWORK_USAGE,
            // this.nodeID, timeID, o.toString()));

        }
        prep.executeBatch();
        prep = this.conn.prepareStatement(INSERT_MEMORY_USAGE);
        prep.setObject(1, this.nodeID);
        prep.setObject(2, timeID);
        prep.setObject(3, o);
        prep.executeUpdate();
        // saveOrUpdateToDatabase(String.format(INSERT_MEMORY_USAGE,
        // this.nodeID, timeID, mData.getMem().toString()));

        conn.setAutoCommit(true);
        return true;

    } catch (SQLException ex) {
        Logger.getLogger(HistoricalDatabase.class.getName()).log(
                Level.SEVERE, null, ex);
    }
    return false;
}

您可能需要修改常量中定义的那些SQL查询。

相关问题