KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > mvnforum > db > jdbc > MemberDAOImplJDBC


1 /*
2  * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/MemberDAOImplJDBC.java,v 1.48 2006/04/14 17:05:26 minhnn Exp $
3  * $Author: minhnn $
4  * $Revision: 1.48 $
5  * $Date: 2006/04/14 17:05:26 $
6  *
7  * ====================================================================
8  *
9  * Copyright (C) 2002-2006 by MyVietnam.net
10  *
11  * All copyright notices regarding mvnForum MUST remain
12  * intact in the scripts and in the outputted HTML.
13  * The "powered by" text/logo with a link back to
14  * http://www.mvnForum.com and http://www.MyVietnam.net in
15  * the footer of the pages MUST remain visible when the pages
16  * are viewed on the internet or intranet.
17  *
18  * This program is free software; you can redistribute it and/or modify
19  * it under the terms of the GNU General Public License as published by
20  * the Free Software Foundation; either version 2 of the License, or
21  * any later version.
22  *
23  * This program is distributed in the hope that it will be useful,
24  * but WITHOUT ANY WARRANTY; without even the implied warranty of
25  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
26  * GNU General Public License for more details.
27  *
28  * You should have received a copy of the GNU General Public License
29  * along with this program; if not, write to the Free Software
30  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
31  *
32  * Support can be obtained from support forums at:
33  * http://www.mvnForum.com/mvnforum/index
34  *
35  * Correspondence and Marketing Questions can be sent to:
36  * info at MyVietnam net
37  *
38  * @author: Minh Nguyen
39  * @author: Mai Nguyen
40  */

41 package com.mvnforum.db.jdbc;
42
43 import java.sql.*;
44 import java.sql.Date JavaDoc;
45 import java.util.ArrayList JavaDoc;
46 import java.util.Collection JavaDoc;
47
48 import com.mvnforum.db.*;
49 import net.myvietnam.mvncore.db.DBUtils;
50 import net.myvietnam.mvncore.exception.*;
51 import net.myvietnam.mvncore.util.StringUtil;
52 import org.apache.commons.logging.Log;
53 import org.apache.commons.logging.LogFactory;
54
55 public class MemberDAOImplJDBC implements MemberDAO {
56
57     private static Log log = LogFactory.getLog(MemberDAOImplJDBC.class);
58
59     // this variable will support caching if cache for this class is needed
60
private static boolean m_dirty = true;
61
62     public MemberDAOImplJDBC() {
63     }
64
65     protected static boolean isDirty() {
66         return m_dirty;
67     }
68
69     protected static void setDirty(boolean dirty) {
70         m_dirty = dirty;
71     }
72
73     public void findByPrimaryKey(int memberID)
74         throws ObjectNotFoundException, DatabaseException {
75
76         Connection connection = null;
77         PreparedStatement statement = null;
78         ResultSet resultSet = null;
79         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
80         sql.append("SELECT MemberID");
81         sql.append(" FROM " + TABLE_NAME);
82         sql.append(" WHERE MemberID = ?");
83         try {
84             connection = DBUtils.getConnection();
85             statement = connection.prepareStatement(sql.toString());
86             statement.setInt(1, memberID);
87             resultSet = statement.executeQuery();
88             if (!resultSet.next()) {
89                 throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + ") in table 'Member'.");
90             }
91         } catch(SQLException sqle) {
92             log.error("Sql Execution Error!", sqle);
93             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByPrimaryKey.");
94         } finally {
95             DBUtils.closeResultSet(resultSet);
96             DBUtils.closeStatement(statement);
97             DBUtils.closeConnection(connection);
98         }
99     }
100
101     public void findByPrimaryKey2(int memberID, String JavaDoc memberName)
102         throws ObjectNotFoundException, DatabaseException {
103
104         Connection connection = null;
105         PreparedStatement statement = null;
106         ResultSet resultSet = null;
107         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
108         sql.append("SELECT MemberID, MemberName");
109         sql.append(" FROM " + TABLE_NAME);
110         sql.append(" WHERE MemberID = ? AND MemberName = ?");
111         try {
112             connection = DBUtils.getConnection();
113             statement = connection.prepareStatement(sql.toString());
114             statement.setInt(1, memberID);
115             statement.setString(2, memberName);
116             resultSet = statement.executeQuery();
117             if (!resultSet.next()) {
118                 throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + "," + memberName + ") in table 'Member'.");
119             }
120         } catch(SQLException sqle) {
121             log.error("Sql Execution Error!", sqle);
122             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByPrimaryKey2.");
123         } finally {
124             DBUtils.closeResultSet(resultSet);
125             DBUtils.closeStatement(statement);
126             DBUtils.closeConnection(connection);
127         }
128     }
129
130     public void findByAlternateKey_MemberName(String JavaDoc memberName)
131         throws ObjectNotFoundException, DatabaseException {
132
133         Connection connection = null;
134         PreparedStatement statement = null;
135         ResultSet resultSet = null;
136         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
137         sql.append("SELECT MemberName");
138         sql.append(" FROM " + TABLE_NAME);
139         if ( (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) ||
140              (DBUtils.getDatabaseType() == DBUtils.DATABASE_SAPDB) ||
141              (DBUtils.getDatabaseType() == DBUtils.DATABASE_POSTGRESQL) ) {
142             sql.append(" WHERE lower(MemberName) = lower(?)");
143         } else {
144             sql.append(" WHERE MemberName = ?");
145         }
146         try {
147             connection = DBUtils.getConnection();
148             statement = connection.prepareStatement(sql.toString());
149             statement.setString(1, memberName);
150             resultSet = statement.executeQuery();
151             if (!resultSet.next()) {
152                 throw new ObjectNotFoundException("Cannot find the alternate key [MemberName] (" + memberName + ") in table 'Member'.");
153             }
154         } catch(SQLException sqle) {
155             log.error("Sql Execution Error!", sqle);
156             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberName.");
157         } finally {
158             DBUtils.closeResultSet(resultSet);
159             DBUtils.closeStatement(statement);
160             DBUtils.closeConnection(connection);
161         }
162     }
163
164     public void findByAlternateKey_MemberEmail(String JavaDoc memberEmail)
165         throws ObjectNotFoundException, DatabaseException {
166
167         Connection connection = null;
168         PreparedStatement statement = null;
169         ResultSet resultSet = null;
170         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
171         sql.append("SELECT MemberEmail");
172         sql.append(" FROM " + TABLE_NAME);
173         if ( (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) ||
174              (DBUtils.getDatabaseType() == DBUtils.DATABASE_SAPDB) ||
175              (DBUtils.getDatabaseType() == DBUtils.DATABASE_POSTGRESQL) ) {
176             sql.append(" WHERE lower(MemberEmail) = lower(?)");
177         } else {
178             sql.append(" WHERE MemberEmail = ?");
179         }
180         try {
181             connection = DBUtils.getConnection();
182             statement = connection.prepareStatement(sql.toString());
183             statement.setString(1, memberEmail);
184             resultSet = statement.executeQuery();
185             if (!resultSet.next()) {
186                 throw new ObjectNotFoundException("Cannot find the alternate key [MemberEmail] (" + memberEmail + ") in table 'Member'.");
187             }
188         } catch(SQLException sqle) {
189             log.error("Sql Execution Error!", sqle);
190             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberEmail.");
191         } finally {
192             DBUtils.closeResultSet(resultSet);
193             DBUtils.closeStatement(statement);
194             DBUtils.closeConnection(connection);
195         }
196     }
197
198     /**
199      * Delete the user specified by memberID. Note that this method
200      * will not fail it the given id does not exists.
201      *
202      * @param memberID an <code>int</code> value
203      * @exception DatabaseException if an error occurs
204      */

205     public void deleteByPrimaryKey(int memberID) throws DatabaseException {
206
207         Connection connection = null;
208         PreparedStatement statement = null;
209         ResultSet resultSet = null;
210         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
211         sql.append("DELETE");
212         sql.append(" FROM " + TABLE_NAME);
213         sql.append(" WHERE MemberID = ?");
214         try {
215             connection = DBUtils.getConnection();
216             statement = connection.prepareStatement(sql.toString());
217             statement.setInt(1, memberID);
218             statement.executeUpdate();
219         } catch(SQLException sqle) {
220             log.error("Sql Execution Error!", sqle);
221             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.deleteByPrimaryKey.");
222         } finally {
223             DBUtils.closeResultSet(resultSet);
224             DBUtils.closeStatement(statement);
225             DBUtils.closeConnection(connection);
226         }
227     }
228
229     /*
230      * Included columns: MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible,
231      * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
232      * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
233      * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
234      * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
235      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
236      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
237      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
238      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
239      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
240      * Excluded columns: MemberID
241      */

242     public void create(String JavaDoc memberName, String JavaDoc memberPassword, String JavaDoc memberFirstEmail,
243                         String JavaDoc memberEmail, int memberEmailVisible, int memberNameVisible,
244                         String JavaDoc memberFirstIP, String JavaDoc memberLastIP, int memberViewCount,
245                         int memberPostCount, Timestamp memberCreationDate, Timestamp memberModifiedDate, Timestamp memberExpireDate,
246                         Timestamp memberLastLogon, int memberOption, int memberStatus,
247                         String JavaDoc memberActivateCode, String JavaDoc memberTempPassword, int memberMessageCount,
248                         int memberMessageOption, int memberPostsPerPage, int memberWarnCount,
249                         int memberVoteCount, int memberVoteTotalStars, int memberRewardPoints,
250                         String JavaDoc memberTitle, double memberTimeZone, String JavaDoc memberSignature,
251                         String JavaDoc memberAvatar, String JavaDoc memberSkin, String JavaDoc memberLanguage,
252                         String JavaDoc memberFirstname, String JavaDoc memberLastname, int memberGender,
253                         Date JavaDoc memberBirthday, String JavaDoc memberAddress, String JavaDoc memberCity,
254                         String JavaDoc memberState, String JavaDoc memberCountry, String JavaDoc memberPhone,
255                         String JavaDoc memberMobile, String JavaDoc memberFax, String JavaDoc memberCareer,
256                         String JavaDoc memberHomepage, String JavaDoc memberYahoo, String JavaDoc memberAol,
257                         String JavaDoc memberIcq, String JavaDoc memberMsn, String JavaDoc memberCoolLink1,
258                         String JavaDoc memberCoolLink2)
259                         throws CreateException, DatabaseException, DuplicateKeyException {
260
261         // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
262
// If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
263
try {
264             //Check if alternate key already exists
265
findByAlternateKey_MemberName(memberName);
266             //If so, then we have to throw an exception
267
throw new DuplicateKeyException("Alternate key already exists. Cannot create new Member with the same [MemberName] (" + memberName + ").");
268         } catch(ObjectNotFoundException e) {
269             //Otherwise we can go ahead
270
}
271
272         // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
273
// If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
274
try {
275             //Check if alternate key already exists
276
findByAlternateKey_MemberEmail(memberEmail);
277             //If so, then we have to throw an exception
278
throw new DuplicateKeyException("Alternate key already exists. Cannot create new Member with the same [MemberEmail] (" + memberEmail + ").");
279         } catch(ObjectNotFoundException e) {
280             //Otherwise we can go ahead
281
}
282
283         Connection connection = null;
284         PreparedStatement statement = null;
285         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
286         sql.append("INSERT INTO " + TABLE_NAME + " (MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2)");
287         sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
288         try {
289             connection = DBUtils.getConnection();
290             statement = connection.prepareStatement(sql.toString());
291
292             statement.setString(1, memberName);
293             statement.setString(2, memberPassword);
294             statement.setString(3, memberFirstEmail);
295             statement.setString(4, memberEmail);
296             statement.setInt(5, memberEmailVisible);
297             statement.setInt(6, memberNameVisible);
298             statement.setString(7, memberFirstIP);
299             statement.setString(8, memberLastIP);
300             statement.setInt(9, memberViewCount);
301             statement.setInt(10, memberPostCount);
302             statement.setTimestamp(11, memberCreationDate);
303             statement.setTimestamp(12, memberModifiedDate);
304             statement.setTimestamp(13, memberExpireDate);
305             statement.setTimestamp(14, memberLastLogon);
306             statement.setInt(15, memberOption);
307             statement.setInt(16, memberStatus);
308             statement.setString(17, memberActivateCode);
309             statement.setString(18, memberTempPassword);
310             statement.setInt(19, memberMessageCount);
311             statement.setInt(20, memberMessageOption);
312             statement.setInt(21, memberPostsPerPage);
313             statement.setInt(22, memberWarnCount);
314             statement.setInt(23, memberVoteCount);
315             statement.setInt(24, memberVoteTotalStars);
316             statement.setInt(25, memberRewardPoints);
317             statement.setString(26, memberTitle);
318             statement.setDouble(27, memberTimeZone);
319             statement.setString(28, memberSignature);
320             statement.setString(29, memberAvatar);
321             statement.setString(30, memberSkin);
322             statement.setString(31, memberLanguage);
323             statement.setString(32, memberFirstname);
324             statement.setString(33, memberLastname);
325             statement.setInt(34, memberGender);
326             statement.setDate(35, memberBirthday);
327             statement.setString(36, memberAddress);
328             statement.setString(37, memberCity);
329             statement.setString(38, memberState);
330             statement.setString(39, memberCountry);
331             statement.setString(40, memberPhone);
332             statement.setString(41, memberMobile);
333             statement.setString(42, memberFax);
334             statement.setString(43, memberCareer);
335             statement.setString(44, memberHomepage);
336             statement.setString(45, memberYahoo);
337             statement.setString(46, memberAol);
338             statement.setString(47, memberIcq);
339             statement.setString(48, memberMsn);
340             statement.setString(49, memberCoolLink1);
341             statement.setString(50, memberCoolLink2);
342
343             if (statement.executeUpdate() != 1) {
344                 throw new CreateException("Error adding a row into table 'Member'.");
345             }
346             m_dirty = true;
347         } catch(SQLException sqle) {
348             log.error("Sql Execution Error!", sqle);
349             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.create.");
350         } finally {
351             DBUtils.closeStatement(statement);
352             DBUtils.closeConnection(connection);
353         }
354     }
355
356     /*
357      * Included columns: MemberEmailVisible, MemberNameVisible, MemberModifiedDate, MemberOption, MemberStatus,
358      * MemberMessageOption, MemberPostsPerPage, MemberTimeZone, MemberSkin, MemberLanguage,
359      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
360      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
361      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
362      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
363      * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
364      * MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate,
365      * MemberLastLogon, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberWarnCount,
366      * MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature,
367      * MemberAvatar
368      */

369     public void update(int memberID, // primary key
370
int memberEmailVisible, int memberNameVisible, Timestamp memberModifiedDate,
371                         int memberOption, int memberStatus, int memberMessageOption,
372                         int memberPostsPerPage, double memberTimeZone, String JavaDoc memberSkin,
373                         String JavaDoc memberLanguage, String JavaDoc memberFirstname, String JavaDoc memberLastname,
374                         int memberGender, Date JavaDoc memberBirthday, String JavaDoc memberAddress,
375                         String JavaDoc memberCity, String JavaDoc memberState, String JavaDoc memberCountry,
376                         String JavaDoc memberPhone, String JavaDoc memberMobile, String JavaDoc memberFax,
377                         String JavaDoc memberCareer, String JavaDoc memberHomepage, String JavaDoc memberYahoo,
378                         String JavaDoc memberAol, String JavaDoc memberIcq, String JavaDoc memberMsn,
379                         String JavaDoc memberCoolLink1, String JavaDoc memberCoolLink2)
380                         throws ObjectNotFoundException, DatabaseException {
381
382         Connection connection = null;
383         PreparedStatement statement = null;
384         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
385         sql.append("UPDATE " + TABLE_NAME + " SET MemberEmailVisible = ?, MemberNameVisible = ?, MemberModifiedDate = ?, MemberOption = ?, MemberStatus = ?, MemberMessageOption = ?, MemberPostsPerPage = ?, MemberTimeZone = ?, MemberSkin = ?, MemberLanguage = ?, MemberFirstname = ?, MemberLastname = ?, MemberGender = ?, MemberBirthday = ?, MemberAddress = ?, MemberCity = ?, MemberState = ?, MemberCountry = ?, MemberPhone = ?, MemberMobile = ?, MemberFax = ?, MemberCareer = ?, MemberHomepage = ?, MemberYahoo = ?, MemberAol = ?, MemberIcq = ?, MemberMsn = ?, MemberCoolLink1 = ?, MemberCoolLink2 = ?");
386         sql.append(" WHERE MemberID = ?");
387         try {
388             connection = DBUtils.getConnection();
389             statement = connection.prepareStatement(sql.toString());
390
391             // // column(s) to update
392
statement.setInt(1, memberEmailVisible);
393             statement.setInt(2, memberNameVisible);
394             statement.setTimestamp(3, memberModifiedDate);
395             statement.setInt(4, memberOption);
396             statement.setInt(5, memberStatus);
397             statement.setInt(6, memberMessageOption);
398             statement.setInt(7, memberPostsPerPage);
399             statement.setDouble(8, memberTimeZone);
400             statement.setString(9, memberSkin);
401             statement.setString(10, memberLanguage);
402             statement.setString(11, memberFirstname);
403             statement.setString(12, memberLastname);
404             statement.setInt(13, memberGender);
405             statement.setDate(14, memberBirthday);
406             statement.setString(15, memberAddress);
407             statement.setString(16, memberCity);
408             statement.setString(17, memberState);
409             statement.setString(18, memberCountry);
410             statement.setString(19, memberPhone);
411             statement.setString(20, memberMobile);
412             statement.setString(21, memberFax);
413             statement.setString(22, memberCareer);
414             statement.setString(23, memberHomepage);
415             statement.setString(24, memberYahoo);
416             statement.setString(25, memberAol);
417             statement.setString(26, memberIcq);
418             statement.setString(27, memberMsn);
419             statement.setString(28, memberCoolLink1);
420             statement.setString(29, memberCoolLink2);
421
422             // primary key column(s)
423
statement.setInt(30, memberID);
424
425             if (statement.executeUpdate() != 1) {
426                 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
427             }
428             m_dirty = true;
429         } catch(SQLException sqle) {
430             log.error("Sql Execution Error!", sqle);
431             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.update.");
432         } finally {
433             DBUtils.closeStatement(statement);
434             DBUtils.closeConnection(connection);
435         }
436     }
437
438     /*
439      * Included columns: MemberEmail
440      * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmailVisible,
441      * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
442      * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
443      * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
444      * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
445      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
446      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
447      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
448      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
449      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
450      */

451     public void updateEmail(int memberID, // primary key
452
String JavaDoc memberEmail)
453                         throws ObjectNotFoundException, DatabaseException, DuplicateKeyException {
454
455         //@todo: use a more efficent method
456
MemberBean bean = getMember_forViewCurrentMember(memberID); // @todo: comment or delete this line if no alternate key are included
457

458         if ( !memberEmail.equals(bean.getMemberEmail()) ) {
459             // Member tries to change its alternate key <MemberEmail>, so we must check if it already exist
460
try {
461                 findByAlternateKey_MemberEmail(memberEmail);
462                 throw new DuplicateKeyException("Alternate key [MemberEmail] (" + memberEmail + ")already exists. Cannot update Member.");
463             } catch(ObjectNotFoundException e) {
464                 //Otherwise we can go ahead
465
}
466         }
467
468         Connection connection = null;
469         PreparedStatement statement = null;
470         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
471         sql.append("UPDATE " + TABLE_NAME + " SET MemberEmail = ?");
472         sql.append(" WHERE MemberID = ?");
473         try {
474             connection = DBUtils.getConnection();
475             statement = connection.prepareStatement(sql.toString());
476
477             // // column(s) to update
478
statement.setString(1, memberEmail);
479
480             // primary key column(s)
481
statement.setInt(2, memberID);
482
483             if (statement.executeUpdate() != 1) {
484                 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
485             }
486             m_dirty = true;
487         } catch(SQLException sqle) {
488             log.error("Sql Execution Error!", sqle);
489             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateEmail.");
490         } finally {
491             DBUtils.closeStatement(statement);
492             DBUtils.closeConnection(connection);
493         }
494     }
495
496     /*
497      * Included columns: MemberPassword
498      * Excluded columns: MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible,
499      * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
500      * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
501      * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
502      * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
503      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
504      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
505      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
506      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
507      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
508      */

509     public void updatePassword(int memberID, // primary key
510
String JavaDoc memberPassword)
511                         throws ObjectNotFoundException, DatabaseException {
512
513         Connection connection = null;
514         PreparedStatement statement = null;
515         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
516         sql.append("UPDATE " + TABLE_NAME + " SET MemberPassword = ?");
517         sql.append(" WHERE MemberID = ?");
518         try {
519             connection = DBUtils.getConnection();
520             statement = connection.prepareStatement(sql.toString());
521
522             // // column(s) to update
523
statement.setString(1, memberPassword);
524
525             // primary key column(s)
526
statement.setInt(2, memberID);
527
528             if (statement.executeUpdate() != 1) {
529                 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
530             }
531             m_dirty = true;
532         } catch(SQLException sqle) {
533             log.error("Sql Execution Error!", sqle);
534             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updatePassword.");
535         } finally {
536             DBUtils.closeStatement(statement);
537             DBUtils.closeConnection(connection);
538         }
539     }
540
541     /*
542      * Included columns: MemberTempPassword
543      * Excluded columns: MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible,
544      * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
545      * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
546      * MemberActivateCode, MemberPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
547      * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
548      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
549      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
550      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
551      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
552      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
553      */

554     public void updateTempPassword(int memberID, // primary key
555
String JavaDoc memberTempPassword)
556                         throws ObjectNotFoundException, DatabaseException {
557
558         Connection connection = null;
559         PreparedStatement statement = null;
560         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
561         sql.append("UPDATE " + TABLE_NAME + " SET MemberTempPassword = ?");
562         sql.append(" WHERE MemberID = ?");
563         try {
564             connection = DBUtils.getConnection();
565             statement = connection.prepareStatement(sql.toString());
566
567             // // column(s) to update
568
statement.setString(1, memberTempPassword);
569
570             // primary key column(s)
571
statement.setInt(2, memberID);
572
573             if (statement.executeUpdate() != 1) {
574                 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
575             }
576             m_dirty = true;
577         } catch(SQLException sqle) {
578             log.error("Sql Execution Error!", sqle);
579             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateTempPassword.");
580         } finally {
581             DBUtils.closeStatement(statement);
582             DBUtils.closeConnection(connection);
583         }
584     }
585
586     /*
587      * Included columns: MemberActivateCode
588      * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
589      * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
590      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
591      * MemberStatus, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
592      * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
593      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
594      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
595      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
596      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
597      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
598      */

599     public void updateActivateCode(int memberID, // primary key
600
String JavaDoc memberActivateCode)
601                         throws ObjectNotFoundException, DatabaseException {
602
603         Connection connection = null;
604         PreparedStatement statement = null;
605         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
606         sql.append("UPDATE " + TABLE_NAME + " SET MemberActivateCode = ?");
607         sql.append(" WHERE MemberID = ?");
608         try {
609             connection = DBUtils.getConnection();
610             statement = connection.prepareStatement(sql.toString());
611
612             // // column(s) to update
613
statement.setString(1, memberActivateCode);
614
615             // primary key column(s)
616
statement.setInt(2, memberID);
617
618             if (statement.executeUpdate() != 1) {
619                 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
620             }
621             m_dirty = true;
622         } catch(SQLException sqle) {
623             log.error("Sql Execution Error!", sqle);
624             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateActivateCode.");
625         } finally {
626             DBUtils.closeStatement(statement);
627             DBUtils.closeConnection(connection);
628         }
629     }
630
631     /*
632      * Included columns: MemberAvatar
633      * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
634      * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
635      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
636      * MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption,
637      * MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
638      * MemberTitle, MemberTimeZone, MemberSignature, MemberSkin, MemberLanguage,
639      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
640      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
641      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
642      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
643      */

644     public void updateAvatar(int memberID, // primary key
645
String JavaDoc memberAvatar)
646                         throws ObjectNotFoundException, DatabaseException {
647
648         Connection connection = null;
649         PreparedStatement statement = null;
650         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
651         sql.append("UPDATE " + TABLE_NAME + " SET MemberAvatar = ?");
652         sql.append(" WHERE MemberID = ?");
653         try {
654             connection = DBUtils.getConnection();
655             statement = connection.prepareStatement(sql.toString());
656
657             // // column(s) to update
658
statement.setString(1, memberAvatar);
659
660             // primary key column(s)
661
statement.setInt(2, memberID);
662
663             if (statement.executeUpdate() != 1) {
664                 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
665             }
666             m_dirty = true;
667         } catch(SQLException sqle) {
668             log.error("Sql Execution Error!", sqle);
669             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateAvatar.");
670         } finally {
671             DBUtils.closeStatement(statement);
672             DBUtils.closeConnection(connection);
673         }
674     }
675
676     /*
677      * Included columns: MemberSignature
678      * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
679      * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
680      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
681      * MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption,
682      * MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
683      * MemberTitle, MemberTimeZone, MemberAvatar, MemberSkin, MemberLanguage,
684      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
685      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
686      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
687      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
688      */

689     public void updateSignature(int memberID, // primary key
690
String JavaDoc memberSignature)
691                         throws ObjectNotFoundException, DatabaseException {
692
693         Connection connection = null;
694         PreparedStatement statement = null;
695         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
696         sql.append("UPDATE " + TABLE_NAME + " SET MemberSignature = ?");
697         sql.append(" WHERE MemberID = ?");
698         try {
699             connection = DBUtils.getConnection();
700             statement = connection.prepareStatement(sql.toString());
701
702             // // column(s) to update
703
statement.setString(1, memberSignature);
704
705             // primary key column(s)
706
statement.setInt(2, memberID);
707
708             if (statement.executeUpdate() != 1) {
709                 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
710             }
711             m_dirty = true;
712         } catch(SQLException sqle) {
713             log.error("Sql Execution Error!", sqle);
714             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateSignature.");
715         } finally {
716             DBUtils.closeStatement(statement);
717             DBUtils.closeConnection(connection);
718         }
719     }
720
721     /*
722      * Included columns: MemberTitle
723      * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
724      * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
725      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
726      * MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption,
727      * MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
728      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
729      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
730      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
731      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
732      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
733      */

734     public void updateTitle(int memberID, // primary key
735
String JavaDoc memberTitle)
736                         throws ObjectNotFoundException, DatabaseException {
737
738         Connection connection = null;
739         PreparedStatement statement = null;
740         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
741         sql.append("UPDATE " + TABLE_NAME + " SET MemberTitle = ?");
742         sql.append(" WHERE MemberID = ?");
743         try {
744             connection = DBUtils.getConnection();
745             statement = connection.prepareStatement(sql.toString());
746
747             // // column(s) to update
748
statement.setString(1, memberTitle);
749
750             // primary key column(s)
751
statement.setInt(2, memberID);
752
753             if (statement.executeUpdate() != 1) {
754                 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
755             }
756             m_dirty = true;
757         } catch(SQLException sqle) {
758             log.error("Sql Execution Error!", sqle);
759             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateTitle.");
760         } finally {
761             DBUtils.closeStatement(statement);
762             DBUtils.closeConnection(connection);
763         }
764     }
765
766     /*
767      * Included columns: MemberLastLogon
768      * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
769      * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
770      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberOption, MemberStatus,
771      * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
772      * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
773      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
774      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
775      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
776      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
777      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
778      */

779     public void updateLastLogon(int memberID, // primary key
780
Timestamp memberLastLogon, String JavaDoc memberLastIP)
781                         throws ObjectNotFoundException, DatabaseException {
782
783         Connection connection = null;
784         PreparedStatement statement = null;
785         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
786         sql.append("UPDATE " + TABLE_NAME + " SET MemberLastLogon = ? , MemberLastIP = ? ");
787         sql.append(" WHERE MemberID = ?");
788         try {
789             connection = DBUtils.getConnection();
790             statement = connection.prepareStatement(sql.toString());
791
792             // // column(s) to update
793
statement.setTimestamp(1, memberLastLogon);
794             statement.setString(2, memberLastIP);
795
796             // primary key column(s)
797
statement.setInt(3, memberID);
798
799             if (statement.executeUpdate() != 1) {
800                 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
801             }
802             m_dirty = true;
803         } catch(SQLException sqle) {
804             log.error("Sql Execution Error!", sqle);
805             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateLastLogon.");
806         } finally {
807             DBUtils.closeStatement(statement);
808             DBUtils.closeConnection(connection);
809         }
810     }
811
812     /*
813      * Included columns: MemberPassword
814      * Excluded columns: MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible,
815      * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
816      * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
817      * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
818      * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
819      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
820      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
821      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
822      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
823      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
824      */

825     public String JavaDoc getPassword(int memberID)
826         throws ObjectNotFoundException, DatabaseException {
827
828         Connection connection = null;
829         PreparedStatement statement = null;
830         ResultSet resultSet = null;
831         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
832         sql.append("SELECT MemberPassword");
833         sql.append(" FROM " + TABLE_NAME);
834         sql.append(" WHERE MemberID = ?");
835         try {
836             connection = DBUtils.getConnection();
837             statement = connection.prepareStatement(sql.toString());
838             statement.setInt(1, memberID);
839             resultSet = statement.executeQuery();
840             if(!resultSet.next()) {
841                 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ").");
842             }
843
844             String JavaDoc retValue = resultSet.getString("MemberPassword");
845             if (retValue == null) retValue = ""; // hack for Oracle database
846
return retValue;
847         } catch(SQLException sqle) {
848             log.error("Sql Execution Error!", sqle);
849             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getPassword(pk).");
850         } finally {
851             DBUtils.closeResultSet(resultSet);
852             DBUtils.closeStatement(statement);
853             DBUtils.closeConnection(connection);
854         }
855     }
856
857     /*
858      * Included columns: MemberTempPassword
859      * Excluded columns: MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible,
860      * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
861      * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
862      * MemberActivateCode, MemberPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
863      * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
864      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
865      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
866      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
867      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
868      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
869      */

870     public String JavaDoc getTempPassword(int memberID)
871         throws ObjectNotFoundException, DatabaseException {
872
873         Connection connection = null;
874         PreparedStatement statement = null;
875         ResultSet resultSet = null;
876         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
877         sql.append("SELECT MemberTempPassword");
878         sql.append(" FROM " + TABLE_NAME);
879         sql.append(" WHERE MemberID = ?");
880         try {
881             connection = DBUtils.getConnection();
882             statement = connection.prepareStatement(sql.toString());
883             statement.setInt(1, memberID);
884             resultSet = statement.executeQuery();
885             if(!resultSet.next()) {
886                 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ").");
887             }
888
889             return StringUtil.getEmptyStringIfNull(resultSet.getString("MemberTempPassword"));
890         } catch(SQLException sqle) {
891             log.error("Sql Execution Error!", sqle);
892             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getTempPassword(pk).");
893         } finally {
894             DBUtils.closeResultSet(resultSet);
895             DBUtils.closeStatement(statement);
896             DBUtils.closeConnection(connection);
897         }
898     }
899
900     /*
901      * Included columns: MemberActivateCode
902      * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
903      * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
904      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
905      * MemberStatus, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
906      * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
907      * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
908      * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
909      * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
910      * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
911      * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
912      */

913     public String JavaDoc getActivateCode(int memberID)
914         throws ObjectNotFoundException, DatabaseException {
915
916         Connection connection = null;
917         PreparedStatement statement = null;
918         ResultSet resultSet = null;
919         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
920         sql.append("SELECT MemberActivateCode");
921         sql.append(" FROM " + TABLE_NAME);
922         sql.append(" WHERE MemberID = ?");
923         try {
924             connection = DBUtils.getConnection();
925             statement = connection.prepareStatement(sql.toString());
926             statement.setInt(1, memberID);
927             resultSet = statement.executeQuery();
928             if(!resultSet.next()) {
929                 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ").");
930             }
931
932             String JavaDoc retValue = resultSet.getString("MemberActivateCode");
933             if (retValue == null) retValue = ""; // hack for Oracle database
934
return retValue;
935         } catch(SQLException sqle) {
936             log.error("Sql Execution Error!", sqle);
937             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getActivateCode(pk).");
938         } finally {
939             DBUtils.closeResultSet(resultSet);
940             DBUtils.closeStatement(statement);
941             DBUtils.closeConnection(connection);
942         }
943     }
944
945     /*
946      * Included columns: MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount,
947      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
948      * MemberStatus, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount,
949      * MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone,
950      * MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname,
951      * MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity,
952      * MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax,
953      * MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq,
954      * MemberMsn, MemberCoolLink1, MemberCoolLink2
955      * As of 26 March: MemberActivateCode
956      * Excluded columns: MemberID, MemberPassword, MemberFirstIP, MemberFirstEmail,
957      * MemberTempPassword
958      */

959     public MemberBean getMember_forViewCurrentMember(int memberID)
960         throws ObjectNotFoundException, DatabaseException {
961
962         Connection connection = null;
963         PreparedStatement statement = null;
964         ResultSet resultSet = null;
965         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
966         sql.append("SELECT MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberLastIP, MemberOption, MemberStatus, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode");
967         sql.append(" FROM " + TABLE_NAME);
968         sql.append(" WHERE MemberID = ?");
969         try {
970             connection = DBUtils.getConnection();
971             statement = connection.prepareStatement(sql.toString());
972             statement.setInt(1, memberID);
973             resultSet = statement.executeQuery();
974             if(!resultSet.next()) {
975                 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ").");
976             }
977
978             MemberBean bean = new MemberBean();
979             // @todo: uncomment the following line(s) as needed
980
bean.setMemberID(memberID);
981             bean.setMemberName(resultSet.getString("MemberName"));
982             bean.setMemberEmail(resultSet.getString("MemberEmail"));
983             bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
984             bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
985             bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
986             bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
987             bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
988             bean.setMemberModifiedDate(resultSet.getTimestamp("MemberModifiedDate"));
989             bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
990             bean.setMemberLastIP(resultSet.getString("MemberLastIP"));
991             bean.setMemberOption(resultSet.getInt("MemberOption"));
992             bean.setMemberStatus(resultSet.getInt("MemberStatus"));
993             bean.setMemberMessageCount(resultSet.getInt("MemberMessageCount"));
994             bean.setMemberMessageOption(resultSet.getInt("MemberMessageOption"));
995             bean.setMemberPostsPerPage(resultSet.getInt("MemberPostsPerPage"));
996             bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
997             bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
998             bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
999             bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
1000            bean.setMemberTitle(resultSet.getString("MemberTitle"));
1001            bean.setMemberTimeZone(resultSet.getDouble("MemberTimeZone"));
1002            bean.setMemberSignature(resultSet.getString("MemberSignature"));
1003            bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
1004            bean.setMemberSkin(resultSet.getString("MemberSkin"));
1005            bean.setMemberLanguage(resultSet.getString("MemberLanguage"));
1006            bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
1007            bean.setMemberLastname(resultSet.getString("MemberLastname"));
1008            bean.setMemberGender(resultSet.getInt("MemberGender"));
1009            bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1010            bean.setMemberAddress(resultSet.getString("MemberAddress"));
1011            bean.setMemberCity(resultSet.getString("MemberCity"));
1012            bean.setMemberState(resultSet.getString("MemberState"));
1013            bean.setMemberCountry(resultSet.getString("MemberCountry"));
1014            bean.setMemberPhone(resultSet.getString("MemberPhone"));
1015            bean.setMemberMobile(resultSet.getString("MemberMobile"));
1016            bean.setMemberFax(resultSet.getString("MemberFax"));
1017            bean.setMemberCareer(resultSet.getString("MemberCareer"));
1018            bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
1019            bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1020            bean.setMemberAol(resultSet.getString("MemberAol"));
1021            bean.setMemberIcq(resultSet.getString("MemberIcq"));
1022            bean.setMemberMsn(resultSet.getString("MemberMsn"));
1023            bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
1024            bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
1025            bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
1026            return bean;
1027        } catch(SQLException sqle) {
1028            log.error("Sql Execution Error!", sqle);
1029            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMember_forViewCurrentMember(pk).");
1030        } finally {
1031            DBUtils.closeResultSet(resultSet);
1032            DBUtils.closeStatement(statement);
1033            DBUtils.closeConnection(connection);
1034        }
1035    }
1036
1037    /*
1038     * Included columns: MemberEmailVisible, MemberNameVisible, MemberOption, MemberStatus, MemberMessageOption,
1039     * MemberPostsPerPage, MemberTimeZone, MemberSkin, MemberLanguage, MemberFirstname,
1040     * MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity,
1041     * MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax,
1042     * MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq,
1043     * MemberMsn, MemberCoolLink1, MemberCoolLink2
1044     * As of 13 Jan 2005: MemberName : this column is for show current member in viewmember in Admin zone
1045     * Excluded columns: MemberID, MemberPassword, MemberFirstEmail, MemberEmail,
1046     * MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate,
1047     * MemberModifiedDate, MemberLastLogon, MemberActivateCode, MemberTempPassword, MemberMessageCount,
1048     * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
1049     * MemberSignature, MemberAvatar
1050     */

1051    public MemberBean getMember_forEditCurrentMember(int memberID)
1052        throws ObjectNotFoundException, DatabaseException {
1053
1054        Connection connection = null;
1055        PreparedStatement statement = null;
1056        ResultSet resultSet = null;
1057        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1058        sql.append("SELECT MemberName, MemberEmailVisible, MemberNameVisible, MemberOption, MemberStatus, MemberMessageOption, MemberPostsPerPage, MemberTimeZone, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2");
1059        sql.append(" FROM " + TABLE_NAME);
1060        sql.append(" WHERE MemberID = ?");
1061        try {
1062            connection = DBUtils.getConnection();
1063            statement = connection.prepareStatement(sql.toString());
1064            statement.setInt(1, memberID);
1065            resultSet = statement.executeQuery();
1066            if(!resultSet.next()) {
1067                throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ").");
1068            }
1069
1070            MemberBean bean = new MemberBean();
1071            // @todo: uncomment the following line(s) as needed
1072
bean.setMemberID(memberID);
1073            bean.setMemberName(resultSet.getString("MemberName"));
1074            bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
1075            bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
1076            bean.setMemberOption(resultSet.getInt("MemberOption"));
1077            bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1078            bean.setMemberMessageOption(resultSet.getInt("MemberMessageOption"));
1079            bean.setMemberPostsPerPage(resultSet.getInt("MemberPostsPerPage"));
1080            bean.setMemberTimeZone(resultSet.getDouble("MemberTimeZone"));
1081            bean.setMemberSkin(resultSet.getString("MemberSkin"));
1082            bean.setMemberLanguage(resultSet.getString("MemberLanguage"));
1083            bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
1084            bean.setMemberLastname(resultSet.getString("MemberLastname"));
1085            bean.setMemberGender(resultSet.getInt("MemberGender"));
1086            bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1087            bean.setMemberAddress(resultSet.getString("MemberAddress"));
1088            bean.setMemberCity(resultSet.getString("MemberCity"));
1089            bean.setMemberState(resultSet.getString("MemberState"));
1090            bean.setMemberCountry(resultSet.getString("MemberCountry"));
1091            bean.setMemberPhone(resultSet.getString("MemberPhone"));
1092            bean.setMemberMobile(resultSet.getString("MemberMobile"));
1093            bean.setMemberFax(resultSet.getString("MemberFax"));
1094            bean.setMemberCareer(resultSet.getString("MemberCareer"));
1095            bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
1096            bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1097            bean.setMemberAol(resultSet.getString("MemberAol"));
1098            bean.setMemberIcq(resultSet.getString("MemberIcq"));
1099            bean.setMemberMsn(resultSet.getString("MemberMsn"));
1100            bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
1101            bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
1102            return bean;
1103        } catch(SQLException sqle) {
1104            log.error("Sql Execution Error!", sqle);
1105            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMember_forEditCurrentMember(pk).");
1106        } finally {
1107            DBUtils.closeResultSet(resultSet);
1108            DBUtils.closeStatement(statement);
1109            DBUtils.closeConnection(connection);
1110        }
1111    }
1112
1113    /*
1114     * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
1115     * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
1116     * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1117     * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
1118     * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
1119     * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
1120     * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
1121     * MemberCoolLink1, MemberCoolLink2
1122     * As of 29 Jun 2003: MemberActivateCode
1123     * As of 20 Oct 2004: MemberExpireDate
1124     * As of 10 Dec 2004: MemberFirstIP, MemberLastIP : for admin to view it
1125     * As of 4 Jan 2005: MemberFirstEmail : for admin to view it
1126     * As of 4 Jan 2005: MemberModifiedDate : for admin to view it
1127     * Excluded columns: MemberPassword,
1128     * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1129     * MemberTimeZone, MemberSkin
1130     */

1131    public MemberBean getMember_forPublic(int memberID)
1132        throws ObjectNotFoundException, DatabaseException {
1133
1134        Connection connection = null;
1135        PreparedStatement statement = null;
1136        ResultSet resultSet = null;
1137        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1138        sql.append("SELECT MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode, MemberFirstIP, MemberLastIP, MemberLanguage");
1139        sql.append(" FROM " + TABLE_NAME);
1140        sql.append(" WHERE MemberID = ?");
1141        try {
1142            connection = DBUtils.getConnection();
1143            statement = connection.prepareStatement(sql.toString());
1144            statement.setInt(1, memberID);
1145            resultSet = statement.executeQuery();
1146            if(!resultSet.next()) {
1147                throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ").");
1148            }
1149
1150            MemberBean bean = new MemberBean();
1151            // @todo: uncomment the following line(s) as needed
1152
//bean.setMemberID(memberID);
1153
bean.setMemberID(resultSet.getInt("MemberID"));
1154            bean.setMemberName(resultSet.getString("MemberName"));
1155            bean.setMemberFirstEmail(resultSet.getString("MemberFirstEmail"));
1156            bean.setMemberEmail(resultSet.getString("MemberEmail"));
1157            bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
1158            bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
1159            bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
1160            bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
1161            bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
1162            bean.setMemberModifiedDate(resultSet.getTimestamp("MemberModifiedDate"));
1163            bean.setMemberExpireDate(resultSet.getTimestamp("MemberExpireDate"));
1164            bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
1165            bean.setMemberOption(resultSet.getInt("MemberOption"));
1166            bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1167            bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
1168            bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
1169            bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
1170            bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
1171            bean.setMemberTitle(resultSet.getString("MemberTitle"));
1172            bean.setMemberSignature(resultSet.getString("MemberSignature"));
1173            bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
1174            bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
1175            bean.setMemberLastname(resultSet.getString("MemberLastname"));
1176            bean.setMemberGender(resultSet.getInt("MemberGender"));
1177            bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1178            bean.setMemberLanguage(resultSet.getString("MemberLanguage"));
1179            bean.setMemberAddress(resultSet.getString("MemberAddress"));
1180            bean.setMemberCity(resultSet.getString("MemberCity"));
1181            bean.setMemberState(resultSet.getString("MemberState"));
1182            bean.setMemberCountry(resultSet.getString("MemberCountry"));
1183            bean.setMemberPhone(resultSet.getString("MemberPhone"));
1184            bean.setMemberMobile(resultSet.getString("MemberMobile"));
1185            bean.setMemberFax(resultSet.getString("MemberFax"));
1186            bean.setMemberCareer(resultSet.getString("MemberCareer"));
1187            bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
1188            bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1189            bean.setMemberAol(resultSet.getString("MemberAol"));
1190            bean.setMemberIcq(resultSet.getString("MemberIcq"));
1191            bean.setMemberMsn(resultSet.getString("MemberMsn"));
1192            bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
1193            bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
1194            bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
1195            bean.setMemberFirstIP(resultSet.getString("MemberFirstIP"));
1196            bean.setMemberLastIP(resultSet.getString("MemberLastIP"));
1197            return bean;
1198        } catch(SQLException sqle) {
1199            log.error("Sql Execution Error!", sqle);
1200            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMember_forPublic(pk).");
1201        } finally {
1202            DBUtils.closeResultSet(resultSet);
1203            DBUtils.closeStatement(statement);
1204            DBUtils.closeConnection(connection);
1205        }
1206    }
1207
1208    /**
1209     * Returns number of members in the database. Virtual guest is included.
1210     * @return number of members
1211     * @throws AssertionException
1212     * @throws DatabaseException
1213     */

1214    public int getNumberOfMembers()
1215        throws AssertionException, DatabaseException {
1216
1217        Connection connection = null;
1218        PreparedStatement statement = null;
1219        ResultSet resultSet = null;
1220        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1221        sql.append("SELECT Count(*)");
1222        sql.append(" FROM " + TABLE_NAME);
1223        //sql.append(" WHERE "); // @todo: uncomment as needed
1224
try {
1225            connection = DBUtils.getConnection();
1226            statement = connection.prepareStatement(sql.toString());
1227            resultSet = statement.executeQuery();
1228            if (!resultSet.next()) {
1229                throw new AssertionException("Assertion in MemberDAOImplJDBC.getNumberOfMembers.");
1230            }
1231            return resultSet.getInt(1);
1232        } catch(SQLException sqle) {
1233            log.error("Sql Execution Error!", sqle);
1234            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers.");
1235        } finally {
1236            DBUtils.closeResultSet(resultSet);
1237            DBUtils.closeStatement(statement);
1238            DBUtils.closeConnection(connection);
1239        }
1240    }
1241
1242    /**
1243     * Returns number of members based on the memberStatus. Virtual guest is included.
1244     * @return number of members, based on the memberStatus
1245     * @throws AssertionException
1246     * @throws DatabaseException
1247     */

1248    public int getNumberOfMembers_inMemberStatus(int memberStatus)
1249        throws AssertionException, DatabaseException {
1250
1251        Connection connection = null;
1252        PreparedStatement statement = null;
1253        ResultSet resultSet = null;
1254        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1255        sql.append("SELECT Count(*)");
1256        sql.append(" FROM " + TABLE_NAME);
1257        sql.append(" WHERE MemberStatus = ? ");
1258        try {
1259            connection = DBUtils.getConnection();
1260            statement = connection.prepareStatement(sql.toString());
1261            statement.setInt(1, memberStatus);
1262            resultSet = statement.executeQuery();
1263            if (!resultSet.next()) {
1264                throw new AssertionException("Assertion in MemberDAOImplJDBC.getNumberOfMembers_inMemberStatus.");
1265            }
1266            return resultSet.getInt(1);
1267        } catch(SQLException sqle) {
1268            log.error("Sql Execution Error!", sqle);
1269            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers_inMemberStatus.");
1270        } finally {
1271            DBUtils.closeResultSet(resultSet);
1272            DBUtils.closeStatement(statement);
1273            DBUtils.closeConnection(connection);
1274        }
1275    }
1276
1277    /**
1278     * Returns number of members based on the memberActivateCode. Virtual guest is included.
1279     * @return number of members, based on the memberActivateCode
1280     * @throws AssertionException
1281     * @throws DatabaseException
1282     */

1283    public int getNumberOfMembers_inActivationStatus(boolean activated)
1284        throws AssertionException, DatabaseException {
1285
1286        Connection connection = null;
1287        PreparedStatement statement = null;
1288        ResultSet resultSet = null;
1289        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1290        sql.append("SELECT Count(*)");
1291        sql.append(" FROM " + TABLE_NAME);
1292        if (activated) {
1293            sql.append(" WHERE MemberActivateCode = 'activated' ");
1294        } else {
1295            // use IS NULL to fix problem of Oracle (Thanks sssimon)
1296
sql.append(" WHERE (MemberActivateCode <> 'activated') OR (MemberActivateCode IS NULL) ");
1297        }
1298        try {
1299            connection = DBUtils.getConnection();
1300            statement = connection.prepareStatement(sql.toString());
1301            resultSet = statement.executeQuery();
1302            if (!resultSet.next()) {
1303                throw new AssertionException("Assertion in MemberDAOImplJDBC.getNumberOfMembers_inActivationStatus.");
1304            }
1305            return resultSet.getInt(1);
1306        } catch(SQLException sqle) {
1307            log.error("Sql Execution Error!", sqle);
1308            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers_inActivationStatus.");
1309        } finally {
1310            DBUtils.closeResultSet(resultSet);
1311            DBUtils.closeStatement(statement);
1312            DBUtils.closeConnection(connection);
1313        }
1314    }
1315
1316
1317/************************************************
1318 * Customized methods come below
1319 ************************************************/

1320
1321    /* @todo check if this method work with other DBMS other than MySql (check case-sensitive) */
1322    public final int getMemberIDFromMemberName(String JavaDoc memberName)
1323        throws ObjectNotFoundException, DatabaseException {
1324
1325        Connection connection = null;
1326        PreparedStatement statement = null;
1327        ResultSet resultSet = null;
1328        String JavaDoc sql = "SELECT MemberID FROM " + TABLE_NAME + " WHERE MemberName = ?";
1329        try {
1330            connection = DBUtils.getConnection();
1331            statement = connection.prepareStatement(sql);
1332            statement.setString(1, memberName);
1333            resultSet = statement.executeQuery();
1334            if(!resultSet.next()) {
1335                throw new ObjectNotFoundException("Cannot find the row in table Member where MemberName = " + memberName);
1336            }
1337            return resultSet.getInt(1);
1338        } catch(SQLException sqle) {
1339            log.error("Sql Execution Error!", sqle);
1340            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMemberIDFromMemberName.");
1341        } finally {
1342            DBUtils.closeResultSet(resultSet);
1343            DBUtils.closeStatement(statement);
1344            DBUtils.closeConnection(connection);
1345        }
1346    }
1347
1348    /* @todo check if this method work with other DBMS other than MySql (check case-sensitive) */
1349    public final int getMemberIDFromMemberEmail(String JavaDoc memberEmail)
1350        throws ObjectNotFoundException, DatabaseException {
1351
1352        Connection connection = null;
1353        PreparedStatement statement = null;
1354        ResultSet resultSet = null;
1355        String JavaDoc sql = "SELECT MemberID FROM " + TABLE_NAME + " WHERE MemberEmail = ?";
1356        try {
1357            connection = DBUtils.getConnection();
1358            statement = connection.prepareStatement(sql);
1359            statement.setString(1, memberEmail);
1360            resultSet = statement.executeQuery();
1361            if(!resultSet.next()) {
1362                throw new ObjectNotFoundException("Cannot find the row in table Member where MemberEmail = " + memberEmail);
1363            }
1364            return resultSet.getInt(1);
1365        } catch(SQLException sqle) {
1366            log.error("Sql Execution Error!", sqle);
1367            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMemberIDFromMemberEmail.");
1368        } finally {
1369            DBUtils.closeResultSet(resultSet);
1370            DBUtils.closeStatement(statement);
1371            DBUtils.closeConnection(connection);
1372        }
1373    }
1374
1375    public Collection JavaDoc getMembers_withSortSupport_limit(int offset, int rowsToReturn, String JavaDoc sort, String JavaDoc order)
1376        throws IllegalArgumentException JavaDoc, DatabaseException {
1377        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
1378            return getBeans_withSortSupport_limit_mysql(offset, rowsToReturn, sort, order);
1379        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
1380            return getBeans_withSortSupport_limit_noscroll(offset, rowsToReturn, sort, order);
1381        }
1382        return getBeans_withSortSupport_limit_general(offset, rowsToReturn, sort, order);
1383    }
1384
1385    /*
1386     * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
1387     * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
1388     * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1389     * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
1390     * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
1391     * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
1392     * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
1393     * MemberCoolLink1, MemberCoolLink2
1394     * as of 10 March, add MemberActivateCode
1395     * Excluded columns: MemberPassword, MemberFirstEmail, MemberFirstIP, MemberLastIP, MemberModifiedDate,
1396     * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1397     * MemberTimeZone, MemberSkin, MemberLanguage
1398     */

1399    /**
1400     * This method support sorting and for PUBLIC view
1401     */

1402    /* @todo fix bug that cannot prepare sort and order */
1403    private Collection JavaDoc getBeans_withSortSupport_limit_mysql(int offset, int rowsToReturn, String JavaDoc sort, String JavaDoc order)
1404        throws IllegalArgumentException JavaDoc, DatabaseException {
1405
1406        if (offset < 0) throw new IllegalArgumentException JavaDoc("The offset < 0 is not allowed.");
1407        if (rowsToReturn <= 0) throw new IllegalArgumentException JavaDoc("The rowsToReturn <= 0 is not allowed.");
1408    /*
1409     * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1410     * MemberTitle,
1411     * MemberCity, MemberState,
1412     * MemberCountry, MemberCareer,
1413     */

1414        if ((!sort.equals("MemberID")) &&
1415            (!sort.equals("MemberName")) &&
1416            (!sort.equals("MemberFirstname")) &&
1417            (!sort.equals("MemberLastname")) &&
1418            (!sort.equals("MemberGender")) &&
1419            (!sort.equals("MemberBirthday")) &&
1420            (!sort.equals("MemberCreationDate")) &&
1421            (!sort.equals("MemberLastLogon")) &&
1422            (!sort.equals("MemberViewCount")) &&
1423            (!sort.equals("MemberPostCount")) ) {
1424            throw new IllegalArgumentException JavaDoc("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
1425        }
1426
1427        if ((!order.equals("ASC")) &&
1428            (!order.equals("DESC")) ) {
1429            throw new IllegalArgumentException JavaDoc("Cannot sort, reason: dont understand the order '" + order + "'.");
1430        }
1431
1432        Connection connection = null;
1433        PreparedStatement statement = null;
1434        ResultSet resultSet = null;
1435        Collection JavaDoc retValue = new ArrayList JavaDoc();
1436        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1437        sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode");
1438        sql.append(" FROM " + TABLE_NAME);
1439        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1440
sql.append(" LIMIT ?, ?");
1441
1442        try {
1443            connection = DBUtils.getConnection();
1444            statement = connection.prepareStatement(sql.toString());
1445            statement.setInt(1, offset);
1446            statement.setInt(2, rowsToReturn);
1447            resultSet = statement.executeQuery();
1448            while (resultSet.next()) {
1449                MemberBean bean = new MemberBean();
1450                bean.setMemberID(resultSet.getInt("MemberID"));
1451                bean.setMemberName(resultSet.getString("MemberName"));
1452                bean.setMemberEmail(resultSet.getString("MemberEmail"));
1453                bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
1454                bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
1455                bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
1456                bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
1457                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
1458                bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
1459                bean.setMemberOption(resultSet.getInt("MemberOption"));
1460                bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1461                bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
1462                bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
1463                bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
1464                bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
1465                bean.setMemberTitle(resultSet.getString("MemberTitle"));
1466                bean.setMemberSignature(resultSet.getString("MemberSignature"));
1467                bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
1468                bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
1469                bean.setMemberLastname(resultSet.getString("MemberLastname"));
1470                bean.setMemberGender(resultSet.getInt("MemberGender"));
1471                bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1472                bean.setMemberAddress(resultSet.getString("MemberAddress"));
1473                bean.setMemberCity(resultSet.getString("MemberCity"));
1474                bean.setMemberState(resultSet.getString("MemberState"));
1475                bean.setMemberCountry(resultSet.getString("MemberCountry"));
1476                bean.setMemberPhone(resultSet.getString("MemberPhone"));
1477                bean.setMemberMobile(resultSet.getString("MemberMobile"));
1478                bean.setMemberFax(resultSet.getString("MemberFax"));
1479                bean.setMemberCareer(resultSet.getString("MemberCareer"));
1480                bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
1481                bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1482                bean.setMemberAol(resultSet.getString("MemberAol"));
1483                bean.setMemberIcq(resultSet.getString("MemberIcq"));
1484                bean.setMemberMsn(resultSet.getString("MemberMsn"));
1485                bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
1486                bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
1487                bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
1488                retValue.add(bean);
1489            }
1490            return retValue;
1491        } catch(SQLException sqle) {
1492            log.error("Sql Execution Error!", sqle);
1493            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getBeans_withSortSupport_limit_mysql.");
1494        } finally {
1495            DBUtils.closeResultSet(resultSet);
1496            DBUtils.closeStatement(statement);
1497            DBUtils.closeConnection(connection);
1498        }
1499    }
1500
1501    /*
1502     * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
1503     * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
1504     * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1505     * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
1506     * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
1507     * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
1508     * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
1509     * MemberCoolLink1, MemberCoolLink2
1510     * as of 10 March, add MemberActivateCode
1511     * Excluded columns: MemberPassword, MemberFirstEmail, MemberFirstIP, MemberLastIP, MemberModifiedDate,
1512     * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1513     * MemberTimeZone, MemberSkin, MemberLanguage
1514     */

1515    /**
1516     * This method support sorting and for PUBLIC view
1517     */

1518    /* @todo fix bug that cannot prepare sort and order */
1519    private Collection JavaDoc getBeans_withSortSupport_limit_noscroll(int offset, int rowsToReturn, String JavaDoc sort, String JavaDoc order)
1520        throws IllegalArgumentException JavaDoc, DatabaseException {
1521        if (offset < 0) throw new IllegalArgumentException JavaDoc("The offset < 0 is not allowed.");
1522        if (rowsToReturn <= 0) throw new IllegalArgumentException JavaDoc("The rowsToReturn <= 0 is not allowed.");
1523    /*
1524     * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1525     * MemberTitle,
1526     * MemberCity, MemberState,
1527     * MemberCountry, MemberCareer,
1528     */

1529        if ((!sort.equals("MemberID")) &&
1530            (!sort.equals("MemberName")) &&
1531            (!sort.equals("MemberFirstname")) &&
1532            (!sort.equals("MemberLastname")) &&
1533            (!sort.equals("MemberGender")) &&
1534            (!sort.equals("MemberBirthday")) &&
1535            (!sort.equals("MemberCreationDate")) &&
1536            (!sort.equals("MemberLastLogon")) &&
1537            (!sort.equals("MemberViewCount")) &&
1538            (!sort.equals("MemberPostCount")) ) {
1539            throw new IllegalArgumentException JavaDoc("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
1540        }
1541
1542        if ((!order.equals("ASC")) &&
1543            (!order.equals("DESC")) ) {
1544            throw new IllegalArgumentException JavaDoc("Cannot sort, reason: dont understand the order '" + order + "'.");
1545        }
1546
1547        Connection connection = null;
1548        PreparedStatement statement = null;
1549        ResultSet resultSet = null;
1550        Collection JavaDoc retValue = new ArrayList JavaDoc();
1551        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1552        sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode");
1553        sql.append(" FROM " + TABLE_NAME);
1554        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1555

1556        try {
1557            connection = DBUtils.getConnection();
1558            statement = connection.prepareStatement(sql.toString());
1559            statement.setMaxRows(offset + rowsToReturn);
1560            resultSet = statement.executeQuery();
1561            int rowIndex = -1;
1562            while (resultSet.next()) {
1563                rowIndex++;
1564                if (rowIndex < offset) continue;
1565                MemberBean bean = new MemberBean();
1566                bean.setMemberID(resultSet.getInt("MemberID"));
1567                bean.setMemberName(resultSet.getString("MemberName"));
1568                bean.setMemberEmail(resultSet.getString("MemberEmail"));
1569                bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
1570                bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
1571                bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
1572                bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
1573                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
1574                bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
1575                bean.setMemberOption(resultSet.getInt("MemberOption"));
1576                bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1577                bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
1578                bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
1579                bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
1580                bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
1581                bean.setMemberTitle(resultSet.getString("MemberTitle"));
1582                bean.setMemberSignature(resultSet.getString("MemberSignature"));
1583                bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
1584                bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
1585                bean.setMemberLastname(resultSet.getString("MemberLastname"));
1586                bean.setMemberGender(resultSet.getInt("MemberGender"));
1587                bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1588                bean.setMemberAddress(resultSet.getString("MemberAddress"));
1589                bean.setMemberCity(resultSet.getString("MemberCity"));
1590                bean.setMemberState(resultSet.getString("MemberState"));
1591                bean.setMemberCountry(resultSet.getString("MemberCountry"));
1592                bean.setMemberPhone(resultSet.getString("MemberPhone"));
1593                bean.setMemberMobile(resultSet.getString("MemberMobile"));
1594                bean.setMemberFax(resultSet.getString("MemberFax"));
1595                bean.setMemberCareer(resultSet.getString("MemberCareer"));
1596                bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
1597                bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1598                bean.setMemberAol(resultSet.getString("MemberAol"));
1599                bean.setMemberIcq(resultSet.getString("MemberIcq"));
1600                bean.setMemberMsn(resultSet.getString("MemberMsn"));
1601                bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
1602                bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
1603                bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
1604                retValue.add(bean);
1605                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
1606
}
1607            return retValue;
1608        } catch(SQLException sqle) {
1609            log.error("Sql Execution Error!", sqle);
1610            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
1611        } finally {
1612            DBUtils.closeResultSet(resultSet);
1613            DBUtils.resetStatement(statement);
1614            DBUtils.closeStatement(statement);
1615            DBUtils.closeConnection(connection);
1616        }
1617    }
1618
1619    /*
1620     * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
1621     * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
1622     * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1623     * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
1624     * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
1625     * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
1626     * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
1627     * MemberCoolLink1, MemberCoolLink2
1628     * as of 10 March, add MemberActivateCode
1629     * Excluded columns: MemberPassword, MemberFirstEmail, MemberFirstIP, MemberLastIP, MemberModifiedDate,
1630     * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1631     * MemberTimeZone, MemberSkin, MemberLanguage
1632     */

1633    /**
1634     * This method support sorting and for PUBLIC view
1635     */

1636    /* @todo fix bug that cannot prepare sort and order */
1637    private Collection JavaDoc getBeans_withSortSupport_limit_general(int offset, int rowsToReturn, String JavaDoc sort, String JavaDoc order)
1638        throws IllegalArgumentException JavaDoc, DatabaseException {
1639
1640        if (offset < 0) throw new IllegalArgumentException JavaDoc("The offset < 0 is not allowed.");
1641        if (rowsToReturn <= 0) throw new IllegalArgumentException JavaDoc("The rowsToReturn <= 0 is not allowed.");
1642    /*
1643     * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1644     * MemberTitle,
1645     * MemberCity, MemberState,
1646     * MemberCountry, MemberCareer,
1647     */

1648        if ((!sort.equals("MemberID")) &&
1649            (!sort.equals("MemberName")) &&
1650            (!sort.equals("MemberFirstname")) &&
1651            (!sort.equals("MemberLastname")) &&
1652            (!sort.equals("MemberGender")) &&
1653            (!sort.equals("MemberBirthday")) &&
1654            (!sort.equals("MemberCreationDate")) &&
1655            (!sort.equals("MemberLastLogon")) &&
1656            (!sort.equals("MemberViewCount")) &&
1657            (!sort.equals("MemberPostCount")) ) {
1658            throw new IllegalArgumentException JavaDoc("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
1659        }
1660
1661        if ((!order.equals("ASC")) &&
1662            (!order.equals("DESC")) ) {
1663            throw new IllegalArgumentException JavaDoc("Cannot sort, reason: dont understand the order '" + order + "'.");
1664        }
1665
1666        Connection connection = null;
1667        PreparedStatement statement = null;
1668        ResultSet resultSet = null;
1669        Collection JavaDoc retValue = new ArrayList JavaDoc();
1670        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1671        sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode");
1672        sql.append(" FROM " + TABLE_NAME);
1673        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1674

1675        try {
1676            connection = DBUtils.getConnection();
1677            statement = connection.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
1678            statement.setMaxRows(offset + rowsToReturn);
1679            try {
1680                statement.setFetchSize(rowsToReturn);
1681            } catch (SQLException sqle) {
1682                //do nothing, postgreSQL doesnt support this method
1683
}
1684            resultSet = statement.executeQuery();
1685            boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
1686
while (loop) {
1687                MemberBean bean = new MemberBean();
1688                bean.setMemberID(resultSet.getInt("MemberID"));
1689                bean.setMemberName(resultSet.getString("MemberName"));
1690                bean.setMemberEmail(resultSet.getString("MemberEmail"));
1691                bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
1692                bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
1693                bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
1694                bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
1695                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
1696                bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
1697                bean.setMemberOption(resultSet.getInt("MemberOption"));
1698                bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1699                bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
1700                bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
1701                bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
1702                bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
1703                bean.setMemberTitle(resultSet.getString("MemberTitle"));
1704                bean.setMemberSignature(resultSet.getString("MemberSignature"));
1705                bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
1706                bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
1707                bean.setMemberLastname(resultSet.getString("MemberLastname"));
1708                bean.setMemberGender(resultSet.getInt("MemberGender"));
1709                bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1710                bean.setMemberAddress(resultSet.getString("MemberAddress"));
1711                bean.setMemberCity(resultSet.getString("MemberCity"));
1712                bean.setMemberState(resultSet.getString("MemberState"));
1713                bean.setMemberCountry(resultSet.getString("MemberCountry"));
1714                bean.setMemberPhone(resultSet.getString("MemberPhone"));
1715                bean.setMemberMobile(resultSet.getString("MemberMobile"));
1716                bean.setMemberFax(resultSet.getString("MemberFax"));
1717                bean.setMemberCareer(resultSet.getString("MemberCareer"));
1718                bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
1719                bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1720                bean.setMemberAol(resultSet.getString("MemberAol"));
1721                bean.setMemberIcq(resultSet.getString("MemberIcq"));
1722                bean.setMemberMsn(resultSet.getString("MemberMsn"));
1723                bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
1724                bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
1725                bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
1726                retValue.add(bean);
1727                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
1728
loop = resultSet.next();
1729            }//while
1730
return retValue;
1731        } catch(SQLException sqle) {
1732            log.error("Sql Execution Error!", sqle);
1733            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getBeans_withSortSupport_limit_general.");
1734        } finally {
1735            DBUtils.closeResultSet(resultSet);
1736            DBUtils.resetStatement(statement);
1737            DBUtils.closeStatement(statement);
1738            DBUtils.closeConnection(connection);
1739        }
1740    }
1741
1742    /*
1743     * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
1744     * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
1745     * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1746     * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
1747     * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
1748     * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
1749     * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
1750     * MemberCoolLink1, MemberCoolLink2
1751     * as of 10 March, add MemberActivateCode
1752     * Excluded columns: MemberPassword, MemberFirstEmail, MemberFirstIP, MemberLastIP, MemberModifiedDate,
1753     * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1754     * MemberTimeZone, MemberSkin, MemberLanguage
1755     */

1756    /**
1757     * This method support sorting and for PUBLIC view
1758     */

1759    public Collection JavaDoc getEnableMembers_inActivationStatus(String JavaDoc kind)
1760        throws IllegalArgumentException JavaDoc, DatabaseException {
1761
1762        Connection connection = null;
1763        PreparedStatement statement = null;
1764        ResultSet resultSet = null;
1765        Collection JavaDoc retValue = new ArrayList JavaDoc();
1766        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1767
1768        sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode");
1769        sql.append(" FROM " + TABLE_NAME);
1770        if (kind.equals("activated")) {
1771            sql.append(" WHERE (MemberStatus = 0) AND (MemberActivateCode = 'activated') ");
1772        } else if (kind.equals("nonactivated")) {
1773            // use IS NULL to fix problem of Oracle (Thanks sssimon)
1774
sql.append(" WHERE (MemberStatus = 0) AND ((MemberActivateCode <> 'activated') OR (MemberActivateCode IS NULL))");
1775        } else if (kind.equals("all")) {
1776            sql.append(" WHERE (MemberStatus = 0)");
1777        } else {
1778            throw new IllegalArgumentException JavaDoc("Cannot process activation kind = " + kind);
1779        }
1780
1781        try {
1782            connection = DBUtils.getConnection();
1783            statement = connection.prepareStatement(sql.toString());
1784
1785            resultSet = statement.executeQuery();
1786            while (resultSet.next()) {
1787                MemberBean bean = new MemberBean();
1788                bean.setMemberID(resultSet.getInt("MemberID"));
1789                bean.setMemberName(resultSet.getString("MemberName"));
1790                bean.setMemberEmail(resultSet.getString("MemberEmail"));
1791                bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
1792                bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
1793                bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
1794                bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
1795                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
1796                bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
1797                bean.setMemberOption(resultSet.getInt("MemberOption"));
1798                bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1799                bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
1800                bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
1801                bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
1802                bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
1803                bean.setMemberTitle(resultSet.getString("MemberTitle"));
1804                bean.setMemberSignature(resultSet.getString("MemberSignature"));
1805                bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
1806                bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
1807                bean.setMemberLastname(resultSet.getString("MemberLastname"));
1808                bean.setMemberGender(resultSet.getInt("MemberGender"));
1809                bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1810                bean.setMemberAddress(resultSet.getString("MemberAddress"));
1811                bean.setMemberCity(resultSet.getString("MemberCity"));
1812                bean.setMemberState(resultSet.getString("MemberState"));
1813                bean.setMemberCountry(resultSet.getString("MemberCountry"));
1814                bean.setMemberPhone(resultSet.getString("MemberPhone"));
1815                bean.setMemberMobile(resultSet.getString("MemberMobile"));
1816                bean.setMemberFax(resultSet.getString("MemberFax"));
1817                bean.setMemberCareer(resultSet.getString("MemberCareer"));
1818                bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
1819                bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1820                bean.setMemberAol(resultSet.getString("MemberAol"));
1821                bean.setMemberIcq(resultSet.getString("MemberIcq"));
1822                bean.setMemberMsn(resultSet.getString("MemberMsn"));
1823                bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
1824                bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
1825                bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
1826                retValue.add(bean);
1827            }//while
1828
return retValue;
1829        } catch(SQLException sqle) {
1830            log.error("Sql Execution Error!", sqle);
1831            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getEnableMembers_inActivationStatus.");
1832        } finally {
1833            DBUtils.closeResultSet(resultSet);
1834            DBUtils.resetStatement(statement);
1835            DBUtils.closeStatement(statement);
1836            DBUtils.closeConnection(connection);
1837        }
1838    }
1839
1840
1841    /*
1842     * Included columns: MemberStatus
1843     * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
1844     * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
1845     * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
1846     * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1847     * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
1848     * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
1849     * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
1850     * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
1851     * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
1852     * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
1853     */

1854    public void updateStatus(int memberID, // primary key
1855
int memberStatus)
1856        throws ObjectNotFoundException, DatabaseException {
1857
1858        Connection connection = null;
1859        PreparedStatement statement = null;
1860        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1861        sql.append("UPDATE " + TABLE_NAME + " SET MemberStatus = ?");
1862        sql.append(" WHERE MemberID = ?");
1863        try {
1864            connection = DBUtils.getConnection();
1865            statement = connection.prepareStatement(sql.toString());
1866
1867            // // column(s) to update
1868
statement.setInt(1, memberStatus);
1869
1870            // primary key column(s)
1871
statement.setInt(2, memberID);
1872
1873            if (statement.executeUpdate() != 1) {
1874                throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
1875            }
1876            setDirty(true);
1877        } catch(SQLException sqle) {
1878            log.error("Sql Execution Error!", sqle);
1879            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateStatus.");
1880        } finally {
1881            DBUtils.closeStatement(statement);
1882            DBUtils.closeConnection(connection);
1883        }
1884    }
1885
1886    public void updatePostCount(int memberID, // primary key
1887
int memberPostCount)
1888        throws ObjectNotFoundException, DatabaseException {
1889
1890        Connection connection = null;
1891        PreparedStatement statement = null;
1892        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
1893        sql.append("UPDATE " + TABLE_NAME + " SET MemberPostCount = ?");
1894        sql.append(" WHERE MemberID = ?");
1895        try {
1896            connection = DBUtils.getConnection();
1897            statement = connection.prepareStatement(sql.toString());
1898
1899            // // column(s) to update
1900
statement.setInt(1, memberPostCount);
1901
1902            // primary key column(s)
1903
statement.setInt(2, memberID);
1904
1905            if (statement.executeUpdate() != 1) {
1906                throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
1907            }
1908            setDirty(true);
1909        } catch(SQLException sqle) {
1910            log.error("Sql Execution Error!", sqle);
1911            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updatePostCount.");
1912        } finally {
1913            DBUtils.closeStatement(statement);
1914            DBUtils.closeConnection(connection);
1915        }
1916    }
1917
1918    /**
1919     * This method should be call only when we can make sure that memberID is in database
1920     */

1921    public void increaseViewCount(int memberID)
1922        throws DatabaseException, ObjectNotFoundException {
1923
1924        Connection connection = null;
1925        PreparedStatement statement = null;
1926        String JavaDoc sql = "UPDATE " + TABLE_NAME + " SET MemberViewCount = MemberViewCount + 1 WHERE MemberID = ?";
1927        try {
1928            connection = DBUtils.getConnection();
1929            statement = connection.prepareStatement(sql);
1930            statement.setInt(1, memberID);
1931            if (statement.executeUpdate() != 1) {
1932                throw new ObjectNotFoundException("Cannot update the MemberViewCount in table Member. Please contact Web site Administrator.");
1933            }
1934            //@todo: coi lai cho nay
1935
// ATTENTION !!!
1936
setDirty(true);
1937        } catch (SQLException sqle) {
1938            log.error("Sql Execution Error!", sqle);
1939            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.increaseViewCount.");
1940        } finally {
1941            DBUtils.closeStatement(statement);
1942            DBUtils.closeConnection(connection);
1943        }
1944    }
1945
1946    /**
1947     * This method should be call only when we can make sure that memberID is in database
1948     */

1949    public void increasePostCount(int memberID)
1950        throws DatabaseException, ObjectNotFoundException {
1951
1952        Connection connection = null;
1953        PreparedStatement statement = null;
1954        String JavaDoc sql = "UPDATE " + TABLE_NAME + " SET MemberPostCount = MemberPostCount + 1 WHERE MemberID = ?";
1955        try {
1956            connection = DBUtils.getConnection();
1957            statement = connection.prepareStatement(sql);
1958            statement.setInt(1, memberID);
1959            if (statement.executeUpdate() != 1) {
1960                throw new ObjectNotFoundException("Cannot update the MemberPostCount in table Member. Please contact Web site Administrator.");
1961            }
1962            //@todo: coi lai cho nay
1963
// ATTENTION !!!
1964
setDirty(true);
1965        } catch (SQLException sqle) {
1966            log.error("Sql Execution Error!", sqle);
1967            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.increasePostCount.");
1968        } finally {
1969            DBUtils.closeStatement(statement);
1970            DBUtils.closeConnection(connection);
1971        }
1972    }
1973
1974    public Collection JavaDoc getMembers_inExpire_limit(Timestamp expireDate, int offset, int rowsToReturn, String JavaDoc sort, String JavaDoc order)
1975        throws IllegalArgumentException JavaDoc, DatabaseException {
1976        if (offset < 0) throw new IllegalArgumentException JavaDoc("The offset < 0 is not allowed.");
1977        if (rowsToReturn <= 0) throw new IllegalArgumentException JavaDoc("The rowsToReturn <= 0 is not allowed.");
1978
1979    /*
1980     * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1981     * MemberTitle,
1982     * MemberCity, MemberState,
1983     * MemberCountry, MemberCareer,
1984     */

1985        if ((!sort.equals("MemberID")) &&
1986            (!sort.equals("MemberName")) &&
1987            (!sort.equals("MemberFirstname")) &&
1988            (!sort.equals("MemberLastname")) &&
1989            (!sort.equals("MemberGender")) &&
1990            (!sort.equals("MemberBirthday")) &&
1991            (!sort.equals("MemberCreationDate")) &&
1992            (!sort.equals("MemberExpireDate")) &&
1993            (!sort.equals("MemberLastLogon")) &&
1994            (!sort.equals("MemberViewCount")) &&
1995            (!sort.equals("MemberPostCount")) ) {
1996            throw new IllegalArgumentException JavaDoc("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
1997        }
1998
1999        if ((!order.equals("ASC")) &&
2000            (!order.equals("DESC")) ) {
2001            throw new IllegalArgumentException JavaDoc("Cannot sort, reason: dont understand the order '" + order + "'.");
2002        }
2003
2004        Connection connection = null;
2005        PreparedStatement statement = null;
2006        ResultSet resultSet = null;
2007        Collection JavaDoc retValue = new ArrayList JavaDoc();
2008        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
2009        sql.append("SELECT MemberID, MemberName, MemberCreationDate, MemberExpireDate, MemberStatus");
2010        sql.append(" FROM " + TABLE_NAME);
2011        sql.append(" WHERE MemberExpireDate <= ? OR MemberExpireDate IS NULL");
2012        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
2013
sql.append(" LIMIT ?, ?");
2014        try {
2015            connection = DBUtils.getConnection();
2016            statement = connection.prepareStatement(sql.toString());
2017            statement.setTimestamp(1, expireDate);
2018            statement.setInt(2, offset);
2019            statement.setInt(3, rowsToReturn);
2020            resultSet = statement.executeQuery();
2021            while (resultSet.next()) {
2022                MemberBean bean = new MemberBean();
2023                bean.setMemberID(resultSet.getInt("MemberID"));
2024                bean.setMemberName(resultSet.getString("MemberName"));
2025                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
2026                bean.setMemberExpireDate(resultSet.getTimestamp("MemberExpireDate"));
2027                bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2028                retValue.add(bean);
2029            }
2030            return retValue;
2031        } catch(SQLException sqle) {
2032            log.error("Sql Execution Error!", sqle);
2033            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMembers_inExpire_limit.");
2034        } finally {
2035            DBUtils.closeResultSet(resultSet);
2036            DBUtils.closeStatement(statement);
2037            DBUtils.closeConnection(connection);
2038        }
2039    }
2040
2041    public int getNumberOfMembers_inExpire(Timestamp expireDate)
2042        throws AssertionException, DatabaseException {
2043
2044        Connection connection = null;
2045        PreparedStatement statement = null;
2046        ResultSet resultSet = null;
2047        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
2048        sql.append("SELECT Count(*)");
2049        sql.append(" FROM " + TABLE_NAME);
2050        sql.append(" WHERE MemberExpireDate <= ? OR MemberExpireDate IS NULL");
2051        try {
2052            connection = DBUtils.getConnection();
2053            statement = connection.prepareStatement(sql.toString());
2054            statement.setTimestamp(1, expireDate);
2055            resultSet = statement.executeQuery();
2056            if (!resultSet.next()) {
2057                throw new AssertionException("Assertion in MemberDAOImplJDBC.getNumberOfMembers_inExpire.");
2058            }
2059            return resultSet.getInt(1);
2060        } catch(SQLException sqle) {
2061            log.error("Sql Execution Error!", sqle);
2062            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers_inExpire.");
2063        } finally {
2064            DBUtils.closeResultSet(resultSet);
2065            DBUtils.closeStatement(statement);
2066            DBUtils.closeConnection(connection);
2067        }
2068    }
2069
2070    public void updateMember_expireDate(int memberID, // primary key
2071
Timestamp expireDate)
2072        throws ObjectNotFoundException, DatabaseException {
2073
2074        Connection connection = null;
2075        PreparedStatement statement = null;
2076        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
2077        sql.append("UPDATE " + TABLE_NAME + " SET MemberExpireDate = ?");
2078        sql.append(" WHERE MemberID = ?");
2079        try {
2080            connection = DBUtils.getConnection();
2081            statement = connection.prepareStatement(sql.toString());
2082
2083            // // column(s) to update
2084
statement.setTimestamp(1, expireDate);
2085
2086            // primary key column(s)
2087
statement.setInt(2, memberID);
2088
2089            if (statement.executeUpdate() != 1) {
2090                throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ").");
2091            }
2092            m_dirty = true;
2093        } catch(SQLException sqle) {
2094            log.error("Sql Execution Error!", sqle);
2095            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateMember_expireDate.");
2096        } finally {
2097            DBUtils.closeStatement(statement);
2098            DBUtils.closeConnection(connection);
2099        }
2100    }
2101
2102    public Collection JavaDoc getMembers()
2103        throws DatabaseException {
2104
2105        Connection connection = null;
2106        Statement statement = null;
2107        ResultSet resultSet = null;
2108        Collection JavaDoc retValue = new ArrayList JavaDoc();
2109        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
2110        sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode");
2111        sql.append(" FROM " + TABLE_NAME);
2112
2113        try {
2114            connection = DBUtils.getConnection();
2115            statement = connection.createStatement();
2116            resultSet = statement.executeQuery(sql.toString());
2117            while (resultSet.next()) {
2118                MemberBean bean = new MemberBean();
2119                bean.setMemberID(resultSet.getInt("MemberID"));
2120                bean.setMemberName(resultSet.getString("MemberName"));
2121                bean.setMemberEmail(resultSet.getString("MemberEmail"));
2122                bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
2123                bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
2124                bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
2125                bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
2126                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
2127                bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
2128                bean.setMemberOption(resultSet.getInt("MemberOption"));
2129                bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2130                bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
2131                bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
2132                bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
2133                bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
2134                bean.setMemberTitle(resultSet.getString("MemberTitle"));
2135                bean.setMemberSignature(resultSet.getString("MemberSignature"));
2136                bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
2137                bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
2138                bean.setMemberLastname(resultSet.getString("MemberLastname"));
2139                bean.setMemberGender(resultSet.getInt("MemberGender"));
2140                bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
2141                bean.setMemberAddress(resultSet.getString("MemberAddress"));
2142                bean.setMemberCity(resultSet.getString("MemberCity"));
2143                bean.setMemberState(resultSet.getString("MemberState"));
2144                bean.setMemberCountry(resultSet.getString("MemberCountry"));
2145                bean.setMemberPhone(resultSet.getString("MemberPhone"));
2146                bean.setMemberMobile(resultSet.getString("MemberMobile"));
2147                bean.setMemberFax(resultSet.getString("MemberFax"));
2148                bean.setMemberCareer(resultSet.getString("MemberCareer"));
2149                bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
2150                bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
2151                bean.setMemberAol(resultSet.getString("MemberAol"));
2152                bean.setMemberIcq(resultSet.getString("MemberIcq"));
2153                bean.setMemberMsn(resultSet.getString("MemberMsn"));
2154                bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
2155                bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
2156                bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
2157                retValue.add(bean);
2158            }
2159            return retValue;
2160        } catch(SQLException sqle) {
2161            log.error("Sql Execution Error!", sqle);
2162            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMembers.");
2163        } finally {
2164            DBUtils.closeResultSet(resultSet);
2165            DBUtils.closeStatement(statement);
2166            DBUtils.closeConnection(connection);
2167        }
2168    }
2169
2170    public Collection JavaDoc getEnableMembers_inGroup(int groupID)
2171        throws DatabaseException {
2172
2173        Connection connection = null;
2174        PreparedStatement statement = null;
2175        ResultSet resultSet = null;
2176        Collection JavaDoc retValue = new ArrayList JavaDoc();
2177        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
2178
2179        sql.append("SELECT m.MemberID, m.MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode");
2180        sql.append(" FROM " + TABLE_NAME + " m , " + MemberGroupDAO.TABLE_NAME + " mg");
2181        sql.append(" WHERE (MemberStatus = 0) AND (mg.MemberID = m.MemberID) AND GroupID = ?");
2182        try {
2183            connection = DBUtils.getConnection();
2184            statement = connection.prepareStatement(sql.toString());
2185            statement.setInt(1, groupID);
2186            resultSet = statement.executeQuery();
2187            while (resultSet.next()) {
2188                MemberBean bean = new MemberBean();
2189                bean.setMemberID(resultSet.getInt("MemberID"));
2190                bean.setMemberName(resultSet.getString("MemberName"));
2191                bean.setMemberEmail(resultSet.getString("MemberEmail"));
2192                bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
2193                bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
2194                bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
2195                bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
2196                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
2197                bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
2198                bean.setMemberOption(resultSet.getInt("MemberOption"));
2199                bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2200                bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
2201                bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
2202                bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
2203                bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
2204                bean.setMemberTitle(resultSet.getString("MemberTitle"));
2205                bean.setMemberSignature(resultSet.getString("MemberSignature"));
2206                bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
2207                bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
2208                bean.setMemberLastname(resultSet.getString("MemberLastname"));
2209                bean.setMemberGender(resultSet.getInt("MemberGender"));
2210                bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
2211                bean.setMemberAddress(resultSet.getString("MemberAddress"));
2212                bean.setMemberCity(resultSet.getString("MemberCity"));
2213                bean.setMemberState(resultSet.getString("MemberState"));
2214                bean.setMemberCountry(resultSet.getString("MemberCountry"));
2215                bean.setMemberPhone(resultSet.getString("MemberPhone"));
2216                bean.setMemberMobile(resultSet.getString("MemberMobile"));
2217                bean.setMemberFax(resultSet.getString("MemberFax"));
2218                bean.setMemberCareer(resultSet.getString("MemberCareer"));
2219                bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
2220                bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
2221                bean.setMemberAol(resultSet.getString("MemberAol"));
2222                bean.setMemberIcq(resultSet.getString("MemberIcq"));
2223                bean.setMemberMsn(resultSet.getString("MemberMsn"));
2224                bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
2225                bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
2226                bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
2227                retValue.add(bean);
2228            }//while
2229
return retValue;
2230        } catch(SQLException sqle) {
2231            log.error("Sql Execution Error!", sqle);
2232            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMembers_inGroup.");
2233        } finally {
2234            DBUtils.closeResultSet(resultSet);
2235            DBUtils.resetStatement(statement);
2236            DBUtils.closeStatement(statement);
2237            DBUtils.closeConnection(connection);
2238        }
2239    }
2240
2241    public Collection JavaDoc getForumsAuthorizedMembers() throws DatabaseException {
2242
2243        Connection connection = null;
2244        PreparedStatement statement = null;
2245        ResultSet resultSet = null;
2246        Collection JavaDoc retValue = new ArrayList JavaDoc();
2247        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
2248
2249        sql.append("SELECT DISTINCT m.MemberID, MemberName, MemberEmail, MemberLastname, MemberFirstname, MemberCreationDate");
2250        sql.append(" FROM " + TABLE_NAME + " m , " + MemberForumDAO.TABLE_NAME + " mf");
2251        sql.append(" WHERE (m.MemberID = mf.MemberID) ");
2252        sql.append(" ORDER BY m.MemberID ASC");
2253        try {
2254            connection = DBUtils.getConnection();
2255            statement = connection.prepareStatement(sql.toString());
2256            resultSet = statement.executeQuery();
2257            while (resultSet.next()) {
2258                MemberBean bean = new MemberBean();
2259                bean.setMemberID(resultSet.getInt("MemberID"));
2260                bean.setMemberName(resultSet.getString("MemberName"));
2261                bean.setMemberEmail(resultSet.getString("MemberEmail"));
2262                bean.setMemberLastname(resultSet.getString("MemberLastname"));
2263                bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
2264                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
2265                retValue.add(bean);
2266            }
2267            return retValue;
2268        } catch(SQLException sqle) {
2269            log.error("Sql Execution Error!", sqle);
2270            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getForumsAuthorizedMembers");
2271        } finally {
2272            DBUtils.closeResultSet(resultSet);
2273            DBUtils.resetStatement(statement);
2274            DBUtils.closeStatement(statement);
2275            DBUtils.closeConnection(connection);
2276        }
2277    }
2278
2279    public Collection JavaDoc getAuthorizedMembers() throws DatabaseException {
2280
2281        Connection connection = null;
2282        PreparedStatement statement = null;
2283        ResultSet resultSet = null;
2284        Collection JavaDoc retValue = new ArrayList JavaDoc();
2285        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
2286
2287        sql.append("SELECT DISTINCT m.MemberID, MemberName, MemberEmail, MemberLastname, MemberFirstname, MemberCreationDate");
2288        sql.append(" FROM " + TABLE_NAME + " m , " + MemberPermissionDAO.TABLE_NAME + " mp");
2289        sql.append(" WHERE (m.MemberID = mp.MemberID) ");
2290        sql.append(" ORDER BY m.MemberID ASC");
2291        try {
2292            connection = DBUtils.getConnection();
2293            statement = connection.prepareStatement(sql.toString());
2294            resultSet = statement.executeQuery();
2295            while (resultSet.next()) {
2296                MemberBean bean = new MemberBean();
2297                bean.setMemberID(resultSet.getInt("MemberID"));
2298                bean.setMemberName(resultSet.getString("MemberName"));
2299                bean.setMemberEmail(resultSet.getString("MemberEmail"));
2300                bean.setMemberLastname(resultSet.getString("MemberLastname"));
2301                bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
2302                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
2303                retValue.add(bean);
2304            }
2305            return retValue;
2306        } catch(SQLException sqle) {
2307            log.error("Sql Execution Error!", sqle);
2308            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getAuthorizedMembers.");
2309        } finally {
2310            DBUtils.closeResultSet(resultSet);
2311            DBUtils.resetStatement(statement);
2312            DBUtils.closeStatement(statement);
2313            DBUtils.closeConnection(connection);
2314        }
2315    }
2316    public Collection JavaDoc getNonActivatedNoPostMembers(Timestamp before)
2317        throws DatabaseException {
2318
2319        Connection connection = null;
2320        PreparedStatement statement = null;
2321        ResultSet resultSet = null;
2322        Collection JavaDoc retValue = new ArrayList JavaDoc();
2323        StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
2324
2325        sql.append("SELECT MemberID, MemberName, MemberFirstName, MemberLastName, MemberEmail, MemberPostCount, MemberCreationDate, MemberStatus");
2326        sql.append(" FROM " + TABLE_NAME);
2327        sql.append(" WHERE ((MemberActivateCode <> 'activated') OR (MemberActivateCode IS NULL))");// not activated
2328
sql.append(" AND (MemberID <> 0) AND (MemberID <> 1) ");// not a guest, not Admin
2329
sql.append(" AND (MemberPostCount = 0) ");// with no posts
2330
sql.append(" AND (MemberCreationDate < ?) ");
2331        try {
2332            connection = DBUtils.getConnection();
2333            statement = connection.prepareStatement(sql.toString());
2334            statement.setTimestamp(1, before);
2335            resultSet = statement.executeQuery();
2336            while (resultSet.next()) {
2337                MemberBean bean = new MemberBean();
2338                bean.setMemberID(resultSet.getInt("MemberID"));
2339                bean.setMemberName(resultSet.getString("MemberName"));
2340                bean.setMemberFirstname(resultSet.getString("MemberFirstName"));
2341                bean.setMemberLastname(resultSet.getString("MemberLastName"));
2342                bean.setMemberEmail(resultSet.getString("MemberEmail"));
2343                bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
2344                bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
2345                bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2346                retValue.add(bean);
2347            }
2348            return retValue;
2349        } catch(SQLException sqle) {
2350            log.error("Sql Execution Error!", sqle);
2351            throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNonActivatedNoPostMembers");
2352        } finally {
2353            DBUtils.closeResultSet(resultSet);
2354            DBUtils.resetStatement(statement);
2355            DBUtils.closeStatement(statement);
2356            DBUtils.closeConnection(connection);
2357        }
2358    }
2359
2360}// end of class MemberDAOImplJDBC
2361
Popular Tags