001 package biz.wedoit4u.databeans;
002
003 import java.io.IOException;
004 import java.io.Writer;
005 import java.util.ArrayList;
006 import java.util.Collection;
007 import java.util.Date;
008 import java.sql.Clob;
009 import java.sql.Connection;
010 import java.sql.PreparedStatement;
011 import java.sql.ResultSet;
012 import java.sql.SQLException;
013 import java.sql.Timestamp;
014
015 import javax.naming.NamingException;
016 import oracle.sql.CLOB;
017 import biz.wedoit4u.Logger;
018
019 /**
020 * A helper class that provides convenient methods to interact with
021 * the <code>contents</code> table. This class is meant to
022 * be used from the {@link DatabaseHelper} class, or other classes
023 * in this package only.
024 *
025 * <p>Copyright 2003, Rakesh Vidyadharan and wedoit4u.biz</p>
026 *
027 * @author Rakesh Vidyadharan 21<sup><small>st</small></sup> October 2003
028 *
029 * @version $Id: ContentHelper.java,v 1.3 2004/05/26 11:42:31 rakesh Exp $
030 */
031 final class ContentHelper extends Object
032 {
033 /**
034 * The <code>sequence</code> set in the database for the <code>
035 * content_id primary key</code> column.
036 */
037 static final String CONTENT_ID_SEQUENCE = "sequence_content_id";
038
039 /**
040 * The statement that is executed to fetch all the records from the
041 * <code>contents</code> table.
042 */
043 static final String ALL_CONTENTS = "select content_id from contents";
044
045 /**
046 * The statement that is executed to fetch all the records from the
047 * <code>contents</code> table that are associated with the specified
048 * <code>category_id</code>.
049 */
050 static final String FIND_BY_CATEGORY = "select content_id from contents where category_id = ?";
051
052 /**
053 * The statement that is executed to insert a new record into the
054 * <code>contents</code> table.
055 */
056 static final String INSERT_CONTENTS = "insert into contents cols( content_id, category_id, title, brief, picture, body ) values ( ?, ?, ?, ?, ?, ' ' )";
057
058 /**
059 * The statement that is executed to fetch a specific record from the
060 * <code>contents</code> table.
061 */
062 static final String SELECT_CONTENTS = "select * from contents where content_id = ?";
063
064 /**
065 * The update statement to be executed to update a record in the
066 * <code>content_types</code> table.
067 */
068 static final String UPDATE_CONTENTS = "update contents set category_id = ?, title = ?, brief = ?, picture = ? where content_id = ?";
069
070 /**
071 * The delete statement that is executed to delete a record in the
072 * <code>contents</code> table identified by the primary key value.
073 */
074 static final String DELETE_CONTENTS = "delete from contents where content_id = ?";
075
076 /**
077 * The statement that is used to empty the contents of the
078 * <code>body</code> column prior to updating it.
079 */
080 static final String NULL_BODY = "update contents set body = ' ' where content_id = ?";
081
082 /**
083 * The statement that is used to set the value of the <code>body</code>
084 * column.
085 */
086 static final String UPDATE_BODY = "select body from contents where content_id = ? for update";
087
088 /**
089 * Return a <code>Collection</code> of <code>Integer</code> values
090 * that represent all the primary key values in the
091 * <code>contents</code> table.
092 *
093 * @return Collection - The collection of primary key values.
094 * @throws SQLException - If errors are encountered while fetching
095 * the records from the database.
096 * @throws NamingException - If errors are encountered while getting
097 * a connection from the connection pool.
098 */
099 static final Collection findAll() throws SQLException, NamingException
100 {
101 Collection list = new ArrayList();
102 Connection connection = null;
103 PreparedStatement statement = null;
104 ResultSet resultSet = null;
105
106 try
107 {
108 connection = DatabaseHelper.getConnection();
109 statement = connection.prepareStatement( ContentHelper.ALL_CONTENTS );
110 resultSet = statement.executeQuery();
111
112 while ( resultSet.next() )
113 {
114 list.add( new Integer( resultSet.getInt( "content_id" ) ) );
115 }
116 }
117 finally
118 {
119 try
120 {
121 if ( resultSet != null ) resultSet.close();
122 }
123 catch ( SQLException sex )
124 {
125 Logger.error( "Error closing resultSet in ContentHelper.getAllContentIds.", sex );
126 }
127
128 try
129 {
130 if ( statement != null ) statement.close();
131 }
132 catch ( SQLException sex )
133 {
134 Logger.error( "Error closing statement in ContentHelper.getAllContentIds.", sex );
135 }
136
137 try
138 {
139 if ( connection != null ) connection.close();
140 }
141 catch ( SQLException sex )
142 {
143 Logger.error( "Error closing connection in ContentHelper.getAllContentIds.", sex );
144 }
145 }
146
147 return list;
148 }
149
150 /**
151 * Return a <code>Collection</code> of <code>Integer</code> values
152 * that represent all the primary key values in the
153 * <code>contents</code> table that are associated with the specified
154 * <code>category_id</code>.
155 *
156 * @param categoryId - The <code>category_id</code> value.
157 * @return Collection - The collection of primary key values.
158 * @throws SQLException - If errors are encountered while fetching
159 * the records from the database.
160 * @throws NamingException - If errors are encountered while getting
161 * a connection from the connection pool.
162 */
163 static final Collection findByCategory( int categoryId )
164 throws SQLException, NamingException
165 {
166 Collection list = new ArrayList();
167 Connection connection = null;
168 PreparedStatement statement = null;
169 ResultSet resultSet = null;
170
171 try
172 {
173 connection = DatabaseHelper.getConnection();
174 statement = connection.prepareStatement( ContentHelper.FIND_BY_CATEGORY );
175 statement.setInt( 1, categoryId );
176 resultSet = statement.executeQuery();
177
178 while ( resultSet.next() )
179 {
180 list.add( new Integer( resultSet.getInt( "content_id" ) ) );
181 }
182 }
183 finally
184 {
185 try
186 {
187 if ( resultSet != null ) resultSet.close();
188 }
189 catch ( SQLException sex )
190 {
191 Logger.error( "Error closing resultSet in ContentHelper.getContentIdsByCategory for category_id " + categoryId + ".", sex );
192 }
193
194 try
195 {
196 if ( statement != null ) statement.close();
197 }
198 catch ( SQLException sex )
199 {
200 Logger.error( "Error closing statement in ContentHelper.getContentIdsByCategory for category_id " + categoryId + ".", sex );
201 }
202
203 try
204 {
205 if ( connection != null ) connection.close();
206 }
207 catch ( SQLException sex )
208 {
209 Logger.error( "Error closing connection in ContentHelper.getContentIdsByCategory for category_id " + categoryId + ".", sex );
210 }
211 }
212
213 return list;
214 }
215
216 /**
217 * Select the specified record from the <code>contents</code>
218 * table. The values selected from the database are used to set the
219 * values of the bean fields.
220 *
221 * @param cb - The java bean instance that
222 * represents the record. The primary key value to use to look
223 * up the record is obtained from this bean.
224 * @throws SQLException - If errors are encountered while fetching
225 * the record from the database.
226 * @throws NamingException - If errors are encountered while getting
227 * a connection from the connection pool.
228 */
229 static final void select( ContentBean cb )
230 throws SQLException, NamingException
231 {
232 Connection connection = null;
233 PreparedStatement statement = null;
234 ResultSet resultSet = null;
235
236 try
237 {
238 connection = DatabaseHelper.getConnection();
239 statement = connection.prepareStatement( ContentHelper.SELECT_CONTENTS );
240 statement.setInt( 1, cb.getContentId() );
241 resultSet = statement.executeQuery();
242 resultSet.next();
243 cb.categoryId = resultSet.getInt( "category_id" );
244 cb.title = resultSet.getString( "title" );
245 cb.brief = resultSet.getString( "brief" );
246 cb.picture = resultSet.getString( "picture" );
247 Clob clob = resultSet.getClob( "body" );
248 cb.body = clob.getSubString( 1, (int) clob.length() );
249 cb.creationDate.setTime( resultSet.getTimestamp( "creation_date" ).getTime() );
250 cb.modificationDate.setTime( resultSet.getTimestamp( "modification_date" ).getTime() );
251 }
252 finally
253 {
254 try
255 {
256 if ( resultSet != null ) resultSet.close();
257 }
258 catch ( SQLException sex )
259 {
260 Logger.error( "Error closing resultSet in ContentHelper.selectContents for content_id " + cb.getContentId() + ".", sex );
261 }
262
263 try
264 {
265 if ( statement != null ) statement.close();
266 }
267 catch ( SQLException sex )
268 {
269 Logger.error( "Error closing statement in ContentHelper.selectContents for content_id " + cb.getContentId() + ".", sex );
270 }
271
272 try
273 {
274 if ( connection != null ) connection.close();
275 }
276 catch ( SQLException sex )
277 {
278 Logger.error( "Error closing connection in ContentHelper.selectContents for content_id " + cb.getContentId() + ".", sex );
279 }
280 }
281 }
282
283 /**
284 * Insert a new record into the <code>contents</code> table
285 * with the values specified.
286 *
287 * @param contentId - The primary key values to use. If this
288 * is <code>0</code>, then the next value from the {@link
289 * #CONTENT_ID_SEQUENCE} is used.
290 * @param categoryId - The <code>category_id</code> value.
291 * @param title - The <code>title</code> value.
292 * @param brief - The <code>brief</code> value.
293 * @param picture - The <code>picture</code> value.
294 * @param body - The <code>body</code> value.
295 * @throws SQLException - If database errors are encountered while
296 * writing to the database.
297 * @throws NamingException - If errors are encountered while getting
298 * a connection from the connection pool.
299 */
300 static final int insert( int contentId, int categoryId,
301 String title, String brief, String picture, String body )
302 throws SQLException, NamingException
303 {
304 Connection connection = null;
305 PreparedStatement statement = null;
306
307 try
308 {
309 connection = DatabaseHelper.getConnection();
310
311 if ( contentId == 0 )
312 {
313 contentId = DatabaseHelper.getNextSequenceValue( ContentHelper.CONTENT_ID_SEQUENCE );
314 }
315
316 statement = connection.prepareStatement( ContentHelper.INSERT_CONTENTS );
317 statement.setInt( 1, contentId );
318 statement.setInt( 2, categoryId );
319 statement.setString( 3, title );
320 statement.setString( 4, brief );
321 statement.setString( 5, picture );
322 statement.executeUpdate();
323 }
324 finally
325 {
326 try
327 {
328 if ( statement != null ) statement.close();
329 }
330 catch ( SQLException sex )
331 {
332 Logger.error( "Error closing statement in ContentHelper.insertContents for content_id " + contentId + ".", sex );
333 }
334
335 try
336 {
337 if ( connection != null ) connection.close();
338 }
339 catch ( SQLException sex )
340 {
341 Logger.error( "Error closing connection in ContentHelper.insertContents for content_id " + contentId + ".", sex );
342 }
343 }
344
345 // Write the contents to the body column
346 writeBody( contentId, body );
347
348 return contentId;
349 }
350
351 /**
352 * Update the values in the <code>contents</code> table with
353 * the specified values.
354 *
355 * @param cb - The java bean that represents a
356 * record in the database.
357 * @throws SQLException - If database errors are encountered while
358 * writing to the database.
359 * @throws NamingException - If errors are encountered while getting
360 * a connection from the connection pool.
361 */
362 static final void update( ContentBean cb )
363 throws SQLException, NamingException
364 {
365 Connection connection = null;
366 PreparedStatement statement = null;
367
368 try
369 {
370 connection = DatabaseHelper.getConnection();
371 statement = connection.prepareStatement( ContentHelper.UPDATE_CONTENTS );
372 statement.setInt( 1, cb.categoryId );
373 statement.setString( 2, cb.title );
374 statement.setString( 3, cb.brief );
375 statement.setString( 4, cb.picture );
376 statement.setInt( 5, cb.getContentId() );
377 statement.executeUpdate();
378 }
379 finally
380 {
381 try
382 {
383 if ( statement != null ) statement.close();
384 }
385 catch ( SQLException sex )
386 {
387 Logger.error( "Error closing statement in ContentHelper.updateContents for content_id " + cb.getContentId() + ".", sex );
388 }
389
390 try
391 {
392 if ( connection != null ) connection.close();
393 }
394 catch ( SQLException sex )
395 {
396 Logger.error( "Error closing connection in ContentHelper.updateContents for content_id " + cb.getContentId() + ".", sex );
397 }
398 }
399
400 writeBody( cb.getContentId(), cb.body );
401 }
402
403 /**
404 * Write the specified contents to the <code>body</code> column of the
405 * specified content record.
406 *
407 * @see #nullBody( int )
408 * @see #updateBody( int, String )
409 * @param contentId - The primary key value whose body is to
410 * be updated.
411 * @param body - The new <code>body</code> value.
412 * @throws SQLException - If errors are encountered while writing
413 * to the database.
414 * @throws NamingException - If errors are encountered while getting
415 * a connection from the connection pool.
416 */
417 static final void writeBody( int contentId, String body )
418 throws SQLException, NamingException
419 {
420 nullBody( contentId );
421 updateBody( contentId, body );
422 }
423
424 /**
425 * Reset the contents of the <code>body</code> column to an empty
426 * string.
427 *
428 * @param contentId - The primary key value whose body is to
429 * be updated.
430 * @throws SQLException - If errors are encountered while writing
431 * to the database.
432 * @throws NamingException - If errors are encountered while getting
433 * a connection from the connection pool.
434 */
435 static final void nullBody( int contentId )
436 throws SQLException, NamingException
437 {
438 Connection connection = null;
439 PreparedStatement statement = null;
440
441 try
442 {
443 connection = DatabaseHelper.getConnection();
444 statement = connection.prepareStatement( ContentHelper.NULL_BODY );
445 statement.setInt( 1, contentId );
446 statement.executeUpdate();
447 }
448 finally
449 {
450 try
451 {
452 if ( statement != null ) statement.close();
453 }
454 catch ( SQLException sex )
455 {
456 Logger.error( "Error closing Statement in ContentHelper.updateBody for contentId " + contentId + ".", sex );
457 }
458
459 try
460 {
461 if ( connection != null ) connection.close();
462 }
463 catch ( SQLException sex )
464 {
465 Logger.error( "Error closing connection in ContentHelper.updateBody for content_id " + contentId + ".", sex );
466 }
467 }
468 }
469
470 /**
471 * Write the specified string as the contents of the <code>body</code>
472 * column.
473 *
474 * <p><b>Note:</b> You <b>must</b> truncate any existing content in
475 * the <code>body</code> column prior to using this method to ensure
476 * that you get the expected results. Just calling this method will
477 * simply replace the first <code>body.length()</code> characters
478 * with the characters from the specified body.</p>
479 *
480 * @param contentId - The primary key value whose body is to
481 * be updated.
482 * @param body - The new <code>body</code> value.
483 * @throws SQLException - If errors are encountered while writing
484 * to the database.
485 * @throws NamingException - If errors are encountered while getting
486 * a connection from the connection pool.
487 */
488 static final void updateBody( int contentId, String body )
489 throws SQLException, NamingException
490 {
491 Connection connection = null;
492 PreparedStatement statement = null;
493 ResultSet resultSet = null;
494
495 try
496 {
497 connection = DatabaseHelper.getConnection();
498 connection.setAutoCommit( false );
499
500 statement = connection.prepareStatement( ContentHelper.UPDATE_BODY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
501 statement.setInt( 1, contentId );
502 resultSet = statement.executeQuery();
503 resultSet.next();
504 CLOB clob = (CLOB) resultSet.getClob( "body" );
505
506 try
507 {
508 DatabaseHelper.writeToClob( clob, body );
509 }
510 catch ( IOException ioex )
511 {
512 throw new SQLException( "Error updating contents.body in ContentHelper.writeBody for content_id " + contentId + ". " + ioex.toString() );
513 }
514
515 connection.commit();
516 }
517 finally
518 {
519 try
520 {
521 if ( resultSet != null ) resultSet.close();
522 }
523 catch ( SQLException sex )
524 {
525 Logger.error( "Error closing ResultSet in ContentHelper.updateBody for contentId " + contentId + ".", sex );
526 }
527
528 try
529 {
530 if ( statement != null ) statement.close();
531 }
532 catch ( SQLException sex )
533 {
534 Logger.error( "Error closing Statement in ContentHelper.updateBody for contentId " + contentId + ".", sex );
535 }
536
537 try
538 {
539 if ( connection != null ) connection.close();
540 }
541 catch ( SQLException sex )
542 {
543 Logger.error( "Error closing connection in ContentHelper.updateBody for content_id " + contentId + ".", sex );
544 }
545 }
546 }
547
548 /**
549 * Delete the record from the database identified by the primary key
550 * value specified.
551 *
552 * @param contentId - The <code>content_id</code>
553 * primary key value.
554 * @throws SQLException - If database errors are encountered while
555 * writing to the database.
556 * @throws NamingException - If errors are encountered while getting
557 * a connection from the connection pool.
558 */
559 static final void delete( int contentId )
560 throws SQLException, NamingException
561 {
562 Connection connection = null;
563 PreparedStatement statement = null;
564
565 try
566 {
567 connection = DatabaseHelper.getConnection();
568 statement = connection.prepareStatement( ContentHelper.DELETE_CONTENTS );
569 statement.setInt( 1, contentId );
570 statement.executeUpdate();
571 }
572 finally
573 {
574 try
575 {
576 if ( statement != null ) statement.close();
577 }
578 catch ( SQLException sex )
579 {
580 Logger.error( "Error closing statement in ContentHelper.delete for content_id " + contentId + ".", sex );
581 }
582
583 try
584 {
585 if ( connection != null ) connection.close();
586 }
587 catch ( SQLException sex )
588 {
589 Logger.error( "Error closing connection in ContentHelper.delete for content_id " + contentId + ".", sex ); }
590 }
591 }
592
593 /**
594 * Default constructor. Cannot be instantiated.
595 */
596 private ContentHelper() {}
597 }