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>partners</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 2004, Rakesh Vidyadharan and wedoit4u.biz</p>
023 *
024 * @author Rakesh Vidyadharan 1<sup><small>st</small></sup> January 2004
025 *
026 * @version $Id: PartnerHelper.java,v 1.3 2004/05/26 11:42:32 rakesh Exp $
027 */
028 final class PartnerHelper extends Object
029 {
030 /**
031 * The <code>sequence</code> set in the database for the <code>
032 * partner_id primary key</code> column.
033 */
034 static final String PARTNER_ID_SEQUENCE = "sequence_partner_id";
035
036 /**
037 * The statement that is executed to fetch all the records from the
038 * <code>partners</code> table.
039 */
040 static final String ALL_PARTNERS = "select partner_id from partners";
041
042 /**
043 * The statement that is executed to fetch the matching record from
044 * the <code>partner</code> table for the specified <code>username
045 * </code> value.
046 */
047 static final String FIND_BY_USERNAME_AND_PASSWORD = "select partner_id from partners where username = ? and password = ?";
048
049 /**
050 * The statement that is executed to insert a new record into the
051 * <code>partners</code> table.
052 */
053 static final String INSERT_PARTNERS = "insert into partners cols( partner_id, username, password, active, expiration_date, partner_name, description, email, telephone, mobile_phone ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
054
055 /**
056 * The statement that is executed to fetch a specific record from the
057 * <code>partners</code> table.
058 */
059 static final String SELECT_PARTNERS = "select * from partners where partner_id = ?";
060
061 /**
062 * The update statement to be executed to update a record in the
063 * <code>partners</code> table.
064 */
065 static final String UPDATE_PARTNERS = "update partners set username = ?, password = ? active = ?, expiration_date = ?, partner_name = ?, description = ?, email = ?, telephone = ?, mobile_phone = ? where partner_id = ?";
066
067 /**
068 * The delete statement to be executed to delete a record in the
069 * <code>partners</code> table identified by the primary key value.
070 */
071 static final String DELETE_PARTNERS = "delete from partners where partner_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>partners</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( PartnerHelper.ALL_PARTNERS );
095 resultSet = statement.executeQuery();
096
097 while ( resultSet.next() )
098 {
099 list.add( new Integer( resultSet.getInt( "partner_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 PartnerHelper.getAllPartnerIds.", 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 PartnerHelper.getAllPartnerIds.", 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 PartnerHelper.getAllPartnerIds.", sex );
129 }
130 }
131
132 return list;
133 }
134
135 /**
136 * Select the record from the <code>partners</code> table that has
137 * the specified <code>username</code> and <code>password</code>
138 * values.
139 *
140 * @param username - The <code>username</code> value.
141 * @param password - The <code>username</code> value.
142 * @return int - The partner_id value.
143 * @throws SQLException - If errors are encountered while fetching
144 * the record from the database.
145 * @throws NamingException - If errors are encountered while getting
146 * a connection from the connection pool.
147 */
148 static final int findByUsernameAndPassword( String username,
149 String password ) throws SQLException, NamingException
150 {
151 int partnerId = 0;
152 Connection connection = null;
153 PreparedStatement statement = null;
154 ResultSet resultSet = null;
155
156 try
157 {
158 connection = DatabaseHelper.getConnection();
159 statement = connection.prepareStatement( PartnerHelper.FIND_BY_USERNAME_AND_PASSWORD );
160 statement.setString( 1, username );
161 statement.setString( 2, password );
162 resultSet = statement.executeQuery();
163
164 resultSet.next();
165 partnerId = resultSet.getInt( "partner_id" );
166 }
167 finally
168 {
169 try
170 {
171 if ( resultSet != null ) resultSet.close();
172 }
173 catch ( SQLException sex )
174 {
175 Logger.error( "Error closing resultSet in PartnerHelper.findByUsername for username " + username + ".", sex );
176 }
177
178 try
179 {
180 if ( statement != null ) statement.close();
181 }
182 catch ( SQLException sex )
183 {
184 Logger.error( "Error closing statement in PartnerHelper.findByUsername for username " + username + ".", sex );
185 }
186
187 try
188 {
189 if ( connection != null ) connection.close();
190 }
191 catch ( SQLException sex )
192 {
193 Logger.error( "Error closing connection in PartnerHelper.findByUsername for username " + username + ".", sex );
194 }
195 }
196
197 return partnerId;
198 }
199
200 /**
201 * Select the specified record from the <code>partners</code>
202 * table. The values selected from the database are used to set the
203 * values of the bean fields.
204 *
205 * @param pb - The java bean instance that represents the
206 * record. The primary key value to use to look up the record is
207 * obtained from this bean.
208 * @throws SQLException - If errors are encountered while fetching
209 * the record from the database.
210 * @throws NamingException - If errors are encountered while getting
211 * a connection from the connection pool.
212 */
213 static final void select( PartnerBean pb )
214 throws SQLException, NamingException
215 {
216 Connection connection = null;
217 PreparedStatement statement = null;
218 ResultSet resultSet = null;
219
220 try
221 {
222 connection = DatabaseHelper.getConnection();
223 statement = connection.prepareStatement( PartnerHelper.SELECT_PARTNERS );
224 statement.setInt( 1, pb.getPartnerId() );
225 resultSet = statement.executeQuery();
226 resultSet.next();
227 pb.username = resultSet.getString( "username" );
228 pb.password = resultSet.getString( "password" );
229 pb.active = resultSet.getString( "active" ).charAt( 0 );
230 pb.creationDate.setTime( resultSet.getTimestamp( "creation_date" ).getTime() );
231 pb.expirationDate.setTime( resultSet.getTimestamp( "expiration_date" ).getTime() );
232 pb.partnerName = resultSet.getString( "partner_name" );
233 pb.description = resultSet.getString( "description" );
234 pb.email = resultSet.getString( "email" );
235 pb.telephone = resultSet.getString( "telephone" );
236 pb.mobilePhone = resultSet.getString( "mobile_phone" );
237 }
238 finally
239 {
240 try
241 {
242 if ( resultSet != null ) resultSet.close();
243 }
244 catch ( SQLException sex )
245 {
246 Logger.error( "Error closing resultSet in PartnerHelper.selectPartners for partner_id " + pb.getPartnerId() + ".", sex );
247 }
248
249 try
250 {
251 if ( statement != null ) statement.close();
252 }
253 catch ( SQLException sex )
254 {
255 Logger.error( "Error closing statement in PartnerHelper.selectPartners for partner_id " + pb.getPartnerId() + ".", sex );
256 }
257
258 try
259 {
260 if ( connection != null ) connection.close();
261 }
262 catch ( SQLException sex )
263 {
264 Logger.error( "Error closing connection in PartnerHelper.selectPartners for partner_id " + pb.getPartnerId() + ".", sex );
265 }
266 }
267 }
268
269 /**
270 * Insert a new record into the <code>partners</code> table
271 * with the values specified.
272 *
273 * @param partnerId - The primary key values to use. If this
274 * is <code>0</code>, then the next value from the {@link
275 * #PARTNER_ID_SEQUENCE} is used.
276 * @param username - The <code>username</code> value.
277 * @param password - The <code>password</code> value.
278 * @param active - The <code>active</code> value.
279 * @param expirationDate - The <code>expiration_date</code> value.
280 * @param partnerName - The <code>partner_name</code> value.
281 * @param description - The <code>description</code> value.
282 * @param email - The <code>email</code> value.
283 * @param telephone - The <code>telephone</code> value.
284 * @param mobilePhone - The <code>mobile_phone</code> value.
285 * @throws SQLException - If database errors are encountered while
286 * writing to the database.
287 * @throws NamingException - If errors are encountered while getting
288 * a connection from the connection pool.
289 */
290 static final int insert( int partnerId, String username,
291 String password, char active, Date expirationDate,
292 String partnerName, String description, String email,
293 String telephone, String mobilePhone )
294 throws SQLException, NamingException
295 {
296 Connection connection = null;
297 PreparedStatement statement = null;
298
299 try
300 {
301 connection = DatabaseHelper.getConnection();
302
303 if ( partnerId == 0 )
304 {
305 partnerId = DatabaseHelper.getNextSequenceValue( PartnerHelper.PARTNER_ID_SEQUENCE );
306 }
307
308 statement = connection.prepareStatement( PartnerHelper.INSERT_PARTNERS );
309 statement.setInt( 1, partnerId );
310 statement.setString( 2, username );
311 statement.setString( 3, password );
312 statement.setString( 4, String.valueOf( active ) );
313 statement.setTimestamp( 5, new Timestamp( expirationDate.getTime() ) );
314 statement.setString( 6, partnerName );
315 statement.setString( 7, description );
316 statement.setString( 8, email );
317 statement.setString( 9, telephone );
318 statement.setString( 10, mobilePhone );
319 statement.executeUpdate();
320 }
321 finally
322 {
323 try
324 {
325 if ( statement != null ) statement.close();
326 }
327 catch ( SQLException sex )
328 {
329 Logger.error( "Error closing statement in PartnerHelper.insertPartners for partner_id " + partnerId + ".", sex );
330 }
331
332 try
333 {
334 if ( connection != null ) connection.close();
335 }
336 catch ( SQLException sex )
337 {
338 Logger.error( "Error closing connection in PartnerHelper.insertPartners for partner_id " + partnerId + ".", sex );
339 }
340 }
341
342 return partnerId;
343 }
344
345 /**
346 * Update the values in the <code>partners</code> table with
347 * the specified values.
348 *
349 * @param pb - The java bean that represents a
350 * record in the database.
351 * @throws SQLException - If database errors are encountered while
352 * writing to the database.
353 * @throws NamingException - If errors are encountered while getting
354 * a connection from the connection pool.
355 */
356 static final void update( PartnerBean pb )
357 throws SQLException, NamingException
358 {
359 Connection connection = null;
360 PreparedStatement statement = null;
361
362 try
363 {
364 connection = DatabaseHelper.getConnection();
365 statement = connection.prepareStatement( PartnerHelper.UPDATE_PARTNERS );
366 statement.setString( 1, pb.username );
367 statement.setString( 2, pb.password );
368 statement.setString( 3, String.valueOf( pb.active ) );
369 statement.setTimestamp( 4, new Timestamp( pb.expirationDate.getTime() ) );
370 statement.setString( 5, pb.partnerName );
371 statement.setString( 6, pb.description );
372 statement.setString( 7, pb.email );
373 statement.setString( 8, pb.telephone );
374 statement.setString( 9, pb.mobilePhone );
375 statement.setInt( 10, pb.getPartnerId() );
376 statement.executeUpdate();
377 }
378 finally
379 {
380 try
381 {
382 if ( statement != null ) statement.close();
383 }
384 catch ( SQLException sex )
385 {
386 Logger.error( "Error closing statement in PartnerHelper.updatePartners for partner_id " + pb.getPartnerId() + ".", sex );
387 }
388
389 try
390 {
391 if ( connection != null ) connection.close();
392 }
393 catch ( SQLException sex )
394 {
395 Logger.error( "Error closing connection in PartnerHelper.updatePartners for partner_id " + pb.getPartnerId() + ".", sex );
396 }
397 }
398 }
399
400 /**
401 * Delete the record from the database identified by the primary key
402 * value specified.
403 *
404 * @param partnerId - The <code>partner_id</code>
405 * primary key value.
406 * @throws SQLException - If database errors are encountered while
407 * writing to the database.
408 * @throws NamingException - If errors are encountered while getting
409 * a connection from the connection pool.
410 */
411 static final void delete( int partnerId )
412 throws SQLException, NamingException
413 {
414 Connection connection = null;
415 PreparedStatement statement = null;
416
417 try
418 {
419 connection = DatabaseHelper.getConnection();
420 statement = connection.prepareStatement( PartnerHelper.DELETE_PARTNERS );
421 statement.setInt( 1, partnerId );
422 statement.executeUpdate();
423 }
424 finally
425 {
426 try
427 {
428 if ( statement != null ) statement.close();
429 }
430 catch ( SQLException sex )
431 {
432 Logger.error( "Error closing statement in PartnerHelper.delete for partner_id " + partnerId + ".", sex );
433 }
434
435 try
436 {
437 if ( connection != null ) connection.close();
438 }
439 catch ( SQLException sex )
440 {
441 Logger.error( "Error closing connection in PartnerHelper.delete for partner_id " + partnerId + ".", sex );
442 }
443 }
444 }
445
446 /**
447 * Default constructor. Cannot be instantiated.
448 */
449 private PartnerHelper() {}
450 }