001: /* ====================================================================
002: * The QueryForm License, Version 1.1
003: *
004: * Copyright (c) 1998 - 2003 David F. Glasser. All rights
005: * reserved.
006: *
007: * Redistribution and use in source and binary forms, with or without
008: * modification, are permitted provided that the following conditions
009: * are met:
010: *
011: * 1. Redistributions of source code must retain the above copyright
012: * notice, this list of conditions and the following disclaimer.
013: *
014: * 2. Redistributions in binary form must reproduce the above copyright
015: * notice, this list of conditions and the following disclaimer in
016: * the documentation and/or other materials provided with the
017: * distribution.
018: *
019: * 3. The end-user documentation included with the redistribution,
020: * if any, must include the following acknowledgment:
021: * "This product includes software developed by
022: * David F. Glasser."
023: * Alternately, this acknowledgment may appear in the software itself,
024: * if and wherever such third-party acknowledgments normally appear.
025: *
026: * 4. The names "QueryForm" and "David F. Glasser" must
027: * not be used to endorse or promote products derived from this
028: * software without prior written permission. For written
029: * permission, please contact dglasser@pobox.com.
030: *
031: * 5. Products derived from this software may not be called "QueryForm",
032: * nor may "QueryForm" appear in their name, without prior written
033: * permission of David F. Glasser.
034: *
035: * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
036: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
037: * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
038: * DISCLAIMED. IN NO EVENT SHALL DAVID F. GLASSER, THE APACHE SOFTWARE
039: * FOUNDATION OR ITS CONTRIBUTORS, OR ANY AUTHORS OR DISTRIBUTORS
040: * OF THIS SOFTWARE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
041: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
042: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
043: * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
044: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
045: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
046: * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
047: * SUCH DAMAGE.
048: * ====================================================================
049: *
050: * This product includes software developed by the
051: * Apache Software Foundation (http://www.apache.org/).
052: *
053: * ====================================================================
054: *
055: * $Source: /cvsroot/qform/qform/src/org/glasser/sql/ExportManager.java,v $
056: * $Revision: 1.4 $
057: * $Author: dglasser $
058: * $Date: 2005/05/23 01:55:36 $
059: *
060: * --------------------------------------------------------------------
061: */
062: package org.glasser.sql;
063:
064: import org.glasser.util.Formatter;
065: import org.glasser.util.Util;
066: import java.util.HashMap;
067: import java.util.List;
068: import java.util.Arrays;
069: import java.io.*;
070: import java.sql.*;
071:
072: public class ExportManager {
073:
074: private static int lastInsertStatementLength = 100;
075:
076: public static String buildInsertStatement(List rowData,
077: String tableName, String[] columnNames,
078: Formatter[] formatters) {
079: return buildInsertStatement(rowData, tableName, columnNames,
080: formatters, false);
081: }
082:
083: private final static String ENDL = System
084: .getProperty("line.separator");
085:
086: private final static String BROKEN_VALUES = ") " + ENDL + "VALUES(";
087:
088: public static String buildInsertStatement(List rowData,
089: String tableName, String[] columnNames,
090: Formatter[] formatters, boolean foldLine) {
091:
092: StringBuffer buffer = new StringBuffer(
093: lastInsertStatementLength + 50);
094:
095: buffer.append("INSERT INTO ");
096: buffer.append(tableName);
097: buffer.append(" (");
098: boolean needComma = false;
099: for (int j = 0; j < columnNames.length; j++) {
100: String columnName = columnNames[j];
101: if (columnName == null)
102: continue;
103: if (needComma) {
104: buffer.append(", ");
105: } else {
106: needComma = true;
107: }
108: buffer.append(columnName);
109: }
110: if (foldLine) {
111: buffer.append(BROKEN_VALUES);
112: } else {
113: buffer.append(") VALUES (");
114: }
115:
116: needComma = false;
117: for (int j = 0; j < columnNames.length; j++) {
118: String columnName = columnNames[j];
119: if (columnName == null)
120: continue;
121: if (needComma) {
122: buffer.append(", ");
123: } else {
124: needComma = true;
125: }
126:
127: Formatter formatter = formatters[j];
128:
129: if (formatters[j] == null) {
130: buffer.append("NULL");
131: } else {
132: buffer.append(formatter.getFormattedString(rowData
133: .get(j)));
134: }
135: }
136: buffer.append(")");
137:
138: String s = buffer.toString();
139: lastInsertStatementLength = Math.max(lastInsertStatementLength,
140: 100);
141: return s;
142: }
143:
144: private final static Formatter nullFormatter = new LiteralFormatter(
145: "NULL");
146:
147: private final static Object[][] dateFormatMappings = {
148: { new Integer(Types.DATE), "'{d' ''yyyy-MM-dd'''}'" },
149: { new Integer(Types.TIME), "'{t' ''HH:mm:ss'''}'" },
150: { new Integer(Types.TIMESTAMP),
151: "'{ts' ''yyyy-MM-dd HH:mm:ss'''}'" } };
152:
153: private final static HashMap defaultDateFormatMap = Util
154: .buildMap(dateFormatMappings);
155:
156: public static void exportResultSet(PrintWriter writer,
157: ResultSet resultSet, String tableName, String lineTerminal,
158: HashMap typeMap, HashMap dateFormatMap,
159: HashMap columnNameMap, Character openQuoteChar,
160: Character closeQuoteChar, boolean foldLines, int maxRows)
161: throws SQLException, IOException {
162:
163: char openQuote = '"', closeQuote = '"';
164: if (openQuoteChar != null)
165: openQuote = openQuoteChar.charValue();
166: if (closeQuoteChar != null)
167: closeQuote = closeQuoteChar.charValue();
168:
169: if (dateFormatMap == null) {
170: dateFormatMap = defaultDateFormatMap;
171: }
172:
173: ResultSetMetaData rsmd = resultSet.getMetaData();
174: int colCount = rsmd.getColumnCount();
175: String[] columnNames = new String[colCount];
176: Formatter[] formatters = new Formatter[colCount];
177: for (int j = 0; j < colCount; j++) {
178:
179: columnNames[j] = rsmd.getColumnName(j + 1);
180: if (columnNames[j] == null
181: || columnNames[j].trim().length() == 0) {
182: columnNames[j] = "@COLUMN-" + (j + 1) + "@";
183: }
184: if (columnNameMap != null) {
185: String tmp = (String) columnNameMap.get(columnNames[j]);
186: if (tmp != null)
187: columnNames[j] = tmp;
188: }
189: if (columnNames[j].indexOf(" ") > -1) {
190: columnNames[j] = openQuote + columnNames[j]
191: + closeQuote;
192: }
193:
194: Integer colType = new Integer(rsmd.getColumnType(j + 1));
195: if (typeMap != null) {
196: Integer tmp = (Integer) typeMap.get(colType);
197: if (tmp != null)
198: colType = tmp;
199: }
200: int sqlType = colType.intValue();
201:
202: if (DBUtil.isCharType(sqlType)) {
203: formatters[j] = CharFieldFormatter.SINGLETON;
204: } else if (DBUtil.isNumericType(sqlType)) {
205: formatters[j] = CharFieldFormatter.SINGLETON;
206: } else if (DBUtil.isDateTimeType(sqlType)) {
207: String formatString = (String) dateFormatMap
208: .get(colType);
209: if (formatString == null)
210: formatString = (String) defaultDateFormatMap
211: .get(colType);
212: formatters[j] = new DateFormatter(formatString);
213: } else {
214: formatters[j] = nullFormatter;
215: }
216: }
217:
218: List[] rows = DBUtil.readResultSet2(resultSet, maxRows);
219:
220: exportInsertStatements(writer, Arrays.asList(rows), null,
221: tableName, columnNames, formatters, lineTerminal,
222: foldLines);
223: }
224:
225: public static void exportInsertStatements(PrintWriter writer,
226: List resultSet, int[] rowsToExport, String tableName,
227: String[] columnNames, Formatter[] formatters,
228: String lineTerminal) throws IOException {
229: exportInsertStatements(writer, resultSet, rowsToExport,
230: tableName, columnNames, formatters, lineTerminal, false);
231: }
232:
233: public static void exportInsertStatements(PrintWriter writer,
234: List resultSet, int[] rowsToExport, String tableName,
235: String[] columnNames, Formatter[] formatters,
236: String lineTerminal, boolean foldLines) throws IOException {
237:
238: int limit = resultSet.size();
239:
240: if (rowsToExport != null) {
241: limit = rowsToExport.length;
242: }
243:
244: for (int j = 0; j < limit; j++) {
245:
246: int rowToExport = j;
247: if (rowsToExport != null) {
248: rowToExport = rowsToExport[j];
249: }
250:
251: String insertStatement = buildInsertStatement(
252: (List) resultSet.get(rowToExport), tableName,
253: columnNames, formatters, foldLines);
254: if (lineTerminal == null) {
255: writer.println(insertStatement);
256: } else {
257: writer.print(insertStatement);
258: writer.print(lineTerminal);
259: writer.println();
260: }
261: }
262: }
263:
264: private static int lastDelimitedRecordLength = 100;
265:
266: public static String buildDelimitedRecord(List rowData,
267: Formatter[] formatters, String delimiter) {
268:
269: StringBuffer buffer = new StringBuffer(
270: lastDelimitedRecordLength + 50);
271: boolean firstFieldWritten = false;
272: for (int j = 0; j < formatters.length; j++) {
273: if (formatters[j] == null)
274: continue;
275: if (firstFieldWritten) {
276: buffer.append(delimiter);
277: } else {
278: firstFieldWritten = true;
279: }
280: buffer.append(formatters[j].getFormattedString(rowData
281: .get(j)));
282: }
283:
284: String s = buffer.toString();
285: lastDelimitedRecordLength = Math.max(100, s.length());
286: return s;
287: }
288:
289: public static void exportDelimited(PrintWriter writer,
290: List resultSet, int[] rowsToExport, String delimiter,
291: String[] columnHeaders, Formatter[] formatters,
292: String lineTerminal) throws IOException {
293:
294: // if the columnHeaders array is non-null, write a header record.
295: if (columnHeaders != null) {
296: StringBuffer buffer = new StringBuffer(200);
297: boolean firstFieldWritten = false;
298: for (int j = 0; j < formatters.length; j++) {
299: if (formatters[j] == null)
300: continue;
301: if (firstFieldWritten) {
302: buffer.append(delimiter);
303: } else {
304: firstFieldWritten = true;
305: }
306: buffer.append(columnHeaders[j]);
307: }
308: String header = buffer.toString();
309: if (lineTerminal == null) {
310: writer.println(header);
311: } else {
312: writer.print(header);
313: writer.println(lineTerminal);
314: }
315: }
316:
317: // now write the rows to be exported
318: int limit = resultSet.size();
319:
320: if (rowsToExport != null) {
321: limit = rowsToExport.length;
322: }
323:
324: for (int j = 0; j < limit; j++) {
325:
326: int rowToExport = j;
327: if (rowsToExport != null) {
328: rowToExport = rowsToExport[j];
329: }
330:
331: String record = buildDelimitedRecord((List) resultSet
332: .get(rowToExport), formatters, delimiter);
333: if (lineTerminal == null) {
334: writer.println(record);
335: } else {
336: writer.print(record);
337: writer.println(lineTerminal);
338: }
339: }
340: }
341:
342: }
|