View Javadoc

1   /*
2    * $Id: AbstractBaseStorage.java,v 1.1 2006/03/06 11:30:53 azzazzel Exp $
3    *
4    * Copyright 2006 Commsen International
5    * 
6    * Licensed under the Common Public License, Version 1.0 (the "License");
7    * you may not use this file except in compliance with the License.
8    * You may obtain a copy of the License at
9    * 
10   *      http://www.opensource.org/licenses/cpl1.0.txt
11   * 
12   * THE PROGRAM IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, 
13   * EITHER EXPRESS OR IMPLIED INCLUDING, WITHOUT LIMITATION, ANY WARRANTIES OR CONDITIONS 
14   * OF TITLE, NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.
15   *
16   */
17  package com.commsen.stopwatch.storages;
18  
19  import java.sql.Connection;
20  import java.sql.DriverManager;
21  import java.sql.PreparedStatement;
22  import java.sql.ResultSet;
23  import java.sql.SQLException;
24  import java.sql.Statement;
25  import java.sql.Timestamp;
26  import java.util.ArrayList;
27  import java.util.Arrays;
28  import java.util.Calendar;
29  import java.util.Date;
30  import java.util.Properties;
31  
32  import org.apache.log4j.Logger;
33  
34  import com.commsen.stopwatch.Report;
35  import com.commsen.stopwatch.StopwatchStorage;
36  import com.commsen.stopwatch.StopwatchStorageException;
37  import com.commsen.stopwatch.reports.DefaultStopwatchReport;
38  
39  /***
40   * Abstract class representig database storage. It has predefined methods to crete table(s) as well
41   * as insert, update and delete records. The default implementation is limited to the very basic
42   * measurment data - time and count. If any other info is to be stored, the extending class should
43   * overwrite appropriate <code>getXxxxQuery()</code> method(s)
44   * 
45   * @author Milen Dyankov
46   * 
47   */
48  public abstract class AbstractDatabaseStorage implements StopwatchStorage {
49  
50  	protected Properties properties;
51  
52  
53  	/***
54  	 * Called to obtain the JDBC driver to use
55  	 * 
56  	 * @return the fully qualified class name
57  	 */
58  	protected abstract String getDriver();
59  
60  
61  	/***
62  	 * Called to obtain the connection string
63  	 * 
64  	 * @return the connection string
65  	 */
66  	protected abstract String getConnectionString();
67  
68  
69  	/***
70  	 * Called to obtain the database user name
71  	 * 
72  	 * @return database user name
73  	 */
74  	protected abstract String getUser();
75  
76  
77  	/***
78  	 * Called to obtain the database password
79  	 * 
80  	 * @return database password
81  	 */
82  	protected abstract String getPassword();
83  
84  
85  	/***
86  	 * Provides the table name
87  	 * 
88  	 * @return table name
89  	 */
90  	protected abstract String getTableName();
91  
92  
93  	/***
94  	 * Provides SQL query which will be executed to check if table exist
95  	 * <p>
96  	 * Default is: <code>select 1 from {@link #getTableName()}</code>
97  	 * 
98  	 * @return SQL query to check if table exist
99  	 */
100 	protected String getCheckTableQuery() {
101 		return "select 1 from " + getTableName();
102 	}
103 
104 
105 	/***
106 	 * Called to obtain the query to be executed when table needs to be created.
107 	 * <p>
108 	 * Default is: <code><pre>
109 	 * create table {@link #getTableName()} (
110 	 * 	_id INT GENERATED BY DEFAULT AS IDENTITY,
111 	 * 	_group VARCHAR,
112 	 * 	_label VARCHAR,
113 	 * 	_start TIMESTAMP,
114 	 * 	_end TIMESTAMP
115 	 * )
116 	 * </pre></code>
117 	 * 
118 	 * @return SQL query to create table
119 	 */
120 	protected String getCreateTableQuery() {
121 		return " create table " + getTableName() + " (" + "   _id INT GENERATED BY DEFAULT AS IDENTITY," + "   _group VARCHAR," + "   _label VARCHAR," + "   _start TIMESTAMP," + "   _end TIMESTAMP"
122 		        + ")";
123 	}
124 
125 
126 	/***
127 	 * Called to obtain the query to be executed when table needs to be truncated
128 	 * <p>
129 	 * Default is: <code>delete from {@link #getTableName()}</code>
130 	 * 
131 	 * @return SQL query to truncate table
132 	 */
133 	protected String getTruncTableQuery() {
134 		return "delete from " + getTableName();
135 	}
136 
137 
138 	/***
139 	 * Called to obtain the columns to be returned. The columns returned by this method are used in
140 	 * all report queries.
141 	 * 
142 	 * @return part of SQL query
143 	 */
144 	protected abstract String getReturnColumns();
145 
146 
147 	/***
148 	 * The group by clause used in all report queries Default is: <code>_group, _label</code>
149 	 * 
150 	 * @return part of SQL query
151 	 */
152 	protected String getGroupBy() {
153 		return "_group, _label";
154 	}
155 
156 
157 	/***
158 	 * The order by clause used in all report queries Default is: <code>_group, _label</code>
159 	 * 
160 	 * @return part of SQL query
161 	 */
162 	protected String getOrderBy() {
163 		return "_group, _label";
164 	}
165 
166 
167 	/***
168 	 * Called to obtain the query to be executed when report about all groups and labels is to be
169 	 * generated
170 	 * <p>
171 	 * Default is: <code><pre>
172 	 * select {@link #getReturnColumns()} 
173 	 * from {@link #getTableName()} 
174 	 * where _end is not null
175 	 * group by {@link #getGroupBy()}
176 	 * order by {@link #getOrderBy()}
177 	 * )
178 	 * </pre></code>
179 	 * 
180 	 * @return SQL query used to generate report
181 	 */
182 	protected String getAllReportQuery() {
183 		return " select _group, _label, " + getReturnColumns() + " from " + getTableName() + " main " + " where _end is not null " + " group by " + getGroupBy() + " order by " + getOrderBy();
184 	}
185 
186 
187 	/***
188 	 * Called to obtain the query to be executed when summary report about all groups is to be
189 	 * generated
190 	 * <p>
191 	 * Default is: <code><pre>
192 	 * select {@link #getReturnColumns()} 
193 	 * from {@link #getTableName()} 
194 	 * where _end is not null
195 	 * group by _group
196 	 * order by _group
197 	 * )
198 	 * </pre></code>
199 	 * 
200 	 * @return SQL query used to generate report
201 	 */
202 	protected String getAllByGroupReportQuery() {
203 		return " select _group, '', " + getReturnColumns() + " from " + getTableName() + " main " + " where _end is not null " + " group by _group" + " order by _group";
204 	}
205 
206 
207 	/***
208 	 * Called to obtain the query to be executed when summary report about all labels is to be
209 	 * generated
210 	 * <p>
211 	 * Default is: <code><pre>
212 	 * select {@link #getReturnColumns()} 
213 	 * from {@link #getTableName()} 
214 	 * where _end is not null
215 	 * group by _label
216 	 * order by _label
217 	 * )
218 	 * </pre></code>
219 	 * 
220 	 * @return SQL query used to generate report
221 	 */
222 	protected String getAllByLabelReportQuery() {
223 		return " select '', _label, " + getReturnColumns() + " from " + getTableName() + " main " + " where _end is not null " + " group by _label" + " order by _label";
224 	}
225 
226 
227 	/***
228 	 * Called to obtain the query to be executed when report about given group and label is to be
229 	 * generated
230 	 * <p>
231 	 * Default is: <code><pre>
232 	 * select {@link #getReturnColumns()} 
233 	 * from {@link #getTableName()} 
234 	 * where _end is not null and _group = ? and _label = ? 
235 	 * group by {@link #getGroupBy()}
236 	 * order by {@link #getOrderBy()}
237 	 * )
238 	 * </pre></code>
239 	 * 
240 	 * @return SQL query used to generate report
241 	 */
242 	protected String getSingleReportQuery() {
243 		return " select _group, _label, " + getReturnColumns() + " from " + getTableName() + " main " + " where _end is not null and _group = ? and _label = ? " + " group by " + getGroupBy()
244 		        + " order by " + getOrderBy();
245 	}
246 
247 
248 	/***
249 	 * Called to obtain the query to be executed when report about given group and label is to be
250 	 * generated
251 	 * <p>
252 	 * Default is: <code><pre>
253 	 * select {@link #getReturnColumns()} 
254 	 * from {@link #getTableName()} 
255 	 * where _end is not null and _label = ? 
256 	 * group by {@link #getGroupBy()}
257 	 * order by {@link #getOrderBy()}
258 	 * )
259 	 * </pre></code>
260 	 * 
261 	 * @return SQL query used to generate report
262 	 */
263 	protected String getLabelReportQuery() {
264 		return " select _group, _label, " + getReturnColumns() + " from " + getTableName() + " main " + " where _end is not null and _label = ? " + " group by " + getGroupBy() + " order by "
265 		        + getOrderBy();
266 	}
267 
268 
269 	/***
270 	 * Called to obtain the query to be executed when report about given group and label is to be
271 	 * generated
272 	 * <p>
273 	 * Default is: <code><pre>
274 	 * select {@link #getReturnColumns()} 
275 	 * from {@link #getTableName()} 
276 	 * where _end is not null and _group = ? 
277 	 * group by {@link #getGroupBy()}
278 	 * order by {@link #getOrderBy()}
279 	 * )
280 	 * </pre></code>
281 	 * 
282 	 * @return SQL query used to generate report
283 	 */
284 	protected String getGroupReportQuery() {
285 		return " select _group, _label, " + getReturnColumns() + " from " + getTableName() + " main " + " where _end is not null and _group = ? " + " group by " + getGroupBy() + " order by "
286 		        + getOrderBy();
287 	}
288 
289 
290 	public String getInsertQuery() {
291 		return "insert into " + getTableName() + " (_group, _label, _start) values (?, ?, ?)";
292 	}
293 
294 
295 	protected String getUpdateQuery() {
296 		return "update " + getTableName() + " set _end = ? where _id = ? and _end IS NULL";
297 	}
298 
299 
300 	protected String getDeleteQuery() {
301 		return "delete from " + getTableName() + " where _id = ?";
302 	}
303 
304 
305 	protected abstract String getLastIdentityQuery();
306 
307 
308 	protected String getLoadQuery() {
309 		return "select count(1) from " + getTableName() + " where (_start < ? and _end > ?) or (_start > ? and _start < ?) or (_end > ? and _end < ?)";
310 	}
311 
312 
313 	protected String getGroupLoadQuery() {
314 		return "select count(1) from " + getTableName() + " where _group=? and ((_start < ? and _end > ?) or (_start > ? and _start < ?) or (_end > ? and _end < ?))";
315 	}
316 
317 
318 	protected String getLabelLoadQuery() {
319 		return "select count(1) from " + getTableName() + " where _label=? and ((_start < ? and _end > ?) or (_start > ? and _start < ?) or (_end > ? and _end < ?))";
320 	}
321 
322 
323 	protected String getGroupLabelLoadQuery() {
324 		return "select count(1) from " + getTableName() + " where _group=? and _label=? and ((_start < ? and _end > ?) or (_start > ? and _start < ?) or (_end > ? and _end < ?))";
325 	}
326 
327 	/***
328 	 * Prepared statement initialized in {@link #open()} method with query returned by
329 	 * {@link #getInsertQuery()}
330 	 */
331 	protected PreparedStatement insertPreparedStatement;
332 
333 	/***
334 	 * Prepared statement initialized in {@link #open()} method with query returned by
335 	 * {@link #getUpdateQuery()}
336 	 */
337 	protected PreparedStatement updatePreparedStatement;
338 
339 	/***
340 	 * Prepared statement initialized in {@link #open()} method with query returned by
341 	 * {@link #getDeleteQuery()}
342 	 */
343 	protected PreparedStatement deletePreparedStatement;
344 
345 	/***
346 	 * Prepared statement initialized in {@link #open()} method with query returned by
347 	 * {@link #getLastIdentityQuery()}
348 	 */
349 	protected PreparedStatement lastIdentityStatement;
350 
351 	/***
352 	 * Prepared statement initialized in {@link #open()} method with query returned by
353 	 * {@link #getAllReportQuery()}
354 	 */
355 	protected PreparedStatement allReportStatement;
356 
357 	/***
358 	 * Prepared statement initialized in {@link #open()} method with query returned by
359 	 * {@link #getAllByGroupReportQuery()}
360 	 */
361 	protected PreparedStatement allByGroupReportStatement;
362 
363 	/***
364 	 * Prepared statement initialized in {@link #open()} method with query returned by
365 	 * {@link #getAllByLabelReportQuery()}
366 	 */
367 	protected PreparedStatement allByLabelReportStatement;
368 
369 	/***
370 	 * Prepared statement initialized in {@link #open()} method with query returned by
371 	 * {@link #getGroupReportQuery()}
372 	 */
373 	protected PreparedStatement groupReportStatement;
374 
375 	/***
376 	 * Prepared statement initialized in {@link #open()} method with query returned by
377 	 * {@link #getLabelReportQuery()}
378 	 */
379 	protected PreparedStatement labelReportStatement;
380 
381 	/***
382 	 * Prepared statement initialized in {@link #open()} method with query returned by
383 	 * {@link #getSingleReportQuery()}
384 	 */
385 	protected PreparedStatement singleReportStatement;
386 
387 	/***
388 	 * 
389 	 */
390 	protected PreparedStatement groupLabelLoadStatement;
391 
392 	/***
393 	 * 
394 	 */
395 	protected PreparedStatement groupLoadStatement;
396 
397 	/***
398 	 * 
399 	 */
400 	protected PreparedStatement labelLoadStatement;
401 
402 	/***
403 	 * 
404 	 */
405 	protected PreparedStatement loadStatement;
406 
407 	/***
408 	 * Detabase connection used to execute all update queries
409 	 */
410 	Connection updateConnection;
411 
412 	/***
413 	 * Detabase connection used to execute all select queries
414 	 */
415 	Connection selectConnection;
416 
417 
418 	/***
419 	 * 
420 	 * @see com.commsen.stopwatch.StopwatchStorage#open()
421 	 */
422 	public void open() throws StopwatchStorageException {
423 		try {
424 			Class.forName(getDriver());
425 		} catch (ClassNotFoundException e) {
426 			throw new StopwatchStorageException("failed to load " + getDriver() + " driver.", e);
427 		}
428 
429 		try {
430 			if (isDebug()) getLogger().debug("Connecting to database ... ");
431 
432 			updateConnection = DriverManager.getConnection(getConnectionString(), getUser(), getPassword());
433 			selectConnection = DriverManager.getConnection(getConnectionString(), getUser(), getPassword());
434 
435 			if (isDebug()) getLogger().debug("connection sucsessful. Checking tables ... ");
436 
437 		} catch (SQLException e) {
438 			throw new StopwatchStorageException("database connection error", e);
439 		}
440 
441 		// check if table exists
442 		Statement statement = null;
443 		try {
444 			statement = selectConnection.createStatement();
445 			statement.execute(getCheckTableQuery());
446 			if (getTruncTableQuery() != null && !"".equals(getTruncTableQuery().trim())) {
447 				try {
448 					statement = updateConnection.createStatement();
449 					statement.executeUpdate(getTruncTableQuery());
450 					statement.close();
451 				} catch (SQLException e) {
452 					throw new StopwatchStorageException("Can not truncate table", e);
453 				}
454 			}
455 			if (isDebug()) getLogger().debug("table(s) exists. Engine will now attempt to create prepared statements ... ");
456 		} catch (SQLException e) {
457 			try {
458 				if (statement != null) statement.close();
459 				statement = updateConnection.createStatement();
460 				statement.execute(getCreateTableQuery());
461 				if (isDebug()) getLogger().debug("table(s) created. Engine will now attempt to create prepared statements ... ");
462 			} catch (SQLException e1) {
463 				throw new StopwatchStorageException("Can not create table(s)", e1);
464 			}
465 		} finally {
466 			if (statement != null) {
467 				try {
468 					statement.close();
469 				} catch (SQLException e) {
470 					throw new StopwatchStorageException("database error", e);
471 				}
472 			}
473 		}
474 
475 		try {
476 			insertPreparedStatement = updateConnection.prepareStatement(getInsertQuery());
477 			updatePreparedStatement = updateConnection.prepareStatement(getUpdateQuery());
478 			deletePreparedStatement = updateConnection.prepareStatement(getDeleteQuery());
479 			lastIdentityStatement = updateConnection.prepareStatement(getLastIdentityQuery());
480 
481 			allReportStatement = selectConnection.prepareStatement(getAllReportQuery());
482 			allByGroupReportStatement = selectConnection.prepareStatement(getAllByGroupReportQuery());
483 			allByLabelReportStatement = selectConnection.prepareStatement(getAllByLabelReportQuery());
484 			groupReportStatement = selectConnection.prepareStatement(getGroupReportQuery());
485 			labelReportStatement = selectConnection.prepareStatement(getLabelReportQuery());
486 			singleReportStatement = selectConnection.prepareStatement(getSingleReportQuery());
487 
488 			loadStatement = selectConnection.prepareStatement(getLoadQuery());
489 			groupLoadStatement = selectConnection.prepareStatement(getGroupLoadQuery());
490 			labelLoadStatement = selectConnection.prepareStatement(getLabelLoadQuery());
491 			groupLabelLoadStatement = selectConnection.prepareStatement(getGroupLabelLoadQuery());
492 
493 			if (isDebug()) getLogger().debug("Prepared statements created!");
494 		} catch (SQLException e) {
495 			throw new StopwatchStorageException("can not create prepared statements", e);
496 		}
497 	}
498 
499 
500 	/***
501 	 * 
502 	 * @see com.commsen.stopwatch.StopwatchStorage#freeze()
503 	 */
504 	public void freeze() throws StopwatchStorageException {
505 		// by default do nothing on freeze
506 	}
507 
508 
509 	/***
510 	 * 
511 	 * @see com.commsen.stopwatch.StopwatchStorage#unfreeze()
512 	 */
513 	public void unfreeze() throws StopwatchStorageException {
514 		// by default do nothing on unfreeze
515 	}
516 
517 
518 	/***
519 	 * 
520 	 * @see com.commsen.stopwatch.StopwatchStorage#close()
521 	 */
522 	public void close() throws StopwatchStorageException {
523 		try {
524 			updateConnection.close();
525 			updateConnection = null;
526 			selectConnection.close();
527 			selectConnection = null;
528 			if (isDebug()) getLogger().debug("Database shut down !!!");
529 		} catch (SQLException e) {
530 			throw new StopwatchStorageException("database error", e);
531 		}
532 	}
533 
534 
535 	/***
536 	 * 
537 	 * @see com.commsen.stopwatch.StopwatchStorage#newRecord(java.lang.Object[])
538 	 */
539 	public long newRecord(Object[] parameters) throws StopwatchStorageException {
540 
541 		if (insertPreparedStatement == null) return -1;
542 		try {
543 			synchronized (insertPreparedStatement.getConnection()) {
544 				insertPreparedStatement.setString(1, (String) parameters[0]);
545 				insertPreparedStatement.setString(2, (String) parameters[1]);
546 				insertPreparedStatement.setTimestamp(3, new Timestamp(((Long) parameters[2]).longValue()));
547 				insertPreparedStatement.executeUpdate();
548 				ResultSet resultSet = lastIdentityStatement.executeQuery();
549 				resultSet.next();
550 				long result = resultSet.getLong(1);
551 				resultSet.close();
552 				return result;
553 			}
554 		} catch (SQLException e) {
555 			throw new StopwatchStorageException("database error", e);
556 		}
557 	}
558 
559 
560 	/***
561 	 * 
562 	 * @see com.commsen.stopwatch.StopwatchStorage#newRecord(java.lang.Object[])
563 	 */
564 	public long newCompleteRecord(Object[] startParameters, Object[] endParameters) throws StopwatchStorageException {
565 		long id = newRecord(startParameters);
566 		completeRecord(id, endParameters);
567 		return id;
568 	}
569 
570 
571 	/***
572 	 * 
573 	 * @see com.commsen.stopwatch.StopwatchStorage#removeRecord(long)
574 	 */
575 	public boolean removeRecord(long id) throws StopwatchStorageException {
576 		if (id < 0) return false;
577 		try {
578 			synchronized (deletePreparedStatement.getConnection()) {
579 				deletePreparedStatement.setLong(1, id);
580 				deletePreparedStatement.executeUpdate();
581 				return true;
582 			}
583 		} catch (SQLException e) {
584 			throw new StopwatchStorageException("database error", e);
585 		}
586 	}
587 
588 
589 	/***
590 	 * 
591 	 * @see com.commsen.stopwatch.StopwatchStorage#completeRecord(long, Object[])
592 	 */
593 	public boolean completeRecord(long id, Object[] parameters) throws StopwatchStorageException {
594 		if (id < 0) return false;
595 		try {
596 			synchronized (updatePreparedStatement.getConnection()) {
597 				updatePreparedStatement.setTimestamp(1, new Timestamp(((Long) parameters[0]).longValue()));
598 				updatePreparedStatement.setLong(2, id);
599 				updatePreparedStatement.executeUpdate();
600 				return true;
601 			}
602 		} catch (SQLException e) {
603 			throw new StopwatchStorageException("database error", e);
604 		}
605 
606 	}
607 
608 
609 	/***
610 	 * This method simply calls {@link #prepareReports(PreparedStatement, Object[])} passing
611 	 * {@link #allReportStatement} as statement and no parameters (<code>null</code> value).
612 	 * 
613 	 * @see StopwatchStorage#getReports()
614 	 */
615 	public Report[] getReports() {
616 		try {
617 			return prepareReports(allReportStatement, null);
618 		} catch (SQLException e) {
619 			getLogger().error("database error!", e);
620 		}
621 		return null;
622 	}
623 
624 
625 	/***
626 	 * This method simply calls {@link #prepareReports(PreparedStatement, Object[])} passing
627 	 * {@link #allByGroupReportStatement} as statement and no parameters (<code>null</code>
628 	 * value).
629 	 * 
630 	 * @see StopwatchStorage#getReports()
631 	 */
632 	public Report[] getAllByGroupReports() {
633 		try {
634 			return prepareReports(allByGroupReportStatement, null);
635 		} catch (SQLException e) {
636 			getLogger().error("database error!", e);
637 		}
638 		return null;
639 	}
640 
641 
642 	/***
643 	 * This method simply calls {@link #prepareReports(PreparedStatement, Object[])} passing
644 	 * {@link #allByLabelReportStatement} as statement and no parameters (<code>null</code>
645 	 * value).
646 	 * 
647 	 * @see StopwatchStorage#getReports()
648 	 */
649 	public Report[] getAllByLabelReports() {
650 		try {
651 			return prepareReports(allByLabelReportStatement, null);
652 		} catch (SQLException e) {
653 			getLogger().error("database error!", e);
654 		}
655 		return null;
656 	}
657 
658 
659 	/***
660 	 * This method simply calls {@link #prepareReports(PreparedStatement, Object[])} passing
661 	 * {@link #singleReportStatement} as statement and <code>group</code> and <code>label</code>
662 	 * as parameters.
663 	 * 
664 	 * @see StopwatchStorage#getReport(java.lang.String, java.lang.String)
665 	 */
666 	public Report getReport(String group, String label) {
667 		Report[] reports = null;
668 		try {
669 			reports = prepareReports(singleReportStatement, new Object[] { group, label });
670 		} catch (SQLException e) {
671 			getLogger().error("database error!", e);
672 		}
673 
674 		if (reports != null && reports.length > 0) {
675 			return reports[0];
676 		}
677 
678 		return null;
679 	}
680 
681 
682 	/***
683 	 * This method simply calls {@link #prepareReports(PreparedStatement, Object[])} passing
684 	 * {@link #groupReportStatement} as statement and <code>group</code> as parameter.
685 	 * 
686 	 * @see StopwatchStorage#getGroupReports(java.lang.String)
687 	 */
688 	public Report[] getGroupReports(String group) {
689 		try {
690 			return prepareReports(groupReportStatement, new Object[] { group });
691 		} catch (SQLException e) {
692 			getLogger().error("database error!", e);
693 		}
694 		return null;
695 	}
696 
697 
698 	/***
699 	 * This method simply calls {@link #prepareReports(PreparedStatement, Object[])} passing
700 	 * {@link #labelReportStatement} as statement and <code>label</code> as parameter.
701 	 * 
702 	 * @see StopwatchStorage#getLabelReports(java.lang.String)
703 	 */
704 	public Report[] getLabelReports(String label) {
705 		try {
706 			return prepareReports(labelReportStatement, new Object[] { label });
707 		} catch (SQLException e) {
708 			getLogger().error("database error!", e);
709 		}
710 		return null;
711 	}
712 
713 
714 	/***
715 	 * This method simply executes given <code>statement</code> with given <code>params</code>.
716 	 * All report queries call this method to obtain array of reports. This method should be
717 	 * overwriten by extending classes that need to provide more measurment information then basic
718 	 * time and count.
719 	 * 
720 	 * @param statement the prepared statement to execute
721 	 * @param params the params to passed to the prepared statement
722 	 * @return array of {@link Report}
723 	 * @throws SQLException on database connection error or other errors
724 	 */
725 	protected Report[] prepareReports(PreparedStatement statement, Object[] params) throws SQLException {
726 
727 		if (statement == null) return new Report[0];
728 		ArrayList list = new ArrayList();
729 		synchronized (statement.getConnection()) {
730 
731 			if (params != null && params.length > 0) {
732 				for (int i = 0; i < params.length; i++) {
733 					statement.setObject(i + 1, params[i]);
734 				}
735 			}
736 
737 			ResultSet resultSet = statement.executeQuery();
738 			while (resultSet.next()) {
739 				String group = resultSet.getString(1);
740 				String name = resultSet.getString(2);
741 				long count = resultSet.getLong(3);
742 				double min = resultSet.getDouble(4);
743 				double max = resultSet.getDouble(5);
744 				double total = resultSet.getDouble(7);
745 				// double avg = resultSet.getDouble(6);
746 				double avg = total / count;
747 
748 				list.add(new DefaultStopwatchReport(group, name, count, min, max, avg, total));
749 			}
750 			resultSet.close();
751 		}
752 		return (Report[]) list.toArray(new Report[list.size()]);
753 	}
754 
755 
756 	/***
757 	 * @see com.commsen.stopwatch.StopwatchStorage#getLoad(int, int)
758 	 */
759 	public long[] getLoad(String group, String label, int field, int value) {
760 
761 		ArrayList paramsArray = new ArrayList();
762 		PreparedStatement statement;
763 		if (group == null && label == null) {
764 			statement = loadStatement;
765 		} else if (group == null) {
766 			statement = labelLoadStatement;
767 			paramsArray.add(label);
768 		} else if (label == null) {
769 			statement = groupLoadStatement;
770 			paramsArray.add(group);
771 		} else {
772 			statement = groupLabelLoadStatement;
773 			paramsArray.add(group);
774 			paramsArray.add(label);
775 
776 		}
777 
778 		long[] result = new long[value];
779 		Arrays.fill(result, 0);
780 		Calendar calendarEnd = Calendar.getInstance();
781 		Calendar calendarStart = Calendar.getInstance();
782 		calendarStart.add(field, -1);
783 		try {
784 			for (int i = 0; i < value; i++) {
785 				ArrayList params = new ArrayList(paramsArray);
786 				params.add(calendarStart.getTime());
787 				params.add(calendarEnd.getTime());
788 				params.add(calendarStart.getTime());
789 				params.add(calendarEnd.getTime());
790 				params.add(calendarStart.getTime());
791 				params.add(calendarEnd.getTime());
792 				synchronized (statement.getConnection()) {
793 					for (int index = 0; index < params.size(); index++) {
794 						Object o = params.get(index);
795 						if (o instanceof Date) {
796 							statement.setTimestamp(index + 1, new Timestamp(((Date) o).getTime()));
797 						} else {
798 							statement.setObject(index + 1, o);
799 						}
800 					}
801 					ResultSet rs = statement.executeQuery();
802 
803 					if (rs.next() == true) result[value - i - 1] = rs.getLong(1);
804 				}
805 
806 				calendarEnd.add(field, -1);
807 				calendarStart.add(field, -1);
808 
809 			}
810 		} catch (SQLException e) {
811 			getLogger().error("database error!", e);
812 		}
813 
814 		return result;
815 	}
816 
817 
818 	/***
819 	 * Returns the logger for this class
820 	 * 
821 	 * @return the logger for this class
822 	 */
823 	protected abstract Logger getLogger();
824 
825 
826 	/***
827 	 * Checks if debug log level is enabled in both Stopwatch and Log4j.
828 	 * 
829 	 * @return <code>true</code> if debug log level is enabled, <code>false</code> otherwise.
830 	 */
831 	protected boolean isDebug() {
832 		return isDebugEnabled() && getLogger().isDebugEnabled();
833 	}
834 
835 
836 	/***
837 	 * @return Returns the debugEnabled.
838 	 * @see com.commsen.stopwatch.StopwatchEngine#setDebugEnabled(boolean)
839 	 */
840 	public abstract boolean isDebugEnabled();
841 
842 
843 	/***
844 	 * {@inheritDoc}
845 	 */
846 	public Properties getProperties() {
847 		return properties;
848 	}
849 
850 
851 	/***
852 	 * {@inheritDoc}
853 	 */
854 	public void setProperties(Properties properties) {
855 		this.properties = properties;
856 	}
857 
858 }