001: /*
002: Copyright (C) 2004 Know Gate S.L. All rights reserved.
003: C/Oņa, 107 1š2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.datacopy;
034:
035: import com.knowgate.debug.DebugFile;
036:
037: import java.sql.Connection;
038: import java.sql.SQLException;
039: import java.sql.PreparedStatement;
040: import java.sql.ResultSet;
041: import java.sql.ResultSetMetaData;
042:
043: import com.knowgate.misc.Gadgets;
044:
045: /**
046: * @author Sergio Montoro Ten
047: * @version 2.0
048: */
049:
050: public class CopyRegisters {
051:
052: public static int FETCH_SIZE = 100;
053:
054: private String sSchema;
055:
056: private String sCatalog;
057:
058: // ---------------------------------------------------------------------------
059:
060: private class ExtendedStatement {
061: public PreparedStatement sqlstatement;
062: public String[] columns;
063:
064: ExtendedStatement(PreparedStatement oStmt, String sCols) {
065: sqlstatement = oStmt;
066: columns = Gadgets.split(sCols, ',');
067: }
068: }
069:
070: // ---------------------------------------------------------------------------
071:
072: public CopyRegisters() {
073: sSchema = null;
074: sCatalog = null;
075: }
076:
077: public CopyRegisters(String schema, String catalog) {
078: sSchema = schema;
079: sCatalog = catalog;
080: }
081:
082: // ---------------------------------------------------------------------------
083:
084: private PreparedStatement prepareReadStatement(
085: Connection oOriginConn, Connection oTargetConn,
086: DataTblDef oOriginDef, DataTblDef oTargetDef, String sWhere)
087: throws SQLException {
088: PreparedStatement oReadStmt;
089:
090: if (DebugFile.trace) {
091: DebugFile
092: .writeln("Begin CopyRegisters.prepareReadStatement(...)");
093: DebugFile.incIdent();
094: }
095:
096: int iColPos;
097: String sColList = "";
098:
099: for (int c = 0; c < oTargetDef.ColCount; c++) {
100: iColPos = oOriginDef
101: .findColumnPosition(oTargetDef.ColNames[c]);
102:
103: if (-1 == iColPos)
104: sColList += "NULL AS " + oTargetDef.ColNames[c] + ",";
105: else
106: sColList += oTargetDef.ColNames[c] + ",";
107: }
108:
109: if (DebugFile.trace)
110: DebugFile.writeln("SELECT "
111: + sColList.substring(0, sColList.length() - 1)
112: + " FROM " + oOriginDef.BaseTable + " "
113: + (sWhere != null ? sWhere : ""));
114:
115: oReadStmt = oOriginConn.prepareStatement("SELECT "
116: + sColList.substring(0, sColList.length() - 1)
117: + " FROM " + oOriginDef.BaseTable + " "
118: + (sWhere != null ? sWhere : ""));
119:
120: if (DebugFile.trace) {
121: DebugFile.decIdent();
122: DebugFile
123: .writeln("End CopyRegisters.prepareReadStatement() : [PreparedStatement]");
124: }
125:
126: return oReadStmt;
127: }
128:
129: // ---------------------------------------------------------------------------
130:
131: private ExtendedStatement prepareInsertStatement(
132: Connection oTargetConn, DataTblDef oTargetDef)
133: throws SQLException {
134: PreparedStatement oInsrtStmt;
135:
136: if (DebugFile.trace) {
137: DebugFile
138: .writeln("Begin CopyRegisters.prepareInsertStatement(...)");
139: DebugFile.incIdent();
140: }
141:
142: String sColList = "";
143: String sValues = "(";
144:
145: for (int c = 0; c < oTargetDef.ColCount; c++) {
146: if (c != oTargetDef.ColCount - 1) {
147: sColList += oTargetDef.ColNames[c] + ",";
148: sValues += "?,";
149: } else {
150: sColList += oTargetDef.ColNames[c];
151: sValues += "?)";
152: }
153: }
154:
155: if (DebugFile.trace)
156: DebugFile
157: .writeln("Connection.prepareStatement(INSERT INTO "
158: + oTargetDef.BaseTable + " (" + sColList
159: + ") VALUES " + sValues + ")");
160:
161: oInsrtStmt = oTargetConn.prepareStatement("INSERT INTO "
162: + oTargetDef.BaseTable + " (" + sColList + ") VALUES "
163: + sValues);
164:
165: if (DebugFile.trace) {
166: DebugFile.decIdent();
167: DebugFile
168: .writeln("End CopyRegisters.prepareInsertStatement() : [ExtendedStatement]");
169: }
170:
171: return new ExtendedStatement(oInsrtStmt, sColList);
172: }
173:
174: // ---------------------------------------------------------------------------
175:
176: private ExtendedStatement prepareUpdateStatement(
177: Connection oTargetConn, DataTblDef oTargetDef)
178: throws SQLException {
179: PreparedStatement oUpdtStmt;
180:
181: if (DebugFile.trace) {
182: DebugFile
183: .writeln("Begin CopyRegisters.prepareUpdateStatement(...)");
184: DebugFile.incIdent();
185: }
186:
187: String sSQL = "UPDATE " + oTargetDef.BaseTable + " SET ";
188: String sColList = "", sColumns = "";
189:
190: for (int c = 0; c < oTargetDef.ColCount; c++) {
191: if (!oTargetDef.isPrimaryKey(c)) {
192: if (sColList.length() == 0)
193: sColList = oTargetDef.ColNames[c] + "=?";
194: else
195: sColList += "," + oTargetDef.ColNames[c] + "=?";
196:
197: sColumns += (sColumns.length() == 0 ? oTargetDef.ColNames[c]
198: : "," + oTargetDef.ColNames[c]);
199: } // fi (!oTargetDef.isPrimaryKey(c))
200: } // next
201:
202: if (sColList.length() == 0) {
203: if (DebugFile.trace) {
204: DebugFile.decIdent();
205: DebugFile
206: .writeln("End CopyRegisters.prepareUpdateStatement() : null");
207: }
208: return null;
209: }
210:
211: sSQL += sColList + " WHERE ";
212: sColList = "";
213:
214: for (int c = 0; c < oTargetDef.ColCount; c++) {
215: if (oTargetDef.isPrimaryKey(c)) {
216: if (sColList.length() == 0)
217: sColList = oTargetDef.ColNames[c] + "=?";
218: else
219: sColList += " AND " + oTargetDef.ColNames[c] + "=?";
220:
221: sColumns += (sColumns.length() == 0 ? oTargetDef.ColNames[c]
222: : "," + oTargetDef.ColNames[c]);
223: } // fi (oTargetDef.isPrimaryKey(c))
224: } // next
225:
226: if (sColList.length() == 0)
227: throw new SQLException(
228: "Could not find primary key for table "
229: + oTargetDef.BaseTable, "42S12");
230:
231: sSQL += sColList;
232:
233: if (DebugFile.trace)
234: DebugFile.writeln("Connection.prepareStatement(" + sSQL
235: + ")");
236:
237: oUpdtStmt = oTargetConn.prepareStatement(sSQL);
238:
239: if (DebugFile.trace) {
240: DebugFile.decIdent();
241: DebugFile
242: .writeln("End CopyRegisters.prepareUpdateStatement() : [PreparedStatement]");
243: }
244:
245: return new ExtendedStatement(oUpdtStmt, sColumns);
246: }
247:
248: // ---------------------------------------------------------------------------
249:
250: public Object cast(Object oOrigin, int iOriginType, int iTargetType) {
251: return oOrigin;
252: }
253:
254: // ---------------------------------------------------------------------------
255:
256: private boolean existsRow(Connection oConn, String sTable,
257: String sPKCols, ResultSet oRow) throws SQLException {
258: String[] aCols = Gadgets.split(sPKCols, ',');
259: String sSQL = "SELECT NULL FROM " + sTable + " WHERE ";
260:
261: for (int c = 0; c < aCols.length; c++) {
262: if (c > 0)
263: sSQL += " AND ";
264: sSQL += aCols[c] + "=?";
265: } // next
266:
267: if (DebugFile.trace)
268: DebugFile.writeln("Connection.prepareStatement(" + sSQL
269: + ")");
270:
271: PreparedStatement oStmt = oConn.prepareStatement(sSQL);
272: for (int c = 0; c < aCols.length; c++)
273: oStmt.setObject(c + 1, oRow.getObject(aCols[c]));
274: ResultSet oRSet = oStmt.executeQuery();
275: boolean bExists = oRSet.next();
276: oRSet.close();
277: oStmt.close();
278:
279: if (DebugFile.trace) {
280: for (int c = 0; c < aCols.length; c++) {
281: if (c > 0)
282: DebugFile.write(",");
283: DebugFile.write(aCols[c] + "="
284: + oRow.getObject(aCols[c]));
285: }
286: DebugFile.writeln(bExists ? " exists" : " does not exist");
287: }
288:
289: return bExists;
290: } // existsRow
291:
292: // ---------------------------------------------------------------------------
293:
294: /**
295: * <p>Insert registers from origin table to target table.</p>
296: * @param oOrigin JDBC Origin Connection
297: * @param oTarget JDBC Target Connection
298: * @param sOriginTable Origin Table Name
299: * @param sTargetTable Target Table Name
300: * @param sWhere SQL filter clause to be applied at origin fron retrieving only a register subset
301: * @return Number of inserted rows
302: * @throws SQLException A duplicated primary key exception is thrown if any inserted register already exists at target table
303: */
304: public int insert(Connection oOrigin, Connection oTarget,
305: String sOriginTable, String sTargetTable, String sWhere)
306: throws SQLException {
307:
308: if (DebugFile.trace) {
309: DebugFile.writeln("Begin CopyRegisters.insert("
310: + sOriginTable + "," + sTargetTable + "," + sWhere
311: + ")");
312: DebugFile.incIdent();
313: }
314:
315: int iInserted = 0;
316: ResultSet oReadRSet = null;
317: ExtendedStatement oInsrtStmt = null;
318: PreparedStatement oReadStmt = null;
319:
320: DataTblDef oOriginDef = new DataTblDef();
321: DataTblDef oTargetDef = new DataTblDef();
322:
323: oOriginDef.readMetaData(oOrigin, sOriginTable, null);
324: oTargetDef.readMetaData(oTarget, sTargetTable, null);
325:
326: try {
327: oInsrtStmt = prepareInsertStatement(oTarget, oTargetDef);
328:
329: oReadStmt = prepareReadStatement(oOrigin, oTarget,
330: oOriginDef, oTargetDef, sWhere);
331:
332: try {
333: oReadStmt.setFetchSize(FETCH_SIZE);
334: } catch (SQLException ignore) {
335: }
336:
337: oReadRSet = oReadStmt.executeQuery();
338:
339: final int iCols = oTargetDef.ColCount;
340:
341: while (oReadRSet.next()) {
342:
343: for (int c = 1; c <= iCols; c++) {
344: oInsrtStmt.sqlstatement.setObject(c, cast(oReadRSet
345: .getObject(c), oOriginDef.ColTypes[c - 1],
346: oTargetDef.ColTypes[c - 1]),
347: oTargetDef.ColTypes[c - 1]);
348: }
349: iInserted += oInsrtStmt.sqlstatement.executeUpdate();
350: } // wend
351: } catch (SQLException sqle) {
352: throw new SQLException(sqle.getMessage(), sqle
353: .getSQLState(), sqle.getErrorCode());
354: } finally {
355: if (null != oReadRSet)
356: oReadRSet.close();
357:
358: if (null != oReadStmt)
359: oReadStmt.close();
360:
361: if (null != oInsrtStmt)
362: oInsrtStmt.sqlstatement.close();
363: }
364:
365: if (DebugFile.trace) {
366: DebugFile.decIdent();
367: DebugFile.writeln("End CopyRegisters.insert() : "
368: + String.valueOf(iInserted));
369: }
370:
371: return iInserted;
372: } // insert
373:
374: // ---------------------------------------------------------------------------
375:
376: /**
377: * <p>Replace registers from origin table to target table.</p>
378: * <p>Registers not already present at target table will not be inserted from origin table</p>
379: * @param oOrigin JDBC Origin Connection
380: * @param oTarget JDBC Target Connection
381: * @param sOriginTable Origin Table Name
382: * @param sTargetTable Target Table Name
383: * @param sWhere SQL filter clause to be applied at origin fron retrieving only a register subset
384: * @return Number of replaced rows
385: * @throws SQLException
386: */
387: public int replace(Connection oOrigin, Connection oTarget,
388: String sOriginTable, String sTargetTable, String sWhere)
389: throws SQLException {
390:
391: if (DebugFile.trace) {
392: DebugFile.writeln("Begin CopyRegisters.replace("
393: + sOriginTable + "," + sTargetTable + "," + sWhere
394: + ")");
395: DebugFile.incIdent();
396: }
397:
398: int iReplaced = 0;
399: ResultSet oReadRSet = null;
400: ResultSetMetaData oReadMDat = null;
401: PreparedStatement oReadStmt = null;
402: ExtendedStatement oUpdtStmt = null;
403:
404: DataTblDef oOriginDef = new DataTblDef();
405: DataTblDef oTargetDef = new DataTblDef();
406:
407: String sTargetPK = oTargetDef.getPrimaryKeys(oOrigin, sSchema,
408: sCatalog, sTargetTable);
409:
410: oOriginDef.readMetaData(oOrigin, sOriginTable, null);
411: oTargetDef.readMetaData(oTarget, sTargetTable, sTargetPK);
412:
413: try {
414: oUpdtStmt = prepareUpdateStatement(oTarget, oTargetDef);
415:
416: oReadStmt = prepareReadStatement(oOrigin, oTarget,
417: oOriginDef, oTargetDef, sWhere);
418:
419: try {
420: oReadStmt.setFetchSize(FETCH_SIZE);
421: } catch (SQLException ignore) {
422: }
423:
424: oReadRSet = oReadStmt.executeQuery();
425: oReadMDat = oReadRSet.getMetaData();
426:
427: int iCols, iColPos, iOriginType, iTargetType;
428:
429: if (oUpdtStmt != null)
430: iCols = oUpdtStmt.columns.length;
431: else
432: iCols = 0;
433:
434: while (oReadRSet.next()) {
435:
436: for (int c = 1; c <= iCols; c++) {
437: iColPos = oReadRSet
438: .findColumn(oUpdtStmt.columns[c - 1]);
439: iOriginType = oReadMDat.getColumnType(iColPos);
440: iTargetType = oTargetDef.ColTypes[oTargetDef
441: .findColumnPosition(oUpdtStmt.columns[c - 1])];
442:
443: oUpdtStmt.sqlstatement.setObject(c, cast(oReadRSet
444: .getObject(iColPos), iOriginType,
445: iTargetType), iTargetType);
446: }
447: if (oUpdtStmt != null)
448: iReplaced += oUpdtStmt.sqlstatement.executeUpdate();
449: else
450: iReplaced = (existsRow(oTarget, sTargetTable,
451: sTargetPK, oReadRSet) ? 1 : 0);
452: } // wend
453: } catch (SQLException sqle) {
454: throw new SQLException(sqle.getMessage(), sqle
455: .getSQLState(), sqle.getErrorCode());
456: } finally {
457: if (null != oReadRSet)
458: oReadRSet.close();
459:
460: if (null != oReadStmt)
461: oReadStmt.close();
462:
463: if (null != oUpdtStmt)
464: oUpdtStmt.sqlstatement.close();
465: }
466:
467: if (DebugFile.trace) {
468: DebugFile.decIdent();
469: DebugFile.writeln("End CopyRegisters.replace() : "
470: + String.valueOf(iReplaced));
471: }
472: return iReplaced;
473: } // replace
474:
475: // ---------------------------------------------------------------------------
476:
477: /**
478: * <p>Append registers from origin table to target table.</p>
479: * <p>Registers not present at target table will be inserted from origin table and those present will be updated</p>
480: * @param oOrigin JDBC Origin Connection
481: * @param oTarget JDBC Target Connection
482: * @param sOriginTable Origin Table Name
483: * @param sTargetTable Target Table Name
484: * @param sWhere SQL filter clause to be applied at origin fron retrieving only a register subset
485: * @return Number of replaced rows
486: * @throws SQLException
487: */
488: public int append(Connection oOrigin, Connection oTarget,
489: String sOriginTable, String sTargetTable, String sWhere)
490: throws SQLException {
491:
492: if (DebugFile.trace) {
493: DebugFile.writeln("Begin CopyRegisters.append("
494: + sOriginTable + "," + sTargetTable + "," + sWhere
495: + ")");
496: DebugFile.incIdent();
497: }
498:
499: int iAppended = 0;
500: ResultSet oReadRSet = null;
501: ResultSetMetaData oReadMDat = null;
502: PreparedStatement oReadStmt = null;
503: ExtendedStatement oUpdtStmt = null;
504: ExtendedStatement oInsrtStmt = null;
505:
506: DataTblDef oOriginDef = new DataTblDef();
507: DataTblDef oTargetDef = new DataTblDef();
508:
509: String sTargetPK = oTargetDef.getPrimaryKeys(oOrigin, sSchema,
510: sCatalog, sTargetTable);
511:
512: oOriginDef.readMetaData(oOrigin, sOriginTable, null);
513: oTargetDef.readMetaData(oTarget, sTargetTable, sTargetPK);
514:
515: final int iInsrtCols = oTargetDef.ColCount;
516:
517: try {
518: oInsrtStmt = prepareInsertStatement(oTarget, oTargetDef);
519:
520: oUpdtStmt = prepareUpdateStatement(oTarget, oTargetDef);
521:
522: oReadStmt = prepareReadStatement(oOrigin, oTarget,
523: oOriginDef, oTargetDef, sWhere);
524:
525: try {
526: oReadStmt.setFetchSize(FETCH_SIZE);
527: } catch (SQLException ignore) {
528: }
529:
530: oReadRSet = oReadStmt.executeQuery();
531: oReadMDat = oReadRSet.getMetaData();
532:
533: int iUpdated, iUpdtCols, iColPos, iOriginType, iTargetType;
534:
535: if (oUpdtStmt != null)
536: iUpdtCols = oUpdtStmt.columns.length;
537: else
538: iUpdtCols = 0;
539:
540: while (oReadRSet.next()) {
541:
542: for (int c = 1; c <= iUpdtCols; c++) {
543: iColPos = oReadRSet
544: .findColumn(oUpdtStmt.columns[c - 1]);
545: iOriginType = oReadMDat.getColumnType(iColPos);
546: iTargetType = oTargetDef.ColTypes[oTargetDef
547: .findColumnPosition(oUpdtStmt.columns[c - 1])];
548:
549: //if (DebugFile.trace) DebugFile.writeln("UpdateStatement.setObject (" + String.valueOf(c) + "," + cast(oReadRSet.getObject(iColPos), iOriginType, iTargetType) + "," + iTargetType + ")");
550:
551: oUpdtStmt.sqlstatement.setObject(c, cast(oReadRSet
552: .getObject(iColPos), iOriginType,
553: iTargetType), iTargetType);
554: }
555:
556: if (DebugFile.trace)
557: DebugFile
558: .writeln("ExtendedStatement.executeUpdate("
559: + sTargetTable + ")");
560:
561: if (iUpdtCols > 0)
562: iUpdated = oUpdtStmt.sqlstatement.executeUpdate();
563: else {
564: iUpdated = (existsRow(oTarget, sTargetTable,
565: sTargetPK, oReadRSet) ? 1 : 0);
566: }
567:
568: if (0 == iUpdated) {
569: for (int c = 1; c <= iInsrtCols; c++) {
570:
571: iColPos = oReadRSet
572: .findColumn(oInsrtStmt.columns[c - 1]);
573: iOriginType = oReadMDat.getColumnType(iColPos);
574: iTargetType = oTargetDef.ColTypes[oTargetDef
575: .findColumnPosition(oInsrtStmt.columns[c - 1])];
576:
577: //if (DebugFile.trace) DebugFile.writeln("InsertStatement.setObject (" + String.valueOf(c) + "," + cast(oReadRSet.getObject(iColPos), iOriginType, iTargetType) + "," + iTargetType + ")");
578:
579: oInsrtStmt.sqlstatement.setObject(c, cast(
580: oReadRSet.getObject(iColPos),
581: iOriginType, iTargetType), iTargetType);
582: }
583:
584: if (DebugFile.trace)
585: DebugFile
586: .writeln("ExtendedStatement.executeInsert("
587: + sTargetTable + ")");
588:
589: iUpdated += oInsrtStmt.sqlstatement.executeUpdate();
590: } // fi (0==iUpdated)
591:
592: iAppended += iUpdated;
593: } // wend
594: } catch (SQLException sqle) {
595:
596: if (null != oReadRSet)
597: oReadRSet.close();
598: if (null != oReadStmt)
599: oReadStmt.close();
600: if (null != oUpdtStmt)
601: if (null != oUpdtStmt.sqlstatement)
602: oUpdtStmt.sqlstatement.close();
603:
604: throw new SQLException(sqle.getMessage(), sqle
605: .getSQLState(), sqle.getErrorCode());
606: }
607: if (null != oReadRSet)
608: oReadRSet.close();
609: if (null != oReadStmt)
610: oReadStmt.close();
611: if (null != oUpdtStmt)
612: if (null != oUpdtStmt.sqlstatement)
613: oUpdtStmt.sqlstatement.close();
614:
615: if (DebugFile.trace) {
616: DebugFile.decIdent();
617: DebugFile.writeln("End CopyRegisters.append() : "
618: + String.valueOf(iAppended));
619: }
620: return iAppended;
621: } // append
622:
623: }
|