001: package net.xoetrope.optional.data.sql;
002:
003: import java.io.IOException;
004: import java.io.Writer;
005:
006: import net.xoetrope.debug.DumpWriter;
007: import net.xoetrope.xui.build.BuildProperties;
008: import net.xoetrope.xui.data.XBaseModel;
009: import net.xoetrope.xui.data.XModel;
010: import net.xoetrope.xui.data.XRowSelector;
011:
012: /**
013: * <p>A wrapper for a database table allowing it to integrate with the XModel</p>
014: * <p>Copyright: Copyright (c) 2003<br>
015: * License: see license.txt</p>
016: * $Revision: 1.5 $
017: */
018: public class DatabaseTableModel extends XBaseModel implements
019: DumpWriter, XRowSelector {
020: CachedDatabaseTable xtable;
021: boolean dirty;
022: boolean readOnly;
023: protected String modelId;
024:
025: /**
026: * Create a new model node for a database table
027: */
028: public DatabaseTableModel() {
029: dirty = true;
030: readOnly = true;
031: }
032:
033: /**
034: * Sets the name attribute and constructs a new table of that name
035: * @param newName the new name
036: */
037: public void setName(String newName) {
038: modelId = newName;
039: }
040:
041: /**
042: * Get the hashcode for this model node
043: * @return the hashcode
044: */
045: public int hashCode() {
046: return modelId.hashCode();
047: }
048:
049: /**
050: * List the fields retrieved from the table
051: * @param tableName the database table name
052: */
053: public void setupTable(String tableName) {
054: setupTable(tableName, null, null);
055: }
056:
057: /**
058: * List the fields retrieved from the table
059: * @param tableName the database table name
060: * @param fields the database field names, or null if all fields are to be retrieved
061: * @param whereClause the where clause, or null if all rows are to be selected
062: */
063: public void setupTable(String tableName, String fields,
064: String whereClause) {
065: setupTable(tableName, fields, whereClause, null, false);
066: }
067:
068: /**
069: * List the fields retrieved from the table
070: * @param tableName the database table name
071: * @param fields the database field names, or null if all fields are to be retrieved
072: * @param whereClause the where clause, or null if all rows are to be selected
073: * @param connName the connection name
074: */
075: public void setupTable(String tableName, String fields,
076: String whereClause, String connName, boolean writable) {
077: xtable = new CachedDatabaseTable(tableName, fields,
078: whereClause, connName, writable);
079: if (modelId == null)
080: modelId = tableName;
081: dirty = true;
082:
083: // sync();
084: }
085:
086: /**
087: * Specify the sql statement in its entirety
088: * @param sqlStr the full SQL statement
089: * @param connName the connection name
090: */
091: public void setSqlStatement(String sqlStr, String connName,
092: boolean writable) {
093: xtable = new CachedDatabaseTable(sqlStr, connName, writable);
094: dirty = true;
095: }
096:
097: /**
098: * Update the underlying database table using the specified SQL statement
099: * @param sqlStr the full UPDATE SQL statement
100: * @return the number of rows affected by the update
101: */
102: public int executeUpdate(String sqlStr) throws Exception {
103: dirty = true;
104: return xtable.executeUpdate(sqlStr);
105: }
106:
107: /**
108: * Set the table to retrieve distinct rows
109: */
110: public void setDistinct(boolean isDistinct) {
111: xtable.setDistinct(isDistinct);
112: }
113:
114: /**
115: * Check if the table is set to retrieve distinct rows
116: * @return the distinct flag
117: */
118: public boolean isDistinct() {
119: return xtable.isDistinct();
120: }
121:
122: /**
123: * Set the field(s) used ORDER clause of the SQL query
124: * @param fld the field name(s)
125: */
126: public void setOrderField(String fld) {
127: xtable.setOrderField(fld);
128: }
129:
130: /**
131: * List the fields retrieved from the table
132: * @param tableName the database table name
133: * @param fields the database field names, or null if all fields are to be retrieved
134: * @param whereClause the where clause, or null if all rows are to be selected
135: * @param writable sets the table as writable, normally this should be false
136: */
137: public void setupTable(String tableName, String fields,
138: String whereClause, boolean writable) {
139: xtable = new CachedDatabaseTable(tableName, fields,
140: whereClause, null, writable);
141: if (modelId == null)
142: modelId = tableName;
143: dirty = true;
144:
145: // sync();
146: }
147:
148: /**
149: * Sets the read only flag indicating whether or not the underlying table
150: * is read-only
151: * @param b true for a read-only table.
152: */
153: public void setReadOnly(boolean b) {
154: readOnly = b;
155: }
156:
157: /**
158: * Syncs the model with the service response is the xtable has been changed
159: * or if the node has timed out.
160: */
161: protected void sync() {
162: if (dirty) { //|| xtable.hasExpired()) {
163: xtable.retrieve();
164: dirty = false;
165: }
166: }
167:
168: /**
169: * Get the value of the element located at the path in the element parameter
170: * If the attribName parameter is not null we get the value of the
171: * attributeValues
172: * @param element The path to the XModel we require
173: * @param attribName The name of the attribute we require or null if we just
174: * want the XModel value
175: * @return The value of the XModel or the attribute
176: */
177: public Object get(String element) {
178: sync();
179: DatabaseRowModel drm = new DatabaseRowModel(this , xtable
180: .getCurrentRow());
181: return drm.get(element);
182: }
183:
184: /**
185: * Get the value of the element located at the path in the element parameter
186: * If the attribName parameter is not null we get the value of the
187: * attributeValues
188: * @param element The path to the XModel we require
189: * @param attribName The name of the attribute we require or null if we just
190: * want the XModel value
191: * @return The value of the XModel or the attribute
192: */
193: // public Object get( String element, String attribName )
194: // {
195: // sync();
196: // return null;
197: // }
198: /**
199: * Set the value of the attribute in the XModel located at the elementName
200: * @param elementName The path to the XModel in the format 'base/foo
201: * @param attribName The name of the attribute whose value we require
202: * @param newObject The new value of the attribute
203: */
204: public void set(String elementName, String attribName,
205: Object newObject) {
206: if (readOnly)
207: return;
208: dirty = true;
209: fireModelUpdated();
210: }
211:
212: /**
213: * Set the value of the XModel located at the elementName
214: * @param elementName The path to the XModel in the format 'base/foo
215: * @param newObject The new value of the XModel
216: */
217: public void set(String attribName, Object newObject) {
218: if (readOnly)
219: return;
220: dirty = true;
221: fireModelUpdated();
222: }
223:
224: /**
225: * Get the XModel at element i
226: * @param i The index of the values array
227: * @return The XModel at location i
228: */
229: public XModel get(int i) {
230: sync();
231: return new DatabaseRowModel(this , i);
232: }
233:
234: /**
235: * gets the value attribute
236: * @return the value of the model
237: */
238: public Object get() {
239: sync();
240: return this ;
241: }
242:
243: public Object append(String id) {
244: return null;
245: }
246:
247: /**
248: * Sets the model value
249: * @param s the new value
250: */
251: public void set(Object s) {
252: if (readOnly)
253: return;
254: dirty = true;
255: fireModelUpdated();
256: }
257:
258: /**
259: * Get the value of an attribute (row)
260: * @param i The index of the attributeValues array whose value we want
261: * @return The string value of the attributeValues array at position i
262: */
263: public Object getAttribValue(int i) {
264: sync();
265: return xtable.getValue(i);
266: }
267:
268: /**
269: * @param i The index of the attributeValues array whose value we want
270: * @return The string value of the attributeValues array at position i
271: */
272: public String getAttribValueAsString(int i) {
273: sync();
274: return xtable.getValue(i);
275: }
276:
277: /**
278: * @param i The index of the attributeValues array whose value we want
279: * @return The string value of the attributeValues array at position i
280: */
281: public double getAttribValueAsDouble(int i) {
282: sync();
283: try {
284: String obj = xtable.getValue(i);
285: if (obj == null)
286: return 0.0;
287: return Double.parseDouble(obj);
288: //return new Double( obj ).doubleValue();
289: } catch (NullPointerException e) {
290: return 0.0;
291: } catch (ArrayIndexOutOfBoundsException e) {
292: return 0.0;
293: }
294: }
295:
296: /**
297: * @param i The index of the attributeValues array whose value we want
298: * @return The string value of the attributeValues array at position i
299: */
300: public int getAttribValueAsInt(int i) {
301: sync();
302: try {
303: return Integer.parseInt(xtable.getValue(i));
304: //return new Integer( xtable.getValue( i ) ).intValue();
305: } catch (NullPointerException e) {
306: return 0;
307: }
308: }
309:
310: /**
311: * returns the index of the attribiteNames array whose value is the same
312: * as the attribName
313: * @param attribName The name of the attribute we are trying to locate
314: * @return The index of the attributeNames array containg the name
315: */
316: public int getAttribute(String attribName) {
317: sync();
318: return xtable.getFieldIndex(attribName);
319: }
320:
321: // Table specific access methods----------------------------------------------
322: /**
323: * Get the table data.
324: */
325: public void retrieve() {
326: sync();
327: }
328:
329: /**
330: * Moves the table's cursor to the first row
331: */
332: public void first() {
333: xtable.first();
334: }
335:
336: /**
337: * Moves the table's cursor to the last row
338: */
339: public void last() {
340: xtable.last();
341: }
342:
343: /**
344: * Moves the table's cursor to the next row
345: */
346: public boolean next() {
347: return xtable.next();
348: }
349:
350: /**
351: * Moves the table's cursor to the previous row
352: */
353: public boolean previous() {
354: return xtable.previous();
355: }
356:
357: /**
358: * Provides iterative access to the table values. The current row in the table
359: * is used to index the data. This method does not implicitly
360: * retrieve the data so you must call the retrieve method explicitly.
361: * @return the data
362: */
363: public String getValue() {
364: return getFieldValue(0, 0);
365: }
366:
367: /**
368: * @param i The index into the values array
369: * @return The XModel at position i of the values array.
370: */
371: public XModel getValue(int i) {
372: return get(i);
373: }
374:
375: /**
376: * Provides iterative access to the table values. The current row in the table
377: * is used to index the data. This method does not implicitly
378: * retrieve the data so you must call the retrieve method explicitly.
379: * @param colIdx the column of field index
380: * @return the data
381: */
382: public String getFieldValue(int colIdx) {
383: return xtable.getValue(colIdx);
384: }
385:
386: /**
387: * Provides random access to the table values. This method does not implicitly
388: * retrieve the data so you must call the retrieve method explicitly.
389: * @param rowIdx the row index
390: * @param colIdx the column of field index
391: * @return the data
392: */
393: public String getFieldValue(int rowIdx, int colIdx) {
394: return xtable.getValue(rowIdx, colIdx);
395: }
396:
397: public void setFieldValue(int colIdx, String newValue) {
398: xtable.setValue(colIdx, newValue);
399: fireModelUpdated();
400: }
401:
402: // End of table specific access methods---------------------------------------
403:
404: /**
405: * Gets the value attribute as a Double value
406: * @param elementName
407: * @return
408: */
409: public double getValueAsDouble(String elementName) {
410: return Double.parseDouble(getFieldValue(xtable
411: .getFieldIndex(elementName)));
412: }
413:
414: /**
415: * Gets the value attribute of the specified node as an int.
416: * @param elementName
417: * @return
418: */
419: public int getValueAsInt(String elementName) {
420: return Integer.parseInt(getFieldValue(xtable
421: .getFieldIndex(elementName)));
422: //return new Integer( getFieldValue( xtable.getFieldIndex( elementName ) ) ).intValue();
423: }
424:
425: /**
426: * Gets the attribute name for field i. For this node the table field name is
427: * returned.
428: * @param i The index of the attributeNames array whose value we want
429: * @return The string value of the attributeNames array at position i
430: */
431: public String getAttribName(int i) {
432: return xtable.getFieldName(i);
433: }
434:
435: /**
436: * Gets the name attribute
437: * @return
438: */
439: public String getId() {
440: return modelId;
441: }
442:
443: /**
444: * Sets the attribute value
445: * @param i The index of the attributeValues array whose value we want
446: * @param value the value object
447: */
448: public void setAttribValue(int i, Object value) {
449: setFieldValue(i, value.toString());
450: }
451:
452: /**
453: * Gets the number of rows for this table
454: * @return the number of rows
455: */
456: public int getNumChildren() {
457: return xtable.getNumRows();
458: }
459:
460: /**
461: * Get the number of fields in this TableModel
462: * @return the number of fields
463: */
464: public int getNumAttributes() {
465: return xtable.getNumFields();
466: }
467:
468: /**
469: * Gets the model element tag name, e.g. 'Component' from the XML fragment
470: * <Component ....
471: * @return the model element name
472: */
473: public String getTagName() {
474: return "";
475: }
476:
477: /**
478: * Find a set of rows with a specified field value
479: * @param fieldIdx the index of the search field
480: * @param value the search value
481: * @return the table model containing the selected rows. null is returned if
482: * this table has uncommitted updates
483: */
484: public DatabaseTableModel findRows(int fieldIdx, String value) {
485: // if ( dirty )
486: // return null;
487:
488: DatabaseTableModel resTable = new DatabaseTableModel();
489: resTable.setupTable(xtable.getTableName(), null, xtable
490: .getFieldName(fieldIdx)
491: + "='" + value + "'");
492: resTable.sync();
493: return resTable;
494: }
495:
496: /**
497: * Find a set of rows with a specified field value
498: * @param where the search criteria
499: * @return the table model containing the selected rows. null is returned if
500: * this table has uncommitted updates
501: */
502: public DatabaseTableModel findRows(String where) {
503: return findRows(xtable.getFields(), where);
504: }
505:
506: /**
507: * Find a set of rows with a specified field value
508: * @param requestFields the fields to return
509: * @param where the search criteria
510: * @return the table model containing the selected rows. null is returned if
511: * this table has uncommitted updates
512: */
513: public DatabaseTableModel findRows(String requestFields,
514: String where) {
515: // if ( dirty )
516: // return null;
517:
518: DatabaseTableModel resTable = new DatabaseTableModel();
519: if (xtable.getTableName() != null) {
520: String parentWhere = xtable.getWhereClause();
521: resTable.setupTable(xtable.getTableName(), requestFields,
522: where
523: + ((parentWhere != null) ? " AND "
524: + parentWhere : ""), xtable
525: .getConnName(), false);
526: resTable.setDistinct(xtable.isDistinct());
527: resTable.setOrderField(xtable.orderField);
528: } else {
529: String sql = xtable.getSQL("");
530:
531: // Find the end of the specified SQL
532: String sqlStmt = sql.toUpperCase();
533: int endPos = sqlStmt.indexOf("GROUP BY");
534: if (endPos < 0)
535: endPos = sqlStmt.indexOf("HAVING ");
536: if (endPos < 0)
537: endPos = sqlStmt.indexOf("ORDER BY");
538:
539: // Append/insert the new where clause
540: if (endPos > 0)
541: sqlStmt = sql.substring(0, endPos) + where + " "
542: + sql.substring(endPos);
543: else
544: sqlStmt = sql + " " + where;
545:
546: resTable.setSqlStatement(sqlStmt, xtable.getConnName(),
547: false);
548: }
549: resTable.sync();
550: return resTable;
551: }
552:
553: /**
554: * Find a row with a specified field value
555: * @param fieldIdx the index of the search field
556: * @param value the search value
557: * @return the table row model containing the selected row. null is returned if
558: * this table has uncommitted updates
559: */
560: public DatabaseRowModel findRow(int fieldIdx, String value) {
561: if (dirty)
562: return null;
563:
564: return (DatabaseRowModel) findRows(fieldIdx, value).get(0);
565: }
566:
567: /**
568: * Find a row with a specified field value
569: * @param where the search criteria
570: * @return the table row model containing the selected row. null is returned if
571: * this table has uncommitted updates
572: */
573: public DatabaseRowModel findRow(String where) {
574: if (dirty)
575: return null;
576:
577: return (DatabaseRowModel) findRows(where).get(0);
578: }
579:
580: /**
581: * Look up a table by name in the overall model.
582: * @param tableName the table name
583: * @return the table node
584: */
585: public static DatabaseTableModel getTable(String tableName) {
586: return (DatabaseTableModel) ((XModel) XModel.getInstance().get(
587: tableName));
588: }
589:
590: /**
591: * Write teh contents of this model node to a stream
592: * @param w the write/stream
593: */
594: public void dump(Writer w) {
595: if (BuildProperties.DEBUG) {
596: try {
597: int numRows = xtable.getNumRows();
598: int numFields = xtable.getNumFields();
599: for (int i = 0; i < numRows; i++) {
600: for (int j = 0; j < numFields; j++) {
601: if (j > 0)
602: w.write(", ");
603:
604: w.write(xtable.getValue(i, j));
605: }
606: w.write("\n");
607: }
608: } catch (IOException ex) {
609: }
610: }
611: }
612:
613: /**
614: * Set the row selection index
615: * @param rowIdx the new row selection index (zero based)
616: */
617: public void setSelectedRow(int rowIdx) {
618: xtable.setCurrentRow(rowIdx);
619: }
620:
621: /**
622: * Get the row selection index
623: * @return the current row selection index (zero based)
624: */
625: public int getSelectedRow() {
626: return xtable.getCurrentRow();
627: }
628: }
|