Audit of duplication of OBD records



  • There are two OBD-Oracle and postgresql. Through java in the interface, there's a button on which the next-- Oracle releases data from one table and is submitted to the essence, and then to the OBD postgresql. How do we ban the recording of already outdated data when activated? Do you have to do this at the OBD level or Java level? This is the example of the code, with certain features that relate 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 */ }
    }
    

    }



  • Request option for a check on the OBD 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 side of java:

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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2