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>customers</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: CustomerHelper.java,v 1.4 2004/05/26 11:42:31 rakesh Exp $
027 */
028 final class CustomerHelper extends Object
029 {
030 /**
031 * The <code>sequence</code> set in the database for the <code>
032 * customer_id primary key</code> column.
033 */
034 static final String CUSTOMER_ID_SEQUENCE = "sequence_customer_id";
035
036 /**
037 * The statement that is executed to fetch all the records from the
038 * <code>customers</code> table.
039 */
040 static final String ALL_CUSTOMERS = "select customer_Id from customers";
041
042 /**
043 * The statement that is executed to fetch all the records in the
044 * <code>customer</code> table that belong to a specific
045 * <code>customer_type_id</code>.
046 */
047 static final String FIND_BY_CUSTOMER_TYPE = "select customer_id from customers where customer_type_id = ?";
048
049 /**
050 * The statement that is executed to fetch the matching record from
051 * the <code>customer</code> table for the specified <code>username
052 * </code> value.
053 */
054 static final String FIND_BY_USERNAME_AND_PASSWORD = "select customer_id from customers where username = ? and password = ?";
055
056 /**
057 * The statement that is executed to insert a new record into the
058 * <code>customers</code> table.
059 */
060 static final String INSERT_CUSTOMERS = "insert into customers cols( customer_id, username, password, email, domain, active, activation_date, expiration_date, customer_type_id ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";
061
062 /**
063 * The statement that is executed to fetch a specific record from the
064 * <code>customers</code> table.
065 */
066 static final String SELECT_CUSTOMERS = "select * from customers where customer_id = ?";
067
068 /**
069 * The update statement to be executed to update a record in the
070 * <code>customers</code> table.
071 */
072 static final String UPDATE_CUSTOMERS = "update customers set username = ?, password = ?, email = ?, domain = ?, active = ?, activation_date = ?, expiration_date = ?, customer_type_id = ? where customer_id = ?";
073
074 /**
075 * The delete statement to be executed to delete a record in the
076 * <code>customers</code> table identified by the primary key value.
077 */
078 static final String DELETE_CUSTOMERS = "delete from customers where customer_id = ?";
079
080 /**
081 * Return a <code>Collection</code> of <code>Integer</code> values
082 * that represent all the primary key values in the
083 * <code>customers</code> table.
084 *
085 * @return Collection - The collection of primary key values.
086 * @throws SQLException - If errors are encountered while fetching
087 * the records from the database.
088 * @throws NamingException - If errors are encountered while getting
089 * a connection from the connection pool.
090 */
091 static final Collection findAll() throws SQLException, NamingException
092 {
093 Collection list = new ArrayList();
094 Connection connection = null;
095 PreparedStatement statement = null;
096 ResultSet resultSet = null;
097
098 try
099 {
100 connection = DatabaseHelper.getConnection();
101 statement = connection.prepareStatement( CustomerHelper.ALL_CUSTOMERS );
102 resultSet = statement.executeQuery();
103
104 while ( resultSet.next() )
105 {
106 list.add( new Integer( resultSet.getInt( "customer_id" ) ) );
107 }
108 }
109 finally
110 {
111 try
112 {
113 if ( resultSet != null ) resultSet.close();
114 }
115 catch ( SQLException sex )
116 {
117 Logger.error( "Error closing resultSet in CustomerHelper.getAllCustomerIds.", sex );
118 }
119
120 try
121 {
122 if ( statement != null ) statement.close();
123 }
124 catch ( SQLException sex )
125 {
126 Logger.error( "Error closing statement in CustomerHelper.getAllCustomerIds.", sex );
127 }
128
129 try
130 {
131 if ( connection != null ) connection.close();
132 }
133 catch ( SQLException sex )
134 {
135 Logger.error( "Error closing connection in CustomerHelper.getAllCustomerIds.", sex );
136 }
137 }
138
139 return list;
140 }
141
142 /**
143 * Select specified records from the <code>customers</code>
144 * table that belong to the specified <code>customer_type_id</code>.
145 *
146 * @param customerTypeId - The <code>customer_type_id</code> value.
147 * @return Collection - The list of primary key values.
148 * @throws SQLException - If errors are encountered while fetching
149 * the record from the database.
150 * @throws NamingException - If errors are encountered while getting
151 * a connection from the connection pool.
152 */
153 static final Collection findByCustomerType( int customerTypeId )
154 throws SQLException, NamingException
155 {
156 Collection list = new ArrayList();
157 Connection connection = null;
158 PreparedStatement statement = null;
159 ResultSet resultSet = null;
160
161 try
162 {
163 connection = DatabaseHelper.getConnection();
164 statement = connection.prepareStatement( CustomerHelper.FIND_BY_CUSTOMER_TYPE );
165 statement.setInt( 1, customerTypeId );
166 resultSet = statement.executeQuery();
167
168 while ( resultSet.next() )
169 {
170 Integer idObject = new Integer( resultSet.getInt( "customer_id" ) );
171 list.add( idObject );
172 }
173 }
174 finally
175 {
176 try
177 {
178 if ( resultSet != null ) resultSet.close();
179 }
180 catch ( SQLException sex )
181 {
182 Logger.error( "Error closing resultSet in CustomerHelper.findByCustomerType for customer_type_id " + customerTypeId + ".", sex );
183 }
184
185 try
186 {
187 if ( statement != null ) statement.close();
188 }
189 catch ( SQLException sex )
190 {
191 Logger.error( "Error closing statement in CustomerHelper.findByCustomerType for customer_type_id " + customerTypeId + ".", sex );
192 }
193
194 try
195 {
196 if ( connection != null ) connection.close();
197 }
198 catch ( SQLException sex )
199 {
200 Logger.error( "Error closing connection in CustomerHelper.findByCustomerType for customer_type_id " + customerTypeId + ".", sex );
201 }
202 }
203
204 return list;
205 }
206
207 /**
208 * Select the record from the <code>customers</code> table that has
209 * the specified <code>username</code> and <code>password</code>
210 * values.
211 *
212 * @param username - The <code>username</code> value.
213 * @param password - The <code>username</code> value.
214 * @return int - The customer_id value.
215 * @throws SQLException - If errors are encountered while fetching
216 * the record from the database.
217 * @throws NamingException - If errors are encountered while getting
218 * a connection from the connection pool.
219 */
220 static final int findByUsernameAndPassword( String username,
221 String password ) throws SQLException, NamingException
222 {
223 int customerId = 0;
224 Connection connection = null;
225 PreparedStatement statement = null;
226 ResultSet resultSet = null;
227
228 try
229 {
230 connection = DatabaseHelper.getConnection();
231 statement = connection.prepareStatement( CustomerHelper.FIND_BY_USERNAME_AND_PASSWORD );
232 statement.setString( 1, username );
233 statement.setString( 2, password );
234 resultSet = statement.executeQuery();
235
236 resultSet.next();
237 customerId = resultSet.getInt( "customer_id" );
238 }
239 finally
240 {
241 try
242 {
243 if ( resultSet != null ) resultSet.close();
244 }
245 catch ( SQLException sex )
246 {
247 Logger.error( "Error closing resultSet in CustomerHelper.findByUsername for username " + username + ".", sex );
248 }
249
250 try
251 {
252 if ( statement != null ) statement.close();
253 }
254 catch ( SQLException sex )
255 {
256 Logger.error( "Error closing statement in CustomerHelper.findByUsername for username " + username + ".", sex );
257 }
258
259 try
260 {
261 if ( connection != null ) connection.close();
262 }
263 catch ( SQLException sex )
264 {
265 Logger.error( "Error closing connection in CustomerHelper.findByUsername for username " + username + ".", sex );
266 }
267 }
268
269 return customerId;
270 }
271
272 /**
273 * Select the specified record from the <code>customers</code>
274 * table. The values selected from the database are used to set the
275 * values of the bean fields.
276 *
277 * @param cb - The java bean instance that
278 * represents the record. The primary key value to use to look
279 * up the record is obtained from this bean.
280 * @throws SQLException - If errors are encountered while fetching
281 * the record from the database.
282 * @throws NamingException - If errors are encountered while getting
283 * a connection from the connection pool.
284 */
285 static final void select( CustomerBean cb )
286 throws SQLException, NamingException
287 {
288 Connection connection = null;
289 PreparedStatement statement = null;
290 ResultSet resultSet = null;
291
292 try
293 {
294 connection = DatabaseHelper.getConnection();
295 statement = connection.prepareStatement( CustomerHelper.SELECT_CUSTOMERS );
296 statement.setInt( 1, cb.getCustomerId() );
297 resultSet = statement.executeQuery();
298 resultSet.next();
299 cb.username = resultSet.getString( "username" );
300 cb.password = resultSet.getString( "password" );
301 cb.email = resultSet.getString( "email" );
302 cb.domain = resultSet.getString( "domain" );
303 cb.active = resultSet.getString( "active" ).charAt( 0 );
304 cb.creationDate.setTime( resultSet.getTimestamp( "creation_date" ).getTime() );
305 cb.activationDate.setTime( resultSet.getTimestamp( "activation_date" ).getTime() );
306 cb.expirationDate.setTime( resultSet.getTimestamp( "expiration_date" ).getTime() );
307 cb.customerTypeId = resultSet.getInt( "customer_type_id" );
308 }
309 finally
310 {
311 try
312 {
313 if ( resultSet != null ) resultSet.close();
314 }
315 catch ( SQLException sex )
316 {
317 Logger.error( "Error closing resultSet in CustomerHelper.selectCustomers for customer_id " + cb.getCustomerId() + ".", sex );
318 }
319
320 try
321 {
322 if ( statement != null ) statement.close();
323 }
324 catch ( SQLException sex )
325 {
326 Logger.error( "Error closing statement in CustomerHelper.selectCustomers for customer_id " + cb.getCustomerId() + ".", sex );
327 }
328
329 try
330 {
331 if ( connection != null ) connection.close();
332 }
333 catch ( SQLException sex )
334 {
335 Logger.error( "Error closing connection in CustomerHelper.selectCustomers for customer_id " + cb.getCustomerId() + ".", sex );
336 }
337 }
338 }
339
340 /**
341 * Insert a new record into the <code>customers</code> table
342 * with the values specified.
343 *
344 * @param customerId - The primary key values to use. If this
345 * is <code>0</code>, then the next value from the {@link
346 * #CUSTOMER_ID_SEQUENCE} is used.
347 * @param username - The <code>username</code> value.
348 * @param password - The <code>password</code> value.
349 * @param email - The <code>email</code> value.
350 * @param domain - The <code>domain</code> value.
351 * @param active - The <code>active</code> value.
352 * @param activationDate - The <code>activation_date</code> value.
353 * @param expirationDate - The <code>expiration_date</code> value.
354 * @param customerTypeId - The <code>customer_type_id</code>
355 * foreign key value.
356 * @throws SQLException - If database errors are encountered while
357 * writing to the database.
358 * @throws NamingException - If errors are encountered while getting
359 * a connection from the connection pool.
360 */
361 static final int insert( int customerId, String username,
362 String password, String email, String domain, char active,
363 Date activationDate, Date expirationDate, int customerTypeId )
364 throws SQLException, NamingException
365 {
366 Connection connection = null;
367 PreparedStatement statement = null;
368
369 try
370 {
371 connection = DatabaseHelper.getConnection();
372
373 if ( customerId == 0 )
374 {
375 customerId = DatabaseHelper.getNextSequenceValue( CustomerHelper.CUSTOMER_ID_SEQUENCE );
376 }
377
378 statement = connection.prepareStatement( CustomerHelper.INSERT_CUSTOMERS );
379 statement.setInt( 1, customerId );
380 statement.setString( 2, username );
381 statement.setString( 3, password );
382 statement.setString( 4, email );
383 statement.setString( 5, domain );
384 statement.setString( 6, String.valueOf( active ) );
385 statement.setTimestamp( 7, new Timestamp( activationDate.getTime() ) );
386 statement.setTimestamp( 8, new Timestamp( expirationDate.getTime() ) );
387 statement.setInt( 9, customerTypeId );
388 statement.executeUpdate();
389 }
390 finally
391 {
392 try
393 {
394 if ( statement != null ) statement.close();
395 }
396 catch ( SQLException sex )
397 {
398 Logger.error( "Error closing statement in CustomerHelper.insertCustomers for customer_id " + customerId + ".", sex );
399 }
400
401 try
402 {
403 if ( connection != null ) connection.close();
404 }
405 catch ( SQLException sex )
406 {
407 Logger.error( "Error closing connection in CustomerHelper.insertCustomers for customer_id " + customerId + ".", sex );
408 }
409 }
410
411 return customerId;
412 }
413
414 /**
415 * Update the values in the <code>customers</code> table with
416 * the specified values.
417 *
418 * @param cb - The java bean that represents a
419 * record in the database.
420 * @throws SQLException - If database errors are encountered while
421 * writing to the database.
422 * @throws NamingException - If errors are encountered while getting
423 * a connection from the connection pool.
424 */
425 static final void update( CustomerBean cb )
426 throws SQLException, NamingException
427 {
428 Connection connection = null;
429 PreparedStatement statement = null;
430
431 try
432 {
433 connection = DatabaseHelper.getConnection();
434 statement = connection.prepareStatement( CustomerHelper.UPDATE_CUSTOMERS );
435 statement.setString( 1, cb.username );
436 statement.setString( 2, cb.password );
437 statement.setString( 3, cb.email );
438 statement.setString( 4, cb.domain );
439 statement.setString( 5, String.valueOf( cb.active ) );
440 statement.setTimestamp( 6, new Timestamp( cb.activationDate.getTime() ) );
441 statement.setTimestamp( 7, new Timestamp( cb.expirationDate.getTime() ) );
442 statement.setInt( 8, cb.customerTypeId );
443 statement.setInt( 9, cb.getCustomerId() );
444 statement.executeUpdate();
445 }
446 finally
447 {
448 try
449 {
450 if ( statement != null ) statement.close();
451 }
452 catch ( SQLException sex )
453 {
454 Logger.error( "Error closing statement in CustomerHelper.updateCustomers for customer_id " + cb.getCustomerId() + ".", sex );
455 }
456
457 try
458 {
459 if ( connection != null ) connection.close();
460 }
461 catch ( SQLException sex )
462 {
463 Logger.error( "Error closing connection in CustomerHelper.updateCustomers for customer_id " + cb.getCustomerId() + ".", sex );
464 }
465 }
466 }
467
468 /**
469 * Delete the record from the database identified by the primary key
470 * value specified.
471 *
472 * @param customerId - The <code>customer_id</code>
473 * primary key value.
474 * @throws SQLException - If database errors are encountered while
475 * writing to the database.
476 * @throws NamingException - If errors are encountered while getting
477 * a connection from the connection pool.
478 */
479 static final void delete( int customerId )
480 throws SQLException, NamingException
481 {
482 Connection connection = null;
483 PreparedStatement statement = null;
484
485 try
486 {
487 connection = DatabaseHelper.getConnection();
488 statement = connection.prepareStatement( CustomerHelper.DELETE_CUSTOMERS );
489 statement.setInt( 1, customerId );
490 statement.executeUpdate();
491 }
492 finally
493 {
494 try
495 {
496 if ( statement != null ) statement.close();
497 }
498 catch ( SQLException sex )
499 {
500 Logger.error( "Error closing statement in CustomerHelper.delete for customer_id " + customerId + ".", sex );
501 }
502
503 try
504 {
505 if ( connection != null ) connection.close();
506 }
507 catch ( SQLException sex )
508 {
509 Logger.error( "Error closing connection in CustomerHelper.delete for customer_id " + customerId + ".", sex );
510 }
511 }
512 }
513
514 /**
515 * Default constructor. Cannot be instantiated.
516 */
517 private CustomerHelper() {}
518 }