There are two db-oracle and postgresql. Through java, in the interface there is a button, after clicking on which the following occurs, data from one table is extracted from Oracle and inserted into the essence, and then in the postgresql database. How to implement a ban on entering data already taken out with a subsequent activation? Should it be done at the database level or at the Java level? Here is a sample code, with some features, relating exclusively to the system itself.

private static Statement stmt; private static ResultSet rs; private static Connection connection; private void getUsrAndCreateUsr(){ connection = null; String query="select usr_key, usr_status, usr_udf_hrid, usr_last_name, usr_first_name, usr_middle_name, usr_email, usr_udf_hrdateofbirth from usr where usr_status !='Deleted'"; try{ connection = getConnect(); stmt = connection.createStatement(); rs = stmt.executeQuery(query); while (rs.next()){ Long usr_key = rs.getLong("usr_key"); String usr_status = rs.getString("usr_status"); String usr_udf_hrid = rs.getString("usr_udf_hrid"); String usr_last_name = rs.getString("usr_last_name"); String usr_first_name = rs.getString("usr_first_name"); String usr_middle_name = rs.getString("usr_middle_name"); String usr_email = rs.getString("usr_email"); java.sql.Date usr_udf_hrdateofbirth = rs.getDate("usr_udf_hrdateofbirth"); RUser newUser = new RUser(); newUser.setStatus(dictContentService.getDictContentByCode(111L, usr_status)); newUser.setCode(usr_udf_hrid); newUser.setLastName(usr_last_name); newUser.setFirstName(usr_first_name); newUser.setMiddleName(usr_middle_name); newUser.setEmail(usr_email); newUser.setBirthDate(usr_udf_hrdateofbirth); newUser.setDescription("from Oracle"); newUser.setLocked(false); userService.saveBase(newUser); } }catch (SQLException sqlEx) { sqlEx.printStackTrace(); }finally{ try { connection.close(); } catch(SQLException se) { /*can't do anything */ } try { stmt.close(); } catch(SQLException se) { /*can't do anything */ } try { rs.close(); } catch(SQLException se) { /*can't do anything */ } } }
  • Depending on the context of the task, it is possible to use a unique index. - DanielOlivo
  • suppose if in Oracle usr_udf_hrid a unique number, but we get it is transferred to the Code field - Aleksander Shakotko
  • Indexes can be composite. Any field or set of fields that can uniquely identify a record is assigned a unique index of the table in the post-regression, and in case of an attempt to re-insert an existing index, an exception will be thrown. - DanielOlivo

1 answer 1

Variant of request for the implementation of verification on the database side:

 INSERT INTO usr( usr_key, usr_status, usr_udf_hrid, usr_last_name, usr_first_name, usr_middle_name, usr_email, usr_udf_hrdateofbirth ) SELECT <здесь_новые_данные> WHERE NOT EXISTS( SELECT 1 FROM usr where usr_key=<новый_usr_key> AND <проверка_других_уникальных_полей> );

On the java side:

 SELECT_STRING = "SELECT 1 FROM usr WHERE <проверка_уникальных_полей>"; INSERT_STRING = "INSERT INTO usr(..." try ( Statement statement = connection.createStatement() ) { //check boolean isExist = false; try( ResultSet result = statement.executeQuery( SELECT_STRING ) ){ while( result.next() ){ isExist = true; break; } } catch( Exception e ) { throw e; } if ( !isExist ) { statement.executeUpdate( INSERT_STRING ); } else { //statement.executeUpdate( UPDATE_STRING ); } } catch( Exception e ) { e.printStackTrace(); }
  • not exactly, it turns out all the same, the guide goes to the database. I think this can be done by checking the getsertion of the RUser entity for getByCode. - Aleksander Shakotko