Integrity constraint violation: unique constraint or index violation HSQLDB

Question

For example if I add new Department(new BigInteger("50"), "ODD", "SPB"), all work, it values are insert into database. But if I want again insert for example new Department(new BigInteger("50"), "ODDMOD", "SPBMOD"), appear java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; SYS_PK_10092 table: DEPARTMENT. I know that can not insert values with the same primary key, but how can update values if primary key exists or other solutions?

 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;
        }

Answer 1

You want an upsert here:

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();

    // ...
}

A MERGE behaves by doing an insert if the department id does not already exist in the table. Otherwise it will do an update. Note that if you shift to JPA/Hibernate from pure JDBC, the JPA save() method can upsert automatically for you under the hood.