KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2   Copyright (C) 2006 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 java.sql.SQLException JavaDoc;
36 import java.sql.Statement JavaDoc;
37 import java.sql.PreparedStatement JavaDoc;
38 import java.sql.ResultSet JavaDoc;
39 import java.sql.ResultSetMetaData JavaDoc;
40
41 import java.util.HashMap JavaDoc;
42
43 import java.io.IOException JavaDoc;
44 import java.io.StringReader JavaDoc;
45
46 import org.xml.sax.SAXException JavaDoc;
47
48 import com.knowgate.debug.DebugFile;
49
50 import com.knowgate.datacopy.DataStruct;
51
52 /**
53  * <p>Copy data from one table to another</p>
54  * @author Sergio Montoro Ten
55  * @version 3.0
56  */
57 public class FullTableCopy extends DataStruct {
58
59   public FullTableCopy() {
60   }
61
62   /**
63    * Copy some data from origin table to target table
64    * @param sOriginTable String
65    * @param sTargetTable String
66    * @param sOriginWhere String Filter clause (SQL WHERE) to be applied to origin table
67    * @param bTruncate boolean if <b>true</b> Truncate target table before inserting
68    * @throws SQLException
69    * @throws ClassNotFoundException
70    * @throws IOException
71    * @throws InstantiationException
72    * @throws IllegalAccessException
73    * @throws SAXException
74    */
75   public void insert(String JavaDoc sOriginTable, String JavaDoc sTargetTable,
76                      String JavaDoc sOriginWhere, boolean bTruncate)
77     throws SQLException JavaDoc,ClassNotFoundException JavaDoc,IOException JavaDoc,InstantiationException JavaDoc,
78            IllegalAccessException JavaDoc,SAXException JavaDoc {
79     PreparedStatement JavaDoc oStInsert;
80     PreparedStatement JavaDoc oStSelect;
81     Statement JavaDoc oStDelete;
82     Statement JavaDoc oStMeta;
83     ResultSet JavaDoc oRsMeta;
84     ResultSet JavaDoc oRsSelect;
85     ResultSetMetaData JavaDoc oRsMetaData;
86     StringReader JavaDoc oRead;
87     Object JavaDoc oField;
88     String JavaDoc sSQL;
89     int iColCount;
90     int iSQLType;
91     HashMap JavaDoc oSourceCols;
92     String JavaDoc sColList = "";
93
94     oStMeta = getOriginConnection().createStatement();
95     oRsMeta = oStMeta.executeQuery("SELECT * FROM " + sOriginTable + " WHERE 1=0");
96     oRsMetaData = oRsMeta.getMetaData();
97     iColCount = oRsMetaData.getColumnCount();
98     oSourceCols = new HashMap JavaDoc(iColCount*2+2);
99     for (int c=1; c<=iColCount; c++)
100       oSourceCols.put(oRsMetaData.getColumnName(c),null);
101     oRsMeta.close();
102     oStMeta.close();
103
104     sSQL = "INSERT INTO "+sTargetTable+" VALUES (?";
105     for (int c=2; c<=iColCount; c++) sSQL+=",?";
106     sSQL += ")";
107
108     oStInsert = getTargetConnection().prepareStatement(sSQL);
109
110     oStMeta = getTargetConnection().createStatement();
111     oRsMeta = oStMeta.executeQuery("SELECT * FROM " + sTargetTable + " WHERE 1=0");
112     oRsMetaData = oRsMeta.getMetaData();
113     iColCount = oRsMetaData.getColumnCount();
114     for (int c=1; c<=iColCount; c++) {
115       if (oSourceCols.containsKey(oRsMetaData.getColumnName(c)))
116         sColList += (sColList.length()==0 ? "" : ",") + oRsMetaData.getColumnName(c);
117       else
118         sColList += (sColList.length()==0 ? "" : ",") + "NULL AS "+oRsMetaData.getColumnName(c);
119     } // next
120
121     if (null==sOriginWhere)
122       oStSelect = getOriginConnection().prepareStatement("SELECT "+sColList+" FROM "+sOriginTable);
123     else if (sOriginWhere.trim().length()==0)
124       oStSelect = getOriginConnection().prepareStatement("SELECT "+sColList+" FROM "+sOriginTable);
125     else
126       oStSelect = getOriginConnection().prepareStatement("SELECT "+sColList+" FROM "+sOriginTable+" WHERE "+sOriginWhere);
127
128     oRsSelect = oStSelect.executeQuery();
129
130     if (bTruncate) {
131       oStDelete = getTargetConnection().createStatement();
132       oStDelete.execute("DELETE FROM " + sTargetTable);
133       oStDelete.close();
134     } // fi (bTruncate)
135
136     iRows = 0;
137
138     while (oRsSelect.next()) {
139       oRead = null;
140       for (int p=1; p<=iColCount; p++) {
141         iSQLType = oRsMetaData.getColumnType(p);
142         oField = oRsSelect.getObject(p);
143         if (iSQLType==-1) {
144           oRead = new StringReader JavaDoc(oField.toString()+" ");
145           oStInsert.setCharacterStream(p, oRead, oField.toString().length()-1);
146           }
147         else
148           oStInsert.setObject ( p,
149                                 convert(oField,iSQLType),
150                                 mapType(iSQLType));
151       } // next
152 oStInsert.executeUpdate();
153       if (oRead!=null) oRead.close();
154
155       iRows++;
156     } // wend
157
158     oRsSelect.close();
159     oStSelect.close();
160     oRsMeta.close();
161     oStMeta.close();
162     oStInsert.close();
163   }
164
165   /**
166    * Copy all data from origin table to target table
167    * @param sOriginTable String
168    * @param sTargetTable String
169    * @param bTruncate boolean if <b>true</b> Truncate target table before inserting
170    * @throws SQLException
171    * @throws ClassNotFoundException
172    * @throws IOException
173    * @throws InstantiationException
174    * @throws IllegalAccessException
175    * @throws SAXException
176    */
177   public void insert(String JavaDoc sOriginTable, String JavaDoc sTargetTable, boolean bTruncate)
178     throws SQLException JavaDoc,ClassNotFoundException JavaDoc,IOException JavaDoc,InstantiationException JavaDoc,
179            IllegalAccessException JavaDoc,SAXException JavaDoc {
180     insert (sOriginTable, sTargetTable, null, bTruncate);
181   }
182
183 }
184
Free Books   Free Magazines  
Popular Tags