例如，如果我添加new Department(new BigInteger("50"), "ODD", "SPB")，所有作業，它的值都會插入到資料庫中。但是如果我想再次插入例如new Department(new BigInteger("50"), "ODDMOD", "SPBMOD")出現java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; SYS_PK_10092 table: DEPARTMENT。我知道不能插入具有相同主鍵的值，但是如果主鍵存在或其他解決方案如何更新值？
public Department save(Department department) throws SQLException {
Connection connection = ConnectionSource.instance().createConnection();
String sql = "insert into department values (?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setLong(1, Long.parseLong(String.valueOf(department.getId())));
statement.setString(2, department.getName());
statement.setString(3, department.getLocation());
statement.executeUpdate();
PreparedStatement st = connection.prepareStatement("select * from department where id = ? ");
st.setLong(1, Long.parseLong(String.valueOf(department.getId())));
ResultSet resultSet = st.executeQuery();
resultSet.next();
Department demper = new Department(
new BigInteger(String.valueOf(resultSet.getInt("id"))),
resultSet.getString("name"),
resultSet.getString("location")
);
return demper;
}
uj5u.com熱心網友回復：
你想要一個 upsert 在這里：
public Department save(Department department) throws SQLException {
Connection connection = ConnectionSource.instance().createConnection();
String sql = "MERGE INTO department d1 "
"USING (VALUES ?, ?, ?) d2 (id, name, location) "
" ON (d1.id = d2.id) "
" WHEN MATCHED THEN UPDATE SET "
" d1.name = d2.name, d1.location = d2.location "
" WHEN NOT MATCHED THEN INSERT (id, name, location) VALUES (d2.id, d2.name, d2.location)";
PreparedStatement statement = connection.prepareStatement(sql);
// execute merge here as before
statement.setLong(1, Long.parseLong(String.valueOf(department.getId())));
statement.setString(2, department.getName());
statement.setString(3, department.getLocation());
statement.executeUpdate();
// ...
}
MERGE如果部門id不存在于表中，則A通過執行插入操作。否則它會進行更新。請注意，如果您從純 JDBC 轉向 JPA/Hibernate，那么 JPAsave()方法可以在幕后自動為您更新插入。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/389909.html
下一篇：postgresql總列總和