java - Mysql 更新了太多行

Question

我遇到了 mysql/jdbc 更新太多行或为多行获取相同值的情况。数据是一个 4x4 变换矩阵，以加密字符串形式保存在 blob 字段中(来源为 js/JSON)。这些列是projectid、modelid、instance 和transform。在本例中存在三个实例 (0,1,2)。我专门选择了projectid、modelid和instance，但是所有三个实例的转换都发生了变化。它的行为就像未指定实例一样。

更新代码

private boolean updateTransform(int projectId, int modelId, int instance, String transform) {
    Connection conn = null;
    try {
        conn = getConnection();
        conn.setAutoCommit(false);
        byte[] encryptedTransform = Encryption.encrypt(transform);
        String sql = "Update Creator3d.projectsmodels set transform=? where projectId=? and modelid=? and instance=?"; 
        System.out.println("sql: " + sql);
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            Blob transformBlob = conn.createBlob();
            transformBlob.setBytes(1, encryptedTransform);
            int index = 1;
            stmt.setBlob(index++, transformBlob);
            stmt.setInt(index++, projectId);
            stmt.setInt(index++, modelId);
            stmt.setInt(index++, instance);
            int numChange = stmt.executeUpdate();
            int count = stmt.getUpdateCount();
            System.out.println("count: " + count + ", numChange: " + numChange);
            conn.commit();
        }
        checkTransforms(projectId);
    } catch (SQLException ex) {
        Logger.getLogger(UpdateModelTransformHandler.class.getName()).log(Level.SEVERE, "Failed to update transform", ex);
        return false;
    } finally {
        try {
            if (conn != null) {
                conn.setAutoCommit(true);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UpdateModelTransformHandler.class.getName()).log(Level.SEVERE, "Error closing db connection", ex);
            return false;
        }
    }

    return true;
}

检查功能

private void checkTransforms(int projectId) throws SQLException {
    Connection conn = getConnection();
    try (Statement stat = conn.createStatement()) {
        String sql = "select * from Creator3d.projectsmodels where projectId=" + projectId;
        ResultSet result = stat.executeQuery(sql);
        while (result.next()) {
            int modelId = result.getInt("modelid");
            int instance = result.getInt("instance");
            Blob transformBlob = result.getBlob("transform");
            String transformString = Encryption.decrypt(transformBlob.getBytes(1, (int)transformBlob.length()));
            System.out.println("modelId: " + modelId + ", instance: " + instance + ", transform: " + transformString);
        }
    }
}

输出

sql: Update Creator3d.projectsmodels set transform=? where projectId=? and modelid=? and instance=?
count: 1, numChange: 1
modelId: 150, instance: 0, transform: [1,0,0,0,0,1,0,0,0,0,1,0,-3.4766407012939453,0,0,1]
modelId: 150, instance: 1, transform: [1,0,0,0,0,1,0,0,0,0,1,0,-3.4766407012939453,0,0,1]
modelId: 150, instance: 2, transform: [1,0,0,0,0,1,0,0,0,0,1,0,-3.4766407012939453,0,0,1]
modelId: 161, instance: 0, transform: [1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1]
sql: Update Creator3d.projectsmodels set transform=? where projectId=? and modelid=? and instance=?
count: 1, numChange: 1
modelId: 150, instance: 0, transform: [1,0,0,0,0,1,0,0,0,0,1,0,-0.1613478660583496,2.452868938446045,0,1]
modelId: 150, instance: 1, transform: [1,0,0,0,0,1,0,0,0,0,1,0,-0.1613478660583496,2.452868938446045,0,1]
modelId: 150, instance: 2, transform: [1,0,0,0,0,1,0,0,0,0,1,0,-0.1613478660583496,2.452868938446045,0,1]
modelId: 161, instance: 0, transform: [1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1]

Answer 1

尝试以下代码，它应该可以工作:

String sqlSelectBlobForUpdate =  "SELECT transform FROM"+ 
                                    +"Creator3d.projectsmodels"+
             +"WHERE "+"projectId=? and modelid=? and instance=?for update"; 
OracleCallableStatement stmt=(OracleCallableStatement)  
                             conn.prepareCall(sqlSelectBlobForUpdate);
stmt.setInt(coordinateprojectID, projectId);
stmt.setInt(coordinatemodelId, modelId);
stmt.setInt(coordinateInstance, instance);
ResultSet lRs = null;
lRs=stmt.executeQuery();
while(lRs.next())
{
 Blob lBlob = lRs.getBlob(1);
 OutputStream blobOutputStream = ((oracle.sql.BLOB) lBlob)
                    .getBinaryOutputStream();
 blobOutputStream.write(encryptedTransform ); // here put your data
 blobOutputStream.close();
 }
//after you commit 

那么您确定只会更新一个 blob