001: /*
002: * ValueConverter.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.util;
013:
014: import java.io.File;
015: import java.math.BigDecimal;
016: import java.sql.Types;
017: import java.text.ParseException;
018: import java.text.SimpleDateFormat;
019: import java.util.Calendar;
020: import java.util.Collection;
021: import java.util.HashSet;
022: import java.util.List;
023: import workbench.log.LogMgr;
024: import workbench.resource.Settings;
025:
026: /**
027: * Utility class to parse Strings into approriate Java classes according
028: * to a type from java.sql.Types.
029: *
030: * @author support@sql-workbench.net
031: */
032: public class ValueConverter {
033: /**
034: * Often used date formats which are tried when parsing a Date
035: * or a TimeStamp column
036: */
037: private final String[] dateFormats = new String[] { "yyyy-MM-dd",
038: "dd.MM.yyyy", "dd.MM.yy", "MM/dd/yy", "MM/dd/yyyy",
039: "dd-MMM-yyyy", "yyyy-MM-dd HH:mm:ss.SS",
040: "yyyy-MM-dd HH:mm:ss", "dd.MM.yyyy HH:mm:ss",
041: "MM/dd/yy HH:mm:ss", "MM/dd/yyyy HH:mm:ss" };
042:
043: private final String[] timestampFormats = new String[] {
044: "yyyy-MM-dd HH:mm:ss.SS", "yyyy-MM-dd HH:mm:ss",
045: "dd.MM.yyyy HH:mm:ss.SS", "dd.MM.yyyy HH:mm:ss",
046: "dd.MM.yy HH:mm:ss.SS", "dd.MM.yy HH:mm:ss",
047: "MM/dd/yyyy HH:mm:ss.SS", "MM/dd/yyyy HH:mm:ss",
048: "MM/dd/yy HH:mm:ss.SS", "MM/dd/yy HH:mm:ss", "yyyy-MM-dd",
049: "dd.MM.yyyy", "dd.MM.yy", "MM/dd/yy", "MM/dd/yyyy", };
050:
051: private final String[] timeFormats = new String[] { "HH:mm:ss.SS",
052: "HH:mm:ss", "HH:mm" };
053:
054: private String defaultDateFormat;
055: private String defaultTimestampFormat;
056: private char decimalCharacter = '.';
057: private SimpleDateFormat dateFormatter;
058: private SimpleDateFormat timestampFormatter;
059: private SimpleDateFormat formatter = new SimpleDateFormat();
060: private boolean autoConvertBooleanNumbers = true;
061: private Collection<String> booleanTrueValues = null;
062: private Collection<String> booleanFalseValues = null;
063:
064: private final Integer INT_TRUE = Integer.valueOf(1);
065: private final Integer INT_FALSE = Integer.valueOf(0);
066: private final Long LONG_TRUE = Long.valueOf(1);
067: private final Long LONG_FALSE = Long.valueOf(0);
068: private final BigDecimal BIG_TRUE = BigDecimal.valueOf(1);
069: private final BigDecimal BIG_FALSE = BigDecimal.valueOf(0);
070:
071: private static final String FORMAT_MILLIS = "millis";
072:
073: public ValueConverter() {
074: Settings sett = Settings.getInstance();
075: this .setDefaultDateFormat(sett.getDefaultDateFormat());
076: this
077: .setDefaultTimestampFormat(sett
078: .getDefaultTimestampFormat());
079: }
080:
081: public ValueConverter(String aDateFormat, String aTimeStampFormat) {
082: if (StringUtil.isEmptyString(aDateFormat)) {
083: this .setDefaultDateFormat(Settings.getInstance()
084: .getDefaultDateFormat());
085: } else {
086: this .setDefaultDateFormat(aDateFormat);
087: }
088:
089: if (StringUtil.isEmptyString(aTimeStampFormat)) {
090: this .setDefaultTimestampFormat(Settings.getInstance()
091: .getDefaultTimestampFormat());
092: } else {
093: this .setDefaultTimestampFormat(aTimeStampFormat);
094: }
095: }
096:
097: public void setDefaultDateFormat(String aFormat)
098: throws IllegalArgumentException {
099: if (!StringUtil.isEmptyString(aFormat)) {
100: if (aFormat.equalsIgnoreCase(FORMAT_MILLIS)) {
101: this .defaultTimestampFormat = FORMAT_MILLIS;
102: this .dateFormatter = null;
103: } else {
104: this .defaultDateFormat = aFormat;
105: this .dateFormatter = new SimpleDateFormat(aFormat);
106: }
107: }
108: }
109:
110: public void setDefaultTimestampFormat(String aFormat)
111: throws IllegalArgumentException {
112: if (!StringUtil.isEmptyString(aFormat)) {
113: if (aFormat.equalsIgnoreCase(FORMAT_MILLIS)) {
114: this .defaultTimestampFormat = FORMAT_MILLIS;
115: this .dateFormatter = null;
116: } else {
117: this .defaultTimestampFormat = aFormat;
118: this .timestampFormatter = new SimpleDateFormat(aFormat);
119: }
120: }
121: }
122:
123: public void setDecimalCharacter(char aChar) {
124: this .decimalCharacter = aChar;
125: }
126:
127: public void setAutoConvertBooleanNumbers(boolean flag) {
128: this .autoConvertBooleanNumbers = flag;
129: }
130:
131: /**
132: * Define a list of literals that should be treated as true or
133: * false when converting input values.
134: * If either collection is null, both are considered null
135: * If these values are not defined, the default boolean conversion implemented
136: * in {@link workbench.util.StringUtil#stringToBool(String)} is used (this is the
137: * default)
138: * @param trueValues String literals to be considered as <tt>true</tt>
139: * @param falseValues String literals to be considered as <tt>false</tt>
140: */
141: public void setBooleanLiterals(Collection<String> trueValues,
142: Collection<String> falseValues) {
143: if (trueValues == null || falseValues == null
144: || trueValues.size() == 0 || falseValues.size() == 0) {
145: this .booleanFalseValues = null;
146: this .booleanTrueValues = null;
147: } else {
148: this .booleanFalseValues = new HashSet<String>(falseValues);
149: this .booleanTrueValues = new HashSet<String>(trueValues);
150: }
151: }
152:
153: private Number getNumberFromString(String value, boolean useInt) {
154: if (value == null)
155: return null;
156:
157: try {
158: BigDecimal d = new BigDecimal(this
159: .adjustDecimalString(value));
160: if (useInt) {
161: return new Integer(d.intValueExact());
162: } else {
163: return new Long(d.longValueExact());
164: }
165: } catch (Exception e) {
166: // Ignore
167: }
168: return null;
169: }
170:
171: private Number getLong(String value) throws ConverterException {
172: if (value.length() == 0)
173: return null;
174:
175: try {
176: return new Long(value);
177: } catch (NumberFormatException e) {
178: // Maybe the long value is disguised as a decimal
179: Number n = getNumberFromString(value, false);
180: if (n != null) {
181: return n;
182: }
183:
184: // When exporting from a database that supports the boolean datatype
185: // into a database that maps this to an integer, we assume that
186: // true/false should be 1/0
187: if (autoConvertBooleanNumbers) {
188: Boolean b = getBoolean(value);
189: if (b != null) {
190: if (b.booleanValue()) {
191: return LONG_TRUE;
192: } else {
193: return LONG_FALSE;
194: }
195: }
196: }
197:
198: throw new ConverterException(value, Types.BIGINT, e);
199: }
200: }
201:
202: private Number getInt(String value, int type)
203: throws ConverterException {
204: if (value.length() == 0)
205: return null;
206:
207: try {
208: return new Integer(value);
209: } catch (NumberFormatException e) {
210: // Maybe the integer value is disguised as a decimal
211: Number n = getNumberFromString(value, true);
212: if (n != null)
213: return n;
214:
215: // When exporting from a database that supports the boolean datatype
216: // into a database that maps this to an integer, we assume that
217: // true/false should be 1/0
218: if (autoConvertBooleanNumbers) {
219: Boolean b = getBoolean(value);
220: if (b != null) {
221: if (b.booleanValue()) {
222: return INT_TRUE;
223: } else {
224: return INT_FALSE;
225: }
226: }
227: }
228:
229: throw new ConverterException(value, type, e);
230: }
231: }
232:
233: private Number getBigDecimal(String value, int type)
234: throws ConverterException {
235: if (value.length() == 0)
236: return null;
237:
238: try {
239: return new BigDecimal(this .adjustDecimalString(value));
240: } catch (NumberFormatException e) {
241: // When exporting from a database that supports the boolean datatype
242: // into a database that maps this to an integer, we assume that
243: // true/false should be 1/0
244: if (autoConvertBooleanNumbers) {
245: Boolean b = getBoolean(value);
246: if (b != null) {
247: if (b.booleanValue()) {
248: return BIG_TRUE;
249: } else {
250: return BIG_FALSE;
251: }
252: }
253: }
254: throw new ConverterException(value, type, e);
255: }
256: }
257:
258: /**
259: * Convert the given input value to a class instance
260: * according to the given type (from java.sql.Types)
261: * If the value is a blob file parameter as defined by {@link workbench.util.LobFileParameter}
262: * then a File object is returned that points to the data file (as passed in the
263: * blob file parameter)
264: * @see workbench.storage.DataStore#convertCellValue(Object, int)
265: */
266: public Object convertValue(Object aValue, int type)
267: throws ConverterException {
268: if (aValue == null) {
269: return null;
270: }
271:
272: String v = aValue.toString().trim();
273:
274: switch (type) {
275: case Types.BIGINT:
276: return getLong(v);
277:
278: case Types.INTEGER:
279: case Types.SMALLINT:
280: case Types.TINYINT:
281: return getInt(v, type);
282:
283: case Types.NUMERIC:
284: case Types.DECIMAL:
285: case Types.DOUBLE:
286: case Types.REAL:
287: case Types.FLOAT:
288: return getBigDecimal(v, type);
289:
290: case Types.CHAR:
291: case Types.VARCHAR:
292: case Types.LONGVARCHAR:
293: return aValue.toString();
294:
295: case Types.DATE:
296: if (v.length() == 0)
297: return null;
298:
299: try {
300: return this .parseDate((String) aValue);
301: } catch (Exception e) {
302: throw new ConverterException(aValue, type, e);
303: }
304:
305: case Types.TIMESTAMP:
306: if (v.length() == 0)
307: return null;
308: try {
309: return this .parseTimestamp((String) aValue);
310: } catch (Exception e) {
311: throw new ConverterException(aValue, type, e);
312: }
313:
314: case Types.TIME:
315: if (v.length() == 0)
316: return null;
317:
318: try {
319: return this .parseTime((String) aValue);
320: } catch (Exception e) {
321: throw new ConverterException(aValue, type, e);
322: }
323:
324: case Types.BLOB:
325: case Types.BINARY:
326: case Types.LONGVARBINARY:
327: case Types.VARBINARY:
328: if (aValue instanceof String) {
329: LobFileParameterParser p = null;
330: try {
331: p = new LobFileParameterParser(aValue.toString());
332: } catch (Exception e) {
333: throw new ConverterException(aValue, type, e);
334: }
335: LobFileParameter[] parms = p.getParameters();
336: if (parms == null)
337: return null;
338: String fname = parms[0].getFilename();
339: if (fname == null)
340: return null;
341: return new File(fname);
342: } else if (aValue instanceof File) {
343: return aValue;
344: } else if (aValue instanceof byte[]) {
345: return aValue;
346: }
347: return null;
348:
349: case Types.BIT:
350: case Types.BOOLEAN:
351: return convertBool(v, type);
352:
353: default:
354: return aValue;
355: }
356: }
357:
358: public String getDatePattern() {
359: return this .defaultDateFormat;
360: }
361:
362: public String getTimestampPattern() {
363: return this .defaultTimestampFormat;
364: }
365:
366: public java.sql.Time parseTime(String time) throws ParseException {
367: if (isCurrentTime(time)) {
368: Calendar c = Calendar.getInstance();
369: c.clear(Calendar.YEAR);
370: c.clear(Calendar.DAY_OF_MONTH);
371: c.clear(Calendar.MONTH);
372: java.util.Date now = c.getTime();
373: return new java.sql.Time(now.getTime());
374: }
375:
376: java.util.Date parsed = null;
377:
378: synchronized (this .formatter) {
379: for (int i = 0; i < timeFormats.length; i++) {
380: try {
381: this .formatter.applyPattern(timeFormats[i]);
382: parsed = this .formatter.parse(time);
383: LogMgr.logInfo("ValueConverter.parseTime()",
384: "Succeeded parsing the time string ["
385: + time + "] using the format: "
386: + formatter.toPattern());
387: break;
388: } catch (Exception e) {
389: parsed = null;
390: }
391: }
392: }
393:
394: if (parsed != null) {
395: return new java.sql.Time(parsed.getTime());
396: }
397: throw new ParseException("Could not parse [" + time
398: + "] as a time value!", 0);
399: }
400:
401: private java.sql.Date getToday() {
402: Calendar c = Calendar.getInstance();
403: c.set(Calendar.HOUR_OF_DAY, 0);
404: c.set(Calendar.HOUR, 0);
405: c.clear(Calendar.MINUTE);
406: c.clear(Calendar.SECOND);
407: c.clear(Calendar.MILLISECOND);
408: java.util.Date now = c.getTime();
409: return new java.sql.Date(now.getTime());
410: }
411:
412: public java.sql.Timestamp parseTimestamp(String aDate)
413: throws ParseException, NumberFormatException {
414: if (isCurrentTimestamp(aDate)) {
415: java.sql.Timestamp ts = new java.sql.Timestamp(System
416: .currentTimeMillis());
417: return ts;
418: }
419:
420: if (isCurrentDate(aDate)) {
421: return new java.sql.Timestamp(getToday().getTime());
422: }
423:
424: java.util.Date result = null;
425:
426: if (this .defaultTimestampFormat != null) {
427: try {
428: if (FORMAT_MILLIS
429: .equalsIgnoreCase(defaultTimestampFormat)) {
430: long value = Long.parseLong(aDate);
431: result = new java.util.Date(value);
432: } else {
433: synchronized (this .timestampFormatter) {
434: result = this .timestampFormatter.parse(aDate);
435: }
436: }
437: } catch (Exception e) {
438: LogMgr
439: .logWarning(
440: "ValueConverter.parseTimestamp()",
441: "Could not parse '"
442: + aDate
443: + "' using default format "
444: + this .timestampFormatter
445: .toPattern()
446: + ". Trying to recognize the format...",
447: null);
448: result = null;
449: }
450: }
451:
452: if (result == null) {
453: int usedPattern = -1;
454: synchronized (this .formatter) {
455: for (int i = 0; i < dateFormats.length; i++) {
456: try {
457: this .formatter
458: .applyPattern(timestampFormats[i]);
459: result = this .formatter.parse(aDate);
460: usedPattern = i;
461: break;
462: } catch (ParseException e) {
463: result = null;
464: }
465: }
466: }
467: if (usedPattern > -1) {
468: LogMgr.logInfo("ValueConverter.parseTimestamp()",
469: "Succeeded parsing '" + aDate
470: + "' using the format: "
471: + timestampFormats[usedPattern]);
472: }
473: }
474:
475: if (result != null) {
476: return new java.sql.Timestamp(result.getTime());
477: }
478: throw new ParseException("Could not convert [" + aDate
479: + "] to a timestamp value!", 0);
480: }
481:
482: public java.sql.Date parseDate(String aDate) throws ParseException {
483: if (isCurrentDate(aDate)) {
484: return getToday();
485: }
486:
487: if (isCurrentTimestamp(aDate)) {
488: return new java.sql.Date(System.currentTimeMillis());
489: }
490:
491: java.util.Date result = null;
492:
493: if (this .defaultDateFormat != null) {
494: try {
495: if (FORMAT_MILLIS
496: .equalsIgnoreCase(defaultTimestampFormat)) {
497: long value = Long.parseLong(aDate);
498: result = new java.util.Date(value);
499: } else {
500: synchronized (this .dateFormatter) {
501: result = this .dateFormatter.parse(aDate);
502: }
503: }
504: } catch (Exception e) {
505: LogMgr.logWarning("ValueConverter.parseDate()",
506: "Could not parse [" + aDate + "] using: "
507: + this .dateFormatter.toPattern(), null);
508: // Do not throw the exception yet as we will try the defaultTimestampFormat as well.
509: result = null;
510: }
511: }
512:
513: if (result == null && this .defaultTimestampFormat != null) {
514: try {
515: result = this .timestampFormatter.parse(aDate);
516: } catch (ParseException e) {
517: LogMgr
518: .logWarning(
519: "ValueConverter.parseDate()",
520: "Could not parse ["
521: + aDate
522: + "] using: "
523: + this .timestampFormatter
524: .toPattern()
525: + ". Trying to recognize the format...",
526: null);
527: }
528: }
529:
530: if (result == null) {
531: synchronized (this .formatter) {
532: for (int i = 0; i < dateFormats.length; i++) {
533: try {
534: this .formatter.applyPattern(dateFormats[i]);
535: result = this .formatter.parse(aDate);
536: LogMgr.logInfo("ValueConverter.parseDate()",
537: "Succeeded parsing [" + aDate
538: + "] using the format: "
539: + dateFormats[i]);
540: break;
541: } catch (Exception e) {
542: result = null;
543: }
544: }
545: }
546: }
547:
548: if (result != null) {
549: return new java.sql.Date(result.getTime());
550: }
551:
552: throw new ParseException("Could not convert [" + aDate
553: + "] to a date", 0);
554: }
555:
556: private boolean isCurrentTime(String arg) {
557: return isKeyword("current_time", arg);
558: }
559:
560: private boolean isCurrentDate(String arg) {
561: return isKeyword("current_date", arg);
562: }
563:
564: private boolean isCurrentTimestamp(String arg) {
565: return isKeyword("current_timestamp", arg);
566: }
567:
568: private boolean isKeyword(String type, String arg) {
569: if (StringUtil.isEmptyString(arg)) {
570: return false;
571: }
572:
573: List<String> keywords = Settings.getInstance().getListProperty(
574: "workbench.db.keyword." + type, true);
575: return keywords.contains(arg.toLowerCase());
576: }
577:
578: private String adjustDecimalString(String input) {
579: if (input == null) {
580: return input;
581: }
582: String value = input.trim();
583: int len = value.length();
584: if (len == 0) {
585: return value;
586: }
587: StringBuilder result = new StringBuilder(len);
588: int pos = value.lastIndexOf(this .decimalCharacter);
589: for (int i = 0; i < len; i++) {
590: char c = value.charAt(i);
591: if (i == pos) {
592: // replace the decimal char with a . as that is required by BigDecimal(String)
593: // this way we only leave the last decimal character
594: result.append('.');
595: }
596: // filter out everything but valid number characters
597: else if ("+-0123456789eE".indexOf(c) > -1) {
598: result.append(c);
599: }
600: }
601:
602: return result.toString();
603: }
604:
605: private Boolean getBoolean(String value) throws ConverterException {
606: if (this .booleanFalseValues != null
607: && this .booleanTrueValues != null) {
608: if (booleanFalseValues.contains(value)) {
609: return Boolean.FALSE;
610: }
611: if (booleanTrueValues.contains(value)) {
612: return Boolean.TRUE;
613: }
614: throw new ConverterException(
615: "Input value ["
616: + value
617: + "] not in the list of defined true or false literals");
618: } else {
619: if ("false".equalsIgnoreCase(value)) {
620: return Boolean.FALSE;
621: }
622: if ("true".equalsIgnoreCase(value)) {
623: return Boolean.TRUE;
624: }
625: }
626: return null;
627: }
628:
629: private Boolean convertBool(String value, int type)
630: throws ConverterException {
631: Boolean b = getBoolean(value);
632: if (b != null) {
633: return b;
634: }
635:
636: throw new ConverterException(value, type, null);
637: }
638: }
|