Java > Open Source Codes > org > jivesoftware > database > ProfiledConnection


1 /**
2  * $RCSfile: ProfiledConnection.java,v $
3  * $Revision: 1.2 $
4  * $Date: 2005/03/10 17:53:39 $
5  *
6  * Copyright (C) 2004 Jive Software. All rights reserved.
7  *
8  * This software is published under the terms of the GNU Public License (GPL),
9  * a copy of which is included in this distribution.
10  */
11
12 package org.jivesoftware.database;
13
14 import java.sql.*;
15 import java.util.Enumeration ;
16 import java.util.Hashtable ;
17
18 /**
19  * Wraps a Connection object and collects statistics about the database queries
20  * that are performed.<p>
21  * <p/>
22  * Statistics of the profiled Connections can be obtained from the static
23  * methods of this class. Instances of this class are the actual wrappers that
24  * perform profiling.
25  *
26  * @author Jive Software
27  */
28 public class ProfiledConnection extends AbstractConnection {
29
30     /**
31      * Constant for SELECT database queries.
32      */
33     public static final int SELECT = 0;
34
35     /**
36      * Constant for UPDATE database queries.
37      */
38     public static final int UPDATE = 1;
39
40     /**
41      * Constant for INSERT database queries.
42      */
43     public static final int INSERT = 2;
44
45     /**
46      * Constant for DELETE database queries.
47      */
48     public static final int DELETE = 3;
49
50     private static long startInsertTime = 0;
51     private static long startUpdateTime = 0;
52     private static long startSelectTime = 0;
53     private static long startDeleteTime = 0;
54
55     private static long endInsertTime = 0;
56     private static long endUpdateTime = 0;
57     private static long endSelectTime = 0;
58     private static long endDeleteTime = 0;
59
60     private static long insertCount = 0;
61     private static long updateCount = 0;
62     private static long selectCount = 0;
63     private static long deleteCount = 0;
64
65     private static long totalInsertTime = 0;
66     private static long totalUpdateTime = 0;
67     private static long totalSelectTime = 0;
68     private static long totalDeleteTime = 0;
69
70     private static Hashtable insertQueries = new Hashtable ();
71     private static Hashtable updateQueries = new Hashtable ();
72     private static Hashtable selectQueries = new Hashtable ();
73     private static Hashtable deleteQueries = new Hashtable ();
74
75     /**
76      * Start profiling.
77      */
78     public static void start() {
79         long now = System.currentTimeMillis();
80         startInsertTime = startUpdateTime = startSelectTime = startDeleteTime = now;
81     }
82
83     /**
84      * Stop profiling.
85      */
86     public static void stop() {
87         endInsertTime = endUpdateTime = endSelectTime = endDeleteTime = 0;
88     }
89
90     /**
91      * Returns the total number database queries of a particular type performed.
92      * Valid types are ProfiledConnection.SELECT, ProfiledConnection.UPDATE,
93      * ProfiledConnection.INSERT, and ProfiledConnection.DELETE.
94      *
95      * @param type the type of query to get the count for.
96      * @return the number queries of type <tt>type</tt> performed.
97      */
98     public static long getQueryCount(int type) {
99         switch (type) {
100             case SELECT:
101                 return selectCount;
102             case UPDATE:
103                 return updateCount;
104             case INSERT:
105                 return insertCount;
106             case DELETE:
107                 return deleteCount;
108             default:
109                 throw new IllegalArgumentException ("Invalid type");
110         }
111     }
112
113     /**
114      * @param sql the insert sql string.
115      * @param time the length of time the query took in milliseconds
116      */
117     public static void addQuery(int type, String sql, long time) {
118         // Do nothing if we didn't receive a sql statement
119 if (sql == null || sql.equals("")) {
120             return;
121         }
122
123         // clean up sql to insert spaces after every ','
124 sql = reformatQuery(sql);
125
126         // remove values from query
127 sql = removeQueryValues(sql);
128
129         ProfiledConnectionEntry entry = null;
130         switch (type) {
131             case SELECT:
132                 selectCount++;
133                 totalSelectTime += time;
134                 entry = (ProfiledConnectionEntry)selectQueries.get(sql);
135                 if (entry == null) {
136                     entry = new ProfiledConnectionEntry(sql);
137                     selectQueries.put(sql, entry);
138                 }
139                 break;
140             case UPDATE:
141                 updateCount++;
142                 totalUpdateTime += time;
143                 entry = (ProfiledConnectionEntry)updateQueries.get(sql);
144                 if (entry == null) {
145                     entry = new ProfiledConnectionEntry(sql);
146                     updateQueries.put(sql, entry);
147                 }
148                 break;
149             case INSERT:
150                 insertCount++;
151                 totalInsertTime += time;
152                 entry = (ProfiledConnectionEntry)insertQueries.get(sql);
153                 if (entry == null) {
154                     entry = new ProfiledConnectionEntry(sql);
155                     insertQueries.put(sql, entry);
156                 }
157                 break;
158             case DELETE:
159                 deleteCount++;
160                 totalDeleteTime += time;
161                 entry = (ProfiledConnectionEntry)deleteQueries.get(sql);
162                 if (entry == null) {
163                     entry = new ProfiledConnectionEntry(sql);
164                     deleteQueries.put(sql, entry);
165                 }
166                 break;
167             default:
168                 throw new IllegalArgumentException ("Invalid type");
169         }
170
171         entry.count++;
172         entry.totalTime += time;
173     }
174
175     /**
176      * Returns the average number of queries of a certain type that have been
177      * performed per second since profiling started. If profiling has been
178      * stopped, that moment in time is used for the calculation. Otherwise,
179      * the current moment in time is used.
180      *
181      * @param type the type of database query to check.
182      * @return the average number of queries of a certain typed performed per
183      * second.
184      */
185     public static double getQueriesPerSecond(int type) {
186         long count, start, end;
187
188         switch (type) {
189             case SELECT:
190                 count = selectCount;
191                 start = startSelectTime;
192                 end = endSelectTime;
193                 break;
194             case UPDATE:
195                 count = updateCount;
196                 start = startUpdateTime;
197                 end = endUpdateTime;
198                 break;
199             case INSERT:
200                 count = insertCount;
201                 start = startInsertTime;
202                 end = endInsertTime;
203                 break;
204             case DELETE:
205                 count = deleteCount;
206                 start = startDeleteTime;
207                 end = endDeleteTime;
208                 break;
209             default:
210                 throw new IllegalArgumentException ("Invalid type");
211         }
212         // if no queries yet, return 0;
213 if (count == 0) {
214             return 0;
215         }
216         // If the profiling hasn't been stopped yet, we want to give
217 // profiling values up to the current time instead.
218 if (end == 0) {
219             end = System.currentTimeMillis();
220         }
221         // Compute the number of seconds
222 double time = (end - start) / 1000.0;
223         // Finally, return the average.
224 return count / time;
225     }
226
227     /**
228      * Returns the average amount of time spent executing the specified type
229      * of query.
230      *
231      * @param type the type of query.
232      * @return a double representing the average time spent executing the type
233      * of query.
234      */
235     public static double getAverageQueryTime(int type) {
236         long time, count;
237
238         switch (type) {
239             case SELECT:
240                 count = selectCount;
241                 time = totalSelectTime;
242                 break;
243             case UPDATE:
244                 count = updateCount;
245                 time = totalUpdateTime;
246                 break;
247             case INSERT:
248                 count = insertCount;
249                 time = totalInsertTime;
250                 break;
251             case DELETE:
252                 count = deleteCount;
253                 time = totalDeleteTime;
254                 break;
255             default:
256                 throw new IllegalArgumentException ("Invalid type");
257         }
258
259         if (count != 0) {
260             return time / (double)count;
261         }
262         else {
263             return 0.0;
264         }
265     }
266
267     /**
268      * Returns the total amount of time in milliseconds spent doing a particular
269      * type of query. Note that this isn't necessarily representative of actual real
270      * time since db queries often occur in parallel.
271      *
272      * @param type the type of query to check.
273      * @return the number of milliseconds spent executing the specified type of
274      * query.
275      */
276     public static long getTotalQueryTime(int type) {
277         switch (type) {
278             case SELECT:
279                 return totalSelectTime;
280             case UPDATE:
281                 return totalUpdateTime;
282             case INSERT:
283                 return totalInsertTime;
284             case DELETE:
285                 return totalDeleteTime;
286             default:
287                 throw new IllegalArgumentException ("Invalid type");
288         }
289     }
290
291     /**
292      * Returns an array of sorted queries (as ProfiledConnectionEntry objects) by type
293      *
294      * @param type the type of query to check
295      * @param sortByTime sort the resulting list by Time if true,
296      * otherwise sort by count if false (default)
297      * @return an array of ProfiledConnectionEntry objects
298      */
299     public static ProfiledConnectionEntry[] getSortedQueries(int type, boolean sortByTime) {
300         Hashtable queries;
301
302         switch (type) {
303             case SELECT:
304                 queries = selectQueries;
305                 break;
306             case UPDATE:
307                 queries = updateQueries;
308                 break;
309             case INSERT:
310                 queries = insertQueries;
311                 break;
312             case DELETE:
313                 queries = deleteQueries;
314                 break;
315             default:
316                 throw new IllegalArgumentException ("Invalid type");
317         }
318
319         ProfiledConnectionEntry[] result = new ProfiledConnectionEntry[queries.size()];
320
321         // no queries, return null set
322 if (queries.size() < 1) {
323             return null;
324         }
325
326         // since the values of the hashtable contain everything that
327 // we need (including the sql statement), ignore the keys
328 Enumeration e = queries.elements();
329
330         int c = 0;
331         while (e.hasMoreElements()) {
332             result[c++] = (ProfiledConnectionEntry)e.nextElement();
333         }
334
335         quickSort(result, sortByTime, 0, result.length - 1);
336         return result;
337     }
338
339     /**
340      * Reset all statistics.
341      */
342     public static void resetStatistics() {
343         startInsertTime = startUpdateTime = startSelectTime = startDeleteTime = 0;
344         endInsertTime = endUpdateTime = endSelectTime = endDeleteTime = 0;
345         insertCount = updateCount = selectCount = deleteCount = 0;
346         totalInsertTime = totalUpdateTime = totalSelectTime = totalDeleteTime = 0;
347
348         insertQueries.clear();
349         updateQueries.clear();
350         selectQueries.clear();
351         deleteQueries.clear();
352     }
353
354     /**
355      * @param entries entries
356      * @param sortByTime sort by time if true, otherwise sort by count
357      * @param first first index to sort on. Normally 0
358      * @param last last index to sort on. Normally length -1
359      */
360     private static void quickSort(ProfiledConnectionEntry[] entries, boolean sortByTime, int first, int last) {
361
362         // do nothing if array contains fewer than two elements
363 if (first >= last || entries.length < 2) {
364             return;
365         }
366
367         swap(entries, first, (first + last) / 2);
368
369         int index = first;
370         for (int i = first + 1; i <= last; i++) {
371             if (sortByTime && ((entries[first].totalTime / entries[first].count) < (entries[i].totalTime / entries[i].count))) {
372                 swap(entries, ++index, i);
373             }
374             else if (!sortByTime && entries[first].count < entries[i].count) {
375                 swap(entries, ++index, i);
376             }
377         }
378         swap(entries, first, index);
379         quickSort(entries, sortByTime, first, index - 1);
380         quickSort(entries, sortByTime, index + 1, last);
381     }
382
383     private static void swap(Object [] list, int i, int j) {
384         Object tmp = list[i];
385         list[i] = list[j];
386         list[j] = tmp;
387     }
388
389     private static String removeQueryValues(String _sql) {
390         int length = _sql.length();
391
392         if (_sql.indexOf("=") == -1) {
393             return _sql;
394         }
395
396         StringBuilder sql = new StringBuilder (_sql);
397         boolean inValue = false;
398         boolean afterEquals = false;
399         boolean hasQuotes = false;
400         int startValue = -1;
401         int endValue = -1;
402         int charRemoved = 0;
403
404         for (int x = 0; x < length; x++) {
405             char c = _sql.charAt(x);
406
407             switch (c) {
408                 case '=':
409                     {
410                         if (!afterEquals) {
411                             afterEquals = true;
412                         }
413                         break;
414                     }
415                 case ' ':
416                     {
417                         if (!hasQuotes && inValue) {
418                             endValue = x;
419                             inValue = false;
420                             hasQuotes = false;
421                             afterEquals = false;
422                         }
423                         break;
424                     }
425                 case '\'':
426                     {
427                         if (afterEquals && !inValue) {
428                             startValue = x;
429                             inValue = true;
430                             hasQuotes = true;
431                         }
432                         else if (afterEquals && inValue && hasQuotes) {
433                             endValue = x + 1;
434                             inValue = false;
435                             hasQuotes = false;
436                             afterEquals = false;
437                         }
438                         break;
439                     }
440                 case '-':
441                     {
442                         if (afterEquals && !inValue) {
443                             startValue = x;
444                             inValue = true;
445
446                         }
447                         break;
448                     }
449                 case '+':
450                     {
451                         if (afterEquals && !inValue) {
452                             startValue = x;
453                             inValue = true;
454                         }
455                         break;
456                     }
457                 case '0':
458                     {
459                         if (afterEquals && !inValue) {
460                             startValue = x;
461                             inValue = true;
462                         }
463                         break;
464                     }
465                 case '1':
466                     {
467                         if (afterEquals && !inValue) {
468                             startValue = x;
469                             inValue = true;
470                         }
471                         break;
472                     }
473                 case '2':
474                     {
475                         if (afterEquals && !inValue) {
476                             startValue = x;
477                             inValue = true;
478                         }
479                         break;
480                     }
481                 case '3':
482                     {
483                         if (afterEquals && !inValue) {
484                             startValue = x;
485                             inValue = true;
486                         }
487                         break;
488                     }
489                 case '4':
490                     {
491                         if (afterEquals && !inValue) {
492                             startValue = x;
493                             inValue = true;
494                         }
495                         break;
496                     }
497                 case '5':
498                     {
499                         if (afterEquals && !inValue) {
500                             startValue = x;
501                             inValue = true;
502                         }
503                         break;
504                     }
505                 case '6':
506                     {
507                         if (afterEquals && !inValue) {
508                             startValue = x;
509                             inValue = true;
510                         }
511                         break;
512                     }
513                 case '7':
514                     {
515                         if (afterEquals && !inValue) {
516                             startValue = x;
517                             inValue = true;
518                         }
519                         break;
520                     }
521                 case '8':
522                     {
523                         if (afterEquals && !inValue) {
524                             startValue = x;
525                             inValue = true;
526                         }
527                         break;
528                     }
529                 case '9':
530                     {
531                         if (afterEquals && !inValue) {
532                             startValue = x;
533                             inValue = true;
534                         }
535                         break;
536                     }
537                 default:
538                     {
539                         if (afterEquals && !inValue) {
540                             afterEquals = false;
541                         }
542                     }
543             }
544
545             if (x == length - 1 && afterEquals) {
546                 endValue = x + 1;
547             }
548
549             if (startValue != -1 && endValue != -1) {
550                 sql.replace(startValue - charRemoved, endValue - charRemoved, "?");
551
552                 charRemoved += endValue - startValue - 1;
553                 startValue = -1;
554                 endValue = -1;
555             }
556         }
557
558         return sql.toString();
559     }
560
561     private static String reformatQuery(String _sql) {
562         int length = _sql.length();
563         int charAdded = 0;
564         StringBuilder sql = new StringBuilder (_sql);
565
566         for (int x = 0; x < length; x++) {
567             char c = _sql.charAt(x);
568
569             if (c == ',' && x < length - 1 && _sql.charAt(x + 1) != ' ') {
570                 sql.replace(x + charAdded, x + 1 + charAdded, ", ");
571                 charAdded++;
572             }
573         }
574
575         return sql.toString();
576     }
577
578     //--------------------- Connection Wrapping Code ---------------------//
579
580
581     /**
582      * Creates a new ProfiledConnection that wraps the specified connection.
583      *
584      * @param connection the Connection to wrap and collect stats for.
585      */
586     public ProfiledConnection(Connection connection) {
587         super(connection);
588     }
589
590     public void close() throws SQLException {
591         // Close underlying connection.
592 if (connection != null) {
593             connection.close();
594         }
595     }
596
597     public Statement createStatement() throws SQLException {
598         // Returned a TimedStatement so that we can do db timings.
599 return new TimedStatement(connection.createStatement());
600     }
601
602     public PreparedStatement prepareStatement(String sql) throws SQLException {
603         // Returned a TimedPreparedStatement so that we can do db timings.
604 return new TimedPreparedStatement(connection.prepareStatement(sql), sql);
605     }
606
607     public Statement createStatement(int resultSetType, int resultSetConcurrency)
608             throws SQLException {
609         return new TimedStatement(connection.createStatement(resultSetType,
610                 resultSetConcurrency));
611     }
612
613     public PreparedStatement prepareStatement(String sql, int resultSetType,
614                                               int resultSetConcurrency) throws SQLException {
615         return new TimedPreparedStatement(connection.prepareStatement(sql, resultSetType, resultSetConcurrency), sql);
616     }
617
618     public CallableStatement prepareCall(String sql) throws SQLException {
619         return new TimedCallableStatement(connection.prepareCall(sql), sql);
620     }
621
622     public CallableStatement prepareCall(String sql, int i, int i1) throws SQLException {
623         return new TimedCallableStatement(connection.prepareCall(sql, i, i1), sql);
624     }
625
626     /**
627      * An implementation of the Statement interface that wraps an underlying
628      * Statement object and performs timings of the database queries. The class
629      * does not handle batch queries but should generally work otherwise.
630      */
631     class TimedStatement extends StatementWrapper {
632
633         private Statement stmt;
634
635         /**
636          * Creates a new TimedStatement that wraps <tt>stmt</tt>.
637          */
638         public TimedStatement(Statement stmt) {
639             super(stmt);
640             this.stmt = stmt;
641         }
642
643         public boolean execute(String sql) throws SQLException {
644
645             long t1 = System.currentTimeMillis();
646             boolean result = stmt.execute(sql);
647             long t2 = System.currentTimeMillis();
648
649             // determine the type of query
650 String sqlL = sql.toLowerCase().trim();
651
652             if (sqlL.startsWith("insert")) {
653                 addQuery(INSERT, sql, t2 - t1);
654             }
655             else if (sqlL.startsWith("update")) {
656                 addQuery(UPDATE, sql, t2 - t1);
657             }
658             else if (sqlL.startsWith("delete")) {
659                 addQuery(DELETE, sql, t2 - t1);
660             }
661             else {
662                 addQuery(SELECT, sql, t2 - t1);
663             }
664             return result;
665         }
666
667         public ResultSet executeQuery(String sql) throws SQLException {
668             long t1 = System.currentTimeMillis();
669             ResultSet result = stmt.executeQuery(sql);
670             long t2 = System.currentTimeMillis();
671
672             // determine the type of query
673 String sqlL = sql.toLowerCase().trim();
674
675             if (sqlL.startsWith("insert")) {
676                 addQuery(INSERT, sql, t2 - t1);
677             }
678             else if (sqlL.startsWith("update")) {
679                 addQuery(UPDATE, sql, t2 - t1);
680             }
681             else if (sqlL.startsWith("delete")) {
682                 addQuery(DELETE, sql, t2 - t1);
683             }
684             else {
685                 addQuery(SELECT, sql, t2 - t1);
686             }
687             return result;
688         }
689
690         public int executeUpdate(String sql) throws SQLException {
691             long t1 = System.currentTimeMillis();
692             int result = stmt.executeUpdate(sql);
693             long t2 = System.currentTimeMillis();
694
695             // determine the type of query
696 String sqlL = sql.toLowerCase().trim();
697
698             if (sqlL.startsWith("insert")) {
699                 addQuery(INSERT, sql, t2 - t1);
700             }
701             else if (sqlL.startsWith("update")) {
702                 addQuery(UPDATE, sql, t2 - t1);
703             }
704             else if (sqlL.startsWith("delete")) {
705                 addQuery(DELETE, sql, t2 - t1);
706             }
707             else {
708                 addQuery(SELECT, sql, t2 - t1);
709             }
710             return result;
711         }
712     }
713
714     /**
715      * An implementation of the PreparedStatement interface that wraps an
716      * underlying PreparedStatement object and performs timings of the database
717      * queries.
718      */
719     class TimedPreparedStatement extends PreparedStatementWrapper {
720
721         private String sql;
722         private int type = SELECT;
723
724         public TimedPreparedStatement(PreparedStatement pstmt, String sql) {
725             super(pstmt);
726             this.sql = sql;
727
728             // determine the type of query
729 String sqlL = sql.toLowerCase().trim();
730
731             if (sqlL.startsWith("insert")) {
732                 type = INSERT;
733             }
734             else if (sqlL.startsWith("update")) {
735                 type = UPDATE;
736             }
737             else if (sqlL.startsWith("delete")) {
738                 type = DELETE;
739             }
740             else {
741                 type = SELECT;
742             }
743         }
744
745         public boolean execute() throws SQLException {
746             // Perform timing of this method.
747 long t1 = System.currentTimeMillis();
748             boolean result = pstmt.execute();
749             long t2 = System.currentTimeMillis();
750
751             switch (type) {
752                 case SELECT:
753                     addQuery(SELECT, sql, t2 - t1);
754                     break;
755                 case UPDATE:
756                     addQuery(UPDATE, sql, t2 - t1);
757                     break;
758                 case INSERT:
759                     addQuery(INSERT, sql, t2 - t1);
760                     break;
761                 case DELETE:
762                     addQuery(DELETE, sql, t2 - t1);
763                     break;
764             }
765             return result;
766         }
767
768         /*
769          * This is one of the methods that we wish to time
770          */
771         public ResultSet executeQuery() throws SQLException {
772
773             long t1 = System.currentTimeMillis();
774             ResultSet result = pstmt.executeQuery();
775             long t2 = System.currentTimeMillis();
776
777             switch (type) {
778                 case SELECT:
779                     addQuery(SELECT, sql, t2 - t1);
780                     break;
781                 case UPDATE:
782                     addQuery(UPDATE, sql, t2 - t1);
783                     break;
784                 case INSERT:
785                     addQuery(INSERT, sql, t2 - t1);
786                     break;
787                 case DELETE:
788                     addQuery(DELETE, sql, t2 - t1);
789                     break;
790             }
791             return result;
792         }
793
794         /*
795          * This is one of the methods that we wish to time
796          */
797         public int executeUpdate() throws SQLException {
798
799             long t1 = System.currentTimeMillis();
800             int result = pstmt.executeUpdate();
801             long t2 = System.currentTimeMillis();
802
803             switch (type) {
804                 case SELECT:
805                     addQuery(SELECT, sql, t2 - t1);
806                     break;
807                 case UPDATE:
808                     addQuery(UPDATE, sql, t2 - t1);
809                     break;
810                 case INSERT:
811                     addQuery(INSERT, sql, t2 - t1);
812                     break;
813                 case DELETE:
814                     addQuery(DELETE, sql, t2 - t1);
815                     break;
816             }
817             return result;
818         }
819
820         // The following methods are from the Statement class - the
821 // SuperInterface of PreparedStatement
822 // without these this class won't compile
823
824         public boolean execute(String _sql) throws SQLException {
825
826             long t1 = System.currentTimeMillis();
827             boolean result = pstmt.execute(_sql);
828             long t2 = System.currentTimeMillis();
829
830             // determine the type of query
831 String sqlL = _sql.toLowerCase().trim();
832
833             if (sqlL.startsWith("insert")) {
834                 addQuery(INSERT, _sql, t2 - t1);
835             }
836             else if (sqlL.startsWith("update")) {
837                 addQuery(UPDATE, _sql, t2 - t1);
838             }
839             else if (sqlL.startsWith("delete")) {
840                 addQuery(DELETE, _sql, t2 - t1);
841             }
842             else {
843                 addQuery(SELECT, _sql, t2 - t1);
844             }
845             return result;
846         }
847
848         public int[] executeBatch() throws SQLException {
849
850             long t1 = System.currentTimeMillis();
851             int[] result = pstmt.executeBatch();
852             long t2 = System.currentTimeMillis();
853
854             switch (type) {
855                 case SELECT:
856                     addQuery(SELECT, sql, t2 - t1);
857                     break;
858                 case UPDATE:
859                     addQuery(UPDATE, sql, t2 - t1);
860                     break;
861                 case INSERT:
862                     addQuery(INSERT, sql, t2 - t1);
863                     break;
864                 case DELETE:
865                     addQuery(DELETE, sql, t2 - t1);
866                     break;
867             }
868             return result;
869         }
870
871         public ResultSet executeQuery(String _sql) throws SQLException {
872             long t1 = System.currentTimeMillis();
873             ResultSet result = pstmt.executeQuery(_sql);
874             long t2 = System.currentTimeMillis();
875
876             // determine the type of query
877 String sqlL = _sql.toLowerCase().trim();
878
879             if (sqlL.startsWith("insert")) {
880                 addQuery(INSERT, _sql, t2 - t1);
881             }
882             else if (sqlL.startsWith("update")) {
883                 addQuery(UPDATE, _sql, t2 - t1);
884             }
885             else if (sqlL.startsWith("delete")) {
886                 addQuery(DELETE, _sql, t2 - t1);
887             }
888             else {
889                 addQuery(SELECT, _sql, t2 - t1);
890             }
891             return result;
892         }
893
894         public int executeUpdate(String _sql) throws SQLException {
895
896             long t1 = System.currentTimeMillis();
897             int result = pstmt.executeUpdate(_sql);
898             long t2 = System.currentTimeMillis();
899
900             // determine the type of query
901 String sqlL = _sql.toLowerCase().trim();
902
903             if (sqlL.startsWith("insert")) {
904                 addQuery(INSERT, _sql, t2 - t1);
905             }
906             else if (sqlL.startsWith("update")) {
907                 addQuery(UPDATE, _sql, t2 - t1);
908             }
909             else if (sqlL.startsWith("delete")) {
910                 addQuery(DELETE, _sql, t2 - t1);
911             }
912             else {
913                 addQuery(SELECT, _sql, t2 - t1);
914             }
915             return result;
916         }
917     }
918
919     /**
920      * An implementation of the CallableStatement interface that wraps an
921      * underlying CallableStatement object and performs timings of the database
922      * queries.
923      */
924     class TimedCallableStatement extends CallableStatementWrapper {
925
926         private String sql;
927         private int type = SELECT;
928
929         public TimedCallableStatement(CallableStatement cstmt, String sql) {
930             super(cstmt);
931             this.sql = sql;
932
933             // determine the type of query
934 String sqlL = sql.toLowerCase().trim();
935
936             if (sqlL.startsWith("insert")) {
937                 type = INSERT;
938             }
939             else if (sqlL.startsWith("update")) {
940                 type = UPDATE;
941             }
942             else if (sqlL.startsWith("delete")) {
943                 type = DELETE;
944             }
945             else {
946                 type = SELECT;
947             }
948         }
949
950         public boolean execute() throws SQLException {
951             // Perform timing of this method.
952 long t1 = System.currentTimeMillis();
953             boolean result = cstmt.execute();
954             long t2 = System.currentTimeMillis();
955
956             switch (type) {
957                 case SELECT:
958                     addQuery(SELECT, sql, t2 - t1);
959                     break;
960                 case UPDATE:
961                     addQuery(UPDATE, sql, t2 - t1);
962                     break;
963                 case INSERT:
964                     addQuery(INSERT, sql, t2 - t1);
965                     break;
966                 case DELETE:
967                     addQuery(DELETE, sql, t2 - t1);
968                     break;
969             }
970             return result;
971         }
972
973         /*
974          * This is one of the methods that we wish to time
975          */
976         public ResultSet executeQuery() throws SQLException {
977
978             long t1 = System.currentTimeMillis();
979             ResultSet result = cstmt.executeQuery();
980             long t2 = System.currentTimeMillis();
981
982             switch (type) {
983                 case SELECT:
984                     addQuery(SELECT, sql, t2 - t1);
985                     break;
986                 case UPDATE:
987                     addQuery(UPDATE, sql, t2 - t1);
988                     break;
989                 case INSERT:
990                     addQuery(INSERT, sql, t2 - t1);
991                     break;
992                 case DELETE:
993                     addQuery(DELETE, sql, t2 - t1);
994                     break;
995             }
996             return result;
997         }
998
999         /*
1000         * This is one of the methods that we wish to time
1001         */
1002        public int executeUpdate() throws SQLException {
1003
1004            long t1 = System.currentTimeMillis();
1005            int result = cstmt.executeUpdate();
1006            long t2 = System.currentTimeMillis();
1007
1008            switch (type) {
1009                case SELECT:
1010                    addQuery(SELECT, sql, t2 - t1);
1011                    break;
1012                case UPDATE:
1013                    addQuery(UPDATE, sql, t2 - t1);
1014                    break;
1015                case INSERT:
1016                    addQuery(INSERT, sql, t2 - t1);
1017                    break;
1018                case DELETE:
1019                    addQuery(DELETE, sql, t2 - t1);
1020                    break;
1021            }
1022            return result;
1023        }
1024
1025        // The following methods are from the Statement class - the
1026 // SuperInterface of PreparedStatement
1027 // without these this class won't compile
1028
1029        public boolean execute(String _sql) throws SQLException {
1030
1031            long t1 = System.currentTimeMillis();
1032            boolean result = cstmt.execute(_sql);
1033            long t2 = System.currentTimeMillis();
1034
1035            // determine the type of query
1036 String sqlL = _sql.toLowerCase().trim();
1037
1038            if (sqlL.startsWith("insert")) {
1039                addQuery(INSERT, _sql, t2 - t1);
1040            }
1041            else if (sqlL.startsWith("update")) {
1042                addQuery(UPDATE, _sql, t2 - t1);
1043            }
1044            else if (sqlL.startsWith("delete")) {
1045                addQuery(DELETE, _sql, t2 - t1);
1046            }
1047            else {
1048                addQuery(SELECT, _sql, t2 - t1);
1049            }
1050            return result;
1051        }
1052
1053        public int[] executeBatch() throws SQLException {
1054
1055            long t1 = System.currentTimeMillis();
1056            int[] result = cstmt.executeBatch();
1057            long t2 = System.currentTimeMillis();
1058
1059            switch (type) {
1060                case SELECT:
1061                    addQuery(SELECT, sql, t2 - t1);
1062                    break;
1063                case UPDATE:
1064                    addQuery(UPDATE, sql, t2 - t1);
1065                    break;
1066                case INSERT:
1067                    addQuery(INSERT, sql, t2 - t1);
1068                    break;
1069                case DELETE:
1070                    addQuery(DELETE, sql, t2 - t1);
1071                    break;
1072            }
1073            return result;
1074        }
1075
1076        public ResultSet executeQuery(String _sql) throws SQLException {
1077            long t1 = System.currentTimeMillis();
1078            ResultSet result = cstmt.executeQuery(_sql);
1079            long t2 = System.currentTimeMillis();
1080
1081            // determine the type of query
1082 String sqlL = _sql.toLowerCase().trim();
1083
1084            if (sqlL.startsWith("insert")) {
1085                addQuery(INSERT, _sql, t2 - t1);
1086            }
1087            else if (sqlL.startsWith("update")) {
1088                addQuery(UPDATE, _sql, t2 - t1);
1089            }
1090            else if (sqlL.startsWith("delete")) {
1091                addQuery(DELETE, _sql, t2 - t1);
1092            }
1093            else {
1094                addQuery(SELECT, _sql, t2 - t1);
1095            }
1096            return result;
1097        }
1098
1099        public int executeUpdate(String _sql) throws SQLException {
1100
1101            long t1 = System.currentTimeMillis();
1102            int result = cstmt.executeUpdate(_sql);
1103            long t2 = System.currentTimeMillis();
1104
1105            // determine the type of query
1106 String sqlL = _sql.toLowerCase().trim();
1107
1108            if (sqlL.startsWith("insert")) {
1109                addQuery(INSERT, _sql, t2 - t1);
1110            }
1111            else if (sqlL.startsWith("update")) {
1112                addQuery(UPDATE, _sql, t2 - t1);
1113            }
1114            else if (sqlL.startsWith("delete")) {
1115                addQuery(DELETE, _sql, t2 - t1);
1116            }
1117            else {
1118                addQuery(SELECT, _sql, t2 - t1);
1119            }
1120            return result;
1121        }
1122    }
1123}
1124

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags