KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > knowgate > datacopy > CopyRegisters


1 /*
2   Copyright (C) 2004 Know Gate S.L. All rights reserved.
3                       C/Oņa, 107 1š2 28050 Madrid (Spain)
4
5   Redistribution and use in source and binary forms, with or without
6   modification, are permitted provided that the following conditions
7   are met:
8
9   1. Redistributions of source code must retain the above copyright
10      notice, this list of conditions and the following disclaimer.
11
12   2. The end-user documentation included with the redistribution,
13      if any, must include the following acknowledgment:
14      "This product includes software parts from hipergate
15      (http://www.hipergate.org/)."
16      Alternately, this acknowledgment may appear in the software itself,
17      if and wherever such third-party acknowledgments normally appear.
18
19   3. The name hipergate must not be used to endorse or promote products
20      derived from this software without prior written permission.
21      Products derived from this software may not be called hipergate,
22      nor may hipergate appear in their name, without prior written
23      permission.
24
25   This library is distributed in the hope that it will be useful,
26   but WITHOUT ANY WARRANTY; without even the implied warranty of
27   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
28
29   You should have received a copy of hipergate License with this code;
30   if not, visit http://www.hipergate.org or mail to info@hipergate.org
31 */
32
33 package com.knowgate.datacopy;
34
35 import com.knowgate.debug.DebugFile;
36
37 import java.sql.Connection JavaDoc;
38 import java.sql.SQLException JavaDoc;
39 import java.sql.PreparedStatement JavaDoc;
40 import java.sql.ResultSet JavaDoc;
41 import java.sql.ResultSetMetaData JavaDoc;
42
43 import com.knowgate.misc.Gadgets;
44
45 /**
46  * @author Sergio Montoro Ten
47  * @version 2.0
48  */
49
50 public class CopyRegisters {
51
52   public static int FETCH_SIZE = 100;
53
54   private String JavaDoc sSchema;
55
56   private String JavaDoc sCatalog;
57
58   // ---------------------------------------------------------------------------
59
60   private class ExtendedStatement {
61     public PreparedStatement JavaDoc sqlstatement;
62     public String JavaDoc[] columns;
63
64     ExtendedStatement (PreparedStatement JavaDoc oStmt, String JavaDoc sCols) {
65       sqlstatement = oStmt;
66       columns = Gadgets.split(sCols, ',');
67     }
68   }
69
70   // ---------------------------------------------------------------------------
71
72   public CopyRegisters() {
73     sSchema = null;
74     sCatalog = null;
75   }
76
77   public CopyRegisters(String JavaDoc schema, String JavaDoc catalog) {
78     sSchema = schema;
79     sCatalog = catalog;
80   }
81
82   // ---------------------------------------------------------------------------
83
84   private PreparedStatement JavaDoc prepareReadStatement (Connection JavaDoc oOriginConn, Connection JavaDoc oTargetConn, DataTblDef oOriginDef, DataTblDef oTargetDef, String JavaDoc sWhere)
85     throws SQLException JavaDoc {
86     PreparedStatement JavaDoc oReadStmt;
87
88     if (DebugFile.trace) {
89       DebugFile.writeln("Begin CopyRegisters.prepareReadStatement(...)");
90       DebugFile.incIdent();
91     }
92
93     int iColPos;
94     String JavaDoc 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   // ---------------------------------------------------------------------------
119
120   private ExtendedStatement prepareInsertStatement (Connection JavaDoc oTargetConn, DataTblDef oTargetDef)
121     throws SQLException JavaDoc {
122     PreparedStatement JavaDoc oInsrtStmt;
123
124     if (DebugFile.trace) {
125       DebugFile.writeln("Begin CopyRegisters.prepareInsertStatement(...)");
126       DebugFile.incIdent();
127     }
128
129     String JavaDoc sColList = "";
130     String JavaDoc 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   // ---------------------------------------------------------------------------
157
158   private ExtendedStatement prepareUpdateStatement (Connection JavaDoc oTargetConn, DataTblDef oTargetDef)
159     throws SQLException JavaDoc {
160     PreparedStatement JavaDoc oUpdtStmt;
161
162     if (DebugFile.trace) {
163       DebugFile.writeln("Begin CopyRegisters.prepareUpdateStatement(...)");
164       DebugFile.incIdent();
165     }
166
167     String JavaDoc sSQL = "UPDATE " + oTargetDef.BaseTable + " SET ";
168     String JavaDoc 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       } // fi (!oTargetDef.isPrimaryKey(c))
179 } // next
180
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       } // fi (oTargetDef.isPrimaryKey(c))
201 } // next
202
203     if (sColList.length()==0)
204       throw new SQLException JavaDoc("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   // ---------------------------------------------------------------------------
221
222   public Object JavaDoc cast (Object JavaDoc oOrigin, int iOriginType, int iTargetType) {
223     return oOrigin;
224   }
225
226   // ---------------------------------------------------------------------------
227
228   private boolean existsRow (Connection JavaDoc oConn, String JavaDoc sTable, String JavaDoc sPKCols, ResultSet JavaDoc oRow)
229     throws SQLException JavaDoc {
230     String JavaDoc[] aCols = Gadgets.split(sPKCols, ',');
231     String JavaDoc 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     } // next
237
238     if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSQL + ")");
239
240     PreparedStatement JavaDoc oStmt = oConn.prepareStatement(sSQL);
241     for (int c=0; c<aCols.length; c++)
242       oStmt.setObject(c+1, oRow.getObject(aCols[c]));
243     ResultSet JavaDoc 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   } // existsRow
258
259   // ---------------------------------------------------------------------------
260
261   /**
262    * <p>Insert registers from origin table to target table.</p>
263    * @param oOrigin JDBC Origin Connection
264    * @param oTarget JDBC Target Connection
265    * @param sOriginTable Origin Table Name
266    * @param sTargetTable Target Table Name
267    * @param sWhere SQL filter clause to be applied at origin fron retrieving only a register subset
268    * @return Number of inserted rows
269    * @throws SQLException A duplicated primary key exception is thrown if any inserted register already exists at target table
270    */
271   public int insert (Connection JavaDoc oOrigin, Connection JavaDoc oTarget, String JavaDoc sOriginTable, String JavaDoc sTargetTable, String JavaDoc sWhere)
272     throws SQLException JavaDoc {
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 JavaDoc oReadRSet = null;
281     ExtendedStatement oInsrtStmt= null;
282     PreparedStatement JavaDoc 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 JavaDoc 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       } // wend
308 }
309     catch (SQLException JavaDoc sqle) {
310       throw new SQLException JavaDoc (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   } // insert
327
328   // ---------------------------------------------------------------------------
329
330   /**
331    * <p>Replace registers from origin table to target table.</p>
332    * <p>Registers not already present at target table will not be inserted from origin table</p>
333    * @param oOrigin JDBC Origin Connection
334    * @param oTarget JDBC Target Connection
335    * @param sOriginTable Origin Table Name
336    * @param sTargetTable Target Table Name
337    * @param sWhere SQL filter clause to be applied at origin fron retrieving only a register subset
338    * @return Number of replaced rows
339    * @throws SQLException
340    */
341   public int replace (Connection JavaDoc oOrigin, Connection JavaDoc oTarget, String JavaDoc sOriginTable, String JavaDoc sTargetTable, String JavaDoc sWhere)
342     throws SQLException JavaDoc {
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 JavaDoc oReadRSet = null;
351     ResultSetMetaData JavaDoc oReadMDat = null;
352     PreparedStatement JavaDoc oReadStmt = null;
353     ExtendedStatement oUpdtStmt = null;
354
355     DataTblDef oOriginDef = new DataTblDef();
356     DataTblDef oTargetDef = new DataTblDef();
357
358     String JavaDoc 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 JavaDoc 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       } // wend
394 }
395     catch (SQLException JavaDoc sqle) {
396       throw new SQLException JavaDoc (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   } // replace
412
413   // ---------------------------------------------------------------------------
414
415   /**
416    * <p>Append registers from origin table to target table.</p>
417    * <p>Registers not present at target table will be inserted from origin table and those present will be updated</p>
418    * @param oOrigin JDBC Origin Connection
419    * @param oTarget JDBC Target Connection
420    * @param sOriginTable Origin Table Name
421    * @param sTargetTable Target Table Name
422    * @param sWhere SQL filter clause to be applied at origin fron retrieving only a register subset
423    * @return Number of replaced rows
424    * @throws SQLException
425    */
426   public int append (Connection JavaDoc oOrigin, Connection JavaDoc oTarget, String JavaDoc sOriginTable, String JavaDoc sTargetTable, String JavaDoc sWhere)
427     throws SQLException JavaDoc {
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 JavaDoc oReadRSet = null;
436     ResultSetMetaData JavaDoc oReadMDat = null;
437     PreparedStatement JavaDoc oReadStmt = null;
438     ExtendedStatement oUpdtStmt = null;
439     ExtendedStatement oInsrtStmt = null;
440
441     DataTblDef oOriginDef = new DataTblDef();
442     DataTblDef oTargetDef = new DataTblDef();
443
444     String JavaDoc 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 JavaDoc 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           //if (DebugFile.trace) DebugFile.writeln("UpdateStatement.setObject (" + String.valueOf(c) + "," + cast(oReadRSet.getObject(iColPos), iOriginType, iTargetType) + "," + iTargetType + ")");
479
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             //if (DebugFile.trace) DebugFile.writeln("InsertStatement.setObject (" + String.valueOf(c) + "," + cast(oReadRSet.getObject(iColPos), iOriginType, iTargetType) + "," + iTargetType + ")");
499
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         } // fi (0==iUpdated)
507
508         iAppended += iUpdated;
509       } // wend
510 }
511     catch (SQLException JavaDoc 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 JavaDoc (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   } // append
529
530 }
Popular Tags