001: /*
002: * Copyright (C) 2006 Methodhead Software LLC. All rights reserved.
003: *
004: * This file is part of TransferCM.
005: *
006: * TransferCM is free software; you can redistribute it and/or modify it under the
007: * terms of the GNU General Public License as published by the Free Software
008: * Foundation; either version 2 of the License, or (at your option) any later
009: * version.
010: *
011: * TransferCM is distributed in the hope that it will be useful, but WITHOUT ANY
012: * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
013: * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
014: * details.
015: *
016: * You should have received a copy of the GNU General Public License along with
017: * TransferCM; if not, write to the Free Software Foundation, Inc., 51 Franklin St,
018: * Fifth Floor, Boston, MA 02110-1301 USA
019: */
020:
021: package com.methodhead.persistable;
022:
023: import java.sql.ResultSet;
024: import java.sql.SQLException;
025: import java.sql.Timestamp;
026:
027: import java.util.Date;
028: import java.util.List;
029: import java.util.ArrayList;
030: import java.text.DateFormat;
031: import java.text.SimpleDateFormat;
032: import org.apache.commons.beanutils.BasicDynaBean;
033: import org.apache.commons.beanutils.DynaBean;
034: import org.apache.commons.beanutils.DynaClass;
035: import org.apache.commons.beanutils.DynaProperty;
036: import java.text.ParseException;
037: import com.methodhead.persistable.ConnectionSingleton;
038: import org.apache.commons.lang.exception.ExceptionUtils;
039: import com.methodhead.persistable.ConnectionSingleton;
040: import org.apache.log4j.Logger;
041: import com.methodhead.test.TestUtils;
042:
043: /**
044: <p>
045: A base class for database-backed objects. Methods are
046: provided to simplify managing data in a database: {@link
047: #saveNew saveNew()}, {@link #save save()}, {@link #load
048: load()}, {@link #loadAll loadAll()}, and {@link #deleteAll
049: deleteAll()}. Methods are also provided to box and unbox
050: primitive values.
051: </p>
052: <p>
053: <tt>Persistable</tt> subclasses <tt>BasicDynaBean</tt>
054: from the BeanUtils component of <a
055: href="http://jakarta.apache.org/commons/">Jakarta
056: Commons</a>. This makes any <tt>Persistable</tt>-based
057: object compatible with libraries that operate on
058: <tt>DynaBean</tt>s, such as <a
059: href="http://jakarta.apache.org/struts/">Apache Struts</a>.
060: </p>
061: <p>
062: As it is a <tt>DynaBean</tt>, <tt>DynaClass</tt> plays an
063: important role in a persistable's operation. Make sure the
064: persistable's dynaclass is defined appropriately:
065: </p>
066: <ul>
067: <li>
068: <p>
069: The dynaclass's <strong>name should match the
070: table name</strong> in the database.
071: </p>
072: </li>
073: <li>
074: <p>
075: The dynaclass's <strong>dynaproperties should match the
076: corresponding column names and types</strong> in the table.
077: </p>
078: </li>
079: <li>
080: <p>
081: The dynaclass's <strong><tt>newInstance()</tt> should
082: return an appropriate object</strong>.
083: </p>
084: </li>
085: </ul>
086: */
087: public class Persistable extends BasicDynaBean {
088:
089: // constructors /////////////////////////////////////////////////////////////
090:
091: public Persistable(DynaClass dynaClass) {
092: super (dynaClass);
093: }
094:
095: // constants ////////////////////////////////////////////////////////////////
096:
097: // classes //////////////////////////////////////////////////////////////////
098:
099: // methods //////////////////////////////////////////////////////////////////
100:
101: /**
102: * Returns <tt>property</tt>, unboxing the value if necessary.
103: */
104: public String getString(String property) {
105:
106: return (String) get(property);
107: }
108:
109: /**
110: * Sets <tt>property</tt> to <tt>value</tt>, boxing the value if necessary.
111: */
112: public void setString(String property, String value) {
113:
114: set(property, value);
115: }
116:
117: /**
118: * Returns <tt>property</tt>, unboxing the value if necessary; returns 0 if
119: * the property has not been set.
120: */
121: public int getInt(String property) {
122:
123: Integer i = (Integer) get(property);
124:
125: if (i == null)
126: return 0;
127:
128: return i.intValue();
129: }
130:
131: /**
132: * Sets <tt>property</tt> to <tt>value</tt>, boxing the value if necessary.
133: */
134: public void setInt(String property, int value) {
135:
136: set(property, new Integer(value));
137: }
138:
139: /**
140: * Returns <tt>property</tt>, unboxing the value if necessary; <tt>false</tt>
141: * is returned if the property has not been set.
142: */
143: public boolean getBoolean(String property) {
144:
145: Boolean b = (Boolean) get(property);
146:
147: if (b == null)
148: return false;
149:
150: return b.booleanValue();
151: }
152:
153: /**
154: * Sets <tt>property</tt> to <tt>value</tt>, boxing the value if necessary.
155: */
156: public void setBoolean(String property, boolean value) {
157:
158: set(property, new Boolean(value));
159: }
160:
161: /**
162: * Returns <tt>property</tt>, unboxing the value if necessary; <tt>0.0</tt>
163: * is returned if the property has not been set.
164: */
165: public double getDouble(String property) {
166:
167: Double d = (Double) get(property);
168:
169: if (d == null)
170: return 0.0;
171:
172: return d.doubleValue();
173: }
174:
175: /**
176: * Sets <tt>property</tt> to <tt>value</tt>, boxing the value if necessary.
177: */
178: public void setDouble(String property, double value) {
179:
180: set(property, new Double(value));
181: }
182:
183: /**
184: * Returns <tt>property</tt>, unboxing the value if necessary.
185: */
186: public Date getDate(String property) {
187:
188: return (Date) get(property);
189: }
190:
191: /**
192: * Sets <tt>property</tt> to <tt>value</tt>, boxing the value if necessary.
193: */
194: public void setDate(String property, Date value) {
195:
196: set(property, value);
197: }
198:
199: /**
200: * <p>
201: * Sets <tt>property</tt> to <tt>value</tt>, converting
202: * <tt>value</tt> from a string to the appropriate type in a
203: * sensible way.
204: * </p>
205: * <ul>
206: * <li>
207: * Strings: simply <tt>value</tt> or <tt>""</tt> if
208: * <tt>value</tt> is <tt>null</tt>.
209: * </li>
210: * <li>
211: * Integers: <tt>value</tt> if it can be successfully
212: * parsed; <tt>0</tt> if <tt>value</tt> is <tt>null</tt> or an
213: * empty string.
214: * </li>
215: * <li>
216: * Booleans: <tt>true</tt> if <tt>value</tt> is
217: * <tt>"true"</tt>, <tt>"yes"</tt>, or "on" (case insensitive),
218: * <tt>false</tt> otherwise.
219: * </li>
220: * <li>
221: * Doubles: <tt>value</tt> if it can be successfully
222: * parsed; <tt>0.0</tt> if <tt>value</tt> is <tt>null</tt> or
223: * an empty string.
224: * </li>
225: * <li>
226: * Dates: <tt>value</tt> if it can be successfully parsed
227: * by <tt>DateFormat.getDateInstance( DateFormat.SHORT )</tt>
228: * (e.g., "2/20/03") or <tt>DateFormat.getDateTimeInstance(
229: * DateFormat.SHORT, DateFormat.SHORT )</tt> (e.g., "2/20/03
230: * 8:20 PM"); the current date if <tt>value</tt> is
231: * <tt>null</tt> or an empty string.
232: * </li>
233: * </ul>
234: */
235: public void setAsString(String property, String value) {
236:
237: Class c = getDynaClass().getDynaProperty(property).getType();
238:
239: if (c == String.class) {
240: if (value == null)
241: set(property, "");
242: else
243: set(property, value);
244: }
245:
246: else if (c == Integer.class) {
247: if ((value == null) || value.equals(""))
248: set(property, new Integer(0));
249: else
250: set(property, new Integer(value));
251: }
252:
253: else if (c == Boolean.class) {
254:
255: if (value == null)
256: set(property, new Boolean(false));
257: else {
258: value = value.toLowerCase();
259:
260: if (value.equals("true") || value.equals("yes")
261: || value.equals("on"))
262: set(property, new Boolean(true));
263: else
264: set(property, new Boolean(false));
265: }
266: }
267:
268: else if (c == Double.class) {
269: if ((value == null) || value.equals(""))
270: set(property, new Double(0.0));
271: else
272: set(property, new Double(value));
273: }
274:
275: else if (c == java.util.Date.class) {
276: if ((value == null) || value.equals(""))
277: set(property, TestUtils.getCurrentDate());
278: else {
279: Date d = null;
280: DateFormat fmt = DateFormat.getDateTimeInstance(
281: DateFormat.SHORT, DateFormat.SHORT);
282:
283: try {
284: d = fmt.parse(value);
285: } catch (ParseException e) {
286:
287: fmt = DateFormat.getDateInstance(DateFormat.SHORT);
288: try {
289: d = fmt.parse(value);
290: } catch (ParseException e2) {
291: throw new PersistableException(
292: "Couldn't parse date from \"" + value
293: + "\"");
294: }
295: }
296:
297: set(property, d);
298: }
299: }
300: }
301:
302: /**
303: * Sets <tt>property</tt> by calling {@link #setAsString setAsString()} with
304: * <tt>value.toString()</tt>.
305: */
306: public void setAsObject(String property, Object value) {
307:
308: if (value == null)
309: setAsString(property, null);
310: else
311: setAsString(property, value.toString());
312: }
313:
314: /**
315: * Returns a string literal suitable for use in a SQL statement by escaping
316: * single quotes and then wrapping the string in single quotes. For example,
317: * <tt>"it's good to escape"</tt> becomes <tt>"'it''s good to escape'"</tt>
318: */
319: public static String getSqlLiteral(String value) {
320: return "'" + value.replaceAll("'", "''") + "'";
321: }
322:
323: /**
324: * Returns a string literal suitable for use in a SQL statement, using
325: * <tt>'1'</tt> for true and <tt>'0'</tt> for false.
326: */
327: public static String getSqlLiteral(Boolean value) {
328: if (value.booleanValue())
329: return "'1'";
330: else
331: return "'0'";
332: }
333:
334: /**
335: * Returns a date literal suitable for use in a SQL statement by wrapping a
336: * standard SQL date in single quotes.
337: */
338: public static String getSqlLiteral(Date value) {
339: DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
340: return "'" + format.format(value) + "'";
341: }
342:
343: /**
344: * Returns the value of <tt>dynaProperty</tt> in a form suitable for use in a
345: * SQL statement.
346: */
347: protected String getSqlLiteral(DynaProperty dynaProperty) {
348:
349: if (get(dynaProperty.getName()) == null)
350: return "NULL";
351:
352: String property = dynaProperty.getName();
353:
354: if (dynaProperty.getType() == String.class)
355: return getSqlLiteral(get(property).toString());
356: else if (dynaProperty.getType() == Integer.class)
357: return get(property).toString();
358: else if (dynaProperty.getType() == Boolean.class)
359: return getSqlLiteral((Boolean) get(property));
360: else if (dynaProperty.getType() == Double.class)
361: return get(property).toString();
362: else if (dynaProperty.getType() == java.util.Date.class)
363: return getSqlLiteral((Date) get(property));
364:
365: return null;
366: }
367:
368: /**
369: * Convenience method to set the <tt>dynaProperty</tt> of
370: * <tt>persistable</tt> with the corresponding value from <tt>rs</tt>.
371: */
372: protected static void setProperty(DynaBean persistable,
373: DynaProperty dynaProperty, ResultSet rs) {
374:
375: try {
376: if (dynaProperty.getType() == String.class)
377: persistable.set(dynaProperty.getName(), rs
378: .getString(dynaProperty.getName()));
379: else if (dynaProperty.getType() == Integer.class)
380: persistable.set(dynaProperty.getName(), new Integer(rs
381: .getInt(dynaProperty.getName())));
382: else if (dynaProperty.getType() == Boolean.class)
383: persistable.set(dynaProperty.getName(), new Boolean(rs
384: .getBoolean(dynaProperty.getName())));
385: else if (dynaProperty.getType() == Double.class)
386: persistable.set(dynaProperty.getName(), new Double(rs
387: .getDouble(dynaProperty.getName())));
388: else if (dynaProperty.getType() == java.util.Date.class)
389: persistable.set(dynaProperty.getName(), rs
390: .getTimestamp(dynaProperty.getName()));
391: } catch (SQLException e) {
392: throw new PersistableException(
393: "Unexpected SQLException while setting property \""
394: + dynaProperty.getName() + "\":\n"
395: + e.getMessage());
396: }
397: }
398:
399: /**
400: * Saves the persistable by inserting a new row.
401: */
402: public void saveNew() {
403:
404: DynaClass dynaClass = getDynaClass();
405:
406: //
407: // start statement
408: //
409: StringBuffer buf = new StringBuffer();
410:
411: buf.append("INSERT INTO ");
412: buf.append(dynaClass.getName());
413: buf.append(" (");
414:
415: //
416: // append field names
417: //
418: DynaProperty[] dyanProperties = dynaClass.getDynaProperties();
419: for (int i = 0; i < dyanProperties.length; i++) {
420: buf.append(dyanProperties[i].getName());
421:
422: if (i < dyanProperties.length - 1)
423: buf.append(",");
424: }
425:
426: //
427: // append values
428: //
429: buf.append(") VALUES (");
430:
431: for (int i = 0; i < dyanProperties.length; i++) {
432: buf.append(getSqlLiteral(dyanProperties[i]));
433:
434: if (i < dyanProperties.length - 1)
435: buf.append(",");
436: }
437:
438: buf.append(")");
439:
440: //
441: // execute the statement
442: //
443: try {
444: ConnectionSingleton.runUpdate(buf.toString());
445: } catch (SQLException e) {
446: throw new PersistableException("Unexpected SQLException: "
447: + e.getMessage());
448: }
449: }
450:
451: /**
452: * Saves the persistable, by updating any rows to which the specified
453: * <tt>whereClause</tt> clause applies. <tt>whereClause</tt> should not
454: * include the <tt>WHERE</tt> keyword; if <tt>whereClause</tt> is
455: * <tt>null</tt>, all rows in the table are updated.
456: */
457: public void save(String whereClause) throws PersistableException {
458:
459: //
460: // start statement
461: //
462: StringBuffer buf = new StringBuffer();
463:
464: buf.append("UPDATE ");
465: buf.append(getDynaClass().getName());
466: buf.append(" SET ");
467:
468: DynaProperty[] dynaProperties = getDynaClass()
469: .getDynaProperties();
470:
471: //
472: // append field names
473: //
474: for (int i = 0; i < dynaProperties.length; i++) {
475: buf.append(dynaProperties[i].getName());
476: buf.append("=");
477: buf.append(getSqlLiteral(dynaProperties[i]));
478:
479: if (i < dynaProperties.length - 1)
480: buf.append(",");
481: }
482:
483: //
484: // append where clause if it was provided
485: //
486: if (whereClause != null)
487: buf.append(" WHERE " + whereClause);
488:
489: //
490: // execute the statement
491: //
492: try {
493: ConnectionSingleton.runUpdate(buf.toString());
494: } catch (SQLException e) {
495: throw new PersistableException("Unexpected SQLException: "
496: + e.getMessage() + " while executing \""
497: + buf.toString() + "\"");
498: }
499: }
500:
501: /**
502: * Loads the persistable according to the specified <tt>whereClause</tt>. If
503: * no records match <tt>whereClause</tt>, an exception is thrown. If more
504: * than one record matches, only the first is used.
505: */
506: public void load(String whereClause) {
507:
508: DynaProperty[] dynaProperties = getDynaClass()
509: .getDynaProperties();
510:
511: StringBuffer buf = new StringBuffer();
512: buf.append("SELECT ");
513:
514: for (int i = 0; i < dynaProperties.length; i++) {
515: buf.append(dynaProperties[i].getName());
516: if (i < dynaProperties.length - 1)
517: buf.append(",");
518: }
519:
520: buf.append(" FROM ");
521: buf.append(getDynaClass().getName());
522: buf.append(" WHERE ");
523: buf.append(whereClause);
524:
525: ResultSet rs = null;
526: try {
527: rs = ConnectionSingleton.runQuery(buf.toString());
528:
529: if (rs == null)
530: throw new PersistableException(
531: "Couldn't execute statement: " + buf.toString());
532:
533: if (!rs.next()) {
534: throw new PersistableException(
535: "No records for where clause \"" + whereClause
536: + "\".");
537: }
538:
539: for (int i = 0; i < dynaProperties.length; i++)
540: setProperty(this , dynaProperties[i], rs);
541: } catch (SQLException e) {
542: String msg = "Loading for whereClause \"" + whereClause
543: + "\". " + ExceptionUtils.getStackTrace(e);
544: logger_.error(msg);
545: throw new RuntimeException(msg);
546: } finally {
547: ConnectionSingleton.close(rs);
548: }
549: }
550:
551: /**
552: * Returns a list containing all persistables for the specified
553: * <tt>WHERE</tt> and <tt>ORDER BY</tt> clauses. <tt>whereClause</tt> should
554: * not include the <tt>WHERE</tt> keyword; if <tt>whereClause</tt> is
555: * <tt>null</tt> all persistables in the table are loaded. <tt>orderBy</tt>
556: * should not contain the <tt>ORDER BY</tt> keywords; if
557: * <tt>orderByClause</tt> is <tt>null</tt> persistables are sorted as
558: * returned from the database.
559: */
560: public static List loadAll(DynaClass dynaClass, String whereClause,
561: String orderByClause) {
562:
563: DynaProperty[] dynaProperties = dynaClass.getDynaProperties();
564:
565: StringBuffer buf = new StringBuffer();
566: buf.append("SELECT ");
567:
568: for (int i = 0; i < dynaProperties.length; i++) {
569: buf.append(dynaProperties[i].getName());
570: if (i < dynaProperties.length - 1)
571: buf.append(",");
572: }
573:
574: buf.append(" FROM ");
575: buf.append(dynaClass.getName());
576:
577: if (whereClause != null) {
578: buf.append(" WHERE ");
579: buf.append(whereClause);
580: }
581:
582: if (orderByClause != null) {
583: buf.append(" ORDER BY ");
584: buf.append(orderByClause);
585: }
586:
587: ResultSet rs = null;
588: try {
589: rs = ConnectionSingleton.runQuery(buf.toString());
590:
591: if (rs == null)
592: throw new PersistableException(
593: "Couldn't execute statement: " + buf.toString());
594:
595: List persistables = new ArrayList();
596:
597: while (rs.next()) {
598: DynaBean persistable = (DynaBean) dynaClass
599: .newInstance();
600:
601: for (int i = 0; i < dynaProperties.length; i++)
602: setProperty(persistable, dynaProperties[i], rs);
603:
604: persistables.add(persistable);
605: }
606:
607: ConnectionSingleton.close(rs);
608:
609: return persistables;
610: } catch (SQLException e) {
611: if (rs != null)
612: ConnectionSingleton.close(rs);
613: throw new PersistableException("Unexpected SQLException: "
614: + e.getMessage());
615: } catch (IllegalAccessException e) {
616: throw new PersistableException(
617: "Unexpected IllegalAccessException: "
618: + e.getMessage());
619: } catch (InstantiationException e) {
620: throw new PersistableException(
621: "Unexpected InstantiationException: "
622: + e.getMessage());
623: }
624: }
625:
626: /**
627: * Returns a list containing all persistables for the specified
628: * <tt>WHERE</tt> and <tt>ORDER BY</tt> clauses. <tt>whereClause</tt> should
629: * not include the <tt>WHERE</tt> keyword; if <tt>whereClause</tt> is
630: * <tt>null</tt> all persistables in the table are loaded. <tt>orderBy</tt>
631: * should not contain the <tt>ORDER BY</tt> keywords; if
632: * <tt>orderByClause</tt> is <tt>null</tt> persistables are sorted as
633: * returned from the database.
634: */
635: public List loadAll(String whereClause, String orderByClause) {
636:
637: return loadAll(getDynaClass(), whereClause, orderByClause);
638: }
639:
640: /**
641: * Deletes all persistables according to the specified <tt>whereClause</tt>.
642: * <tt>whereClause</tt> should not include the <tt>WHERE</tt> keyword; if
643: * <tt>whereClause</tt> is <tt>null</tt>, all persistables in the table are
644: * deleted.
645: */
646: public static void deleteAll(DynaClass dynaClass, String whereClause)
647: throws PersistableException {
648:
649: StringBuffer buf = new StringBuffer();
650: buf.append("DELETE FROM ");
651: buf.append(dynaClass.getName());
652:
653: if (whereClause != null) {
654: buf.append(" WHERE ");
655: buf.append(whereClause);
656: }
657:
658: try {
659: ConnectionSingleton.runUpdate(buf.toString());
660: } catch (SQLException e) {
661: throw new PersistableException("Unexpected SQLException: "
662: + e.getMessage());
663: }
664: }
665:
666: /**
667: * Deletes all persistables according to the specified <tt>whereClause</tt>.
668: * <tt>whereClause</tt> should not include the <tt>WHERE</tt> keyword; if
669: * <tt>whereClause</tt> is <tt>null</tt>, all persistables in the table are
670: * deleted.
671: */
672: public void deleteAll(String whereClause)
673: throws PersistableException {
674:
675: deleteAll(getDynaClass(), whereClause);
676: }
677:
678: // properties ///////////////////////////////////////////////////////////////
679:
680: // attributes ///////////////////////////////////////////////////////////////
681:
682: private static Logger logger_ = Logger.getLogger(Persistable.class);
683: }
|