001 package biz.wedoit4u.databeans;
002
003 import java.util.ArrayList;
004 import java.util.Collection;
005 import java.util.Date;
006 import java.sql.Connection;
007 import java.sql.PreparedStatement;
008 import java.sql.ResultSet;
009 import java.sql.SQLException;
010 import java.sql.Timestamp;
011
012 import javax.naming.NamingException;
013
014 import biz.wedoit4u.Logger;
015
016 /**
017 * A helper class that provides convenient methods to interact with
018 * the <code>categories</code> table. This class is meant to
019 * be used from the {@link DatabaseHelper} class, or other classes
020 * in this package only.
021 *
022 * <p>Copyright 2003, Rakesh Vidyadharan and wedoit4u.biz</p>
023 *
024 * @author Rakesh Vidyadharan 21<sup><small>st</small></sup> October 2003
025 *
026 * @version $Id: CategoryHelper.java,v 1.3 2004/05/26 11:42:30 rakesh Exp $
027 */
028 final class CategoryHelper extends Object
029 {
030 /**
031 * The <code>sequence</code> set in the database for the <code>
032 * category_id primary key</code> column.
033 */
034 static final String CATEGORY_ID_SEQUENCE = "sequence_category_id";
035
036 /**
037 * The statement that is executed to fetch all the records from the
038 * <code>categories</code> table.
039 */
040 static final String ALL_CATEGORIES = "select category_id from categories";
041
042 /**
043 * The statement that is executed to fetch all the records from the
044 * <code>categories</code> table that belong to the specified
045 * <code>customer_id</code>.
046 */
047 static final String FIND_BY_CUSTOMER = "select category_id from categories where customer_id = ? order by sort_order";
048
049 /**
050 * The statement that is executed to insert a new record into the
051 * <code>categories</code> table.
052 */
053 static final String INSERT_CATEGORIES = "insert into categories cols( category_id, customer_id, menu_name, long_name, brief, picture, description, sort_order ) values ( ?, ?, ?, ?, ?, ?, ?, ? )";
054
055 /**
056 * The statement that is executed to fetch a specific record from the
057 * <code>categories</code> table.
058 */
059 static final String SELECT_CATEGORIES = "select * from categories where category_id = ?";
060
061 /**
062 * The update statement to be executed to update a record in the
063 * <code>categories</code> table.
064 */
065 static final String UPDATE_CATEGORIES = "update categories set customer_id = ?, menu_name = ?, long_name = ?, brief = ?, picture = ?, description = ?, sort_order = ? where category_id = ?";
066
067 /**
068 * The delete statement that is used to delete a record from the
069 * <code>categories</code> table identified by the primary key value.
070 */
071 static final String DELETE_CATEGORIES = "delete from categories where category_id = ?";
072
073 /**
074 * Return a <code>Collection</code> of <code>Integer</code> values
075 * that represent all the primary key values in the
076 * <code>categories</code> table.
077 *
078 * @return Collection - The collection of primary key values.
079 * @throws SQLException - If errors are encountered while fetching
080 * the records from the database.
081 * @throws NamingException - If errors are encountered while getting
082 * a connection from the connection pool.
083 */
084 static final Collection findAll() throws SQLException, NamingException
085 {
086 Collection list = new ArrayList();
087 Connection connection = null;
088 PreparedStatement statement = null;
089 ResultSet resultSet = null;
090
091 try
092 {
093 connection = DatabaseHelper.getConnection();
094 statement = connection.prepareStatement( CategoryHelper.ALL_CATEGORIES );
095 resultSet = statement.executeQuery();
096
097 while ( resultSet.next() )
098 {
099 list.add( new Integer( resultSet.getInt( "category_id" ) ) );
100 }
101 }
102 finally
103 {
104 try
105 {
106 if ( resultSet != null ) resultSet.close();
107 }
108 catch ( SQLException sex )
109 {
110 Logger.error( "Error closing resultSet in CategoryHelper.getAllCategoryIds.", sex );
111 }
112
113 try
114 {
115 if ( statement != null ) statement.close();
116 }
117 catch ( SQLException sex )
118 {
119 Logger.error( "Error closing statement in CategoryHelper.getAllCategoryIds.", sex );
120 }
121
122 try
123 {
124 if ( connection != null ) connection.close();
125 }
126 catch ( SQLException sex )
127 {
128 Logger.error( "Error closing connection in CategoryHelper.getAllCategoryIds.", sex );
129 }
130 }
131
132 return list;
133 }
134
135 /**
136 * Return a <code>Collection</code> of <code>Integer</code> values
137 * that represent all the primary key values in the
138 * <code>categories</code> table that are associated with the
139 * specified <code>customer_id</code>.
140 *
141 * @param customerId - The <code>customer_id</code> value.
142 * @return Collection - The collection of primary key values.
143 * @throws SQLException - If errors are encountered while fetching
144 * the records from the database.
145 * @throws NamingException - If errors are encountered while getting
146 * a connection from the connection pool.
147 */
148 static final Collection findByCustomer( int customerId )
149 throws SQLException, NamingException
150 {
151 Collection list = new ArrayList();
152 Connection connection = null;
153 PreparedStatement statement = null;
154 ResultSet resultSet = null;
155
156 try
157 {
158 connection = DatabaseHelper.getConnection();
159 statement = connection.prepareStatement( CategoryHelper.FIND_BY_CUSTOMER );
160 statement.setInt( 1, customerId );
161 resultSet = statement.executeQuery();
162
163 while ( resultSet.next() )
164 {
165 list.add( new Integer( resultSet.getInt( "category_id" ) ) );
166 }
167 }
168 finally
169 {
170 try
171 {
172 if ( resultSet != null ) resultSet.close();
173 }
174 catch ( SQLException sex )
175 {
176 Logger.error( "Error closing resultSet in CategoryHelper.getCategoryIdsByCustomer.", sex );
177 }
178
179 try
180 {
181 if ( statement != null ) statement.close();
182 }
183 catch ( SQLException sex )
184 {
185 Logger.error( "Error closing statement in CategoryHelper.getCategoryIdsByCustomer.", sex );
186 }
187
188 try
189 {
190 if ( connection != null ) connection.close();
191 }
192 catch ( SQLException sex )
193 {
194 Logger.error( "Error closing connection in CategoryHelper.getCategoryIdsByCustomer.", sex );
195 }
196 }
197
198 return list;
199 }
200
201 /**
202 * Select the specified record from the <code>categories</code>
203 * table. The values selected from the database are used to set the
204 * values of the bean fields.
205 *
206 * @param cb - The java bean instance that
207 * represents the record. The primary key value to use to look
208 * up the record is obtained from this bean.
209 * @throws SQLException - If errors are encountered while fetching
210 * the record from the database.
211 * @throws NamingException - If errors are encountered while getting
212 * a connection from the connection pool.
213 */
214 static final void select( CategoryBean cb )
215 throws SQLException, NamingException
216 {
217 Connection connection = null;
218 PreparedStatement statement = null;
219 ResultSet resultSet = null;
220
221 try
222 {
223 connection = DatabaseHelper.getConnection();
224 statement = connection.prepareStatement( CategoryHelper.SELECT_CATEGORIES );
225 statement.setInt( 1, cb.getCategoryId() );
226 resultSet = statement.executeQuery();
227 resultSet.next();
228 cb.customerId = resultSet.getInt( "customer_id" );
229 cb.menuName = resultSet.getString( "menu_name" );
230 cb.longName = resultSet.getString( "long_name" );
231 cb.brief = resultSet.getString( "brief" );
232 cb.picture = resultSet.getString( "picture" );
233 cb.description = resultSet.getString( "description" );
234 cb.sortOrder = resultSet.getInt( "sort_order" );
235 }
236 finally
237 {
238 try
239 {
240 if ( resultSet != null ) resultSet.close();
241 }
242 catch ( SQLException sex )
243 {
244 Logger.error( "Error closing resultSet in CategoryHelper.selectCategorys for category_id " + cb.getCategoryId() + ".", sex );
245 }
246
247 try
248 {
249 if ( statement != null ) statement.close();
250 }
251 catch ( SQLException sex )
252 {
253 Logger.error( "Error closing statement in CategoryHelper.selectCategorys for category_id " + cb.getCategoryId() + ".", sex );
254 }
255
256 try
257 {
258 if ( connection != null ) connection.close();
259 }
260 catch ( SQLException sex )
261 {
262 Logger.error( "Error closing connection in CategoryHelper.selectCategorys for category_id " + cb.getCategoryId() + ".", sex );
263 }
264 }
265 }
266
267 /**
268 * Insert a new record into the <code>categories</code> table
269 * with the values specified.
270 *
271 * @param categoryId - The primary key values to use. If this
272 * is <code>0</code>, then the next value from the {@link
273 * #CATEGORY_ID_SEQUENCE} is used.
274 * @param customerId - The <code>customer_id</code> value.
275 * @param menuName - The <code>menu_name</code> value.
276 * @param longName - The <code>long_name</code> value.
277 * @param brief - The <code>brief</code> value.
278 * @param picture - The <code>picture</code> value.
279 * @param description - The <code>description</code> value.
280 * @param sortOrder - The <code>sort_order</code> value.
281 * @throws SQLException - If database errors are encountered while
282 * writing to the database.
283 * @throws NamingException - If errors are encountered while getting
284 * a connection from the connection pool.
285 */
286 static final int insert( int categoryId, int customerId,
287 String menuName, String longName, String brief, String picture,
288 String description, int sortOrder )
289 throws SQLException, NamingException
290 {
291 Connection connection = null;
292 PreparedStatement statement = null;
293
294 try
295 {
296 connection = DatabaseHelper.getConnection();
297
298 if ( categoryId == 0 )
299 {
300 categoryId = DatabaseHelper.getNextSequenceValue( CategoryHelper.CATEGORY_ID_SEQUENCE );
301 }
302
303 statement = connection.prepareStatement( CategoryHelper.INSERT_CATEGORIES );
304 statement.setInt( 1, categoryId );
305 statement.setInt( 2, customerId );
306 statement.setString( 3, menuName );
307 statement.setString( 4, longName );
308 statement.setString( 5, brief );
309 statement.setString( 6, picture );
310 statement.setString( 7, description );
311 statement.setInt( 8, sortOrder );
312 statement.executeUpdate();
313 }
314 finally
315 {
316 try
317 {
318 if ( statement != null ) statement.close();
319 }
320 catch ( SQLException sex )
321 {
322 Logger.error( "Error closing statement in CategoryHelper.insertCategorys for category_id " + categoryId + ".", sex );
323 }
324
325 try
326 {
327 if ( connection != null ) connection.close();
328 }
329 catch ( SQLException sex )
330 {
331 Logger.error( "Error closing connection in CategoryHelper.insertCategorys for category_id " + categoryId + ".", sex );
332 }
333 }
334
335 return categoryId;
336 }
337
338 /**
339 * Update the values in the <code>categories</code> table with
340 * the specified values.
341 *
342 * @param cb - The java bean that represents a
343 * record in the database.
344 * @throws SQLException - If database errors are encountered while
345 * writing to the database.
346 * @throws NamingException - If errors are encountered while getting
347 * a connection from the connection pool.
348 */
349 static final void update( CategoryBean cb )
350 throws SQLException, NamingException
351 {
352 Connection connection = null;
353 PreparedStatement statement = null;
354
355 try
356 {
357 connection = DatabaseHelper.getConnection();
358 statement = connection.prepareStatement( CategoryHelper.UPDATE_CATEGORIES );
359 statement.setInt( 1, cb.customerId );
360 statement.setString( 2, cb.menuName );
361 statement.setString( 3, cb.longName );
362 statement.setString( 4, cb.brief );
363 statement.setString( 5, cb.picture );
364 statement.setString( 6, cb.description );
365 statement.setInt( 7, cb.sortOrder );
366 statement.setInt( 8, cb.getCategoryId() );
367 statement.executeUpdate();
368 }
369 finally
370 {
371 try
372 {
373 if ( statement != null ) statement.close();
374 }
375 catch ( SQLException sex )
376 {
377 Logger.error( "Error closing statement in CategoryHelper.updateCategorys for category_id " + cb.getCategoryId() + ".", sex );
378 }
379
380 try
381 {
382 if ( connection != null ) connection.close();
383 }
384 catch ( SQLException sex )
385 {
386 Logger.error( "Error closing connection in CategoryHelper.updateCategorys for category_id " + cb.getCategoryId() + ".", sex );
387 }
388 }
389 }
390
391 /**
392 * Delete the record from the database identified by the primary key
393 * value specified.
394 *
395 * @param categoryId - The <code>category_id</code>
396 * primary key value.
397 * @throws SQLException - If database errors are encountered while
398 * writing to the database.
399 * @throws NamingException - If errors are encountered while getting
400 * a connection from the connection pool.
401 */
402 static final void delete( int categoryId )
403 throws SQLException, NamingException
404 {
405 Connection connection = null;
406 PreparedStatement statement = null;
407
408 try
409 {
410 connection = DatabaseHelper.getConnection();
411 statement = connection.prepareStatement( CategoryHelper.DELETE_CATEGORIES );
412 statement.setInt( 1, categoryId );
413 statement.executeUpdate();
414 }
415 finally
416 {
417 try
418 {
419 if ( statement != null ) statement.close();
420 }
421 catch ( SQLException sex )
422 {
423 Logger.error( "Error closing statement in CategoryHelper.delete for category_id " + categoryId + ".", sex );
424 }
425
426 try
427 {
428 if ( connection != null ) connection.close();
429 }
430 catch ( SQLException sex )
431 {
432 Logger.error( "Error closing connection in CategoryHelper.delete for category_id " + categoryId + ".", sex );
433 }
434 }
435 }
436
437 /**
438 * Default constructor. Cannot be instantiated.
439 */
440 private CategoryHelper() {}
441 }