Source code: com/clra/visitor/ApplicantDBRead.java
1 /*
2 * Copyright (c) Carnegie Lake Rowing Association 2002. All rights reserved.
3 * Distributed under the GPL license. See doc/COPYING.
4 * $RCSfile: ApplicantDBRead.java,v $
5 * $Date: 2003/02/26 03:38:46 $
6 * $Revision: 1.3 $
7 */
8
9 package com.clra.visitor;
10
11 import com.clra.util.ISerializableComparator;
12 import com.clra.util.DBConfiguration;
13 import com.clra.util.ErrorUtils;
14 import java.rmi.RemoteException;
15 import java.sql.Connection;
16 import java.sql.PreparedStatement;
17 import java.sql.ResultSet;
18 import java.sql.SQLException;
19 import java.text.ParsePosition;
20 import java.text.SimpleDateFormat;
21 import java.util.ArrayList;
22 import java.util.Collection;
23 import java.util.Date;
24 import java.util.HashMap;
25 import java.util.Iterator;
26 import java.util.List;
27 import java.util.Map;
28 import java.util.Set;
29 import javax.ejb.EJBException;
30 import javax.ejb.NoSuchEntityException;
31 import org.apache.log4j.Category;
32
33 /**
34 * @version $Revision: 1.3 $ $Date: 2003/02/26 03:38:46 $
35 * @author <a href="mailto:donaldzhu@sympatico.ca">Angela Yue</a>
36 */
37 public final class ApplicantDBRead {
38
39 private final static String base = ApplicantDBRead.class.getName();
40 private final static Category theLog = Category.getInstance( base );
41
42 /** All utilities are static */
43 private ApplicantDBRead() {}
44
45 /** Labels a null integer in the database, per JDBC */
46 public final static int NULL_INT = 0;
47
48 /** The format used to specify a date and time to the database */
49 public final static SimpleDateFormat dateFormat =
50 new SimpleDateFormat( Configuration.SQL_DATE_FORMAT );
51
52 /** The format used to get a date out of the data base */
53 public final static SimpleDateFormat sdfDBDate =
54 new SimpleDateFormat( "yyyy-MM-dd" );
55
56 /** The format used to get a time out of the data base */
57 public final static SimpleDateFormat sdfDBTime =
58 new SimpleDateFormat( "HH:mm:ss" );
59
60 /** The format used to convert a date/time String to a Date */
61 public final static SimpleDateFormat sdfConvert = dateFormat;
62 /* new SimpleDateFormat( "MM/dd/yyyy hh:mm a" ); */
63
64 /** Returns a collection of snapshots for all rowing sessions */
65 public static
66 Collection findAllApplicants() throws ApplicantException {
67
68 Collection retVal = new ArrayList();
69
70 Connection conn = null;
71 PreparedStatement stmt = null;
72 ResultSet rs = null;
73 try {
74 conn = DBConfiguration.getConnection();
75 stmt = conn.prepareStatement(
76 Configuration.SQL_05,
77 ResultSet.TYPE_FORWARD_ONLY,
78 ResultSet.CONCUR_READ_ONLY);
79 rs = stmt.executeQuery();
80 while ( rs.next() ) {
81 ApplicantSnapshot ss = mapResultSetToApplicantSnapshot(rs);
82 retVal.add( ss );
83 } // while
84
85 }
86 catch( Exception x ) {
87 String msg = ErrorUtils.createDbgMsg("findAllRowingSessionSnapshots",x);
88 theLog.error( msg, x );
89 throw new ApplicantException( msg );
90 }
91 finally {
92 DBConfiguration.closeSQLResultSet( rs );
93 DBConfiguration.closeSQLStatement( stmt );
94 DBConfiguration.closeSQLConnection( conn );
95 rs = null;
96 stmt = null;
97 conn = null;
98 }
99
100 return retVal;
101 } // findAllApplicants()
102
103 /**
104 * Returns a collection of snapshots for rowing sessions within an
105 * inclusive date range.
106 */
107 public static
108 Collection findAllApplicantsInRange( Date start, Date finish )
109 throws ApplicantException {
110
111 // Preconditions
112 if ( start == null || finish == null ) {
113 throw new IllegalArgumentException( "null date" );
114 }
115 if ( start.compareTo(finish) > 0 ) {
116 throw new IllegalArgumentException( "start > finish" );
117 }
118
119 Collection retVal = new ArrayList();
120
121 Connection conn = null;
122 PreparedStatement stmt = null;
123 ResultSet rs = null;
124 try {
125 conn = DBConfiguration.getConnection();
126 stmt = conn.prepareStatement(
127 Configuration.SQL_06,
128 ResultSet.TYPE_FORWARD_ONLY,
129 ResultSet.CONCUR_READ_ONLY);
130
131 String strStart = ApplicantDBRead.dateFormat.format( start );
132 String strFinish = ApplicantDBRead.dateFormat.format( finish );
133
134 if ( theLog.isDebugEnabled() ) {
135 final String prefix = "ApplicantDBRead.findAllRowing..InRange: ";
136 theLog.debug( prefix + "strStart == " + strStart );
137 theLog.debug( prefix + "strFinish == " + strFinish );
138 }
139
140 stmt.setString( 1, strStart );
141 stmt.setString( 2, strFinish );
142
143 rs = stmt.executeQuery();
144 while ( rs.next() ) {
145 ApplicantSnapshot ss = mapResultSetToApplicantSnapshot(rs);
146 retVal.add( ss );
147 } // while
148
149 }
150 catch( Exception x ) {
151 String msg =
152 ErrorUtils.createDbgMsg("findAllRowingSessionSnapshotsInRange",x);
153 theLog.error( msg, x );
154 throw new ApplicantException( msg );
155 }
156 finally {
157 DBConfiguration.closeSQLResultSet( rs );
158 DBConfiguration.closeSQLStatement( stmt );
159 DBConfiguration.closeSQLConnection( conn );
160 rs = null;
161 stmt = null;
162 conn = null;
163 }
164
165 return retVal;
166 } // findAllApplicantsInRange( Date start, Date finish )
167
168 public static
169 ApplicantSnapshot mapResultSetToApplicantSnapshot(ResultSet rs)
170 throws SQLException, ApplicantException {
171
172 final String lastname = rs.getString("name_last");
173 final String firstname = rs.getString("name_first");
174 final String middlename = rs.getString("name_middle");
175 final String suffix = rs.getString("name_suffix");
176 final String email = rs.getString("email");
177 final String eveningtel = rs.getString("telephone_evening");
178 final String daytel = rs.getString("telephone_day");
179 final String othertel = rs.getString("telephone_other");
180 final String addrstr1 = rs.getString("address_street1");
181 final String addrstr2 = rs.getString("address_street2");
182 final String city = rs.getString("address_city");
183 final String state = rs.getString("address_state");
184 final String zip = rs.getString("address_zip");
185 final String experienceyear = rs.getString("experience_year");
186 final String recentyear = rs.getString("recent_year");
187 final Date birthday = rs.getDate("birthday");
188 final String sex = rs.getString("sex");
189 final Date applydate = rs.getDate("apply_date");
190 final String status = rs.getString("status");
191
192 if ( theLog.isDebugEnabled() ) {
193 final String prefix = "ApplicantDBRead.mapResultToRowing: ";
194 }
195
196 ApplicantSnapshot retVal =
197 new ApplicantSnapshot(lastname, firstname, middlename, suffix, email,
198 eveningtel, daytel, othertel, addrstr1, addrstr2,
199 city, state, zip, experienceyear, recentyear, birthday,
200 sex, applydate, status);
201
202 return retVal;
203 } // mapResultSetToApplicantSnapshot(ResultSet)
204
205
206 } // ApplicantDBRead
207