Source code: com/clra/rowing/RowingDBRead.java
1 /*
2 * Copyright (c) Carnegie Lake Rowing Association 2002. All rights reserved.
3 * Distributed under the GPL license. See doc/COPYING.
4 * $RCSfile: RowingDBRead.java,v $
5 * $Date: 2003/02/26 03:38:45 $
6 * $Revision: 1.3 $
7 */
8
9 package com.clra.rowing;
10
11 import com.clra.member.MemberName;
12 import com.clra.rowing.Configuration;
13 import com.clra.rowing.DefaultRowingSessionComparator;
14 import com.clra.rowing.RowingException;
15 import com.clra.rowing.RowingSessionStateException;
16 import com.clra.rowing.RowingSessionLevel;
17 import com.clra.rowing.RowingSessionState;
18 import com.clra.rowing.RowingSessionType;
19 import com.clra.rowing.RowingSessionSnapshot;
20 import com.clra.util.ISerializableComparator;
21 import com.clra.util.DBConfiguration;
22 import com.clra.util.ErrorUtils;
23 import java.rmi.RemoteException;
24 import java.sql.Connection;
25 import java.sql.PreparedStatement;
26 import java.sql.ResultSet;
27 import java.sql.SQLException;
28 import java.text.ParsePosition;
29 import java.text.SimpleDateFormat;
30 import java.util.ArrayList;
31 import java.util.Collection;
32 import java.util.Date;
33 import java.util.HashMap;
34 import java.util.Iterator;
35 import java.util.List;
36 import java.util.Map;
37 import java.util.Set;
38 import javax.ejb.EJBException;
39 import javax.ejb.NoSuchEntityException;
40 import org.apache.log4j.Category;
41
42 /**
43 * This utility class defines common routines for reading snapshots
44 * from the database. In cases where read-only lists are presented
45 * to a user, these routines are faster than their ejbFind counterparts.
46 * However, data should never be written directly back to the database,
47 * otherwise in-memory caches maintained by EJB's will be out of synch
48 * and data will be corrupted. The class RowingUtils defines utilities
49 * with similar signatures that are implemented by calls to EJB's. These
50 * operations are appropriate if objects should be cached in memory
51 * because they will be modified shortly.</p><p>
52 *
53 * @version $Revision: 1.3 $ $Date: 2003/02/26 03:38:45 $
54 * @author <a href="mailto:rphall@pluto.njcc.com">Rick Hall</a>
55 */
56 public final class RowingDBRead {
57
58 private final static String base = RowingDBRead.class.getName();
59 private final static Category theLog = Category.getInstance( base );
60
61 /** All utilities are static */
62 private RowingDBRead() {}
63
64 /** Labels a null integer in the database, per JDBC */
65 public final static int NULL_INT = 0;
66
67 /** The format used to specify a date and time to the database */
68 public final static SimpleDateFormat dateFormat =
69 new SimpleDateFormat( Configuration.SQL_DATE_FORMAT );
70
71 /** The format used to get a date out of the data base */
72 public final static SimpleDateFormat sdfDBDate =
73 new SimpleDateFormat( "yyyy-MM-dd" );
74
75 /** The format used to get a time out of the data base */
76 public final static SimpleDateFormat sdfDBTime =
77 new SimpleDateFormat( "HH:mm:ss" );
78
79 /** The format used to convert a date/time String to a Date */
80 public final static SimpleDateFormat sdfConvert = dateFormat;
81 /* new SimpleDateFormat( "MM/dd/yyyy hh:mm a" ); */
82
83 public static
84 RowingSessionSnapshot loadRowingSession( Integer rowingId )
85 throws SQLException, RowingException, NoSuchEntityException {
86
87 // Precondition
88 if ( rowingId == null ) {
89 throw new IllegalArgumentException( "null rowingId" );
90 }
91
92 RowingSessionSnapshot retVal = null;
93
94 Connection conn = null;
95 PreparedStatement stmt = null;
96 ResultSet rs = null;
97 int rowCount = 0;
98 try {
99 conn = DBConfiguration.getConnection();
100 stmt = conn.prepareStatement(
101 Configuration.SQL_SESSION_06,
102 ResultSet.TYPE_FORWARD_ONLY,
103 ResultSet.CONCUR_READ_ONLY);
104 stmt.setInt( 1, rowingId.intValue() );
105 rs = stmt.executeQuery();
106 while ( rs.next() ) {
107 ++rowCount;
108 retVal = mapResultSetToRowingSessionSnapshot(rs);
109 } // while
110
111 }
112 finally {
113 DBConfiguration.closeSQLResultSet( rs );
114 DBConfiguration.closeSQLStatement( stmt );
115 DBConfiguration.closeSQLConnection( conn );
116 rs = null;
117 stmt = null;
118 conn = null;
119 }
120
121 // Postconditions
122 if ( rowCount == 0 ) {
123 throw new NoSuchEntityException( "no id matching " + rowingId );
124 }
125 else if ( rowCount > 1 ) {
126 throw new EJBException( "2 or more id's matching " + rowingId );
127 }
128
129 return retVal;
130 } // loadRowingSession()
131
132 /** Returns a collection of snapshots for all rowing sessions */
133 public static
134 Collection findAllRowingSessionSnapshots() throws RowingException {
135
136 Collection retVal = new ArrayList();
137
138 Connection conn = null;
139 PreparedStatement stmt = null;
140 ResultSet rs = null;
141 try {
142 conn = DBConfiguration.getConnection();
143 stmt = conn.prepareStatement(
144 Configuration.SQL_SESSION_01A,
145 ResultSet.TYPE_FORWARD_ONLY,
146 ResultSet.CONCUR_READ_ONLY);
147 rs = stmt.executeQuery();
148 while ( rs.next() ) {
149 RowingSessionSnapshot rss = mapResultSetToRowingSessionSnapshot(rs);
150 retVal.add( rss );
151 } // while
152
153 }
154 catch( Exception x ) {
155 String msg = ErrorUtils.createDbgMsg("findAllRowingSessionSnapshots",x);
156 theLog.error( msg, x );
157 throw new RowingException( msg );
158 }
159 finally {
160 DBConfiguration.closeSQLResultSet( rs );
161 DBConfiguration.closeSQLStatement( stmt );
162 DBConfiguration.closeSQLConnection( conn );
163 rs = null;
164 stmt = null;
165 conn = null;
166 }
167
168 return retVal;
169 } // findAllRowingSessions()
170
171 /**
172 * Returns a collection of snapshots for rowing sessions within an
173 * inclusive date range.
174 */
175 public static
176 Collection findAllRowingSessionSnapshotsInRange( Date start, Date finish )
177 throws RowingException {
178
179 // Preconditions
180 if ( start == null || finish == null ) {
181 throw new IllegalArgumentException( "null date" );
182 }
183 if ( start.compareTo(finish) > 0 ) {
184 throw new IllegalArgumentException( "start > finish" );
185 }
186
187 Collection retVal = new ArrayList();
188
189 Connection conn = null;
190 PreparedStatement stmt = null;
191 ResultSet rs = null;
192 try {
193 conn = DBConfiguration.getConnection();
194 stmt = conn.prepareStatement(
195 Configuration.SQL_SESSION_03,
196 ResultSet.TYPE_FORWARD_ONLY,
197 ResultSet.CONCUR_READ_ONLY);
198
199 String strStart = RowingDBRead.dateFormat.format( start );
200 String strFinish = RowingDBRead.dateFormat.format( finish );
201
202 if ( theLog.isDebugEnabled() ) {
203 final String prefix = "RowingDBRead.findAllRowing..InRange: ";
204 theLog.debug( prefix + "strStart == " + strStart );
205 theLog.debug( prefix + "strFinish == " + strFinish );
206 }
207
208 stmt.setString( 1, strStart );
209 stmt.setString( 2, strFinish );
210
211 rs = stmt.executeQuery();
212 while ( rs.next() ) {
213 RowingSessionSnapshot rss = mapResultSetToRowingSessionSnapshot(rs);
214 retVal.add( rss );
215 } // while
216
217 }
218 catch( Exception x ) {
219 String msg =
220 ErrorUtils.createDbgMsg("findAllRowingSessionSnapshotsInRange",x);
221 theLog.error( msg, x );
222 throw new RowingException( msg );
223 }
224 finally {
225 DBConfiguration.closeSQLResultSet( rs );
226 DBConfiguration.closeSQLStatement( stmt );
227 DBConfiguration.closeSQLConnection( conn );
228 rs = null;
229 stmt = null;
230 conn = null;
231 }
232
233 return retVal;
234 } // findAllRowingSessionSnapshotsInRange( Date start, Date finish )
235
236 /** Returns a collection of snapshots for all enrollments */
237 public static
238 Collection findAllEnrollmentSnapshots( int member_id )
239 throws RowingException {
240
241 Date start = new Date( Long.MIN_VALUE );
242 Date finish = new Date( Long.MAX_VALUE );
243
244 return findAllEnrollmentSnapshotsInRange(member_id, start, finish);
245 } // findAllEnrollmentSnapshots()
246
247 /**
248 * Returns a collection of snapshots for enrollments within an
249 * inclusive date range.
250 */
251 public static Collection
252 findAllEnrollmentSnapshotsInRange(
253 final int member_id, final Date start, final Date finish )
254 throws RowingException {
255
256 // Preconditions
257 if ( start == null || finish == null ) {
258 throw new IllegalArgumentException( "null date" );
259 }
260 if ( start.compareTo(finish) > 0 ) {
261 throw new IllegalArgumentException( "start > finish" );
262 }
263
264 final Integer memberId = new Integer( member_id );
265
266 Collection retVal = new ArrayList();
267
268 Connection conn = null;
269 PreparedStatement stmt = null;
270 ResultSet rs = null;
271 try {
272
273 /*
274 * Implementation note: the following steps could be accomplished
275 * with a single SQL statement, if UNION and sub-SELECT statements
276 * are supported by the database. Unfortunately, although Oracle
277 * supports UNION and sub-SELECT, mySQL does not.
278 *
279 * As a workaround, valid for both Oracle and mySQL, a set is used
280 * to combine rowing sessions where a member is and is not a participant.
281 * First all rowing sessions are selected with null participation
282 * data. Then rowing session are selected where the member is a
283 * participant. The second selection overwrites null enrollments
284 * where appropriate.
285 *
286 * There's a potentially nasty sorting issue with this approach, unless
287 * the database sorts the original (null-participation) data before
288 * returning it. In the current implementation, both the Oracle and
289 * mySQL SQL statements contain an ORDER BY clause, so the issue is
290 * avoided.
291 *
292 * Here's the issue, for future reference. The database
293 * distinguishes rowing sessions by id, not date. One can have two
294 * distinct rowing session scheduled for exactly the same date and time.
295 * This should only occur in case of an error. Java provides convenient
296 * collection classes for sorting, but only if "equals" and "compareTo"
297 * are consistent; see the documentation for the java.util.Comparator
298 * interface. One can not define a *consistent* date comparator for
299 * rowing sessions without potentially hiding rowing sessions that
300 * (erroneously) have identical dates and times. This is an obscure
301 * issue, but it is particularly ugly since it can hide bad data sets.
302 */
303
304 final Map map = new HashMap();
305 final List sorting = new ArrayList();
306 final String strStart = dateFormat.format( start );
307 final String strFinish = dateFormat.format( finish );
308
309 conn = DBConfiguration.getConnection();
310
311 // Step 1: select sessions regardless of whether a member is enrolled
312 stmt = conn.prepareStatement(
313 Configuration.SQL_ENROLLMENT_01,
314 ResultSet.TYPE_FORWARD_ONLY,
315 ResultSet.CONCUR_READ_ONLY);
316
317 stmt.setString( 1, strStart );
318 stmt.setString( 2, strFinish );
319
320 int rowCount = 0;
321 rs = stmt.executeQuery();
322 while ( rs.next() ) {
323 RowingSessionSnapshot rss = mapResultSetToRowingSessionSnapshot(rs);
324 EnrollmentSnapshot es = new EnrollmentSnapshot( memberId, rss, null );
325 map.put( es.getRowingId(), es );
326 sorting.add( es.getRowingId() );
327 ++rowCount;
328 } // while
329 if ( theLog.isDebugEnabled() ) {
330 String msg =
331 "RowingUtils.findAllEnroll..InRange: result count#1 == " + rowCount;
332 theLog.debug( msg );
333 }
334
335 // Step 2: non-null participation data
336 stmt = conn.prepareStatement(
337 Configuration.SQL_ENROLLMENT_02,
338 ResultSet.TYPE_FORWARD_ONLY,
339 ResultSet.CONCUR_READ_ONLY);
340
341 stmt.setInt( 1, memberId.intValue() );
342 stmt.setString( 2, strStart );
343 stmt.setString( 3, strFinish );
344
345 rowCount = 0;
346 rs = stmt.executeQuery();
347 while ( rs.next() ) {
348 EnrollmentSnapshot es = mapResultSetToEnrollmentSnapshot(rs);
349 map.put( es.getRowingId(), es );
350 ++rowCount;
351 } // while
352 if ( theLog.isDebugEnabled() ) {
353 String msg =
354 "RowingUtils.findAllEnroll..InRange: result count#2 == " + rowCount;
355 theLog.debug( msg );
356 }
357
358 // Sort the enrollments into the return list
359 for ( Iterator iter = sorting.iterator(); iter.hasNext(); ) {
360 Object key = iter.next();
361 Object val = map.get( key );
362 retVal.add( val );
363 }
364
365 }
366 catch( Exception x ) {
367 String msg = "RowingUtils.findAllEnrollmentSnapshotsInRange: "
368 + memberId + "/'" + start + "'/'" + finish + "': "
369 + x.getClass().getName() + ": " + x.getMessage();
370 theLog.error( msg, x );
371 throw new RowingException( msg );
372 }
373 finally {
374 DBConfiguration.closeSQLResultSet( rs );
375 DBConfiguration.closeSQLStatement( stmt );
376 DBConfiguration.closeSQLConnection( conn );
377 rs = null;
378 stmt = null;
379 conn = null;
380 }
381
382 return retVal;
383 } // findAllEnrollmentSnapshotsInRange( Date start, Date finish )
384
385 /**
386 * Returns a Participant identified by a participant id.
387 * @return <strong>NEVER</null>
388 * @exception NoSuchEntityException if the id does not identify
389 * a participant
390 */
391 public static
392 ParticipantSnapshot loadParticipant( Integer participantId )
393 throws SQLException, RowingException, NoSuchEntityException {
394
395 // Precondition
396 if ( participantId == null ) {
397 throw new IllegalArgumentException( "null participantId" );
398 }
399
400 ParticipantSnapshot retVal = null;
401
402 Connection conn = null;
403 PreparedStatement stmt = null;
404 ResultSet rs = null;
405 int rowCount = 0;
406 try {
407 conn = DBConfiguration.getConnection();
408 stmt = conn.prepareStatement(
409 Configuration.SQL_PARTICIPANT_01,
410 ResultSet.TYPE_FORWARD_ONLY,
411 ResultSet.CONCUR_READ_ONLY);
412 stmt.setInt( 1, participantId.intValue() );
413 rs = stmt.executeQuery();
414 while ( rs.next() ) {
415 ++rowCount;
416 retVal = mapResultSetToParticipantSnapshot(rs);
417 } // while
418
419 }
420 finally {
421 DBConfiguration.closeSQLResultSet( rs );
422 DBConfiguration.closeSQLStatement( stmt );
423 DBConfiguration.closeSQLConnection( conn );
424 rs = null;
425 stmt = null;
426 conn = null;
427 }
428
429 // Postconditions
430 if ( rowCount == 0 ) {
431 throw new NoSuchEntityException( "no id matching " + participantId );
432 }
433 else if ( rowCount > 1 ) {
434 throw new EJBException( "2 or more id's matching " + participantId );
435 }
436
437 return retVal;
438 } // loadParticipant()
439
440 /**
441 * Returns a Participant identified by a member and rowing id.
442 * The combination member_id/rowing_id is an alternate key on
443 * the Participant table. Therefore this routine returns either
444 * a null Participant or a unique Participant.
445 * @return possibly null
446 */
447 public static
448 ParticipantSnapshot loadParticipant( Integer memberId, Integer rowingId )
449 throws SQLException, RowingException {
450
451 // Precondition
452 if ( memberId == null ) {
453 throw new IllegalArgumentException( "null memberId" );
454 }
455 if ( rowingId == null ) {
456 throw new IllegalArgumentException( "null rowingId" );
457 }
458
459 ParticipantSnapshot retVal = null;
460
461 Connection conn = null;
462 PreparedStatement stmt = null;
463 ResultSet rs = null;
464 int rowCount = 0;
465 try {
466 conn = DBConfiguration.getConnection();
467 stmt = conn.prepareStatement(
468 Configuration.SQL_PARTICIPANT_03,
469 ResultSet.TYPE_FORWARD_ONLY,
470 ResultSet.CONCUR_READ_ONLY);
471 stmt.setInt( 1, memberId.intValue() );
472 stmt.setInt( 2, rowingId.intValue() );
473 rs = stmt.executeQuery();
474 while ( rs.next() ) {
475 ++rowCount;
476 retVal = mapResultSetToParticipantSnapshot(rs);
477 } // while
478
479 }
480 finally {
481 DBConfiguration.closeSQLResultSet( rs );
482 DBConfiguration.closeSQLStatement( stmt );
483 DBConfiguration.closeSQLConnection( conn );
484 rs = null;
485 stmt = null;
486 conn = null;
487 }
488
489 // Postconditions
490 if ( rowCount > 1 ) {
491 String msg = "2 or more id's matching " + memberId + "/" + rowingId;
492 throw new EJBException( msg );
493 }
494
495 return retVal;
496 } // loadParticipant()
497
498 /**
499 * Returns a collection of Participant2Snapshot instances for
500 * the specified rowing session.
501 * @return a non-null collection of Participant2Snapshot instances,
502 * possibly empty
503 */
504 public static
505 Collection findParticipant2SnapshotsForRowingSession( Integer rowingId )
506 throws RowingException {
507
508 // Preconditions
509 if ( rowingId == null ) {
510 throw new IllegalArgumentException( "null rowingId" );
511 }
512
513 Collection retVal = new ArrayList();
514
515 Connection conn = null;
516 PreparedStatement stmt = null;
517 ResultSet rs = null;
518 try {
519 conn = DBConfiguration.getConnection();
520 stmt = conn.prepareStatement(
521 Configuration.SQL_PARTICIPANT_02,
522 ResultSet.TYPE_FORWARD_ONLY,
523 ResultSet.CONCUR_READ_ONLY);
524
525 stmt.setInt( 1, rowingId.intValue() );
526 rs = stmt.executeQuery();
527 while ( rs.next() ) {
528 Participant2Snapshot ps2 = mapResultSetToParticipant2Snapshot(rs);
529 retVal.add( ps2 );
530 } // while
531
532 }
533 catch( Exception x ) {
534 String msg = "RowingUtils.findParticipant2SnapshotsForRowingSession #"
535 + rowingId + ": "+ x.getClass().getName() + ": " + x.getMessage();
536 theLog.error( msg, x );
537 throw new RowingException( msg );
538 }
539 finally {
540 DBConfiguration.closeSQLResultSet( rs );
541 DBConfiguration.closeSQLStatement( stmt );
542 DBConfiguration.closeSQLConnection( conn );
543 rs = null;
544 stmt = null;
545 conn = null;
546 }
547
548 return retVal;
549 } // findParticipant2SnapshotsForRowingSession(Integer)
550
551 public static
552 RowingSessionSnapshot mapResultSetToRowingSessionSnapshot(ResultSet rs)
553 throws SQLException, RowingException {
554
555 final int rowing_id = rs.getInt( "rowing_id" );
556 final Date rowing_date = rs.getDate( "rowing_date" );
557 final Date rowing_time = rs.getTime( "rowing_date" );
558 final String rowing_level = rs.getString( "rowing_level" ).trim();
559 final String rowing_type = rs.getString( "rowing_type" ).trim();
560 final String rowing_state = rs.getString( "rowing_state" ).trim();
561
562 Integer rowingId = new Integer( rowing_id );
563
564 final String strDate = sdfDBDate.format( rowing_date );
565 final String strTime = sdfDBTime.format( rowing_time );
566 final StringBuffer sbDT = new StringBuffer( strDate );
567 sbDT.append( " " );
568 sbDT.append( strTime );
569 final ParsePosition pos = new ParsePosition( 0 );
570 final Date rowingDateTime = sdfConvert.parse( sbDT.toString(), pos );
571
572 if ( theLog.isDebugEnabled() ) {
573 final String prefix = "RowingDBRead.mapResultToRowing: ";
574 theLog.debug( prefix + "rowing_date == '" + rowing_date + "'" );
575 theLog.debug( prefix + "strDate == '" + strDate + "'" );
576 theLog.debug( prefix + "strTime == '" + strTime + "'" );
577 theLog.debug( prefix + "sbDT == '" + sbDT.toString() + "'" );
578 theLog.debug( prefix + "rowingDateTime == '" + rowingDateTime + "'" );
579 }
580
581 RowingSessionState state = RowingSessionState.getState( rowing_state );
582 RowingSessionLevel level = RowingSessionLevel.getLevel( rowing_level );
583 RowingSessionType type = RowingSessionType.getType( rowing_type );
584
585 RowingSessionSnapshot retVal =
586 new RowingSessionSnapshot(rowingId, state, rowingDateTime, level, type);
587
588 if ( theLog.isDebugEnabled() ) {
589 String msg = "mapResultSetToRowingSessionSnapshot: " + rowing_id + ", '"
590 + sdfConvert.format( rowingDateTime ) + "', " + level.getName()
591 + ", " + type.getName() + ", " + state.getName();
592 theLog.debug( msg );
593 }
594
595 return retVal;
596 } // mapResultSetToRowingSessionSnapshot(ResultSet)
597
598 /**
599 * Constructs an ParticipantSnapshot from the current row of a result set.
600 * @param rs A result set containing the following columns:<ul>
601 * <li>member_id</li>
602 * <li>rowing_id</li>
603 * <li>participant_id</li>
604 * <li>requested</li>
605 * <li>replaces_id</li>
606 * <li>initial_seat</li>
607 * <li>final_seat</li>
608 * <li>attendance</li>
609 * </ul>
610 * @param rs A non-null result set containing the columns specified above.
611 * @exception RowingException if the result set is exhausted.
612 * @exception SQLException if some database-related error occurs
613 */
614 public static
615 ParticipantSnapshot mapResultSetToParticipantSnapshot(ResultSet rs)
616 throws SQLException, RowingException {
617
618 final int member_id = rs.getInt( "member_id" );
619 final int rowing_id = rs.getInt( "rowing_id" );
620 final int participant_id = rs.getInt( "participant_id" );
621 final String requested = rs.getString( "requested" );
622 final int initial_seat = rs.getInt( "initial_seat" );
623 // final int initial_seat_num = rs.getInt( ??? );
624 final int final_seat = rs.getInt( "final_seat" );
625 // final int final_seat_num = rs.getInt( ??? );
626 final int replaces_id = rs.getInt( "replaces_id" );
627 final String strAttendance = rs.getString( "attendance" );
628
629 // Convert int values to Integers, where 0 indicates null
630 final Integer memberId =
631 DBConfiguration.convertIntToIntegerOrNull( member_id );
632 final Integer rowingId =
633 DBConfiguration.convertIntToIntegerOrNull( rowing_id );
634 final Integer participantId =
635 DBConfiguration.convertIntToIntegerOrNull( participant_id );
636 final Integer initialSeatId =
637 DBConfiguration.convertIntToIntegerOrNull( initial_seat );
638 final Integer finalSeatId =
639 DBConfiguration.convertIntToIntegerOrNull( final_seat );
640 final Integer replacesId =
641 DBConfiguration.convertIntToIntegerOrNull( replaces_id );
642
643 if ( theLog.isDebugEnabled() ) {
644 String msg = "mapResultSetToParticipantSnapshot: " + member_id + ", "
645 + rowing_id + ", " + participant_id + ", '"
646 + requested + "', " + replaces_id + ", "
647 + initial_seat + ", " + final_seat + ", '"
648 + strAttendance + "'";
649 theLog.debug( msg );
650 }
651
652 // Convert Strings to objects
653 SeatPreference preference = SeatPreference.getSeatPreference( requested );
654 Attendance attendance = Attendance.getAttendance( strAttendance );
655
656 ParticipantSnapshot retVal = null;
657 if ( participant_id != NULL_INT ) {
658
659 retVal = new ParticipantSnapshot( memberId, rowingId, participantId,
660 preference, replacesId, initialSeatId, finalSeatId, attendance );
661 }
662
663 if ( theLog.isDebugEnabled() ) {
664 String msg = "mapResultSetToParticipantSnapshot: " + memberId + ", "
665 + rowingId + ", " + participantId + ", '"
666 + requested + "', " + replacesId + ", "
667 + initialSeatId + ", " + finalSeatId + ", '"
668 + strAttendance + "'";
669 theLog.debug( msg );
670 }
671
672 return retVal;
673 } // mapResultSetToParticipantSnapshot(ResultSet)
674
675 /**
676 * Constructs an Participant2Snapshot from the current row of a result set.
677 * @param rs A result set containing the same columns as ParticipantSnapshot,
678 * plus following columns:<ul>
679 * <li>name_first</li>
680 * <li>name_middle</li>
681 * <li>name_last</li>
682 * <li>name_suffix</li>
683 * </ul>
684 * @param rs A non-null result set containing the columns specified above.
685 * @exception RowingException if the result set is exhausted.
686 * @exception SQLException if some database-related error occurs
687 */
688 public static
689 Participant2Snapshot mapResultSetToParticipant2Snapshot(ResultSet rs)
690 throws SQLException, RowingException {
691
692 // Preconditions
693 if ( rs == null ) {
694 throw new IllegalArgumentException( "null result set" );
695 }
696
697 ParticipantSnapshot ps = mapResultSetToParticipantSnapshot(rs);
698
699 final String name_last = rs.getString( "name_last" );
700 final String name_first = rs.getString( "name_first" );
701 final String name_middle = rs.getString( "name_middle" );
702 final String name_suffix = rs.getString( "name_suffix" );
703 MemberName memberName =
704 new MemberName(name_first, name_middle, name_last, name_suffix);
705
706 Participant2Snapshot retVal = new Participant2Snapshot(ps,memberName);
707
708 if ( theLog.isDebugEnabled() ) {
709 String msg = "mapResultSetToParticipantSnapshot: "
710 + ps.getMemberId() + "/" + ps.getRowingId() + "/"
711 + ( ps == null ? null : ps.getParticipantId() );
712 theLog.debug( msg );
713 }
714
715 return retVal;
716 } // mapResultSetToParticipant(ResultSet)
717
718 /**
719 * Constructs an EnrollmentSnapshot from the current row of a result set.
720 * @param rs A result set containing the following columns:<ul>
721 * <li>rowing_id</li>
722 * <li>rowing_date</li>
723 * <li>rowing_level</li>
724 * <li>rowing_type</li>
725 * <li>rowing_state</li>
726 * <li>participant_id</li>
727 * <li>requested</li>
728 * <li>initial_seat</li>
729 * <li>final_seat</li>
730 * <li>attendance</li>
731 * <li>member_id</li>
732 * </ul>
733 * @param rs A non-null result set containing the columns specified above.
734 * @exception RowingException if the result set is exhausted.
735 * @exception SQLException if some database-related error occurs
736 */
737 public static
738 EnrollmentSnapshot mapResultSetToEnrollmentSnapshot(ResultSet rs)
739 throws SQLException, RowingException {
740
741 // Preconditions
742 if ( rs == null ) {
743 throw new IllegalArgumentException( "null result set" );
744 }
745
746 final int member_id = rs.getInt( "member_id" );
747 final Integer memberId = new Integer( member_id );
748
749 RowingSessionSnapshot rss = mapResultSetToRowingSessionSnapshot(rs);
750 ParticipantSnapshot ps = mapResultSetToParticipantSnapshot(rs);
751 EnrollmentSnapshot retVal = new EnrollmentSnapshot(memberId,rss,ps);
752
753 if ( theLog.isDebugEnabled() ) {
754 String msg = "mapResultSetToEnrollmentSnapshot: "
755 + member_id + "/" + rss.getId() + "/"
756 + ( ps == null ? null : ps.getParticipantId() );
757 theLog.debug( msg );
758 }
759
760 return retVal;
761 } // mapResultSetToEnrollment(ResultSet)
762
763 } // RowingDBRead
764
765 /*
766 * $Log: RowingDBRead.java,v $
767 * Revision 1.3 2003/02/26 03:38:45 rphall
768 * Added copyright and GPL license
769 *
770 * Revision 1.2 2002/01/30 15:02:47 rphall
771 * Changed date formats fro compatibility with mySQL.
772 * Changed findAllRowingSessionSnapshots* to a simple DB read.
773 * Changed findEnrollmentSnapshots* for compatibility with mySQL.
774 *
775 * Revision 1.16 2002/01/30 01:28:57 rphall
776 * Cleaned up some local variables
777 *
778 * Revision 1.15 2002/01/30 00:12:00 rphall
779 * Removed SQL to select enrollments completely in the database
780 *
781 * Revision 1.14 2002/01/29 21:18:20 rphall
782 * Added debugging
783 *
784 * Revision 1.13 2002/01/29 17:49:36 rphall
785 * Broke up enrollment query into 4 steps for mySQL compatibility
786 *
787 * Revision 1.12 2002/01/24 18:38:15 rphall
788 * Removed some unnecessary EJB imports.
789 * Changed date formats.
790 * Made "findAllRowing..InDateRange" a simple DB read.
791 *
792 * Revision 1.11 2002/01/20 21:16:35 rphall
793 * Set sdfConvert to dateFormat
794 *
795 * Revision 1.10 2002/01/19 23:08:19 rphall
796 * Converted findAllRowingSessionSnapshots to simple DB read
797 *
798 * Revision 1.9 2001/12/31 14:34:30 rphall
799 * Renamed 'member.Name' to 'member.MemberName'
800 *
801 * Revision 1.8 2001/12/19 02:21:23 rphall
802 * Changed log4j info messages to debug messages
803 *
804 * Revision 1.7 2001/12/18 13:36:06 rphall
805 * Documentation
806 *
807 * Revision 1.6 2001/12/15 08:19:59 rphall
808 * Documentation
809 *
810 * Revision 1.5 2001/12/15 05:23:44 rphall
811 * Added DB routine to find Participant by member and rowing id
812 *
813 * Revision 1.4 2001/12/15 02:24:11 rphall
814 * Added participant stuff
815 *
816 * Revision 1.3 2001/12/13 21:17:56 rphall
817 * Fixed null handling in log message
818 *
819 * Revision 1.2 2001/12/13 01:30:21 rphall
820 * Enrollment business and web objects
821 *
822 * Revision 1.1 2001/12/12 04:46:01 rphall
823 * Moved from rowing/remote to rowing
824 *
825 * Revision 1.2 2001/12/12 04:10:19 rphall
826 * Fixed build problems
827 *
828 * Revision 1.1 2001/12/11 16:17:01 rphall
829 * Moved loadRow code to public loadRowingSession utility
830 *
831 */
832