001: package org.dbbrowser.ui.helper.exporthelper;
002:
003: import infrastructure.logging.Log;
004: import java.io.BufferedWriter;
005: import java.io.File;
006: import java.io.FileWriter;
007: import java.io.IOException;
008: import java.text.DateFormat;
009: import java.text.SimpleDateFormat;
010: import java.util.Date;
011: import java.util.List;
012: import javax.swing.event.ChangeEvent;
013: import javax.swing.event.ChangeListener;
014: import javax.swing.table.AbstractTableModel;
015: import org.dbbrowser.db.engine.model.ColumnInfo;
016: import org.dbbrowser.ui.helper.DBTableDataTableModel;
017: import org.dbbrowser.ui.helper.exporthelper.wizard.WizardState;
018:
019: public class ExporterForSQLFile implements ExportHelper {
020: private boolean stop = false;
021: private DateFormat dateFormat = null;
022:
023: /**
024: * Export the data in the table to the specified file as SQL insert statements
025: * @param abstractTableModel
026: * @param changeListener
027: * @param fileToExportTo
028: * @throws ExportHelperException
029: */
030: public void export(AbstractTableModel abstractTableModel,
031: ChangeListener changeListener, File fileToExportTo)
032: throws ExportHelperException {
033: this .stop = false;
034: Log.getInstance().infoMessage("Starting SQL Export...",
035: ExporterForCSVFile.class.getName());
036:
037: //Set the date format
038: String dateFormatString = (String) WizardState.getInstance()
039: .getState("Date format");
040: this .dateFormat = new SimpleDateFormat(dateFormatString);
041:
042: //String buffer to hold sql
043: StringBuffer buffer = new StringBuffer();
044:
045: //Get the SQL prefix e.g. 'insert into <tablename> ('
046: String sqlInsertPrefix = this
047: .getSQLInsertPrefix(abstractTableModel);
048:
049: //Get the list of columns to include
050: List listOfColumns = (List) WizardState.getInstance().getState(
051: "List of columns to include");
052:
053: //Count of number of rows and columns
054: int rowCount = abstractTableModel.getRowCount();
055: int columnCount = abstractTableModel.getColumnCount();
056:
057: BufferedWriter writer = null;
058: try {
059: writer = new BufferedWriter(new FileWriter(fileToExportTo));
060:
061: //Process each row
062: for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
063: //If stopped, exit and export incomplete table
064: if (stop == true) {
065: break;
066: }
067:
068: buffer = new StringBuffer();
069: buffer.append(sqlInsertPrefix);
070:
071: Log.getInstance().debugMessage(
072: "Exporting row " + (rowIndex + 1) + " of "
073: + rowCount + " as SQL...",
074: ExporterForCSVFile.class.getName());
075:
076: //Process each column in the row - start from 1 as the first column is the row number
077: for (int columnIndex = 1; columnIndex < columnCount; columnIndex++) {
078: String columnName = abstractTableModel
079: .getColumnName(columnIndex);
080: //If the user wants this columns, then include it
081: if (listOfColumns.contains(columnName)) {
082: //Get the type of column
083: DBTableDataTableModel dbTableDataModel = (DBTableDataTableModel) abstractTableModel;
084: ColumnInfo ci = (ColumnInfo) dbTableDataModel
085: .getDBTable().getListOfColumnInfos()
086: .get(columnIndex - 1);
087:
088: //Format the value and add to buffer
089: Object cell = dbTableDataModel
090: .getObjectValueAt(rowIndex, columnIndex);
091: String formattedValue = formatValueForSQL(cell,
092: ci.getColumnTypeName());
093: buffer.append(formattedValue);
094: }
095:
096: //if it is the last column, add the new line character, else add a comma
097: if (columnIndex + 1 == columnCount) {
098: buffer.append(");\n");
099: } else {
100: buffer.append(", ");
101: }
102: }
103:
104: String rowString = buffer.toString();
105: writer.write(rowString, 0, rowString.length());
106:
107: //Inform the change listener
108: changeListener.stateChanged(new ChangeEvent(
109: new Integer(rowIndex)));
110: }
111: } catch (IOException exc) {
112: throw new ExportHelperException(exc.getMessage());
113: } finally {
114: try {
115: writer.flush();
116: writer.close();
117: } catch (IOException exc) {
118: //Cant do anything
119: }
120: }
121:
122: Log.getInstance().infoMessage("Finished SQL export",
123: ExporterForCSVFile.class.getName());
124: }
125:
126: /**
127: * Formats the cell object depending on its type
128: * @param cell
129: * @param columnnTypeName
130: * @return
131: * @throws ExportHelperException
132: */
133: private String formatValueForSQL(Object cell, String columnnTypeName)
134: throws ExportHelperException {
135: StringBuffer buffer = new StringBuffer();
136: //if it is a varchar, add it as a string
137: if (ColumnInfo.COLUMN_TYPE_VARCHAR.equals(columnnTypeName)
138: || ColumnInfo.COLUMN_TYPE_VARCHAR2
139: .equals(columnnTypeName)) {
140: if (cell != null) {
141: String value = cell.toString();
142: buffer.append("'" + value + "'");
143: } else {
144: buffer.append("''");
145: }
146: }
147:
148: //if it is a number
149: if (ColumnInfo.COLUMN_TYPE_NUMBER.equals(columnnTypeName)) {
150: if (cell != null) {
151: String value = cell.toString();
152: buffer.append(value);
153: } else {
154: buffer.append("0");
155: }
156: }
157:
158: //if it is a char
159: if (ColumnInfo.COLUMN_TYPE_CHAR.equals(columnnTypeName)) {
160: if (cell != null) {
161: String value = cell.toString();
162: buffer.append("'" + value + "'");
163: } else {
164: buffer.append("''");
165: }
166: }
167:
168: //if it is a date
169: if (ColumnInfo.COLUMN_TYPE_DATE.equals(columnnTypeName)) {
170: if (cell != null) {
171: String dateFormatString = (String) WizardState
172: .getInstance().getState("Date format");
173: buffer.append("to_date('"
174: + this .dateFormat.format((Date) cell) + "', '"
175: + dateFormatString + "')");
176: } else {
177: buffer.append("''");
178: }
179: }
180:
181: return buffer.toString();
182: }
183:
184: /**
185: * Returns the SQL prefix e.g. 'insert into <tablename> (<columnname>)values('
186: * @param abstractTableModel
187: * @return
188: */
189: private String getSQLInsertPrefix(
190: AbstractTableModel abstractTableModel) {
191: DBTableDataTableModel dbTableDataModel = (DBTableDataTableModel) abstractTableModel;
192: StringBuffer buffer = new StringBuffer();
193: List listOfColumns = (List) WizardState.getInstance().getState(
194: "List of columns to include");
195: int columnCount = abstractTableModel.getColumnCount();
196: buffer.append("insert into "
197: + dbTableDataModel.getDBTable().getTableName() + "(");
198:
199: //Processing column names - start from 1 as the first column is the row number
200: for (int columnIndex = 1; columnIndex < columnCount; columnIndex++) {
201: String columnName = abstractTableModel
202: .getColumnName(columnIndex);
203:
204: //If the user wants this columns, then include it
205: if (listOfColumns.contains(columnName)) {
206: //Strip the (PK) from the primary key column name
207: if (columnName.endsWith("(PK)")) {
208: columnName = columnName.substring(0, columnName
209: .length() - 4);
210: }
211:
212: buffer.append(columnName);
213: }
214:
215: //if it is the last column, add the new line character, else add a comma
216: if (columnIndex + 1 == columnCount) {
217: buffer.append(") values (");
218: } else {
219: buffer.append(", ");
220: }
221: }
222:
223: return buffer.toString();
224: }
225:
226: /**
227: * Call this to stop the export. This is required as it may be a long process and it runs in a seperate thread
228: */
229: public void stop() {
230: this .stop = true;
231: }
232: }
|