001: package com.quantum.csv.wizard;
002:
003: import java.io.BufferedReader;
004: import java.io.IOException;
005: import java.io.Writer;
006: import java.sql.Connection;
007: import java.sql.ResultSet;
008: import java.sql.SQLException;
009: import java.sql.Statement;
010: import java.util.Vector;
011: import java.util.zip.ZipOutputStream;
012:
013: import com.quantum.model.Entity;
014: import com.quantum.sql.SQLVirtualResultSet;
015: import com.quantum.util.StringMatrix;
016: import com.quantum.util.connection.NotConnectedException;
017: import com.quantum.util.sql.SQLInstructionBuilder;
018:
019: /**
020: * Converts data from a SQLVirtualResultSet to a CSV (Comma Separated Values)
021: * file. This file can be a regular text file or an entry in a Zip file.
022: *
023: * @author Julen
024: */
025: public class ConverterCSV {
026:
027: private static final String LINE_SEPARATOR = System
028: .getProperty("line.separator");
029:
030: private long numGoodInserts;
031: private long numBadInserts;
032:
033: /**
034: * Main convert function for regular files. It writes from a database to a
035: * CSV file.
036: *
037: * @param writer
038: * A writer to the file where to write the CSV lines
039: * @param entity
040: * The entity source of the data
041: * @param headerRow
042: * true if a header row with the column names is to be written
043: * @throws IOException
044: * @throws NotConnectedException
045: * @throws SQLException
046: */
047: public void convert(Writer writer, Entity entity,
048: boolean headerRow, char columnSeparator)
049: throws IOException, NotConnectedException, SQLException {
050:
051: SQLVirtualResultSet rs = new SQLVirtualResultSet(entity,
052: ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
053: if (headerRow) {
054: writer.write(filter(rs.getColumnNames(), columnSeparator));
055: writer.write(LINE_SEPARATOR);
056: }
057: try {
058: while (rs.next()) {
059: writer.write(filter(rs.getRowAsStringArray(),
060: columnSeparator));
061: writer.write(LINE_SEPARATOR);
062: writer.flush();
063: }
064: } finally {
065: rs.close();
066: }
067: }
068:
069: /**
070: * Main convert function for Zip files.
071: *
072: * @param zipOS
073: * A writer to the zip file where to write the CSV lines
074: * @param entity
075: * The entity source of the data
076: * @param headerRow
077: * true if a header row with the column names is to be written
078: * @throws IOException
079: * @throws NotConnectedException
080: * @throws SQLException
081: */
082: public void convert(ZipOutputStream zipOS, Entity entity,
083: boolean headerRow, char columnSeparator)
084: throws IOException, NotConnectedException, SQLException {
085:
086: SQLVirtualResultSet rs = new SQLVirtualResultSet(entity,
087: ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
088: if (headerRow) {
089: zipOS.write(filter(rs.getColumnNames(), columnSeparator)
090: .getBytes());
091: zipOS.write(LINE_SEPARATOR.getBytes());
092: }
093: try {
094: while (rs.next()) {
095: zipOS.write(filter(rs.getRowAsStringArray(),
096: columnSeparator).getBytes());
097: zipOS.write(LINE_SEPARATOR.getBytes());
098: zipOS.flush();
099: }
100: } finally {
101: rs.close();
102: }
103: }
104:
105: /**
106: * @param columnValues
107: * @return The String values joined in a String, surrounded by commas if
108: * needed, separated by the COLUMN_SEPARATOR character
109: */
110: public static String filter(String[] columnValues,
111: char columnSeparator) {
112: String result = "";
113: for (int i = 0; i < columnValues.length; i++) {
114: if (i > 0)
115: result += columnSeparator;
116: result += filter(columnValues[i], columnSeparator);
117: }
118: return result;
119: }
120:
121: /**
122: * @param string
123: * @return The given string, or the string surrounded by commas, if needed
124: */
125: private static String filter(String string, char columnSeparator) {
126: if (string == null)
127: return "";
128: // Empty strings will be marked in the exported CSV format as quoted
129: // empty
130: // strings, to differentiate them from null values
131: if (isClean(string, columnSeparator) && string.length() > 0)
132: return string;
133: else
134: return quote(string);
135: }
136:
137: /**
138: * @param string
139: * @return The given string surrounded by commas, and included commas
140: * duplicated
141: */
142: private static String quote(String s) {
143: if (s == null)
144: return "";
145: return "\"" + s.replaceAll("\"", "\"\"") + "\"";
146: }
147:
148: /**
149: * @param string
150: * @return true if the given string is clean (does not need to be surrounded
151: * by commas), false if not.
152: */
153: private static boolean isClean(String s, char columnSeparator) {
154: int len = s.length();
155:
156: for (int i = 0; i < len; i++) {
157: char c = s.charAt(i);
158: // if there is a comma or a double quote, is not clean
159: if (c == columnSeparator || c == '"'
160: || (i == 0 && Character.isWhitespace(c)) || // If it starts
161: // with
162: // whitespace
163: (i == (len - 1) && Character.isWhitespace(c))) // or ends
164: // with
165: // whitspace,
166: // is not
167: // clean
168: return false;
169: }
170: // if there is a line separator, is not clean
171: return (s.indexOf(LINE_SEPARATOR) < 0);
172: }
173:
174: /**
175: * @param stream
176: * @param entity
177: * @param b
178: */
179: public void convert(BufferedReader stream, Entity entity,
180: char endOfColumn, boolean hasHeaderRow,
181: boolean ignoreHeader, String endOfLine) {
182: try {
183: CSVParser parser = new CSVParser(stream, endOfLine,
184: endOfColumn);
185: Vector columns = new Vector();
186: Vector emptyStrings = new Vector();
187: Vector headerColumns = null;
188: Connection connection = entity.getBookmark()
189: .getConnection();
190: SQLVirtualResultSet rs = new SQLVirtualResultSet(entity,
191: ResultSet.TYPE_FORWARD_ONLY,
192: ResultSet.CONCUR_UPDATABLE);
193: // If it has a header row, read it
194: if (hasHeaderRow) {
195: parser.parse(columns, emptyStrings);
196: // And see if it's not to be ignored
197: if (!ignoreHeader) {
198: headerColumns = (Vector) columns.clone();
199: }
200: }
201: columns.clear();
202: numGoodInserts = 0;
203: numBadInserts = 0;
204: if (rs.isInsertCapable()) {
205: try {
206: while (parser.parse(columns, emptyStrings) == 0) {
207: if (rs.insertRow(columns, headerColumns,
208: emptyStrings)) {
209: numGoodInserts++;
210: } else {
211: numBadInserts++;
212: }
213: columns.clear();
214: }
215: } finally {
216: rs.close();
217: }
218:
219: } else {
220: // TODO: properly test this branch
221: // Get the names of the columns from the recordset
222: String[] columnNames = rs.getColumnNames();
223: // Match with the header columns, if given
224: if (headerColumns != null) {
225: Vector matchedColumnNames = new Vector();
226: for (int i = 0; i < columnNames.length; i++) {
227: if (headerColumns.contains(columnNames[i])) {
228: matchedColumnNames.add(columnNames[i]);
229: }
230: }
231: columnNames = (String[]) matchedColumnNames
232: .toArray(new String[matchedColumnNames
233: .size()]);
234: }
235: // No further use for the recordset, we'll insert using INSERT
236: // sentences
237: rs.close();
238: StringMatrix columnsMatrix = new StringMatrix();
239: Statement statement = connection.createStatement();
240: while (parser.parse(columns, emptyStrings) == 0) {
241: columnsMatrix.clearValues();
242: for (int i = 0; i < columnNames.length; i++) {
243: // If value not empty, and not an empty string, add to
244: // list of columns
245: if (columnNames[i].length() > 0
246: || ((Boolean) emptyStrings.get(i))
247: .booleanValue())
248: columnsMatrix.addHeader(columnNames[i]);
249: }
250: for (int i = 0; i < columnNames.length; i++) {
251: // If value not empty, and not an empty string, add to
252: // list of values
253: if (columnNames[i].length() > 0
254: || ((Boolean) emptyStrings.get(i))
255: .booleanValue())
256: columnsMatrix.add((String) columns.get(i),
257: 0);
258: }
259: columns.clear();
260: String query = SQLInstructionBuilder.buildInsert(
261: entity, columnsMatrix);
262: // It should be much more efficient to use a prepared
263: // Statement, as coded below,
264: // but for that, every parameter must be passed, using a
265: // different type of function
266: // for every type of data column. Besides, this is only
267: // needed when the JDBC driver
268: // has no insert facility, so it probably wouldn't have
269: // support for prepared statements
270: // String query =
271: // SQLInstructionBuilder.buildPreparedInsert(entity,
272: // columnsMatrix);
273: // PreparedStatement statement =
274: // connection.prepareStatement(query);
275: try {
276: statement.execute(query);
277: } finally {
278: statement.close();
279: }
280: }
281:
282: }
283: } catch (IOException e) {
284: e.printStackTrace();
285: } catch (NotConnectedException e) {
286: e.printStackTrace();
287: } catch (SQLException e) {
288: e.printStackTrace();
289: }
290:
291: }
292:
293: public long getNumBadInserts() {
294: return numBadInserts;
295: }
296:
297: public long getNumGoodInserts() {
298: return numGoodInserts;
299: }
300:
301: }
|