Por ejemplo, si agrego new Department(new BigInteger("50"), "ODD", "SPB") , todo funciona, los valores se insertan en la base de datos. Pero si quiero volver a insertar, por ejemplo, un new Department(new BigInteger("50"), "ODDMOD", "SPBMOD") , aparece java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; SYS_PK_10092 table: DEPARTMENT . Sé que no puedo insertar valores con la misma clave principal, pero ¿cómo puedo actualizar los valores si existe una clave principal u otras soluciones?
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; }Quieres un upsert aquí:
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(); // ... } Un MERGE se comporta haciendo una inserción si la id del departamento aún no existe en la tabla. De lo contrario, hará una actualización. Tenga en cuenta que si cambia a JPA/Hibernate desde JDBC puro, el método JPA save() puede actualizarse automáticamente bajo el capó.