001: package dinamica;
002:
003: import java.sql.*;
004: import java.text.MessageFormat;
005: import java.util.ArrayList;
006: import java.io.BufferedInputStream;
007: import java.io.File;
008: import java.io.FileInputStream;
009: import java.io.PrintWriter;
010: import java.util.HashMap;
011:
012: /**
013: * Core-level framework class: Text Generator.
014: * <br><br>
015: * Encapsulates the JDBC API most common operations
016: * with safe and efficient code. Uses the same connection
017: * for all its operations. Does not close connections, that is the
018: * responsability of the caller (Transaction Manager)
019: * <br>
020: * Creation date: 12/09/2003<br>
021: * Last Update: 12/09/2003<br>
022: * (c) 2003 Martin Cordova<br>
023: * This code is released under the LGPL license<br>
024: * @author Martin Cordova (dinamica@martincordova.com)
025: */
026: public class Db {
027:
028: /** database connection */
029: private Connection _conn = null;
030:
031: /** log writer */
032: private PrintWriter _log = null;
033:
034: /** container for batch commands */
035: private ArrayList<String> _batch = new ArrayList<String>();
036:
037: /** database version info */
038: private String _dbVersion = null;
039:
040: /**
041: * Set the log writer. This class will log
042: * all query executions -as a text report- if this object is set
043: * @param writer
044: */
045: public void setLogWriter(PrintWriter writer) {
046: _log = writer;
047:
048: }
049:
050: /** Default constructor to initialize the object */
051: public Db(Connection conn) {
052: _conn = conn;
053: DatabaseMetaData md;
054: try {
055:
056: md = _conn.getMetaData();
057:
058: _dbVersion = md.getDatabaseProductName() + " "
059: + md.getDatabaseProductVersion() + " ("
060: + md.getDriverName() + " " + md.getDriverVersion()
061: + ")";
062:
063: } catch (SQLException e) {
064: }
065: }
066:
067: /**
068: * Execute an action query (inserts, updates, deletes)
069: * @param sql SQL command to execute
070: * @return The number of rows affected
071: * @throws Throwable
072: */
073: public int exec(String sql) throws Throwable {
074:
075: long t1 = 0;
076: long t2 = 0;
077:
078: Statement s = null;
079: int rows = 0;
080:
081: try {
082:
083: t1 = System.currentTimeMillis();
084:
085: s = _conn.createStatement();
086: rows = s.executeUpdate(sql);
087:
088: t2 = System.currentTimeMillis();
089:
090: if (_log != null) {
091: long time = t2 - t1;
092: _log.println("--JDBC-LOG-START");
093: _log.println("DBMS: " + _dbVersion);
094: _log.println("Date: " + new java.util.Date(t1));
095: _log.println("Thread: "
096: + Thread.currentThread().getName());
097: _log.println("Sql: " + sql);
098: _log.println("Rows affected: " + rows);
099: _log.println("Time (ms): " + time);
100: }
101:
102: return rows;
103: }
104:
105: catch (Throwable e) {
106: String err = e.getMessage() + " [" + sql + "]";
107: throw new Throwable(err, e);
108: }
109:
110: finally {
111: if (s != null)
112: s.close();
113: }
114:
115: }
116:
117: /**
118: * Add sql to list of commands to be executed
119: * in batch using the method exec()
120: * @param sql SQL Command (insert, update or delete)
121: */
122: public void addBatchCommand(String sql) {
123: _batch.add(sql);
124: }
125:
126: /**
127: * Execute commands in batch - the commands must be previously loaded
128: * into this object using the method addBatchCommand()
129: * @param sql SQL command to execute
130: * @return Array where each element contains the number of rows affected for each statement
131: * @throws Throwable
132: */
133: public int[] exec() throws Throwable {
134:
135: long t1 = 0;
136: long t2 = 0;
137:
138: Statement s = null;
139: int rows[] = null;
140:
141: try {
142:
143: t1 = System.currentTimeMillis();
144:
145: s = _conn.createStatement();
146: for (int i = 0; i < _batch.size(); i++) {
147: s.addBatch((String) _batch.get(i));
148: }
149: rows = s.executeBatch();
150:
151: t2 = System.currentTimeMillis();
152:
153: if (_log != null) {
154: long time = t2 - t1;
155: _log.println("--JDBC-LOG-START");
156: _log.println("DBMS: " + _dbVersion);
157: _log.println("Date: " + new java.util.Date(t1));
158: _log.println("Thread: "
159: + Thread.currentThread().getName());
160: _log.println("Batch size: " + _batch.size());
161: _log.println("Time (ms): " + time);
162: }
163:
164: //PATCH 2005-09-02 clear internal batch array list after execute
165: _batch.clear();
166: //END PATCH
167:
168: return rows;
169: }
170:
171: catch (Throwable e) {
172: throw e;
173: }
174:
175: finally {
176: if (s != null)
177: s.close();
178: }
179:
180: }
181:
182: /**
183: * Creates a recordset given a SQL query.
184: * @param conn Database Connection
185: * @param sql SQL Query that returns a Resultset
186: * @throws Throwable
187: */
188: public Recordset get(String sql) throws Throwable {
189: return get(sql, 0);
190: }
191:
192: /**
193: * Creates a recordset given a SQL query.
194: * @param conn Database Connection
195: * @param sql SQL Query that returns a Resultset
196: * @param limit Maximum number of rows to read from the DataBase
197: * @throws Throwable
198: */
199: public Recordset get(String sql, int limit) throws Throwable {
200:
201: long t1 = 0;
202: long t2 = 0;
203: long l1 = 0;
204: long l2 = 0;
205:
206: ResultSet rs = null;
207: Statement stmt = null;
208:
209: try {
210:
211: t1 = System.currentTimeMillis();
212:
213: /* execute query */
214: stmt = _conn.createStatement();
215:
216: if (limit > 0)
217: stmt.setMaxRows(limit);
218:
219: rs = stmt.executeQuery(sql);
220:
221: t2 = System.currentTimeMillis();
222:
223: /* create recordset given the resultset */
224: l1 = System.currentTimeMillis();
225: Recordset recs = new Recordset(rs);
226: l2 = System.currentTimeMillis();
227:
228: if (_log != null) {
229: long time1 = t2 - t1;
230: long time2 = l2 - l1;
231: _log.println("--JDBC-LOG-START");
232: _log.println("DBMS: " + _dbVersion);
233: _log.println("Date: " + new java.util.Date(t1));
234: _log.println("Thread: "
235: + Thread.currentThread().getName());
236: _log.println("sql: " + sql);
237: _log.println("Records: " + recs.getRecordCount());
238: _log.println("Columns: " + recs.getFieldCount());
239: _log.println("Query execution time (ms): " + time1);
240: _log.println("Data loading time (ms): " + time2);
241: }
242:
243: return recs;
244:
245: } catch (Throwable e) {
246: String err = e.getMessage() + " [" + sql + "]";
247: throw new Throwable(err, e);
248: } finally {
249: if (rs != null)
250: rs.close();
251: if (stmt != null)
252: stmt.close();
253: }
254:
255: }
256:
257: /**
258: * Execute prepared statement using a recordset; only the current record is used
259: * @param sql SQL command to precompile
260: * @param rs Recordset containing the values to be set into the prepared statement
261: * @param params Array containing the names of the fields to use. The order must match the place holders (those ? marks) in the prepared statement
262: * @return The number of records affected
263: * @throws Throwable - if the params array contains no elements or contains more elements than the recordset field count
264: */
265: @SuppressWarnings("unchecked")
266: public int exec(String sql, Recordset rs, String[] params)
267: throws Throwable {
268:
269: long t1 = 0;
270: long t2 = 0;
271:
272: /* check for invalid parameters */
273: if (params.length == 0 || params.length > rs.getFieldCount()) {
274: String args[] = { String.valueOf(params.length),
275: String.valueOf(rs.getFieldCount()) };
276: String msg = Errors.INVALID_STMT_PARAMS;
277: msg = MessageFormat.format(msg, (Object[]) args);
278: throw new Throwable(msg);
279: }
280:
281: PreparedStatement s = null;
282: int rows = 0;
283:
284: try {
285:
286: t1 = System.currentTimeMillis();
287:
288: s = _conn.prepareStatement(sql);
289:
290: /* get recordset metadata*/
291: HashMap<String, RecordsetField> flds = rs.getFields();
292:
293: /* set value for each field */
294: for (int i = 0; i < params.length; i++) {
295: RecordsetField f = (RecordsetField) flds.get(params[i]);
296: Object value = rs.getValue(params[i]);
297: if (value == null) {
298: s.setNull(i + 1, f.getType());
299: } else {
300: //PATCH 2005-04-15 - support for SQLDATE instead of DATE
301: if (f.getType() == java.sql.Types.DATE) {
302: java.util.Date d = (java.util.Date) value;
303: value = new Date(d.getTime());
304: }
305: s.setObject(i + 1, value, f.getType());
306: }
307: }
308:
309: rows = s.executeUpdate();
310:
311: t2 = System.currentTimeMillis();
312:
313: if (_log != null) {
314: long time = t2 - t1;
315: _log.println("--JDBC-LOG-START");
316: _log.println("DBMS: " + _dbVersion);
317: _log.println("Date: " + new java.util.Date(t1));
318: _log.println("Thread: "
319: + Thread.currentThread().getName());
320: _log.println("Sql: " + sql);
321: _log.println("Rows affected: " + rows);
322: _log.println("Time (ms): " + time);
323: }
324:
325: return rows;
326: }
327:
328: catch (Throwable e) {
329: String err = e.getMessage() + " [" + sql + "]";
330: throw new Throwable(err, e);
331: }
332:
333: finally {
334: if (s != null)
335: s.close();
336: }
337:
338: }
339:
340: /**
341: * Execute batch prepared statement using a recordset; all the records are used
342: * @param sql SQL command to precompile
343: * @param rs Recordset containing the values to be set into the prepared statement
344: * @param params Array containing the names of the fields to use. The order must match the place holders (those ? marks) in the prepared statement
345: * @return The number of records affected
346: * @throws Throwable - if the params array contains no elements or contains more elements than the recordset field count
347: */
348: public int[] execBatch(String sql, Recordset rs, String[] params)
349: throws Throwable {
350:
351: long t1 = 0;
352: long t2 = 0;
353:
354: /* check for invalid parameters */
355: if (params.length == 0 || params.length > rs.getFieldCount()) {
356: String args[] = { String.valueOf(params.length),
357: String.valueOf(rs.getFieldCount()) };
358: String msg = Errors.INVALID_STMT_PARAMS;
359: msg = MessageFormat.format(msg, (Object[]) args);
360: throw new Throwable(msg);
361: }
362:
363: PreparedStatement s = null;
364: int rows[] = null;
365:
366: try {
367:
368: t1 = System.currentTimeMillis();
369:
370: s = _conn.prepareStatement(sql);
371:
372: /* get recordset metadata*/
373: HashMap<String, RecordsetField> flds = rs.getFields();
374:
375: /* navigate each record in the recordset */
376: rs.top();
377: while (rs.next()) {
378:
379: /* set value for each field */
380: for (int i = 0; i < params.length; i++) {
381: RecordsetField f = (RecordsetField) flds
382: .get(params[i]);
383: Object value = rs.getValue(params[i]);
384: if (value == null) {
385: s.setNull(i + 1, f.getType());
386: } else {
387: //PATCH 2005-04-15 - support for SQLDATE instead of DATE
388: if (f.getType() == java.sql.Types.DATE) {
389: java.util.Date d = (java.util.Date) value;
390: value = new Date(d.getTime());
391: }
392: s.setObject(i + 1, value, f.getType());
393: }
394: }
395:
396: /* add batch statement */
397: s.addBatch();
398:
399: }
400:
401: /* execute all commands */
402: rows = s.executeBatch();
403:
404: t2 = System.currentTimeMillis();
405:
406: if (_log != null) {
407: long time = t2 - t1;
408: _log.println("--JDBC-LOG-START");
409: _log.println("DBMS: " + _dbVersion);
410: _log.println("Date: " + new java.util.Date(t1));
411: _log.println("Thread: "
412: + Thread.currentThread().getName());
413: _log.println("Sql: " + sql);
414: _log.println("Batch size: " + rs.getRecordCount());
415: _log.println("Time (ms): " + time);
416: }
417:
418: return rows;
419: }
420:
421: catch (SQLException sqe) {
422: if (sqe.getNextException() != null)
423: throw new Throwable(
424: sqe.getNextException().getMessage(), sqe
425: .getCause());
426: else
427: throw sqe;
428: }
429:
430: catch (Exception e) {
431: String err = e.getMessage() + " [" + sql + "]";
432: throw new Throwable(err, e);
433: }
434:
435: finally {
436: if (s != null)
437: s.close();
438: }
439:
440: }
441:
442: /**
443: * Save binary file to blob column using a prepared statement.<br>
444: * The prepared statement must contain only one dynamic parameter (?),
445: * and it must correspond to the BLOB column. Example:<br>
446: * insert into images (id, title, imgsize, data) values (1,'my image', 8112, ?)
447: * <br><br>
448: * This means that the SQL must be pre-processed by your code in order to
449: * set the static values. GenericTransaction superclass provides the method getSql()
450: * to help you achieve easy static SQL generation.
451: * @param sql SQL used to build prepared statement. The blob column will be the only dynamic (?) parameter.
452: * @param path File to be uploaded into the blob column
453: * @throws Throwable
454: */
455: public void saveBlob(String sql, String path) throws Throwable {
456:
457: /* create buffer to read image data */
458: File f = new File(path);
459: FileInputStream img = new FileInputStream(f);
460: int size = (int) f.length();
461: BufferedInputStream buf = new BufferedInputStream(img);
462:
463: /* save image using prepared statement */
464: PreparedStatement p = null;
465:
466: try {
467: p = _conn.prepareStatement(sql);
468: p.setBinaryStream(1, buf, size);
469: p.execute();
470: } catch (SQLException sqe) {
471: Throwable t = null;
472: String msg = null;
473: String date = StringUtil.formatDate(new java.util.Date(),
474: "dd-MM-yyyy HH:mm:ss");
475:
476: if (sqe.getNextException() != null) {
477: msg = sqe.getNextException().getMessage();
478: t = sqe.getCause();
479: } else {
480: msg = sqe.getMessage();
481: t = sqe;
482: }
483: System.err.println("[WARNING@" + date
484: + "] Db.saveBlob failed: " + msg + " SQL: [" + sql
485: + "]");
486: throw new Throwable(
487: "Error cargando el archivo en base de datos", t);
488:
489: } catch (Throwable e) {
490: String date = StringUtil.formatDate(new java.util.Date(),
491: "dd-MM-yyyy HH:mm:ss");
492: System.err.println("[WARNING@" + date
493: + "] Db.saveBlob failed: " + e.getMessage()
494: + " SQL: [" + sql + "]");
495: throw new Throwable(
496: "Error cargando el archivo en base de datos", e);
497: } finally {
498: if (p != null)
499: p.close();
500: if (img != null)
501: img.close();
502: if (buf != null)
503: buf.close();
504: }
505:
506: }
507:
508: /**
509: * Execute query and return value of a given column
510: * @param sql Query to execute and retrieve a recordset
511: * @param columnName Name of the column whose value will be retrieved
512: * @return Value of column as Object or null if recordset is empty
513: * @throws Throwable
514: */
515: public Object getColValue(String sql, String columnName)
516: throws Throwable {
517: Recordset rs = get(sql);
518: if (rs.getRecordCount() > 0) {
519: rs.first();
520: return rs.getValue(columnName);
521: } else
522: return null;
523: }
524:
525: /**
526: * Execute query and return value of a given column
527: * @param sql Query to execute and retrieve a recordset
528: * @param columnName Name of the column whose value will be retrieved
529: * @return Value of column as int
530: * @throws Throwable if query fails or if recordset is empty
531: */
532: public int getIntColValue(String sql, String columnName)
533: throws Throwable {
534: Recordset rs = get(sql);
535: if (rs.getRecordCount() > 0) {
536: rs.first();
537: return rs.getInt(columnName);
538: } else
539: throw new Throwable("Recordset is empty!");
540: }
541:
542: /**
543: * Execute query and return value of a given column
544: * @param sql Query to execute and retrieve a recordset
545: * @param columnName Name of the column whose value will be retrieved
546: * @return Value of column as double
547: * @throws Throwable if query fails or if recordset is empty
548: */
549: public double getDoubleColValue(String sql, String columnName)
550: throws Throwable {
551: Recordset rs = get(sql);
552: if (rs.getRecordCount() > 0) {
553: rs.first();
554: return rs.getDouble(columnName);
555: } else
556: throw new Throwable("Recordset is empty!");
557: }
558:
559: }
|