1 32 33 package com.knowgate.datacopy; 34 35 import com.knowgate.debug.DebugFile; 36 37 import java.sql.Connection ; 38 import java.sql.SQLException ; 39 import java.sql.PreparedStatement ; 40 import java.sql.ResultSet ; 41 import java.sql.ResultSetMetaData ; 42 43 import com.knowgate.misc.Gadgets; 44 45 49 50 public class CopyRegisters { 51 52 public static int FETCH_SIZE = 100; 53 54 private String sSchema; 55 56 private String sCatalog; 57 58 60 private class ExtendedStatement { 61 public PreparedStatement sqlstatement; 62 public String [] columns; 63 64 ExtendedStatement (PreparedStatement oStmt, String sCols) { 65 sqlstatement = oStmt; 66 columns = Gadgets.split(sCols, ','); 67 } 68 } 69 70 72 public CopyRegisters() { 73 sSchema = null; 74 sCatalog = null; 75 } 76 77 public CopyRegisters(String schema, String catalog) { 78 sSchema = schema; 79 sCatalog = catalog; 80 } 81 82 84 private PreparedStatement prepareReadStatement (Connection oOriginConn, Connection oTargetConn, DataTblDef oOriginDef, DataTblDef oTargetDef, String sWhere) 85 throws SQLException { 86 PreparedStatement oReadStmt; 87 88 if (DebugFile.trace) { 89 DebugFile.writeln("Begin CopyRegisters.prepareReadStatement(...)"); 90 DebugFile.incIdent(); 91 } 92 93 int iColPos; 94 String sColList = ""; 95 96 for (int c=0; c<oTargetDef.ColCount; c++) { 97 iColPos = oOriginDef.findColumnPosition(oTargetDef.ColNames[c]); 98 99 if (-1==iColPos) 100 sColList += "NULL AS " + oTargetDef.ColNames[c] + ","; 101 else 102 sColList += oTargetDef.ColNames[c] + ","; 103 } 104 105 if (DebugFile.trace) 106 DebugFile.writeln ("SELECT " + sColList.substring(0, sColList.length()-1) + " FROM " + oOriginDef.BaseTable + " " + (sWhere!=null ? sWhere : "")); 107 108 oReadStmt = oOriginConn.prepareStatement("SELECT " + sColList.substring(0, sColList.length()-1) + " FROM " + oOriginDef.BaseTable + " " + (sWhere!=null ? sWhere : "")); 109 110 if (DebugFile.trace) { 111 DebugFile.decIdent(); 112 DebugFile.writeln("End CopyRegisters.prepareReadStatement() : [PreparedStatement]"); 113 } 114 115 return oReadStmt; 116 } 117 118 120 private ExtendedStatement prepareInsertStatement (Connection oTargetConn, DataTblDef oTargetDef) 121 throws SQLException { 122 PreparedStatement oInsrtStmt; 123 124 if (DebugFile.trace) { 125 DebugFile.writeln("Begin CopyRegisters.prepareInsertStatement(...)"); 126 DebugFile.incIdent(); 127 } 128 129 String sColList = ""; 130 String sValues = "("; 131 132 for (int c=0; c<oTargetDef.ColCount; c++) { 133 if (c!=oTargetDef.ColCount-1) { 134 sColList += oTargetDef.ColNames[c] + ","; 135 sValues += "?,"; 136 } 137 else { 138 sColList += oTargetDef.ColNames[c]; 139 sValues += "?)"; 140 } 141 } 142 143 if (DebugFile.trace) 144 DebugFile.writeln("Connection.prepareStatement(INSERT INTO " + oTargetDef.BaseTable + " (" + sColList + ") VALUES " + sValues + ")"); 145 146 oInsrtStmt = oTargetConn.prepareStatement("INSERT INTO " + oTargetDef.BaseTable + " (" + sColList + ") VALUES " + sValues); 147 148 if (DebugFile.trace) { 149 DebugFile.decIdent(); 150 DebugFile.writeln("End CopyRegisters.prepareInsertStatement() : [ExtendedStatement]"); 151 } 152 153 return new ExtendedStatement (oInsrtStmt, sColList); 154 } 155 156 158 private ExtendedStatement prepareUpdateStatement (Connection oTargetConn, DataTblDef oTargetDef) 159 throws SQLException { 160 PreparedStatement oUpdtStmt; 161 162 if (DebugFile.trace) { 163 DebugFile.writeln("Begin CopyRegisters.prepareUpdateStatement(...)"); 164 DebugFile.incIdent(); 165 } 166 167 String sSQL = "UPDATE " + oTargetDef.BaseTable + " SET "; 168 String sColList = "", sColumns = ""; 169 170 for (int c=0; c<oTargetDef.ColCount; c++) { 171 if (!oTargetDef.isPrimaryKey(c)) { 172 if (sColList.length()==0) 173 sColList = oTargetDef.ColNames[c] + "=?"; 174 else 175 sColList += "," + oTargetDef.ColNames[c] + "=?"; 176 177 sColumns += (sColumns.length()==0 ? oTargetDef.ColNames[c] : "," + oTargetDef.ColNames[c]); 178 } } 181 if (sColList.length()==0) { 182 if (DebugFile.trace) { 183 DebugFile.decIdent(); 184 DebugFile.writeln("End CopyRegisters.prepareUpdateStatement() : null"); 185 } 186 return null; 187 } 188 189 sSQL += sColList + " WHERE "; 190 sColList = ""; 191 192 for (int c=0; c<oTargetDef.ColCount; c++) { 193 if (oTargetDef.isPrimaryKey(c)) { 194 if (sColList.length()==0) 195 sColList = oTargetDef.ColNames[c] + "=?"; 196 else 197 sColList += " AND " + oTargetDef.ColNames[c] + "=?"; 198 199 sColumns += (sColumns.length()==0 ? oTargetDef.ColNames[c] : "," + oTargetDef.ColNames[c]); 200 } } 203 if (sColList.length()==0) 204 throw new SQLException ("Could not find primary key for table " + oTargetDef.BaseTable, "42S12"); 205 206 sSQL += sColList; 207 208 if (DebugFile.trace) DebugFile.writeln ("Connection.prepareStatement(" + sSQL + ")"); 209 210 oUpdtStmt = oTargetConn.prepareStatement(sSQL); 211 212 if (DebugFile.trace) { 213 DebugFile.decIdent(); 214 DebugFile.writeln("End CopyRegisters.prepareUpdateStatement() : [PreparedStatement]"); 215 } 216 217 return new ExtendedStatement (oUpdtStmt, sColumns); 218 } 219 220 222 public Object cast (Object oOrigin, int iOriginType, int iTargetType) { 223 return oOrigin; 224 } 225 226 228 private boolean existsRow (Connection oConn, String sTable, String sPKCols, ResultSet oRow) 229 throws SQLException { 230 String [] aCols = Gadgets.split(sPKCols, ','); 231 String sSQL = "SELECT NULL FROM " + sTable + " WHERE "; 232 233 for (int c=0; c<aCols.length; c++) { 234 if (c>0) sSQL += " AND "; 235 sSQL += aCols[c] + "=?"; 236 } 238 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSQL + ")"); 239 240 PreparedStatement oStmt = oConn.prepareStatement(sSQL); 241 for (int c=0; c<aCols.length; c++) 242 oStmt.setObject(c+1, oRow.getObject(aCols[c])); 243 ResultSet oRSet = oStmt.executeQuery(); 244 boolean bExists = oRSet.next(); 245 oRSet.close(); 246 oStmt.close(); 247 248 if (DebugFile.trace) { 249 for (int c=0; c<aCols.length; c++) { 250 if (c>0) DebugFile.write(","); 251 DebugFile.write(aCols[c] + "=" + oRow.getObject(aCols[c])); 252 } 253 DebugFile.writeln(bExists ? " exists" : " does not exist"); 254 } 255 256 return bExists; 257 } 259 261 271 public int insert (Connection oOrigin, Connection oTarget, String sOriginTable, String sTargetTable, String sWhere) 272 throws SQLException { 273 274 if (DebugFile.trace) { 275 DebugFile.writeln("Begin CopyRegisters.insert(" + sOriginTable + "," + sTargetTable + "," + sWhere + ")"); 276 DebugFile.incIdent(); 277 } 278 279 int iInserted = 0; 280 ResultSet oReadRSet = null; 281 ExtendedStatement oInsrtStmt= null; 282 PreparedStatement oReadStmt = null; 283 284 DataTblDef oOriginDef = new DataTblDef(); 285 DataTblDef oTargetDef = new DataTblDef(); 286 287 oOriginDef.readMetaData(oOrigin, sOriginTable, null); 288 oTargetDef.readMetaData(oTarget, sTargetTable, null); 289 290 try { 291 oInsrtStmt = prepareInsertStatement(oTarget, oTargetDef); 292 293 oReadStmt = prepareReadStatement (oOrigin, oTarget, oOriginDef, oTargetDef, sWhere); 294 295 try { oReadStmt.setFetchSize(FETCH_SIZE); } catch (SQLException ignore) { } 296 297 oReadRSet = oReadStmt.executeQuery(); 298 299 final int iCols = oTargetDef.ColCount; 300 301 while (oReadRSet.next()) { 302 303 for (int c=1; c<=iCols; c++) { 304 oInsrtStmt.sqlstatement.setObject (c, cast(oReadRSet.getObject(c), oOriginDef.ColTypes[c-1], oTargetDef.ColTypes[c-1]), oTargetDef.ColTypes[c-1]); 305 } 306 iInserted += oInsrtStmt.sqlstatement.executeUpdate(); 307 } } 309 catch (SQLException sqle) { 310 throw new SQLException (sqle.getMessage(), sqle.getSQLState(), sqle.getErrorCode()); 311 } 312 finally { 313 if (null!=oReadRSet) oReadRSet.close(); 314 315 if (null!=oReadStmt) oReadStmt.close(); 316 317 if (null!=oInsrtStmt) oInsrtStmt.sqlstatement.close(); 318 } 319 320 if (DebugFile.trace) { 321 DebugFile.decIdent(); 322 DebugFile.writeln("End CopyRegisters.insert() : " + String.valueOf(iInserted)); 323 } 324 325 return iInserted; 326 } 328 330 341 public int replace (Connection oOrigin, Connection oTarget, String sOriginTable, String sTargetTable, String sWhere) 342 throws SQLException { 343 344 if (DebugFile.trace) { 345 DebugFile.writeln("Begin CopyRegisters.replace(" + sOriginTable + "," + sTargetTable + "," + sWhere + ")"); 346 DebugFile.incIdent(); 347 } 348 349 int iReplaced = 0; 350 ResultSet oReadRSet = null; 351 ResultSetMetaData oReadMDat = null; 352 PreparedStatement oReadStmt = null; 353 ExtendedStatement oUpdtStmt = null; 354 355 DataTblDef oOriginDef = new DataTblDef(); 356 DataTblDef oTargetDef = new DataTblDef(); 357 358 String sTargetPK = oTargetDef.getPrimaryKeys(oOrigin, sSchema, sCatalog, sTargetTable); 359 360 oOriginDef.readMetaData(oOrigin, sOriginTable, null); 361 oTargetDef.readMetaData(oTarget, sTargetTable, sTargetPK); 362 363 try { 364 oUpdtStmt = prepareUpdateStatement(oTarget, oTargetDef); 365 366 oReadStmt = prepareReadStatement (oOrigin, oTarget, oOriginDef, oTargetDef, sWhere); 367 368 try { oReadStmt.setFetchSize(FETCH_SIZE); } catch (SQLException ignore) { } 369 370 oReadRSet = oReadStmt.executeQuery(); 371 oReadMDat = oReadRSet.getMetaData(); 372 373 int iCols, iColPos, iOriginType, iTargetType; 374 375 if (oUpdtStmt!=null) 376 iCols = oUpdtStmt.columns.length; 377 else 378 iCols = 0; 379 380 while (oReadRSet.next()) { 381 382 for (int c=1; c<=iCols; c++) { 383 iColPos = oReadRSet.findColumn(oUpdtStmt.columns[c-1]); 384 iOriginType = oReadMDat.getColumnType(iColPos); 385 iTargetType = oTargetDef.ColTypes[oTargetDef.findColumnPosition(oUpdtStmt.columns[c-1])]; 386 387 oUpdtStmt.sqlstatement.setObject (c, cast(oReadRSet.getObject(iColPos), iOriginType, iTargetType), iTargetType); 388 } 389 if (oUpdtStmt!=null) 390 iReplaced += oUpdtStmt.sqlstatement.executeUpdate(); 391 else 392 iReplaced = (existsRow(oTarget, sTargetTable, sTargetPK, oReadRSet) ? 1 : 0); 393 } } 395 catch (SQLException sqle) { 396 throw new SQLException (sqle.getMessage(), sqle.getSQLState(), sqle.getErrorCode()); 397 } 398 finally { 399 if (null!=oReadRSet) oReadRSet.close(); 400 401 if (null!=oReadStmt) oReadStmt.close(); 402 403 if (null!=oUpdtStmt) oUpdtStmt.sqlstatement.close(); 404 } 405 406 if (DebugFile.trace) { 407 DebugFile.decIdent(); 408 DebugFile.writeln("End CopyRegisters.replace() : " + String.valueOf(iReplaced)); 409 } 410 return iReplaced; 411 } 413 415 426 public int append (Connection oOrigin, Connection oTarget, String sOriginTable, String sTargetTable, String sWhere) 427 throws SQLException { 428 429 if (DebugFile.trace) { 430 DebugFile.writeln("Begin CopyRegisters.append(" + sOriginTable + "," + sTargetTable + "," + sWhere + ")"); 431 DebugFile.incIdent(); 432 } 433 434 int iAppended = 0; 435 ResultSet oReadRSet = null; 436 ResultSetMetaData oReadMDat = null; 437 PreparedStatement oReadStmt = null; 438 ExtendedStatement oUpdtStmt = null; 439 ExtendedStatement oInsrtStmt = null; 440 441 DataTblDef oOriginDef = new DataTblDef(); 442 DataTblDef oTargetDef = new DataTblDef(); 443 444 String sTargetPK = oTargetDef.getPrimaryKeys(oOrigin, sSchema, sCatalog, sTargetTable); 445 446 oOriginDef.readMetaData(oOrigin, sOriginTable, null); 447 oTargetDef.readMetaData(oTarget, sTargetTable, sTargetPK); 448 449 final int iInsrtCols = oTargetDef.ColCount; 450 451 try { 452 oInsrtStmt = prepareInsertStatement(oTarget, oTargetDef); 453 454 oUpdtStmt = prepareUpdateStatement(oTarget, oTargetDef); 455 456 oReadStmt = prepareReadStatement (oOrigin, oTarget, oOriginDef, oTargetDef, sWhere); 457 458 try { oReadStmt.setFetchSize(FETCH_SIZE); } catch (SQLException ignore) { } 459 460 oReadRSet = oReadStmt.executeQuery(); 461 oReadMDat = oReadRSet.getMetaData(); 462 463 464 int iUpdated, iUpdtCols, iColPos, iOriginType, iTargetType; 465 466 if (oUpdtStmt!=null) 467 iUpdtCols = oUpdtStmt.columns.length; 468 else 469 iUpdtCols = 0; 470 471 while (oReadRSet.next()) { 472 473 for (int c=1; c<=iUpdtCols; c++) { 474 iColPos = oReadRSet.findColumn(oUpdtStmt.columns[c-1]); 475 iOriginType = oReadMDat.getColumnType(iColPos); 476 iTargetType = oTargetDef.ColTypes[oTargetDef.findColumnPosition(oUpdtStmt.columns[c-1])]; 477 478 480 oUpdtStmt.sqlstatement.setObject (c, cast(oReadRSet.getObject(iColPos), iOriginType, iTargetType), iTargetType); 481 } 482 483 if (DebugFile.trace) DebugFile.writeln("ExtendedStatement.executeUpdate(" + sTargetTable + ")"); 484 485 if (iUpdtCols>0) 486 iUpdated = oUpdtStmt.sqlstatement.executeUpdate(); 487 else { 488 iUpdated = (existsRow (oTarget, sTargetTable, sTargetPK, oReadRSet) ? 1 : 0); 489 } 490 491 if (0==iUpdated) { 492 for (int c=1; c<=iInsrtCols; c++) { 493 494 iColPos = oReadRSet.findColumn(oInsrtStmt.columns[c-1]); 495 iOriginType = oReadMDat.getColumnType(iColPos); 496 iTargetType = oTargetDef.ColTypes[oTargetDef.findColumnPosition(oInsrtStmt.columns[c-1])]; 497 498 500 oInsrtStmt.sqlstatement.setObject (c, cast(oReadRSet.getObject(iColPos), iOriginType, iTargetType), iTargetType); 501 } 502 503 if (DebugFile.trace) DebugFile.writeln("ExtendedStatement.executeInsert(" + sTargetTable + ")"); 504 505 iUpdated += oInsrtStmt.sqlstatement.executeUpdate(); 506 } 508 iAppended += iUpdated; 509 } } 511 catch (SQLException sqle) { 512 513 if (null!=oReadRSet) oReadRSet.close(); 514 if (null!=oReadStmt) oReadStmt.close(); 515 if (null!=oUpdtStmt) if (null!=oUpdtStmt.sqlstatement) oUpdtStmt.sqlstatement.close(); 516 517 throw new SQLException (sqle.getMessage(), sqle.getSQLState(), sqle.getErrorCode()); 518 } 519 if (null!=oReadRSet) oReadRSet.close(); 520 if (null!=oReadStmt) oReadStmt.close(); 521 if (null!=oUpdtStmt) if (null!=oUpdtStmt.sqlstatement) oUpdtStmt.sqlstatement.close(); 522 523 if (DebugFile.trace) { 524 DebugFile.decIdent(); 525 DebugFile.writeln("End CopyRegisters.append() : " + String.valueOf(iAppended)); 526 } 527 return iAppended; 528 } 530 } | Popular Tags |