Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jun 13th, 2012  |  syntax: None  |  size: 6.21 KB  |  hits: 13  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Abrupt Resultset behavior in java
  2. static String mergeTable() throws Exception {
  3.     String result = "ERROR";
  4.     int error = 0;
  5.     String tableString = "<table " + tablename + ">";
  6.  
  7.     PreparedStatement preparedSelect = null;
  8.     PreparedStatement preparedSelectTarget = null;
  9.     Statement selectSourceStmt = null;
  10.     ResultSet checkTarget = null;
  11.     ResultSet rs = null;
  12.     try {
  13.  
  14.         logger.println("====================================================================================");
  15.         logger.println("Processing table:" + tablename);
  16.         System.out.println("====================================================================================");
  17.         System.out.println("Processing table:" + tablename);
  18.  
  19.         // Create query to fetch records from the source
  20.         String sourceQuery = "SELECT * FROM " + tablename;
  21.         if (owner.trim().equals("F1") || owner.trim().equals("C1") || owner.trim().equals("CM"))
  22.             sourceQuery = sourceQuery + " WHERE OWNER_FLG='" + owner + "'";
  23.  
  24.         // Get the result set
  25.  
  26.         selectSourceStmt = source.createStatement();
  27.         rs = selectSourceStmt.executeQuery(sourceQuery);
  28.  
  29.         System.out.println(sourceQuery);
  30.  
  31.         String selectSQL = "SELECT COUNT(*) FROM " + tablename + " WHERE ";
  32.         String selectSQLTarget = "SELECT * FROM " + tablename + " WHERE "; // ankush
  33.  
  34.         ResultSetMetaData metaData = rs.getMetaData();
  35.  
  36.         List list = new ArrayList();
  37.         List typesList = new ArrayList();
  38.  
  39.         for (int i = 1; i <= metaData.getColumnCount(); i++) {
  40.             String columnName = metaData.getColumnName(i);
  41.             list.add(columnName); // list contains the entire list of columns of the source
  42.             typesList.add(metaData.getColumnType(i));
  43.  
  44.         }
  45.  
  46.         for (int i = 1; i < keys.length; i++) {
  47.  
  48.             if (i == 1) {
  49.                 selectSQL = selectSQL + " " + keys[i] + "= ?";
  50.                 selectSQLTarget = selectSQLTarget + " " + keys[i] + "= ?"; //ankush
  51.             }
  52.  
  53.             else {
  54.                 selectSQL = selectSQL + " AND " + keys[i] + "= ?";
  55.                 selectSQLTarget = selectSQLTarget + " AND " + keys[i] + "= ?"; //ankush
  56.             }
  57.  
  58.         }
  59.  
  60.         logger.println("Select SQL:" + selectSQL);
  61.         logger.println("selectSQLTarget:" + selectSQLTarget); //ankush
  62.  
  63.         preparedSelect = target.prepareStatement(selectSQL);
  64.         preparedSelectTarget = target.prepareStatement(selectSQLTarget); //ankush
  65.  
  66.         int updateCount = 0, insertCount = 0, errorCount = 0;
  67.         // rs contains the entire table snapshot of source  based on the owner flag
  68.         if (rs != null) {
  69.  
  70.             while (rs.next()) {
  71.                 try {
  72.                     int i, count;
  73.  
  74.                     // check if record exists or not; keys contain the values of primary columns specified in the.lst file
  75.                     for (int j = 1; j < keys.length; j++) {
  76.                         preparedSelect.setObject(j, rs.getObject(keys[j])); // for every single row in source, corresponding rows are fetched from target.Here, where clause is being prepared
  77.  
  78.                     }
  79.  
  80.                     ResultSet check = preparedSelect.executeQuery(); // check is the target resultset for the primary key values in current row of source resultset
  81.  
  82.                     check.next();
  83.  
  84.                     count = check.getInt(1); // count gives the row/s fetched from target based on the values in source.
  85.                     check.close();
  86.  
  87.                     // check if record exists or not; keys contain the values of primary columns specified in the.lst file
  88.                     for (int j = 1; j < keys.length; j++) {
  89.                         // for every single row in source, corresponding rows are fetched from target.Here, where clause is being prepared
  90.                         preparedSelectTarget.setObject(j, rs.getObject(keys[j]));
  91.  
  92.                     }
  93.  
  94.                     // check is the target resultset for the primary key values in current row of source resultset  
  95.                     checkTarget = preparedSelectTarget.executeQuery();
  96.  
  97.                     checkTarget.next();
  98.  
  99.                     // if record exists  UPDATE CONDITION
  100.                     if (true) { // if there is a record in target for a row in source, update target
  101.                         String rowString = "<row>";
  102.                         String rowDiffFlag = "N";
  103.                         // if merge flag is Y
  104.                         if (mergeFlag.equals("Y")) {
  105.                             String colDiffFlag = "";
  106.                             String sourceColVal = "";
  107.                             String targetColVal = "";
  108.                             // list contains the column names
  109.                             for (i = 0; i < list.size(); i++) {
  110.                                 System.out.println("value of i " + i);
  111.                             }
  112.                             i++; // ?????
  113.  
  114.                         } else {
  115.                                 logger.print("Did not update Record:");
  116.  
  117.  
  118.                         }
  119.                         rowString = rowString + "</row>";
  120.  
  121.                         if (rowDiffFlag.equals("Y")) {
  122.                             tableString = tableString + rowString;
  123.                         }
  124.  
  125.                     } else { // if there is no record in target for a row in source, insert into target
  126.                         String sourceColVal = "";
  127.                         String rowString = "<row>";
  128.                         for (i = 0; i < list.size(); i++) { //looping through columns in a row
  129.                             System.out.println("column " + i);
  130.                             }
  131.  
  132.                         rowString = rowString + "</row>";
  133.                         tableString = tableString + rowString;
  134.                     }
  135.  
  136.                 } catch (Exception e1) {
  137.  
  138.                     e1.printStackTrace(logger);
  139.  
  140.                 }
  141.             }
  142.         }
  143.  
  144.     } catch (Exception e) {
  145.  
  146.         e.printStackTrace(logger);
  147.  
  148.     } finally {
  149.         preparedSelect.close();
  150.         preparedSelectTarget.close();
  151.         selectSourceStmt.close();
  152.         checkTarget.close();
  153.         rs.close();
  154.     }
  155.  
  156.     tableString = tableString + "</table>";
  157.     formXmlString(tableString);
  158.  
  159.     if (error == 0) result = "SUCCESS";
  160.  
  161.     return result;
  162. }
create a new version of this paste RAW Paste Data