001: package com.avaje.util.seedgen;
002:
003: import java.io.IOException;
004: import java.io.Writer;
005: import java.text.SimpleDateFormat;
006: import java.util.Date;
007: import java.util.Iterator;
008:
009: import jxl.Cell;
010: import jxl.CellType;
011: import jxl.DateCell;
012:
013: /**
014: * Generates the sql insert statements for a given sheet.
015: */
016: public class XlSeedlInsertGenerator {
017:
018: XlSeedDataSheet dataSheet;
019:
020: /**
021: * Derived insert sql prefix
022: */
023: String rowPrefix;
024:
025: /**
026: * The insert sql suffix
027: */
028: String rowSuffix = ");";
029:
030: /**
031: * Null literal value
032: */
033: String nullLiteral = "null";
034:
035: /**
036: * ANSI SQL Date format.
037: */
038: SimpleDateFormat ansiDate = new SimpleDateFormat("yyyy-MM-dd");
039:
040: /**
041: * ANSI SQL Timestamp format.
042: */
043: SimpleDateFormat ansiTimestamp = new SimpleDateFormat(
044: "yyyy-MM-dd HH:mm:ss");
045:
046: /**
047: * Now string that can be used in timestamp data.
048: */
049: String now = "now";
050:
051: /**
052: * Today string that can be used in date data.
053: */
054: String today = "today";
055:
056: /**
057: * Right now used for now and today output.
058: */
059: Date nowDate = new Date();
060:
061: /**
062: * Used as end of line.
063: */
064: String newLine = "\r\n";
065:
066: /**
067: * Generate the seed sql insert statements to the writer.
068: *
069: * @param writer the destination of the sql insert statements
070: * @param dataSheet the sheet that is the source for generating the sql
071: */
072: public String generate(Writer writer, XlSeedDataSheet dataSheet)
073: throws IOException {
074:
075: this .rowPrefix = null;
076: this .dataSheet = dataSheet;
077:
078: int rowCount = dataSheet.getRowCount();
079:
080: for (int i = 0; i < rowCount; i++) {
081: String line = generateRow(i);
082: writer.write(line);
083: writer.write(newLine);
084: }
085:
086: return "[" + rowCount + "] " + dataSheet.toString();
087: }
088:
089: private String getRowPrefix() {
090: if (rowPrefix == null) {
091: rowPrefix = generateRowPrefix();
092: }
093: return rowPrefix;
094: }
095:
096: private String getRowSuffix() {
097: return rowSuffix;
098: }
099:
100: private String generateRow(int rowIndex) {
101:
102: StringBuffer sb = new StringBuffer();
103: sb.append(getRowPrefix());
104: for (int i = 0; i < dataSheet.getColumnCount(); i++) {
105: if (i > 0) {
106: sb.append(" ,");
107: }
108: XlSeedDataColumn dataColumn = dataSheet.getDataColumn(i);
109: String c = getPaddedContent(i, rowIndex, dataColumn);
110: // String c = getColumnContent(i, rowIndex);
111: sb.append(c);
112: }
113: sb.append(getRowSuffix());
114: return sb.toString();
115: }
116:
117: private String getPaddedContent(int col, int row,
118: XlSeedDataColumn dataColumn) {
119: String c = getColumnContent(col, row);
120: int padding = dataColumn.getMaxDataWidth() - c.length();
121: if (dataColumn.isTypeString()) {
122: padding += 2; // add two for quotes...
123: }
124: if (dataColumn.isTypeDate()) {
125: padding += 6; // add two for quotes...
126: }
127: if (dataColumn.isTypeTimestamp()) {
128: padding += 11; // add two for quotes...
129: }
130: if (padding > 0) {
131: StringBuffer sb = new StringBuffer();
132: sb.append(c);
133: for (int i = 0; i < padding; i++) {
134: sb.append(" ");
135: }
136: return sb.toString();
137: } else {
138: return c;
139: }
140: }
141:
142: private String getColumnContent(int col, int row) {
143: Cell cell = dataSheet.getCell(col, row);
144: String content = cell.getContents();
145: if (content == null || content.length() == 0) {
146: return nullLiteral;
147: }
148: XlSeedDataColumn dataColumn = dataSheet.getDataColumn(col);
149: return getColumnContentAutomatic(col, row, cell, dataColumn);
150: }
151:
152: private String getColumnContentAutomatic(int col, int row,
153: Cell cell, XlSeedDataColumn dataColumn) {
154:
155: String content = cell.getContents();
156: CellType baseType = dataColumn.getBaseCellType();
157:
158: if (baseType.equals(CellType.DATE)) {
159: if (content.equals(now)) {
160: return formatTimestamp(nowDate);
161: }
162: if (content.equals(today)) {
163: return formatDate(nowDate);
164: }
165:
166: DateCell dateCell = (DateCell) cell;
167: Date theDate = dateCell.getDate();
168: if (content.length() > 10) {
169: return formatTimestamp(theDate);
170: } else {
171: return formatDate(theDate);
172: }
173: }
174: if (baseType.equals(CellType.NUMBER)) {
175: return formatNumeric(content);
176: }
177: if (baseType.equals(CellType.BOOLEAN)) {
178: return formatBoolean(content);
179: }
180: return formatVarchar(content);
181: }
182:
183: private String formatBoolean(String content) {
184: return content;
185: }
186:
187: private String formatVarchar(String content) {
188: return "'" + content + "'";
189: }
190:
191: private String formatNumeric(String content) {
192: return content;
193: }
194:
195: private String formatTimestamp(Date theDate) {
196: String c = ansiTimestamp.format(theDate);
197: return "TIMESTAMP'" + c + "'";
198: }
199:
200: private String formatDate(Date theDate) {
201: String c = ansiDate.format(theDate);
202: return "DATE'" + c + "'";
203: }
204:
205: // private String formatTime(String content) {
206: // return "TIME'" + content + "'";
207: // }
208:
209: private String generateRowPrefix() {
210:
211: StringBuffer sb = new StringBuffer();
212: sb.append("insert into ").append(dataSheet.getTableName());
213:
214: Iterator colIt = dataSheet.columns();
215: int colCount = 0;
216: sb.append(" (");
217: while (colIt.hasNext()) {
218: XlSeedDataColumn column = (XlSeedDataColumn) colIt.next();
219: if (colCount > 0) {
220: sb.append(", ");
221: }
222: sb.append(column.getName());
223: colCount++;
224: }
225: sb.append(") values (");
226: return sb.toString();
227: }
228: }
|