1 /***
2 * The contents of this file are subject to the Mozilla Public
3 * License Version 1.1 (the "License"); you may not use this file
4 * except in compliance with the License. You may obtain a copy of
5 * the License at http://www.mozilla.org/MPL/
6 *
7 * Software distributed under the License is distributed on an "AS
8 * IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
9 * implied. See the License for the specific language governing
10 * rights and limitations under the License.
11 *
12 * The Original Code is pow2toolkit library.
13 *
14 * The Initial Owner of the Original Code is
15 * Power Of Two S.R.L. (www.pow2.com)
16 *
17 * Portions created by Power Of Two S.R.L. are
18 * Copyright (C) Power Of Two S.R.L.
19 * All Rights Reserved.
20 *
21 * Contributor(s):
22 */
23
24 package com.pow2.dao;
25
26 import java.sql.*;
27 import java.util.*;
28
29 import org.apache.commons.beanutils.*;
30 import org.apache.log4j.*;
31
32 import com.pow2.resources.*;
33
34
35
36 /***
37 * Abstract Data access object.
38 *
39 * @author Luca Fossato
40 * @version $Id: AbstractDAO.java,v 1.1.1.1 2004/08/31 20:22:46 foxat Exp $
41 */
42 public abstract class AbstractDAO
43 {
44 /*** Log4J category. */
45 protected static Category cat = Category.getInstance(AbstractDAO.class);
46
47 /*** preferences instance */
48 private static Prefs prefs = Prefs.instance();
49
50 /*** connectionFactory instance */
51 private static ConnectionFactory connectionFactory = ConnectionFactory.instance();
52
53
54 /***
55 * Protected default constructor.
56 */
57 protected AbstractDAO()
58 {
59 }
60
61
62 /***
63 * Get the debug status.
64 *
65 * @return the debug status.
66 * True means that all the logging requests of priority DEBUG are enabled;
67 * false means that that logging requests are are disabled
68 */
69 public boolean isDebugEnabled()
70 {
71 return cat.isDebugEnabled();
72 }
73
74
75 /***
76 * Apply the input condition on the input table.
77 *
78 * @param tableName the name of the table where to apply the input condition
79 * @param condition the where condition string
80 * @return true if the input condition retrieves at least one record
81 * from the input table;
82 * false otherwise
83 * @exception Exception if any error occurs
84 */
85 public boolean assertion(String tableName, String condition) throws Exception
86 {
87 return (count(tableName, condition) > 0);
88 }
89
90
91 /***
92 * Get the number of records from the input table.
93 *
94 * @param tableName the name of the table where to retrieve the records number
95 * @return the number of records of the input table
96 * @exception Exception if any error occurs
97 */
98 public long count(String tableName) throws Exception
99 {
100 return count(tableName, null);
101 }
102
103
104 /***
105 * Get the number of records from the input table
106 *
107 * @param tableName the name of the table where to retrieve the records number
108 * @param condition the where condition string, or null (stands for "no condition")
109 * @return the records number of the input table
110 * @exception Exception if any error occurs
111 */
112 public long count(String tableName, String condition) throws Exception
113 {
114 StringBuffer sb = new StringBuffer()
115 .append("select count(*) as RES from ")
116 .append(tableName);
117
118 if (condition != null)
119 sb.append(" where ").append(condition);
120
121 return count(tableName, sb.toString(), "RES");
122 }
123
124
125 /***
126 * Get a new unique key value.
127 *
128 * @return a new unique key value
129 * @exception Exception Description of the Exception
130 */
131 public long getNewKey() throws Exception
132 {
133 return IdGenerator.instance().getId();
134 }
135
136
137 /***
138 * Delete records from the input table, using the input condition.
139 * <br>
140 * This method supports the READ_COMMITTED isolation level
141 * to prevent dirty reads.
142 *
143 * @param condition the query string.
144 * @param tableName Description of the Parameter
145 * @exception Exception Description of the Exception
146 */
147 public void delete(String tableName, String condition) throws Exception
148 {
149 Connection con = null;
150
151 try
152 {
153 con = getConnection(Connection.TRANSACTION_SERIALIZABLE);
154 delete(tableName, condition, con);
155 }
156 finally
157 {
158 closeResources(con, true);
159 }
160 }
161
162
163 /***
164 * Delete records from the input table, using the input condition.
165 * <br>
166 * This method is useful for DbForms interceptor classes, because
167 * it <b>doesn't close</b> the connection object; the connection management
168 * is left to the application developer.
169 *
170 * @param tableName the name of the database table
171 * @param condition the query condition (a SQL 'where' clause
172 * without the 'WHERE' word);
173 * @param con the JDBC Connection object.
174 * @exception Exception if any error occurs
175 */
176 public void delete(String tableName,
177 String condition,
178 Connection con)
179 throws Exception
180 {
181 Statement st = null;
182 String query = null;
183 long i = 0;
184
185
186 if (con == null)
187 throw new Exception("input connection is null");
188
189 StringBuffer qy = new StringBuffer()
190 .append("delete from ")
191 .append(tableName)
192 .append(" where ")
193 .append(condition);
194
195 try
196 {
197 st = con.createStatement();
198 st.executeUpdate(qy.toString());
199 }
200 finally
201 {
202
203 if (st != null) st.close();
204
205 if ((con != null) && !con.getAutoCommit())
206 cat.info("::delete - remember to close the open transaction !");
207 }
208 }
209
210
211 /***
212 * Return a JDBC Connection.
213 * <BR>
214 * note: should <br>NOT</br> be public !!
215 *
216 * @return a JDBC Connection
217 * @exception Exception Exception if any error occurs
218 */
219 public Connection getConnection() throws Exception
220 {
221 return connectionFactory.getConnection();
222 }
223
224
225 /***
226 * Open a new transaction and return a JDBC Connection set with the input
227 * isolation level.
228 *
229 * @param isolationLevel the isolation level to set the connection to
230 * @return the new "transactional" connection object
231 * @throws Exception if any error occurs
232 */
233 public Connection getConnection(int isolationLevel) throws Exception
234 {
235 return connectionFactory.getConnection(isolationLevel);
236 }
237
238
239
240
241 /***
242 * PROTECTED METHODS HERE
243 */
244
245
246 /***
247 * set a SQL LIKE statement
248 *
249 * @param value the value to bound to the LIKE statement
250 * @return the LIKE statement
251 */
252 protected String setLike(String value)
253 {
254 return setLike(value, true, 0);
255 }
256
257
258 /***
259 * set a SQL LIKE statement
260 *
261 * @param value the value to bound to the LIKE statement
262 * @param upperCase true to upper all the characters of the the input value string
263 * @param likeMode 0 stands for "contains": LIKE %${value}%
264 * 1 stands for "starts with": LIKE ${value}%
265 * @return the LIKE statement
266 */
267 protected String setLike(String value, boolean upperCase, int likeMode)
268 {
269 value = (likeMode == 0) ?
270 new StringBuffer("'%").append(value).append("%'").toString() :
271 new StringBuffer("'").append(value).append("%'").toString();
272
273 if (upperCase)
274 value.toUpperCase();
275
276 return value;
277 }
278
279
280 /***
281 * Encode the input statement string, replacing all the "'" characters
282 * with "''" string.
283 *
284 * @param statement the SQL statement string to encode
285 * @return the encoded statement string
286 */
287 protected String encode(String statement)
288 {
289 StringBuffer in = new StringBuffer(statement.trim().toUpperCase());
290 StringBuffer out = new StringBuffer();
291
292 for (int i = 0; i < in.length(); i++)
293 {
294 if (String.valueOf(in.charAt(i)).equals("'"))
295 out.append("''");
296 else
297 out.append(in.charAt(i));
298 }
299
300 return out.toString();
301 }
302
303
304 /***
305 * Transfer a record data into the input value object bean.
306 * <br>
307 * The bean must simply have one or more accessors that match column names in
308 * the Map object generated from the input resultSet.<br>
309 * The columns that have a matching accessor will be used to populate the beans.
310 * Other accessors and columns are ignored.
311 *
312 * @param bean the value object to populate with the data retrieved
313 * from the input resultSet
314 * @param resultSet the resultSet used to retrieve the record data from
315 * @exception SQLException if any error occurs
316 */
317 protected void populate(Object bean, ResultSet resultSet) throws SQLException
318 {
319
320 HashMap properties = new HashMap();
321
322
323 ResultSetMetaData metaData = resultSet.getMetaData();
324 int cols = metaData.getColumnCount();
325
326
327 if (resultSet.next())
328 {
329 for (int i = 1; i <= cols; i++)
330 properties.put(metaData.getColumnName(i), resultSet.getString(i));
331 }
332
333
334 try
335 {
336 BeanUtils.populate(bean, properties);
337 }
338 catch (Exception e)
339 {
340 throw new SQLException("BeanUtils.populate threw " + e.toString());
341 }
342 }
343
344
345 /***
346 * Commit or rollback any uncommitted transaction
347 * and close the input connection.
348 * <br>
349 * Call this method into a <code>finally()</code> clause.
350 *
351 * @param con the Connection object to close
352 * @param commit true to commit the transaction, false to rollback it
353 * @exception SQLException if any error occurs
354 */
355 protected void closeResources(Connection con, boolean commit)
356 throws SQLException
357 {
358 closeResources(null, null, con, commit);
359 }
360
361
362 /***
363 * Close the JDBC statement object, commit or rollback any uncommitted
364 * transaction and close the input connection.
365 * <br>
366 * Call this method into a <code>finally()</code> clause.
367 *
368 * @param st the Statement object to close
369 * @param con the Connection object to close
370 * @param commit true to commit the transaction, false to rollback it
371 * @exception SQLException if any error occurs
372 */
373 protected void closeResources(Statement st, Connection con, boolean commit)
374 throws SQLException
375 {
376 closeResources(null, st, con, commit);
377 }
378
379
380 /***
381 * Close the JDBC resultSet and statement objects, commit or rollback
382 * any uncommitted transaction and close the input connection.
383 * <br>
384 * Call this method into a <code>finally()</code> clause.
385 *
386 * @param rs the recordSet object to close
387 * @param con the Connection object to close
388 * @param st the statement object to close
389 * @param commit true to commit the connection, false to rollback it
390 * @exception SQLException if any error occurs
391 */
392 protected void closeResources(ResultSet rs,
393 Statement st,
394 Connection con,
395 boolean commit)
396 throws SQLException
397 {
398 if (rs != null) rs.close();
399 if (st != null) st.close();
400 if (con != null) finalizeConnection(con, commit, true);
401 }
402
403
404
405
406 /***
407 * PRIVATE METHODS HERE
408 */
409
410
411 /***
412 * Finalize the input connection, following these steps:
413 * <ul>
414 * <li>check if the input connection is in transactional state. If yes:
415 * <ul>
416 * <li>
417 * if the input <code>commit</code> parameter value is true,
418 * commit the transaction; <b>else</b> rollback it
419 * <li>
420 * <li>
421 * set the connection auto-commit mode to <b>true</b>
422 * </li>
423 * </ul>
424 * </li>
425 * <li>
426 * if the input <code>close</code> parameter value is true,
427 * close the connection
428 * </li>
429 * </ul>
430 *
431 * @param con the input connection object
432 * @param commit true to commit the connection, false to rollback it
433 * @param close true to close the input connection; false otherwise
434 * @exception SQLException if any error occurs
435 */
436 private void finalizeConnection(Connection con,
437 boolean commit,
438 boolean close)
439 throws SQLException
440 {
441 if (con == null)
442 {
443 cat.warn("::finalizeConnection - input connection is null");
444 return;
445 }
446
447 SQLException mainSQLException = null;
448
449
450 try
451 {
452
453 if (!con.getAutoCommit())
454 {
455
456 if (commit)
457 {
458 con.commit();
459 cat.info("::finalizeConnection - transaction committed");
460 }
461
462
463 else
464 {
465 con.rollback();
466 cat.info("::finalizeConnection - transaction rollback");
467 }
468 }
469 }
470
471
472
473 catch(SQLException e)
474 {
475 if ((con != null) && !con.getAutoCommit())
476 con.rollback();
477
478 cat.error("::finalizeConnection - MUST rollback this transaction !", e);
479 mainSQLException = e;
480 throw e;
481 }
482
483
484
485
486
487 finally
488 {
489 try
490 {
491
492 if ((con != null) && !con.getAutoCommit())
493 con.setAutoCommit(true);
494
495 if (close && !con.isClosed())
496 con.close();
497 }
498 catch(SQLException e)
499 {
500 cat.error("::finalizeConnection - cannot set auto-commit to true or close the connection; error code, sql ste = ["
501 + e.getErrorCode() + ", " + e.getSQLState() + "]", e);
502
503
504
505 if (mainSQLException != null)
506 mainSQLException.setNextException(e);
507 else
508 throw e;
509 }
510 }
511 }
512
513
514 /***
515 * Execute queries that use aggregate functions.
516 * <br>
517 * This method supports the READ_COMMITTED transaction isolation level
518 * to prevent dirty reads.
519 *
520 * @param tableName the name of the table
521 * @param query the query with aggregate function to execute
522 * @param fieldName the name of the field that contains the query result
523 * @return the query aggregate function result
524 * @exception Exception if any error occurs
525 */
526 private long count(String tableName, String query, String fieldName) throws Exception
527 {
528 Connection con = null;
529 Statement st = null;
530 ResultSet rs = null;
531 long i = 0;
532
533 try
534 {
535 con = getConnection(Connection.TRANSACTION_READ_COMMITTED);
536 st = con.createStatement();
537 rs = st.executeQuery(query);
538
539 if (rs.next())
540 i = rs.getInt(fieldName);
541 }
542 finally
543 {
544 closeResources(rs, st, con, true);
545 }
546
547 return i;
548 }
549 }