001: package net.xoetrope.optional.data.sql;
002:
003: import java.sql.ResultSet;
004: import java.sql.ResultSetMetaData;
005: import java.sql.SQLException;
006: import java.sql.Timestamp;
007:
008: import net.xoetrope.debug.DebugLogger;
009: import net.xoetrope.xui.build.BuildProperties;
010:
011: /**
012: * A holder for information about a database table and the result set obtained
013: * from a database query.
014: * <p>Copyright (c) Xoetrope Ltd. 2001-2003</p>
015: * $Revision: 1.8 $
016: */
017: public class DatabaseTable {
018: /**
019: * The database will always attemp to synchronize
020: */
021: public static final int PARANOID_UPDATE = 0;
022:
023: /**
024: * The database will attempt to synchronize periodically
025: */
026: public static final int NORMAL_UPDATE = 1;
027:
028: /**
029: * The database will only attempt to synchronize when absolutely necessary
030: */
031: public static final int OPTIMISTIC_UPDATE = 2;
032:
033: protected DataConnection connection;
034: private ResultSetMetaData metaData;
035:
036: protected int numFields;
037: protected int numRows = 0;
038: protected int currentRow = 0;
039: protected boolean dirty = false;
040: protected String queryFields;
041: protected String queryTable;
042: protected ResultSet RS;
043: protected boolean distinct = false;
044: protected String orderField;
045: protected String sqlStatement;
046:
047: protected String defaultWhereClause;
048: protected String connName;
049: protected boolean writable;
050: protected static boolean caseSensitive = true;
051:
052: /**
053: * Create a new database table wrapper
054: * @param tableName the table name
055: */
056: public DatabaseTable(String tableName) {
057: this (tableName, null, null, null, false);
058: }
059:
060: /**
061: * Create a new database table wrapper
062: * @param sql the SQL query for the table
063: * @param conn the connection name
064: * @param allowWrites true if the result set is to be updatable
065: */
066: public DatabaseTable(String sql, String conn, boolean allowWrites) {
067: sqlStatement = sql;
068: writable = allowWrites;
069: connName = conn;
070: connection = new DataConnection(connName);
071: }
072:
073: /**
074: * Create a new database table wrapper
075: * @param tableName the table name
076: * @param fields the fields to retrieve
077: * @param where the where clause to use in the query
078: * @param conn the connection name
079: * @param allowWrites true if the result set is to be updatable
080: */
081: public DatabaseTable(String tableName, String fields, String where,
082: String conn, boolean allowWrites) {
083: queryFields = fields;
084: queryTable = tableName;
085: defaultWhereClause = where;
086: connName = conn;
087: writable = allowWrites;
088: connection = new DataConnection(connName);
089: }
090:
091: /**
092: * Set the table to retrieve distinct rows
093: */
094: public void setDistinct(boolean isDistinct) {
095: distinct = isDistinct;
096: }
097:
098: /**
099: * Check if the table is set to retrieve distinct rows
100: * @return the distinct flag
101: */
102: public boolean isDistinct() {
103: return distinct;
104: }
105:
106: /**
107: * Set the field name(s) for the ORDER clause
108: * @param fld
109: */
110: public void setOrderField(String fld) {
111: orderField = fld;
112: }
113:
114: /**
115: * Get a field value
116: * @param fieldIdx the (zero based) field index
117: * @return the value
118: */
119: public String getValue(int fieldIdx) {
120: try {
121: return RS.getString(fieldIdx + 1);
122: } catch (SQLException ex) {
123: return null;
124: }
125: }
126:
127: /**
128: * Get a field value
129: * @param rowIdx the (zero based) row index
130: * @param fieldIdx the (zero based) field/column index
131: * @return
132: */
133: public String getValue(int rowIdx, int fieldIdx) {
134: try {
135: RS.absolute(rowIdx + 1);
136: return RS.getString(fieldIdx + 1);
137: } catch (SQLException ex) {
138: return null;
139: }
140: }
141:
142: /**
143: * Set a field value
144: * @param rowIdx the (zero based) row index
145: * @param fieldIdx the (zero based) field/column index
146: * @param the new field value
147: */
148: public void setValue(int rowIdx, int fieldIdx, String value) {
149: try {
150: if (!metaData.isWritable(fieldIdx + 1))
151: return;
152:
153: RS.absolute(rowIdx + 1);
154: try {
155: RS.refreshRow();
156: } catch (SQLException ex1) {
157: }
158: switch (metaData.getColumnType(fieldIdx + 1)) {
159: case java.sql.Types.INTEGER:
160: RS.updateInt(fieldIdx + 1, new Integer(value)
161: .intValue());
162: break;
163:
164: case java.sql.Types.FLOAT:
165: RS.updateFloat(fieldIdx + 1, new Float(value)
166: .floatValue());
167: break;
168:
169: case java.sql.Types.DOUBLE:
170: RS.updateDouble(fieldIdx + 1, new Double(value)
171: .doubleValue());
172: break;
173:
174: case java.sql.Types.VARCHAR:
175: default:
176: RS.updateString(fieldIdx + 1, value);
177: break;
178: }
179: RS.updateRow();
180: } catch (SQLException ex) {
181: if (BuildProperties.DEBUG)
182: DebugLogger
183: .logWarning("Unable to set database table value directly in table: "
184: + queryTable);
185: setValueWithPseudoColumn(rowIdx, fieldIdx, value);
186: }
187: dirty = true;
188: }
189:
190: /**
191: * Set a field value and update the tracking information about the table row/record
192: * @param rowIdx the (zero based) row index
193: * @param fieldIdx the (zero based) field/column index
194: * @param value teh new field value
195: */
196: private void setValueWithPseudoColumn(int rowIdx, int fieldIdx,
197: String value) {
198: try {
199: if (!metaData.isWritable(fieldIdx + 1))
200: return;
201:
202: // The table is assumed to have an appended pseudoColumn/ID and an
203: // appended timestamp field
204: int pseudoColumn = numFields - 1;
205: RS.absolute(rowIdx + 1);
206:
207: String sql = "UPDATE " + queryTable + " SET "
208: + RS.getMetaData().getColumnName(fieldIdx + 1)
209: + "=";
210:
211: switch (metaData.getColumnType(fieldIdx + 1)) {
212: case java.sql.Types.INTEGER:
213: case java.sql.Types.FLOAT:
214: case java.sql.Types.DOUBLE:
215: sql += value;
216: break;
217:
218: case java.sql.Types.VARCHAR:
219: case java.sql.Types.CHAR:
220: default:
221: sql += "'" + value + "'";
222: break;
223: }
224:
225: sql += ", "
226: + metaData.getColumnName(numFields)
227: + "='"
228: + new Timestamp(new java.util.Date().getTime())
229: .toString() + "'";
230: sql += " WHERE " + metaData.getColumnName(pseudoColumn)
231: + "=" + RS.getString(pseudoColumn);
232: connection.executeUpdate(sql);
233: } catch (Exception ex) {
234: ex.printStackTrace();
235: }
236: }
237:
238: /**
239: * Get the index (zero based) of a named field
240: * @param fieldName the field name
241: * @return the index
242: */
243: public int getFieldIndex(String fieldName) {
244: try {
245: for (int i = 0; i < numFields; i++) {
246: if (!caseSensitive
247: && (fieldName.compareToIgnoreCase(metaData
248: .getColumnName(i + 1)) == 0))
249: return i;
250: else if (fieldName.compareTo(metaData
251: .getColumnName(i + 1)) == 0)
252: return i;
253: }
254: } catch (SQLException ex) {
255: }
256: return -1;
257: }
258:
259: /**
260: * Get the name of a field
261: * @param fieldIdx the field index (zero based)
262: * @return the index
263: */
264: public String getFieldName(int fieldIdx) {
265: try {
266: return metaData.getColumnName(fieldIdx + 1);
267: } catch (SQLException ex) {
268: return null;
269: }
270: }
271:
272: /**
273: * Retrieve values using the deafult where clause
274: */
275: public void retrieve() {
276: retrieve(defaultWhereClause);
277: }
278:
279: /**
280: * Retrieve values using the specified where clause. If a SQL statement has
281: * been specified in its entirety then the where clause argument is ignored.
282: * @param where the SQL where statement (without the WHERE)
283: */
284: public void retrieve(String where) {
285: try {
286: String queryString = getSQL(where).toString();
287: RS = connection.executeQuery(queryString, connName,
288: writable);
289: metaData = RS.getMetaData();
290: numFields = metaData.getColumnCount();
291:
292: // This may not be the most efficient route for getting the row count but
293: // trying to do a COUNT(*) may give inaccurate results for some queries
294: // particularly where DISTICT queries are used.
295: RS.last();
296: numRows = RS.getRow();
297:
298: RS.first();
299: cacheData();
300: } catch (Exception ex) {
301: ex.printStackTrace();
302: }
303: }
304:
305: /**
306: * Execute an update statement
307: * @param sql the UPDATE SQL statement
308: * @throws Exception
309: */
310: public int executeUpdate(String sql) throws Exception {
311: return connection.executeUpdate(sql);
312: }
313:
314: protected void cacheData() {
315: }
316:
317: /**
318: * Get the number of rows/records in the resultset/table
319: * @return
320: */
321: public int getNumRows() {
322: return numRows;
323: }
324:
325: /**
326: * Sets the current row.
327: * @param idx the new row index - 0 based
328: */
329: public void setCurrentRow(int idx) {
330: currentRow = idx;
331: }
332:
333: /**
334: * Gets the current row.
335: * @return the new row index - 0 based
336: */
337: public int getCurrentRow() {
338: return currentRow;
339: }
340:
341: public void first() {
342: currentRow = 0;
343: }
344:
345: public void last() {
346: currentRow = numRows - 1;
347: }
348:
349: public boolean next() {
350: dirty = false;
351: if ((currentRow + 1) == numRows) {
352: return false;
353: } else {
354: currentRow++;
355: return true;
356: }
357: }
358:
359: public boolean previous() {
360: dirty = false;
361: if (currentRow == 0)
362: return false;
363: else {
364: currentRow--;
365: return true;
366: }
367: }
368:
369: protected void finalize() throws Throwable {
370: try {
371: RS.close();
372: } catch (SQLException ex) {
373: // ex.printStackTrace();
374: }
375: }
376:
377: /**
378: * get the number of fields in the table
379: * @return the number of fields
380: */
381: public int getNumFields() {
382: return numFields;
383: }
384:
385: /**
386: * Gets the table name
387: * @return the table name
388: */
389: public String getTableName() {
390: return queryTable;
391: }
392:
393: /**
394: * Get the table name
395: * @param name the table name
396: */
397: public void getTableName(String name) {
398: queryTable = name;
399: }
400:
401: /**
402: * Get the connection name
403: * @return the connection names
404: */
405: public String getConnName() {
406: return connName;
407: }
408:
409: /**
410: * Gets the value of any where clause
411: * @return the where clause
412: */
413: public String getWhereClause() {
414: return defaultWhereClause;
415: }
416:
417: /**
418: * Gets the fields used for the query
419: * @return the where clause
420: */
421: public String getFields() {
422: return queryFields;
423: }
424:
425: /**
426: * Set the case sensitivity for database comparisons. This setting is
427: * application wide
428: * @param state true for case-sensitive comparisons of field names
429: */
430: public static void setCaseSensitive(boolean state) {
431: caseSensitive = state;
432: }
433:
434: /**
435: * Get the SQL for the SELECT statement or return the SQL that was specified
436: * @param where the where clause if any
437: * @return the SQL statement
438: */
439: public String getSQL(String where) {
440: if (sqlStatement != null)
441: return sqlStatement;
442: else {
443: StringBuffer sb = new StringBuffer("SELECT ");
444: if (distinct)
445: sb.append("DISTINCT ");
446: if (queryFields != null)
447: sb.append(queryFields);
448: else
449: sb.append("*");
450:
451: sb.append(" FROM ");
452: sb.append(queryTable);
453: if ((where != null) && (where.length() > 0))
454: sb.append(" WHERE " + where);
455:
456: if (orderField != null)
457: sb.append(" ORDER BY " + orderField);
458: return sb.toString();
459: }
460: }
461: }
|