Mysql正在更新太多的行
Mysql正在更新太多的行
提问于 2017-02-11 06:21:34
我遇到一种情况，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;
}Check函数
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]回答 1
Stack Overflow用户
发布于 2017-02-17 10:28:22
尝试下面的代码，它应该可以工作：
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
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42169581
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号