001: /*
002: Copyright (C) 2006 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 java.sql.SQLException;
036: import java.sql.Statement;
037: import java.sql.PreparedStatement;
038: import java.sql.ResultSet;
039: import java.sql.ResultSetMetaData;
040:
041: import java.util.HashMap;
042:
043: import java.io.IOException;
044: import java.io.StringReader;
045:
046: import org.xml.sax.SAXException;
047:
048: import com.knowgate.debug.DebugFile;
049:
050: import com.knowgate.datacopy.DataStruct;
051:
052: /**
053: * <p>Copy data from one table to another</p>
054: * @author Sergio Montoro Ten
055: * @version 3.0
056: */
057: public class FullTableCopy extends DataStruct {
058:
059: public FullTableCopy() {
060: }
061:
062: /**
063: * Copy some data from origin table to target table
064: * @param sOriginTable String
065: * @param sTargetTable String
066: * @param sOriginWhere String Filter clause (SQL WHERE) to be applied to origin table
067: * @param bTruncate boolean if <b>true</b> Truncate target table before inserting
068: * @throws SQLException
069: * @throws ClassNotFoundException
070: * @throws IOException
071: * @throws InstantiationException
072: * @throws IllegalAccessException
073: * @throws SAXException
074: */
075: public void insert(String sOriginTable, String sTargetTable,
076: String sOriginWhere, boolean bTruncate)
077: throws SQLException, ClassNotFoundException, IOException,
078: InstantiationException, IllegalAccessException,
079: SAXException {
080: PreparedStatement oStInsert;
081: PreparedStatement oStSelect;
082: Statement oStDelete;
083: Statement oStMeta;
084: ResultSet oRsMeta;
085: ResultSet oRsSelect;
086: ResultSetMetaData oRsMetaData;
087: StringReader oRead;
088: Object oField;
089: String sSQL;
090: int iColCount;
091: int iSQLType;
092: HashMap oSourceCols;
093: String sColList = "";
094:
095: oStMeta = getOriginConnection().createStatement();
096: oRsMeta = oStMeta.executeQuery("SELECT * FROM " + sOriginTable
097: + " WHERE 1=0");
098: oRsMetaData = oRsMeta.getMetaData();
099: iColCount = oRsMetaData.getColumnCount();
100: oSourceCols = new HashMap(iColCount * 2 + 2);
101: for (int c = 1; c <= iColCount; c++)
102: oSourceCols.put(oRsMetaData.getColumnName(c), null);
103: oRsMeta.close();
104: oStMeta.close();
105:
106: sSQL = "INSERT INTO " + sTargetTable + " VALUES (?";
107: for (int c = 2; c <= iColCount; c++)
108: sSQL += ",?";
109: sSQL += ")";
110:
111: oStInsert = getTargetConnection().prepareStatement(sSQL);
112:
113: oStMeta = getTargetConnection().createStatement();
114: oRsMeta = oStMeta.executeQuery("SELECT * FROM " + sTargetTable
115: + " WHERE 1=0");
116: oRsMetaData = oRsMeta.getMetaData();
117: iColCount = oRsMetaData.getColumnCount();
118: for (int c = 1; c <= iColCount; c++) {
119: if (oSourceCols.containsKey(oRsMetaData.getColumnName(c)))
120: sColList += (sColList.length() == 0 ? "" : ",")
121: + oRsMetaData.getColumnName(c);
122: else
123: sColList += (sColList.length() == 0 ? "" : ",")
124: + "NULL AS " + oRsMetaData.getColumnName(c);
125: } // next
126:
127: if (null == sOriginWhere)
128: oStSelect = getOriginConnection().prepareStatement(
129: "SELECT " + sColList + " FROM " + sOriginTable);
130: else if (sOriginWhere.trim().length() == 0)
131: oStSelect = getOriginConnection().prepareStatement(
132: "SELECT " + sColList + " FROM " + sOriginTable);
133: else
134: oStSelect = getOriginConnection().prepareStatement(
135: "SELECT " + sColList + " FROM " + sOriginTable
136: + " WHERE " + sOriginWhere);
137:
138: oRsSelect = oStSelect.executeQuery();
139:
140: if (bTruncate) {
141: oStDelete = getTargetConnection().createStatement();
142: oStDelete.execute("DELETE FROM " + sTargetTable);
143: oStDelete.close();
144: } // fi (bTruncate)
145:
146: iRows = 0;
147:
148: while (oRsSelect.next()) {
149: oRead = null;
150: for (int p = 1; p <= iColCount; p++) {
151: iSQLType = oRsMetaData.getColumnType(p);
152: oField = oRsSelect.getObject(p);
153: if (iSQLType == -1) {
154: oRead = new StringReader(oField.toString() + " ");
155: oStInsert.setCharacterStream(p, oRead, oField
156: .toString().length() - 1);
157: } else
158: oStInsert.setObject(p, convert(oField, iSQLType),
159: mapType(iSQLType));
160: } // next
161: oStInsert.executeUpdate();
162: if (oRead != null)
163: oRead.close();
164:
165: iRows++;
166: } // wend
167:
168: oRsSelect.close();
169: oStSelect.close();
170: oRsMeta.close();
171: oStMeta.close();
172: oStInsert.close();
173: }
174:
175: /**
176: * Copy all data from origin table to target table
177: * @param sOriginTable String
178: * @param sTargetTable String
179: * @param bTruncate boolean if <b>true</b> Truncate target table before inserting
180: * @throws SQLException
181: * @throws ClassNotFoundException
182: * @throws IOException
183: * @throws InstantiationException
184: * @throws IllegalAccessException
185: * @throws SAXException
186: */
187: public void insert(String sOriginTable, String sTargetTable,
188: boolean bTruncate) throws SQLException,
189: ClassNotFoundException, IOException,
190: InstantiationException, IllegalAccessException,
191: SAXException {
192: insert(sOriginTable, sTargetTable, null, bTruncate);
193: }
194:
195: }
|