Java > Open Source Codes > dinamica > Db


1 package dinamica;
2
3 import java.sql.*;
4 import java.text.MessageFormat ;
5 import java.util.ArrayList ;
6 import java.io.BufferedInputStream ;
7 import java.io.File ;
8 import java.io.FileInputStream ;
9 import java.io.PrintWriter ;
10 import java.util.HashMap ;
11
12 /**
13  * Core-level framework class: Text Generator.
14  * <br><br>
15  * Encapsulates the JDBC API most common operations
16  * with safe and efficient code. Uses the same connection
17  * for all its operations. Does not close connections, that is the
18  * responsability of the caller (Transaction Manager)
19  * <br>
20  * Creation date: 12/09/2003<br>
21  * Last Update: 12/09/2003<br>
22  * (c) 2003 Martin Cordova<br>
23  * This code is released under the LGPL license<br>
24  * @author Martin Cordova (dinamica@martincordova.com)
25  */
26 public class Db
27 {
28
29     /** database connection */
30     private Connection _conn = null;
31
32     /** log writer */
33     private PrintWriter _log = null;
34
35     /** container for batch commands */
36     private ArrayList _batch = new ArrayList ();
37
38     /** database version info */
39     private String _dbVersion = null;
40
41     /**
42      * Set the log writer. This class will log
43      * all query executions -as a text report- if this object is set
44      * @param writer
45      */
46     public void setLogWriter(PrintWriter writer)
47     {
48         _log = writer;
49     
50     }
51
52     /** Default constructor to initialize the object */
53     public Db(Connection conn)
54     {
55         _conn = conn;
56         DatabaseMetaData md;
57         try
58         {
59             
60             md = _conn.getMetaData();
61             
62             _dbVersion = md.getDatabaseProductName()
63             + " " + md.getDatabaseProductVersion()
64             + " (" + md.getDriverName()
65             + " " + md.getDriverVersion() + ")";
66             
67         }
68         catch (SQLException e)
69         {
70         }
71     }
72
73     /**
74      * Execute an action query (inserts, updates, deletes)
75      * @param sql SQL command to execute
76      * @return The number of rows affected
77      * @throws Throwable
78      */
79     public int exec(String sql) throws Throwable
80     {
81         
82         long t1 = 0;
83         long t2 = 0;
84         
85         Statement s = null;
86         int rows = 0;
87         
88         try
89         {
90             
91             t1 = System.currentTimeMillis();
92             
93             s = _conn.createStatement();
94             rows = s.executeUpdate(sql);
95             
96             t2 = System.currentTimeMillis();
97
98             if (_log!=null)
99             {
100                 long time = t2 - t1;
101                 _log.println("--JDBC-LOG-START");
102                 _log.println("DBMS: " + _dbVersion);
103                 _log.println("Date: " + new java.util.Date (t1));
104                 _log.println("Thread: " + Thread.currentThread().getName());
105                 _log.println("Sql: " + sql);
106                 _log.println("Rows affected: " + rows);
107                 _log.println("Time (ms): " + time);
108             }
109             
110             return rows;
111         }
112         
113         catch (Throwable e)
114         {
115             String err = e.getMessage() + " [" + sql + "]";
116             throw new Throwable (err, e);
117         }
118         
119         finally
120         {
121             if (s!=null) s.close();
122         }
123         
124     }
125
126     /**
127      * Add sql to list of commands to be executed
128      * in batch using the method exec()
129      * @param sql SQL Command (insert, update or delete)
130      */
131     public void addBatchCommand(String sql)
132     {
133         _batch.add(sql);
134     }
135
136     /**
137      * Execute commands in batch - the commands must be previously loaded
138      * into this object using the method addBatchCommand()
139      * @param sql SQL command to execute
140      * @return Array where each element contains the number of rows affected for each statement
141      * @throws Throwable
142      */
143     public int[] exec() throws Throwable
144     {
145         
146         long t1 = 0;
147         long t2 = 0;
148         
149         Statement s = null;
150         int rows[] = null;
151         
152         try
153         {
154             
155             t1 = System.currentTimeMillis();
156             
157             s = _conn.createStatement();
158             for (int i=0; i<_batch.size(); i++)
159             {
160                 s.addBatch((String )_batch.get(i));
161             }
162             rows = s.executeBatch();
163             
164             t2 = System.currentTimeMillis();
165
166             if (_log!=null)
167             {
168                 long time = t2 - t1;
169                 _log.println("--JDBC-LOG-START");
170                 _log.println("DBMS: " + _dbVersion);
171                 _log.println("Date: " + new java.util.Date (t1));
172                 _log.println("Thread: " + Thread.currentThread().getName());
173                 _log.println("Batch size: " + _batch.size());
174                 _log.println("Time (ms): " + time);
175             }
176             
177             //PATCH 2005-09-02 clear internal batch array list after execute
178 _batch.clear();
179             //END PATCH
180
181             return rows;
182         }
183         
184         catch (Throwable e)
185         {
186             throw e;
187         }
188         
189         finally
190         {
191             if (s!=null) s.close();
192         }
193         
194     }
195
196     /**
197      * Creates a recordset given a SQL query.
198      * @param conn Database Connection
199      * @param sql SQL Query that returns a Resultset
200      * @throws Throwable
201      */
202     public Recordset get(String sql) throws Throwable
203     {
204         return get(sql, 0);
205     }
206
207     /**
208      * Creates a recordset given a SQL query.
209      * @param conn Database Connection
210      * @param sql SQL Query that returns a Resultset
211      * @param limit Maximum number of rows to read from the DataBase
212      * @throws Throwable
213      */
214     public Recordset get(String sql, int limit) throws Throwable
215     {
216
217         long t1 = 0;
218         long t2 = 0;
219         long l1 = 0;
220         long l2 = 0;
221
222         ResultSet rs = null;
223         Statement stmt = null;
224
225         try
226         {
227
228             t1 = System.currentTimeMillis();
229
230             /* execute query */
231             stmt = _conn.createStatement();
232
233             if (limit > 0)
234                 stmt.setMaxRows(limit);
235
236             rs = stmt.executeQuery(sql);
237             
238             t2 = System.currentTimeMillis();
239
240             /* create recordset given the resultset */
241             l1 = System.currentTimeMillis();
242             Recordset recs = new Recordset(rs);
243             l2 = System.currentTimeMillis();
244
245             if (_log!=null)
246             {
247                 long time1 = t2 - t1;
248                 long time2 = l2 - l1;
249                 _log.println("--JDBC-LOG-START");
250                 _log.println("DBMS: " + _dbVersion);
251                 _log.println("Date: " + new java.util.Date (t1));
252                 _log.println("Thread: " + Thread.currentThread().getName());
253                 _log.println("sql: " + sql);
254                 _log.println("Records: " + recs.getRecordCount());
255                 _log.println("Columns: " + recs.getFieldCount());
256                 _log.println("Query execution time (ms): " + time1);
257                 _log.println("Data loading time (ms): " + time2);
258             }
259             
260             return recs;
261             
262         }
263         catch (Throwable e)
264         {
265             String err = e.getMessage() + " [" + sql + "]";
266             throw new Throwable (err, e);
267         }
268         finally
269         {
270             if( rs != null ) rs.close();
271             if( stmt != null ) stmt.close();
272         }
273         
274     }
275
276     /**
277      * Execute prepared statement using a recordset; only the current record is used
278      * @param sql SQL command to precompile
279      * @param rs Recordset containing the values to be set into the prepared statement
280      * @param params Array containing the names of the fields to use. The order must match the place holders (those ? marks) in the prepared statement
281      * @return The number of records affected
282      * @throws Throwable - if the params array contains no elements or contains more elements than the recordset field count
283      */
284     public int exec(String sql, Recordset rs, String [] params) throws Throwable
285     {
286
287         long t1 = 0;
288         long t2 = 0;
289     
290         /* check for invalid parameters */
291         if (params.length == 0 || params.length > rs.getFieldCount())
292         {
293             String args[] = {String.valueOf(params.length), String.valueOf(rs.getFieldCount())};
294             String msg = Errors.INVALID_STMT_PARAMS;
295             msg = MessageFormat.format(msg, args);
296             throw new Throwable (msg);
297         }
298         
299         PreparedStatement s = null;
300         int rows = 0;
301         
302         try
303         {
304             
305             t1 = System.currentTimeMillis();
306             
307             s = _conn.prepareStatement(sql);
308             
309             /* get recordset metadata*/
310             HashMap flds = rs.getFields();
311             
312             /* set value for each field */
313             for (int i=0;i<params.length;i++)
314             {
315                 RecordsetField f = (RecordsetField)flds.get(params[i]);
316                 Object value = rs.getValue(params[i]);
317                 if (value==null)
318                 {
319                     s.setNull(i+1, f.getType());
320                 }
321                 else
322                 {
323                     //PATCH 2005-04-15 - support for SQLDATE instead of DATE
324 if (f.getType()==java.sql.Types.DATE)
325                     {
326                         java.util.Date d = (java.util.Date )value;
327                         value = new Date(d.getTime());
328                     }
329                     s.setObject(i+1, value, f.getType());
330                 }
331             }
332             
333             rows = s.executeUpdate();
334             
335             t2 = System.currentTimeMillis();
336
337             if (_log!=null)
338             {
339                 long time = t2 - t1;
340                 _log.println("--JDBC-LOG-START");
341                 _log.println("DBMS: " + _dbVersion);
342                 _log.println("Date: " + new java.util.Date (t1));
343                 _log.println("Thread: " + Thread.currentThread().getName());
344                 _log.println("Sql: " + sql);
345                 _log.println("Rows affected: " + rows);
346                 _log.println("Time (ms): " + time);
347             }
348             
349             return rows;
350         }
351         
352         catch (Throwable e)
353         {
354             String err = e.getMessage() + " [" + sql + "]";
355             throw new Throwable (err, e);
356         }
357         
358         finally
359         {
360             if (s!=null) s.close();
361         }
362
363     }
364
365     /**
366      * Execute batch prepared statement using a recordset; all the records are used
367      * @param sql SQL command to precompile
368      * @param rs Recordset containing the values to be set into the prepared statement
369      * @param params Array containing the names of the fields to use. The order must match the place holders (those ? marks) in the prepared statement
370      * @return The number of records affected
371      * @throws Throwable - if the params array contains no elements or contains more elements than the recordset field count
372      */
373     public int[] execBatch(String sql, Recordset rs, String [] params) throws Throwable
374     {
375
376         long t1 = 0;
377         long t2 = 0;
378     
379         /* check for invalid parameters */
380         if (params.length == 0 || params.length > rs.getFieldCount())
381         {
382             String args[] = {String.valueOf(params.length), String.valueOf(rs.getFieldCount())};
383             String msg = Errors.INVALID_STMT_PARAMS;
384             msg = MessageFormat.format(msg, args);
385             throw new Throwable (msg);
386         }
387         
388         PreparedStatement s = null;
389         int rows[] = null;
390         
391         try
392         {
393             
394             t1 = System.currentTimeMillis();
395             
396             s = _conn.prepareStatement(sql);
397
398             /* get recordset metadata*/
399             HashMap flds = rs.getFields();
400
401             /* navigate each record in the recordset */
402             rs.top();
403             while (rs.next())
404             {
405
406                 /* set value for each field */
407                 for (int i=0;i<params.length;i++)
408                 {
409                     RecordsetField f = (RecordsetField)flds.get(params[i]);
410                     Object value = rs.getValue(params[i]);
411                     if (value==null)
412                     {
413                         s.setNull(i+1, f.getType());
414                     }
415                     else
416                     {
417                         //PATCH 2005-04-15 - support for SQLDATE instead of DATE
418 if (f.getType()==java.sql.Types.DATE)
419                         {
420                             java.util.Date d = (java.util.Date )value;
421                             value = new Date(d.getTime());
422                         }
423                         s.setObject(i+1, value, f.getType());
424                     }
425                 }
426
427                 /* add batch statement */
428                 s.addBatch();
429
430             }
431             
432             /* execute all commands */
433             rows = s.executeBatch();
434             
435             t2 = System.currentTimeMillis();
436
437             if (_log!=null)
438             {
439                 long time = t2 - t1;
440                 _log.println("--JDBC-LOG-START");
441                 _log.println("DBMS: " + _dbVersion);
442                 _log.println("Date: " + new java.util.Date (t1));
443                 _log.println("Thread: " + Thread.currentThread().getName());
444                 _log.println("Sql: " + sql);
445                 _log.println("Batch size: " + rs.getRecordCount());
446                 _log.println("Time (ms): " + time);
447             }
448             
449             return rows;
450         }
451         
452         catch (Throwable e)
453         {
454             String err = e.getMessage() + " [" + sql + "]";
455             throw new Throwable (err, e);
456         }
457         
458         finally
459         {
460             if (s!=null) s.close();
461         }
462
463     }
464
465     /**
466      * Save binary file to blob column using a prepared statement.<br>
467      * The prepared statement must contain only one dynamic parameter (?),
468      * and it must correspond to the BLOB column. Example:<br>
469      * insert into images (id, title, imgsize, data) values (1,'my image', 8112, ?)
470      * <br><br>
471      * This means that the SQL must be pre-processed by your code in order to
472      * set the static values. GenericTransaction superclass provides the method getSql()
473      * to help you achieve easy static SQL generation.
474      * @param sql SQL used to build prepared statement. The blob column will be the only dynamic (?) parameter.
475      * @param path File to be uploaded into the blob column
476      * @throws Throwable
477      */
478     public void saveBlob(String sql, String path) throws Throwable
479     {
480
481         /* create buffer to read image data */
482         File f = new File ( path );
483         FileInputStream img = new FileInputStream (f);
484         int size = (int)f.length();
485         BufferedInputStream buf = new BufferedInputStream (img);
486
487         /* save image using prepared statement */
488         PreparedStatement p = null;
489             
490         try
491         {
492             p = _conn.prepareStatement(sql);
493             p.setBinaryStream(1, buf, size);
494             p.execute();
495         }
496         catch (Throwable e)
497         {
498             String date = StringUtil.formatDate(new java.util.Date (), "dd-MM-yyyy HH:mm:ss");
499             System.err.println("[WARNING@" + date + "] Db.saveBlob failed: " + e.getMessage() + " [" + sql + "]");
500             throw new Throwable ("Error saving BLOB data into database " + "[" + sql + "]", e);
501         }
502         finally
503         {
504             if (p!=null) p.close();
505             if (img!=null) img.close();
506             if (buf!=null) buf.close();
507         }
508
509     }
510
511 }
512

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags