Source code: com/clra/member/MemberDBRead.java
1 /*
2 * Copyright (c) Carnegie Lake Rowing Association 2002. All rights reserved.
3 * Distributed under the GPL license. See doc/COPYING.
4 * $RCSfile: MemberDBRead.java,v $
5 * $Date: 2003/03/10 05:29:28 $
6 * $Revision: 1.11 $
7 */
8
9 package com.clra.member;
10
11 import com.clra.member.Address;
12 import com.clra.member.Configuration;
13 import com.clra.member.MemberException;
14 import com.clra.member.MemberName;
15 import com.clra.member.MemberSnapshot;
16 import com.clra.member.Telephone;
17 import com.clra.util.DBConfiguration;
18 import java.sql.Connection;
19 import java.sql.PreparedStatement;
20 import java.sql.ResultSet;
21 import java.sql.SQLException;
22 import java.text.ParsePosition;
23 import java.text.SimpleDateFormat;
24 import java.util.ArrayList;
25 import java.util.Calendar;
26 import java.util.Collection;
27 import java.util.Date;
28 import java.util.GregorianCalendar;
29 import java.util.HashMap;
30 import java.util.HashSet;
31 import java.util.Iterator;
32 import java.util.Map;
33 import java.util.Set;
34 import javax.ejb.EJBException;
35 import javax.ejb.NoSuchEntityException;
36 import org.apache.log4j.Category;
37
38 /**
39 * This utility class defines common routines for reading snapshots
40 * from the database. In cases where read-only lists are presented
41 * to a user, these routines are faster than their ejbFind counterparts.
42 * However, data should never be written directly back to the database,
43 * otherwise in-memory caches maintained by EJB's will be out of synch
44 * and data will be corrupted.<p>
45 *
46 * @version $Revision: 1.11 $ $Date: 2003/03/10 05:29:28 $
47 * @author <a href="mailto:rphall@pluto.njcc.com">Rick Hall</a>
48 */
49 public final class MemberDBRead {
50
51 private final static String base = MemberDBRead.class.getName();
52 private final static Category theLog = Category.getInstance( base );
53
54 /** All utilities are static */
55 private MemberDBRead() {}
56
57 /** The format used to get a date out of the data base */
58 private final static SimpleDateFormat sdfDBDate =
59 new SimpleDateFormat( "MM/dd/yyyy" );
60
61 /** The format used to get a time out of the data base */
62 private final static SimpleDateFormat sdfDBTime =
63 new SimpleDateFormat( "hh:mm a" );
64
65 // /** The format used to convert a date/time String to a Date */
66 // private final static SimpleDateFormat sdfConvert =
67 // new SimpleDateFormat( "MM/dd/yyyy hh:mm a" );
68
69 /**
70 * Finds all active members of the CLRA. Members are sorted by
71 * lastname-firstname.
72 */
73 public static Collection findAllMembersByLastName()
74 throws MemberException {
75
76 ArrayList list = new ArrayList();
77
78 Connection conn = null;
79 PreparedStatement stmt = null;
80 Collection retVal = null;
81 try {
82 conn = DBConfiguration.getConnection();
83 stmt = conn.prepareStatement(
84 Configuration.SQL_01,
85 ResultSet.TYPE_FORWARD_ONLY,
86 ResultSet.CONCUR_READ_ONLY);
87 retVal = loadMemberCollection( stmt );
88 }
89 catch(SQLException x) {
90 String msg = "SQLException: " + x.getMessage();
91 theLog.fatal( msg, x );
92 throw new MemberException( msg );
93 }
94 finally {
95 DBConfiguration.closeSQLStatement( stmt );
96 DBConfiguration.closeSQLConnection( conn );
97 }
98 conn = null;
99 stmt = null;
100
101 return retVal;
102 } // findAllMembersByLastName()
103
104 /**
105 * Finds all active members with last names greater than
106 * or equal to the specified String. Selection is case-insensitive.
107 * Members are sorted by lastname-firstname.
108 */
109 public static Collection findMembersWithLastNamesGTE( String lower )
110 throws MemberException {
111
112 ArrayList list = new ArrayList();
113
114 Connection conn = null;
115 PreparedStatement stmt = null;
116 Collection retVal = null;
117 try {
118 conn = DBConfiguration.getConnection();
119 stmt = conn.prepareStatement(
120 Configuration.SQL_02,
121 ResultSet.TYPE_FORWARD_ONLY,
122 ResultSet.CONCUR_READ_ONLY);
123 stmt.setString( 1, lower );
124 retVal = loadMemberCollection( stmt );
125 }
126 catch(SQLException x) {
127 String msg = "SQLException: " + x.getMessage();
128 theLog.fatal( msg, x );
129 throw new MemberException( msg );
130 }
131 finally {
132 DBConfiguration.closeSQLStatement( stmt );
133 DBConfiguration.closeSQLConnection( conn );
134 }
135 conn = null;
136 stmt = null;
137
138 return retVal;
139 } // findMembersWithLastNamesGTE(String)
140
141 /**
142 * Finds all active members with last names less than
143 * the specified String. Selection is case-insensitive. Members are
144 * sorted by lastname-firstname.
145 */
146 public static Collection findMembersWithLastNamesLT( String upper )
147 throws MemberException {
148
149 ArrayList list = new ArrayList();
150
151 Connection conn = null;
152 PreparedStatement stmt = null;
153 Collection retVal = null;
154 try {
155 conn = DBConfiguration.getConnection();
156 stmt = conn.prepareStatement(
157 Configuration.SQL_03,
158 ResultSet.TYPE_FORWARD_ONLY,
159 ResultSet.CONCUR_READ_ONLY);
160 stmt.setString( 1, upper );
161 retVal = loadMemberCollection( stmt );
162 }
163 catch(SQLException x) {
164 String msg = "SQLException: " + x.getMessage();
165 theLog.fatal( msg, x );
166 throw new MemberException( msg );
167 }
168 finally {
169 DBConfiguration.closeSQLStatement( stmt );
170 DBConfiguration.closeSQLConnection( conn );
171 }
172 conn = null;
173 stmt = null;
174
175 return retVal;
176 } // findMembersWithLastNamesLT(String)
177
178 /**
179 * Finds all active members with last names greater than
180 * or equal to the specified lower bound, and less than the specified
181 * upper bound. Selection is case-insensitive. Members are sorted by
182 * lastname-firstname.
183 */
184 public static
185 Collection findMembersWithLastNamesInRange( String lower, String upper )
186 throws MemberException {
187
188 ArrayList list = new ArrayList();
189
190 Connection conn = null;
191 PreparedStatement stmt = null;
192 Collection retVal = null;
193 try {
194 conn = DBConfiguration.getConnection();
195 stmt = conn.prepareStatement(
196 Configuration.SQL_04,
197 ResultSet.TYPE_FORWARD_ONLY,
198 ResultSet.CONCUR_READ_ONLY);
199 stmt.setString( 1, lower );
200 stmt.setString( 2, upper );
201 retVal = loadMemberCollection( stmt );
202 }
203 catch(SQLException x) {
204 String msg = "SQLException: " + x.getMessage();
205 theLog.fatal( msg, x );
206 throw new MemberException( msg );
207 }
208 finally {
209 DBConfiguration.closeSQLStatement( stmt );
210 DBConfiguration.closeSQLConnection( conn );
211 }
212 conn = null;
213 stmt = null;
214
215 return retVal;
216 } // findMembersWithLastNamesInRange(String,String)
217
218 /**
219 * Finds the member that has the given account name. Account names
220 * are unique, so this "finder" returns at most one member.
221 */
222 public static MemberSnapshot findMemberByAccountName( String accountName )
223 throws MemberException, NoSuchEntityException {
224
225 Connection conn = null;
226 PreparedStatement stmt = null;
227
228 MemberSnapshot retVal = null;
229 try {
230 conn = DBConfiguration.getConnection();
231 stmt = conn.prepareStatement(
232 Configuration.SQL_05,
233 ResultSet.TYPE_FORWARD_ONLY,
234 ResultSet.CONCUR_READ_ONLY);
235 stmt.setString( 1, accountName );
236 retVal = loadMember( stmt );
237 }
238 catch(SQLException x) {
239 String msg = "SQLException for accountName == '" + accountName + "': "
240 + x.getMessage();
241 theLog.fatal( msg, x );
242 throw new MemberException( msg );
243 }
244 finally {
245 DBConfiguration.closeSQLStatement( stmt );
246 DBConfiguration.closeSQLConnection( conn );
247 }
248 conn = null;
249 stmt = null;
250
251 return retVal;
252 } // findMemberByAccountName(String)
253
254 /**
255 * An alias for ${link #loadMember(Integer) loadMember(Integer)}.
256 * @param a non-null key for an entry in the Member table.
257 */
258 public static MemberSnapshot findMemberByMemberId( Integer memberId )
259 throws MemberException, NoSuchEntityException {
260 return loadMember(memberId);
261 }
262
263 /**
264 * Finds the member that has the given member id. Member id's
265 * are unique, so this "finder" returns at most one snapshot.
266 */
267 public static MemberSnapshot loadMember( Integer memberId )
268 throws MemberException, NoSuchEntityException {
269
270 Connection conn = null;
271 PreparedStatement stmt = null;
272
273 MemberSnapshot retVal = null;
274 try {
275 conn = DBConfiguration.getConnection();
276 stmt = conn.prepareStatement(
277 Configuration.SQL_08,
278 ResultSet.TYPE_FORWARD_ONLY,
279 ResultSet.CONCUR_READ_ONLY);
280 stmt.setInt( 1, memberId.intValue() );
281 retVal = loadMember( stmt );
282 }
283 catch(SQLException x) {
284 String msg = "SQLException for memberId == '" + memberId + "': "
285 + x.getMessage();
286 theLog.fatal( msg, x );
287 throw new MemberException( msg );
288 }
289 finally {
290 DBConfiguration.closeSQLStatement( stmt );
291 DBConfiguration.closeSQLConnection( conn );
292 }
293 conn = null;
294 stmt = null;
295
296 return retVal;
297 } // loadMember(Integer)
298
299 static MemberSnapshot loadMember( PreparedStatement stmt )
300 throws SQLException, MemberException, NoSuchEntityException, EJBException {
301
302 // Precondition
303 if ( stmt == null ) {
304 throw new IllegalArgumentException( "null stmt" );
305 }
306
307 MemberSnapshot retVal = null;
308 int rowCount = 0;
309 Iterator iter = loadMemberCollection( stmt ).iterator();
310 while ( iter.hasNext() && rowCount < 2 ) {
311 ++rowCount;
312 retVal = (MemberSnapshot) iter.next();
313 }
314
315 // Postconditions
316 if ( rowCount == 0 ) {
317 throw new NoSuchEntityException( "no results matching statement" );
318 }
319 else if ( rowCount > 1 ) {
320 throw new EJBException( "2 or more results matching statement" );
321 }
322
323 return retVal;
324 } // loadMember(PreparedStatement);
325
326 public static Collection loadMemberCollection( PreparedStatement stmt )
327 throws SQLException, MemberException, NoSuchEntityException, EJBException
328 {
329
330 // Precondition
331 if ( stmt == null ) {
332 throw new IllegalArgumentException( "null stmt" );
333 }
334
335 final Collection retVal = new ArrayList();
336
337 ResultSet rs = null;
338 try {
339 rs = stmt.executeQuery();
340 while ( rs.next() ) {
341 MemberSnapshot ms = mapResultSetToMemberSnapshot(rs);
342 ms.setMemberRoles(loadMemberRoles(ms.getId().intValue()));
343 retVal.add( ms );
344 } // while
345 }
346 finally {
347 DBConfiguration.closeSQLResultSet( rs );
348 rs = null;
349 }
350
351 return retVal;
352 } // loadMemberCollection(PreparedStatement)
353
354 public static MemberRole[] loadMemberRoles(int member_id)
355 throws MemberException {
356
357 Connection conn = null;
358 PreparedStatement stmt = null;
359 Set set = null;
360 try {
361 conn = DBConfiguration.getConnection();
362 stmt = conn.prepareStatement(
363 Configuration.SQL_07,
364 ResultSet.TYPE_FORWARD_ONLY,
365 ResultSet.CONCUR_READ_ONLY);
366 stmt.setInt( 1, (int)member_id );
367 set = loadMemberRolesSet( stmt );
368 }
369 catch(SQLException x) {
370 String msg = "SQLException: " + x.getMessage();
371 theLog.fatal( msg, x );
372 throw new MemberException( msg );
373 }
374 finally {
375 DBConfiguration.closeSQLStatement( stmt );
376 DBConfiguration.closeSQLConnection( conn );
377 conn = null;
378 stmt = null;
379 }
380
381 MemberRole[] retVal = (MemberRole[]) set.toArray( new MemberRole[0] );
382
383 return retVal;
384 } // loadMemberRoles
385
386 public static HashSet loadMemberRolesSet( PreparedStatement stmt )
387 throws SQLException, MemberException, NoSuchEntityException, EJBException
388 {
389 // Precondition
390 if ( stmt == null ) {
391 throw new IllegalArgumentException( "null stmt" );
392 }
393
394 final HashSet retVal = new HashSet();
395
396 ResultSet rs = null;
397 try {
398 rs = stmt.executeQuery();
399 while ( rs.next() ) {
400 String strRole = rs.getString( "role" );
401 MemberRole role = new MemberRole(strRole);
402 retVal.add( role );
403 } // while
404 }
405 finally {
406 DBConfiguration.closeSQLResultSet( rs );
407 rs = null;
408 }
409
410 return retVal;
411 } // loadMemberRolesSet(PreparedStatement)
412
413 public static
414 MemberSnapshot mapResultSetToMemberSnapshot(ResultSet rs)
415 throws SQLException, MemberException {
416
417 final int member_id = rs.getInt( "member_id" );
418 final int clra_year = rs.getInt( "clra_year" );
419 final Date birthday = rs.getDate( "birthday" );
420 final String account_name = rs.getString( "account_name" );
421 final String account_passwd = rs.getString( "account_passwd" );
422 final String clra_status = rs.getString( "clra_status" );
423 final String name_last = rs.getString( "name_last" );
424 final String name_first = rs.getString( "name_first" );
425 final String name_middle = rs.getString( "name_middle" );
426 final String name_suffix = rs.getString( "name_suffix" );
427 final String strEmail = rs.getString( "email" );
428 final String telephone_evening = rs.getString( "telephone_evening" );
429 final String telephone_day = rs.getString( "telephone_day" );
430 final String telephone_other = rs.getString( "telephone_other" );
431 final String address_street1 = rs.getString( "address_street1" );
432 final String address_street2 = rs.getString( "address_street2" );
433 final String address_city = rs.getString( "address_city" );
434 final String address_state = rs.getString( "address_state" );
435 final String address_zip = rs.getString( "address_zip" );
436
437 if ( theLog.isDebugEnabled() ) {
438 theLog.debug( member_id + ", " + name_last + ", " + name_first );
439 }
440
441 MemberSnapshot retVal = null;
442 try {
443
444 Integer id = new Integer( member_id );
445
446 AccountType accountType = new AccountType( clra_status );
447
448 MemberName memberName =
449 new MemberName(name_first, name_middle, name_last, name_suffix);
450
451 Email email = null;
452 if ( strEmail != null && strEmail.trim().length() > 0 ) {
453 email = new Email(strEmail);
454 }
455
456 Map telephoneNumbers = new HashMap();
457 if (telephone_evening!=null && telephone_evening.trim().length()!=0) {
458 Telephone telephone = new Telephone(telephone_evening);
459 telephoneNumbers.put( Telephone.EVENING, telephone );
460 }
461 if ( telephone_day != null && telephone_day.trim().length() != 0 ) {
462 Telephone telephone = new Telephone(telephone_day);
463 telephoneNumbers.put( Telephone.DAY, telephone );
464 }
465 if ( telephone_other!=null && telephone_other.trim().length()!=0 ) {
466 Telephone telephone = new Telephone(telephone_other);
467 telephoneNumbers.put( Telephone.OTHER, telephone );
468 }
469
470 Address address = new Address( address_street1, address_street2,
471 address_city, address_state, address_zip );
472
473 final int JANUARY = 0;
474 Calendar c = new GregorianCalendar();
475 c.set( clra_year, JANUARY, 1);
476 Date accountYear = c.getTime();
477
478 retVal = new MemberSnapshot( id, account_name, account_passwd,
479 accountType, memberName, email, telephoneNumbers, address,
480 accountYear, birthday );
481
482 }
483 catch( IllegalArgumentException x ) {
484 String msg = "invalid data for id == " +member_id+ ": " + x.getMessage();
485 theLog.error( msg, x );
486 throw new MemberException( msg );
487 }
488
489 return retVal;
490 } // mapResultSetToMemberSnapshot(ResultSet)
491
492 } // MemberDBRead
493
494 /*
495 * $Log: MemberDBRead.java,v $
496 * Revision 1.11 2003/03/10 05:29:28 rphall
497 * Fixed bug caused by new exception message
498 *
499 * Revision 1.10 2003/03/10 05:25:36 rphall
500 * Added id to exception message
501 *
502 * Revision 1.9 2003/02/26 03:38:45 rphall
503 * Added copyright and GPL license
504 *
505 * Revision 1.8 2003/02/20 04:44:38 rphall
506 * Switched from String values to Object values: Email, MemberRole, etc
507 *
508 * Revision 1.7 2003/02/19 22:08:36 rphall
509 * Removed gratuitous use of CLRA acronym
510 *
511 * Revision 1.6 2003/02/18 04:19:14 rphall
512 * Removed ValidationException
513 *
514 * Revision 1.5 2003/02/15 04:31:41 rphall
515 * Changes connected to major revision of MemberBean
516 *
517 */
518