001: package net.sourceforge.squirrel_sql.plugins.dataimport;
002:
003: /*
004: * Copyright (C) 2007 Thorsten Mürell
005: *
006: * This program is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU General Public License
008: * as published by the Free Software Foundation; either version 2
009: * of the License, or any later version.
010: *
011: * This program is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
014: * GNU General Public License for more details.
015: *
016: * You should have received a copy of the GNU General Public License
017: * along with this program; if not, write to the Free Software
018: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
019: */
020: import java.io.IOException;
021: import java.sql.PreparedStatement;
022: import java.sql.SQLException;
023: import java.sql.Types;
024: import java.util.Date;
025: import java.util.List;
026:
027: import javax.swing.JOptionPane;
028:
029: import net.sourceforge.squirrel_sql.client.session.ISession;
030: import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
031: import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
032: import net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo;
033: import net.sourceforge.squirrel_sql.fw.util.StringManager;
034: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
035: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
036: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
037: import net.sourceforge.squirrel_sql.plugins.dataimport.gui.ColumnMappingTableModel;
038: import net.sourceforge.squirrel_sql.plugins.dataimport.gui.ProgressBarDialog;
039: import net.sourceforge.squirrel_sql.plugins.dataimport.gui.SpecialColumnMapping;
040: import net.sourceforge.squirrel_sql.plugins.dataimport.importer.IFileImporter;
041: import net.sourceforge.squirrel_sql.plugins.dataimport.importer.UnsupportedFormatException;
042: import net.sourceforge.squirrel_sql.plugins.dataimport.prefs.DataImportPreferenceBean;
043: import net.sourceforge.squirrel_sql.plugins.dataimport.prefs.PreferencesManager;
044: import net.sourceforge.squirrel_sql.plugins.dataimport.util.DateUtils;
045:
046: /**
047: * This class does the main work for importing the file into the database.
048: *
049: * @author Thorsten Mürell
050: */
051: public class ImportDataIntoTableExecutor {
052: private final static ILogger log = LoggerController
053: .createLogger(ImportDataIntoTableExecutor.class);
054:
055: /** Internationalized strings for this class. */
056: private static final StringManager stringMgr = StringManagerFactory
057: .getStringManager(ImportDataIntoTableExecutor.class);
058:
059: /** the thread we do the work in */
060: private Thread execThread = null;
061:
062: private ISession session = null;
063: private ITableInfo table = null;
064: private TableColumnInfo[] columns = null;
065: private ColumnMappingTableModel columnMapping = null;
066: private IFileImporter importer = null;
067: private List<String> importerColumns = null;
068: private boolean skipHeader = false;
069:
070: /**
071: * The standard constructor
072: *
073: * @param session The session
074: * @param table The table to import into
075: * @param columns The columns of the destination table
076: * @param importerColumns The columns of the importer
077: * @param mapping The mapping of the columns
078: * @param importer The file importer
079: */
080: public ImportDataIntoTableExecutor(ISession session,
081: ITableInfo table, TableColumnInfo[] columns,
082: List<String> importerColumns,
083: ColumnMappingTableModel mapping, IFileImporter importer) {
084: this .session = session;
085: this .table = table;
086: this .columns = columns;
087: this .columnMapping = mapping;
088: this .importer = importer;
089: this .importerColumns = importerColumns;
090: }
091:
092: /**
093: * If the header should be skipped
094: *
095: * @param skip
096: */
097: public void setSkipHeader(boolean skip) {
098: skipHeader = skip;
099: }
100:
101: /**
102: * Starts the thread that executes the insert operation.
103: */
104: public void execute() {
105: Runnable runnable = new Runnable() {
106: public void run() {
107: _execute();
108: }
109: };
110: execThread = new Thread(runnable);
111: execThread.setName("Dataimport Executor Thread");
112: execThread.start();
113: }
114:
115: /**
116: * Performs the table copy operation.
117: */
118: private void _execute() {
119:
120: // Create column list
121: String columnList = createColumnList();
122: ISQLConnection conn = session.getSQLConnection();
123:
124: StringBuffer insertSQL = new StringBuffer();
125: insertSQL.append("insert into ").append(
126: table.getQualifiedName());
127: insertSQL.append(" (").append(columnList).append(") ");
128: insertSQL.append("VALUES ");
129: insertSQL.append(" (").append(
130: getQuestionMarks(getColumnCount())).append(")");
131:
132: PreparedStatement stmt = null;
133: boolean autoCommit = false;
134: int rows = 0;
135: boolean success = false;
136: try {
137: DataImportPreferenceBean settings = PreferencesManager
138: .getPreferences();
139: importer.open();
140: if (skipHeader)
141: importer.next();
142: autoCommit = conn.getAutoCommit();
143: conn.setAutoCommit(false);
144:
145: if (settings.isUseTruncate()) {
146: String sql = "DELETE FROM " + table.getQualifiedName();
147: stmt = conn.prepareStatement(sql);
148: stmt.execute();
149: stmt.close();
150: }
151:
152: stmt = conn.prepareStatement(insertSQL.toString());
153: //i18n[ImportDataIntoTableExecutor.importingDataInto=Importing data into {0}]
154: ProgressBarDialog.getDialog(session.getApplication()
155: .getMainFrame(), stringMgr.getString(
156: "ImportDataIntoTableExecutor.importingDataInto",
157: table.getSimpleName()), false, null);
158: int inputLines = importer.getRows();
159: if (inputLines > 0) {
160: ProgressBarDialog.setBarMinMax(0,
161: inputLines == -1 ? 5000 : inputLines);
162: } else {
163: ProgressBarDialog.setIndeterminate();
164: }
165:
166: while (importer.next()) {
167: rows++;
168: if (inputLines > 0) {
169: ProgressBarDialog.incrementBar(1);
170: }
171: stmt.clearParameters();
172: int i = 1;
173: for (TableColumnInfo column : columns) {
174: String mapping = getMapping(column);
175: try {
176: if (SpecialColumnMapping.SKIP
177: .getVisibleString().equals(mapping)) {
178: continue;
179: } else if (SpecialColumnMapping.FIXED_VALUE
180: .getVisibleString().equals(mapping)) {
181: bindFixedColumn(stmt, i++, column);
182: } else if (SpecialColumnMapping.AUTO_INCREMENT
183: .getVisibleString().equals(mapping)) {
184: bindAutoincrementColumn(stmt, i++, column,
185: rows);
186: } else if (SpecialColumnMapping.NULL
187: .getVisibleString().equals(mapping)) {
188: stmt.setNull(i++, column.getDataType());
189: } else {
190: bindColumn(stmt, i++, column);
191: }
192: } catch (UnsupportedFormatException ufe) {
193: // i18n[ImportDataIntoTableExecutor.wrongFormat=Imported column has not the required format.\nLine is: {0}, column is: {1}]
194: JOptionPane
195: .showMessageDialog(
196: session.getApplication()
197: .getMainFrame(),
198: stringMgr
199: .getString(
200: "ImportDataIntoTableExecutor.wrongFormat",
201: new Object[] {
202: rows,
203: i - 1 }));
204: throw ufe;
205: }
206: }
207: stmt.execute();
208: }
209: conn.commit();
210: conn.setAutoCommit(autoCommit);
211: importer.close();
212:
213: success = true;
214:
215: } catch (SQLException sqle) {
216: //i18n[ImportDataIntoTableExecutor.sqlException=A database error occured while inserting data]
217: //i18n[ImportDataIntoTableExecutor.error=Error]
218: JOptionPane
219: .showMessageDialog(
220: session.getApplication().getMainFrame(),
221: stringMgr
222: .getString("ImportDataIntoTableExecutor.sqlException"),
223: stringMgr
224: .getString("ImportDataIntoTableExecutor.error"),
225: JOptionPane.ERROR_MESSAGE);
226: log.error("Database error", sqle);
227: } catch (UnsupportedFormatException ufe) {
228: try {
229: conn.rollback();
230: } catch (Exception e) {
231: log.error(
232: "Unexpected exception while attempting to rollback: "
233: + e.getMessage(), e);
234: }
235: log.error("Unsupported format.", ufe);
236: } catch (IOException ioe) {
237: //i18n[ImportDataIntoTableExecutor.ioException=An error occured while reading the input file.]
238: JOptionPane
239: .showMessageDialog(
240: session.getApplication().getMainFrame(),
241: stringMgr
242: .getString("ImportDataIntoTableExecutor.ioException"),
243: stringMgr
244: .getString("ImportDataIntoTableExecutor.error"),
245: JOptionPane.ERROR_MESSAGE);
246: log.error("Error while reading file", ioe);
247: } finally {
248: if (stmt != null) {
249: try {
250: stmt.close();
251: } catch (SQLException sqle) { /* Do nothing */
252: }
253: }
254: ProgressBarDialog.dispose();
255: }
256:
257: if (success) {
258: //i18n[ImportDataIntoTableExecutor.success={0,choice,0#No records|1#One record|1<{0} records} successfully inserted.]
259: JOptionPane.showMessageDialog(session.getApplication()
260: .getMainFrame(), stringMgr.getString(
261: "ImportDataIntoTableExecutor.success", rows));
262: }
263: }
264:
265: private void bindAutoincrementColumn(PreparedStatement stmt,
266: int index, TableColumnInfo column, int counter)
267: throws SQLException, UnsupportedFormatException {
268: long value = 0;
269: try {
270: value = Long.parseLong(getFixedValue(column));
271: value += counter;
272: } catch (NumberFormatException nfe) {
273: throw new UnsupportedFormatException();
274: }
275: switch (column.getDataType()) {
276: case Types.BIGINT:
277: stmt.setLong(index, value);
278: break;
279: case Types.INTEGER:
280: case Types.NUMERIC:
281: stmt.setInt(index, (int) value);
282: break;
283: default:
284: throw new UnsupportedFormatException();
285: }
286: }
287:
288: private void bindFixedColumn(PreparedStatement stmt, int index,
289: TableColumnInfo column) throws SQLException,
290: UnsupportedFormatException {
291: String value = getFixedValue(column);
292: Date d = null;
293: switch (column.getDataType()) {
294: case Types.BIGINT:
295: try {
296: stmt.setLong(index, Long.parseLong(value));
297: } catch (NumberFormatException nfe) {
298: throw new UnsupportedFormatException();
299: }
300: break;
301: case Types.INTEGER:
302: case Types.NUMERIC:
303: try {
304: stmt.setInt(index, Integer.parseInt(value));
305: } catch (NumberFormatException nfe) {
306: throw new UnsupportedFormatException();
307: }
308: break;
309: case Types.DATE:
310: d = DateUtils.parseSQLFormats(value);
311: if (d == null)
312: throw new UnsupportedFormatException();
313: stmt.setDate(index, new java.sql.Date(d.getTime()));
314: break;
315: case Types.TIMESTAMP:
316: d = DateUtils.parseSQLFormats(value);
317: if (d == null)
318: throw new UnsupportedFormatException();
319: stmt.setTimestamp(index,
320: new java.sql.Timestamp(d.getTime()));
321: break;
322: case Types.TIME:
323: d = DateUtils.parseSQLFormats(value);
324: if (d == null)
325: throw new UnsupportedFormatException();
326: stmt.setTime(index, new java.sql.Time(d.getTime()));
327: break;
328: default:
329: stmt.setString(index, value);
330: }
331: }
332:
333: private void bindColumn(PreparedStatement stmt, int index,
334: TableColumnInfo column) throws SQLException,
335: UnsupportedFormatException, IOException {
336: switch (column.getDataType()) {
337: case Types.BIGINT:
338: stmt.setLong(index, importer
339: .getLong(getMappedColumn(column)));
340: break;
341: case Types.INTEGER:
342: case Types.NUMERIC:
343: stmt
344: .setInt(index, importer
345: .getInt(getMappedColumn(column)));
346: break;
347: case Types.DATE:
348: stmt.setDate(index, new java.sql.Date(importer.getDate(
349: getMappedColumn(column)).getTime()));
350: break;
351: case Types.TIMESTAMP:
352: stmt.setTimestamp(index, new java.sql.Timestamp(importer
353: .getDate(getMappedColumn(column)).getTime()));
354: break;
355: case Types.TIME:
356: stmt.setTime(index, new java.sql.Time(importer.getDate(
357: getMappedColumn(column)).getTime()));
358: break;
359: default:
360: stmt.setString(index, importer
361: .getString(getMappedColumn(column)));
362: }
363: }
364:
365: private int getMappedColumn(TableColumnInfo column) {
366: return importerColumns.indexOf(getMapping(column));
367: }
368:
369: private String getMapping(TableColumnInfo column) {
370: int pos = columnMapping.findTableColumn(column.getColumnName());
371: return columnMapping.getValueAt(pos, 1).toString();
372: }
373:
374: private String getFixedValue(TableColumnInfo column) {
375: int pos = columnMapping.findTableColumn(column.getColumnName());
376: return columnMapping.getValueAt(pos, 2).toString();
377: }
378:
379: private String createColumnList() {
380: StringBuffer columnsList = new StringBuffer();
381: for (TableColumnInfo column : columns) {
382: String mapping = getMapping(column);
383: if (SpecialColumnMapping.SKIP.getVisibleString().equals(
384: mapping))
385: continue;
386:
387: if (columnsList.length() != 0) {
388: columnsList.append(", ");
389: }
390: columnsList.append(column.getColumnName());
391: }
392: return columnsList.toString();
393: }
394:
395: private int getColumnCount() {
396: int count = 0;
397: for (TableColumnInfo column : columns) {
398: int pos = columnMapping.findTableColumn(column
399: .getColumnName());
400: String mapping = columnMapping.getValueAt(pos, 1)
401: .toString();
402: if (!SpecialColumnMapping.SKIP.getVisibleString().equals(
403: mapping)) {
404: count++;
405: }
406: }
407: return count;
408: }
409:
410: private String getQuestionMarks(int count) {
411: StringBuffer result = new StringBuffer();
412: for (int i = 0; i < count; i++) {
413: result.append("?");
414: if (i < count - 1) {
415: result.append(", ");
416: }
417: }
418: return result.toString();
419: }
420:
421: }
|