0001: /*
0002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/MemberDAOImplJDBC.java,v 1.96 2008/01/15 11:17:52 minhnn Exp $
0003: * $Author: minhnn $
0004: * $Revision: 1.96 $
0005: * $Date: 2008/01/15 11:17:52 $
0006: *
0007: * ====================================================================
0008: *
0009: * Copyright (C) 2002-2007 by MyVietnam.net
0010: *
0011: * All copyright notices regarding mvnForum MUST remain
0012: * intact in the scripts and in the outputted HTML.
0013: * The "powered by" text/logo with a link back to
0014: * http://www.mvnForum.com and http://www.MyVietnam.net in
0015: * the footer of the pages MUST remain visible when the pages
0016: * are viewed on the internet or intranet.
0017: *
0018: * This program is free software; you can redistribute it and/or modify
0019: * it under the terms of the GNU General Public License as published by
0020: * the Free Software Foundation; either version 2 of the License, or
0021: * any later version.
0022: *
0023: * This program is distributed in the hope that it will be useful,
0024: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0025: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0026: * GNU General Public License for more details.
0027: *
0028: * You should have received a copy of the GNU General Public License
0029: * along with this program; if not, write to the Free Software
0030: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0031: *
0032: * Support can be obtained from support forums at:
0033: * http://www.mvnForum.com/mvnforum/index
0034: *
0035: * Correspondence and Marketing Questions can be sent to:
0036: * info at MyVietnam net
0037: *
0038: * @author: Minh Nguyen
0039: * @author: Mai Nguyen
0040: */
0041: package com.mvnforum.db.jdbc;
0042:
0043: import java.sql.*;
0044: import java.util.ArrayList;
0045: import java.util.Collection;
0046:
0047: import net.myvietnam.mvncore.db.DBUtils;
0048: import net.myvietnam.mvncore.exception.*;
0049: import net.myvietnam.mvncore.util.AssertionUtil;
0050: import net.myvietnam.mvncore.util.StringUtil;
0051:
0052: import org.apache.commons.logging.Log;
0053: import org.apache.commons.logging.LogFactory;
0054:
0055: import com.mvnforum.db.*;
0056:
0057: public class MemberDAOImplJDBC implements MemberDAO {
0058:
0059: private static Log log = LogFactory.getLog(MemberDAOImplJDBC.class);
0060:
0061: // this variable will support caching if cache for this class is needed
0062: private static boolean m_dirty = true;
0063:
0064: public MemberDAOImplJDBC() {
0065: }
0066:
0067: protected static boolean isDirty() {
0068: return m_dirty;
0069: }
0070:
0071: protected static void setDirty(boolean dirty) {
0072: m_dirty = dirty;
0073: }
0074:
0075: public boolean isSupportFindByPrimaryKey() {
0076: return true;
0077: }
0078:
0079: public void findByPrimaryKey(int memberID)
0080: throws ObjectNotFoundException, DatabaseException {
0081:
0082: Connection connection = null;
0083: PreparedStatement statement = null;
0084: ResultSet resultSet = null;
0085: StringBuffer sql = new StringBuffer(512);
0086: sql.append("SELECT MemberID");
0087: sql.append(" FROM " + TABLE_NAME);
0088: sql.append(" WHERE MemberID = ?");
0089: try {
0090: connection = DBUtils.getConnection();
0091: statement = connection.prepareStatement(sql.toString());
0092: statement.setInt(1, memberID);
0093: resultSet = statement.executeQuery();
0094: if (!resultSet.next()) {
0095: throw new ObjectNotFoundException(
0096: "Cannot find the primary key (" + memberID
0097: + ") in table 'Member'.");
0098: }
0099: } catch (SQLException sqle) {
0100: log.error("Sql Execution Error!", sqle);
0101: throw new DatabaseException(
0102: "Error executing SQL in MemberDAOImplJDBC.findByPrimaryKey.");
0103: } finally {
0104: DBUtils.closeResultSet(resultSet);
0105: DBUtils.closeStatement(statement);
0106: DBUtils.closeConnection(connection);
0107: }
0108: }
0109:
0110: public boolean isSupportFindByPrimaryKey2() {
0111: return true;
0112: }
0113:
0114: public void findByPrimaryKey2(int memberID, String memberName)
0115: throws ObjectNotFoundException, DatabaseException {
0116:
0117: Connection connection = null;
0118: PreparedStatement statement = null;
0119: ResultSet resultSet = null;
0120: StringBuffer sql = new StringBuffer(512);
0121: sql.append("SELECT MemberID, MemberName");
0122: sql.append(" FROM " + TABLE_NAME);
0123: sql.append(" WHERE MemberID = ? AND MemberName = ?");
0124: try {
0125: connection = DBUtils.getConnection();
0126: statement = connection.prepareStatement(sql.toString());
0127: statement.setInt(1, memberID);
0128: statement.setString(2, memberName);
0129: resultSet = statement.executeQuery();
0130: if (!resultSet.next()) {
0131: throw new ObjectNotFoundException(
0132: "Cannot find the primary key (" + memberID
0133: + "," + memberName
0134: + ") in table 'Member'.");
0135: }
0136: } catch (SQLException sqle) {
0137: log.error("Sql Execution Error!", sqle);
0138: throw new DatabaseException(
0139: "Error executing SQL in MemberDAOImplJDBC.findByPrimaryKey2.");
0140: } finally {
0141: DBUtils.closeResultSet(resultSet);
0142: DBUtils.closeStatement(statement);
0143: DBUtils.closeConnection(connection);
0144: }
0145: }
0146:
0147: public boolean isSupportFindByAlternateKey_MemberName() {
0148: return true;
0149: }
0150:
0151: public String findByAlternateKey_MemberName(String memberName)
0152: throws ObjectNotFoundException, DatabaseException {
0153:
0154: Connection connection = null;
0155: PreparedStatement statement = null;
0156: ResultSet resultSet = null;
0157: StringBuffer sql = new StringBuffer(512);
0158: sql.append("SELECT MemberName");
0159: sql.append(" FROM " + TABLE_NAME);
0160: if (DBUtils.isCaseSensitiveDatebase()) {
0161: sql.append(" WHERE lower(MemberName) = lower(?)");
0162: } else {
0163: sql.append(" WHERE MemberName = ?");
0164: }
0165: try {
0166: connection = DBUtils.getConnection();
0167: statement = connection.prepareStatement(sql.toString());
0168: statement.setString(1, memberName);
0169: resultSet = statement.executeQuery();
0170: if (!resultSet.next()) {
0171: throw new ObjectNotFoundException(
0172: "Cannot find the alternate key [MemberName] ("
0173: + memberName + ") in table 'Member'.");
0174: }
0175: return resultSet.getString("MemberName");
0176: } catch (SQLException sqle) {
0177: log.error("Sql Execution Error!", sqle);
0178: throw new DatabaseException(
0179: "Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberName.");
0180: } finally {
0181: DBUtils.closeResultSet(resultSet);
0182: DBUtils.closeStatement(statement);
0183: DBUtils.closeConnection(connection);
0184: }
0185: }
0186:
0187: public boolean isSupportFindByAlternateKey_MemberEmail() {
0188: return true;
0189: }
0190:
0191: public void findByAlternateKey_MemberEmail(String memberEmail)
0192: throws ObjectNotFoundException, DatabaseException {
0193:
0194: Connection connection = null;
0195: PreparedStatement statement = null;
0196: ResultSet resultSet = null;
0197: StringBuffer sql = new StringBuffer(512);
0198: sql.append("SELECT MemberEmail");
0199: sql.append(" FROM " + TABLE_NAME);
0200: if (DBUtils.isCaseSensitiveDatebase()) {
0201: sql.append(" WHERE lower(MemberEmail) = lower(?)");
0202: } else {
0203: sql.append(" WHERE MemberEmail = ?");
0204: }
0205: try {
0206: connection = DBUtils.getConnection();
0207: statement = connection.prepareStatement(sql.toString());
0208: statement.setString(1, memberEmail);
0209: resultSet = statement.executeQuery();
0210: if (!resultSet.next()) {
0211: throw new ObjectNotFoundException(
0212: "Cannot find the alternate key [MemberEmail] ("
0213: + memberEmail + ") in table 'Member'.");
0214: }
0215: } catch (SQLException sqle) {
0216: log.error("Sql Execution Error!", sqle);
0217: throw new DatabaseException(
0218: "Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberEmail.");
0219: } finally {
0220: DBUtils.closeResultSet(resultSet);
0221: DBUtils.closeStatement(statement);
0222: DBUtils.closeConnection(connection);
0223: }
0224: }
0225:
0226: public boolean isSupportDeleteByPrimaryKey() {
0227: return true;
0228: }
0229:
0230: /**
0231: * Delete the user specified by memberID. Note that this method
0232: * will not fail it the given id does not exists.
0233: *
0234: * @param memberID an <code>int</code> value
0235: * @exception DatabaseException if an error occurs
0236: */
0237: public void deleteByPrimaryKey(int memberID)
0238: throws DatabaseException {
0239:
0240: Connection connection = null;
0241: PreparedStatement statement = null;
0242: ResultSet resultSet = null;
0243: StringBuffer sql = new StringBuffer(512);
0244: sql.append("DELETE");
0245: sql.append(" FROM " + TABLE_NAME);
0246: sql.append(" WHERE MemberID = ?");
0247: try {
0248: connection = DBUtils.getConnection();
0249: statement = connection.prepareStatement(sql.toString());
0250: statement.setInt(1, memberID);
0251: statement.executeUpdate();
0252: } catch (SQLException sqle) {
0253: log.error("Sql Execution Error!", sqle);
0254: throw new DatabaseException(
0255: "Error executing SQL in MemberDAOImplJDBC.deleteByPrimaryKey.");
0256: } finally {
0257: DBUtils.closeResultSet(resultSet);
0258: DBUtils.closeStatement(statement);
0259: DBUtils.closeConnection(connection);
0260: }
0261: }
0262:
0263: public boolean isSupportCreate() {
0264: return true;
0265: }
0266:
0267: /*
0268: * Included columns: MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible,
0269: * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
0270: * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
0271: * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
0272: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
0273: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
0274: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0275: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0276: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0277: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0278: * Excluded columns: MemberID
0279: */
0280: public void create(String memberName, String memberPassword,
0281: String memberFirstEmail, String memberEmail,
0282: int memberEmailVisible, int memberNameVisible,
0283: String memberFirstIP, String memberLastIP,
0284: int memberViewCount, int memberPostCount,
0285: Timestamp memberCreationDate, Timestamp memberModifiedDate,
0286: Timestamp memberExpireDate,
0287: Timestamp memberPasswordExpireDate,
0288: Timestamp memberLastLogon, int memberOption,
0289: int memberStatus, String memberActivateCode,
0290: String memberTempPassword, int memberMessageCount,
0291: int memberMessageOption, int memberPostsPerPage,
0292: int memberWarnCount, int memberVoteCount,
0293: int memberVoteTotalStars, int memberRewardPoints,
0294: String memberTitle, double memberTimeZone,
0295: String memberSignature, String memberAvatar,
0296: String memberSkin, String memberLanguage,
0297: String memberFirstname, String memberLastname,
0298: int memberGender, Date memberBirthday,
0299: String memberAddress, String memberCity,
0300: String memberState, String memberCountry,
0301: String memberPhone, String memberMobile, String memberFax,
0302: String memberCareer, String memberHomepage,
0303: String memberYahoo, String memberAol, String memberIcq,
0304: String memberMsn, String memberCoolLink1,
0305: String memberCoolLink2) throws CreateException,
0306: DatabaseException, DuplicateKeyException,
0307: IllegalArgumentException {
0308:
0309: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
0310: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
0311: try {
0312: //Check if alternate key already exists
0313: findByAlternateKey_MemberName(memberName);
0314: //If so, then we have to throw an exception
0315: throw new DuplicateKeyException(
0316: "Alternate key already exists. Cannot create new Member with the same [MemberName] ("
0317: + memberName + ").");
0318: } catch (ObjectNotFoundException e) {
0319: //Otherwise we can go ahead
0320: }
0321:
0322: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
0323: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
0324: try {
0325: //Check if alternate key already exists
0326: findByAlternateKey_MemberEmail(memberEmail);
0327: //If so, then we have to throw an exception
0328: throw new DuplicateKeyException(
0329: "Alternate key already exists. Cannot create new Member with the same [MemberEmail] ("
0330: + memberEmail + ").");
0331: } catch (ObjectNotFoundException e) {
0332: //Otherwise we can go ahead
0333: }
0334:
0335: if (memberStatus < MemberBean.MEMBER_STATUS_ENABLE
0336: || memberStatus > MemberBean.MEMBER_STATUS_PENDING) {
0337: throw new IllegalArgumentException(
0338: "Cannot resolve illegal argument MemberStatus = "
0339: + memberStatus);
0340: }
0341:
0342: Connection connection = null;
0343: PreparedStatement statement = null;
0344: StringBuffer sql = new StringBuffer(512);
0345: sql
0346: .append("INSERT INTO "
0347: + TABLE_NAME
0348: + " (MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberPasswordExpireDate, 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)");
0349: sql
0350: .append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
0351: try {
0352: connection = DBUtils.getConnection();
0353: statement = connection.prepareStatement(sql.toString());
0354:
0355: statement.setString(1, memberName);
0356: statement.setString(2, memberPassword);
0357: statement.setString(3, memberFirstEmail);
0358: statement.setString(4, memberEmail);
0359: statement.setInt(5, memberEmailVisible);
0360: statement.setInt(6, memberNameVisible);
0361: statement.setString(7, memberFirstIP);
0362: statement.setString(8, memberLastIP);
0363: statement.setInt(9, memberViewCount);
0364: statement.setInt(10, memberPostCount);
0365: statement.setTimestamp(11, memberCreationDate);
0366: statement.setTimestamp(12, memberModifiedDate);
0367: statement.setTimestamp(13, memberExpireDate);
0368: statement.setTimestamp(14, memberPasswordExpireDate);
0369: statement.setTimestamp(15, memberLastLogon);
0370: statement.setInt(16, memberOption);
0371: statement.setInt(17, memberStatus);
0372: statement.setString(18, memberActivateCode);
0373: statement.setString(19, memberTempPassword);
0374: statement.setInt(20, memberMessageCount);
0375: statement.setInt(21, memberMessageOption);
0376: statement.setInt(22, memberPostsPerPage);
0377: statement.setInt(23, memberWarnCount);
0378: statement.setInt(24, memberVoteCount);
0379: statement.setInt(25, memberVoteTotalStars);
0380: statement.setInt(26, memberRewardPoints);
0381: statement.setString(27, memberTitle);
0382: statement.setDouble(28, memberTimeZone);
0383: statement.setString(29, memberSignature);
0384: statement.setString(30, memberAvatar);
0385: statement.setString(31, memberSkin);
0386: statement.setString(32, memberLanguage);
0387: statement.setString(33, memberFirstname);
0388: statement.setString(34, memberLastname);
0389: statement.setInt(35, memberGender);
0390: statement.setDate(36, memberBirthday);
0391: statement.setString(37, memberAddress);
0392: statement.setString(38, memberCity);
0393: statement.setString(39, memberState);
0394: statement.setString(40, memberCountry);
0395: statement.setString(41, memberPhone);
0396: statement.setString(42, memberMobile);
0397: statement.setString(43, memberFax);
0398: statement.setString(44, memberCareer);
0399: statement.setString(45, memberHomepage);
0400: statement.setString(46, memberYahoo);
0401: statement.setString(47, memberAol);
0402: statement.setString(48, memberIcq);
0403: statement.setString(49, memberMsn);
0404: statement.setString(50, memberCoolLink1);
0405: statement.setString(51, memberCoolLink2);
0406:
0407: if (statement.executeUpdate() != 1) {
0408: throw new CreateException(
0409: "Error adding a row into table 'Member'.");
0410: }
0411: m_dirty = true;
0412: } catch (SQLException sqle) {
0413: log.error("Sql Execution Error!", sqle);
0414: throw new DatabaseException(
0415: "Error executing SQL in MemberDAOImplJDBC.create.");
0416: } finally {
0417: DBUtils.closeStatement(statement);
0418: DBUtils.closeConnection(connection);
0419: }
0420: }
0421:
0422: public boolean isSupportUpdate() {
0423: return true;
0424: }
0425:
0426: /*
0427: * Included columns: MemberEmailVisible, MemberNameVisible, MemberModifiedDate, MemberOption, MemberStatus,
0428: * MemberMessageOption, MemberPostsPerPage, MemberTimeZone, MemberSkin, MemberLanguage,
0429: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0430: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0431: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0432: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0433: * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
0434: * MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate,
0435: * MemberLastLogon, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberWarnCount,
0436: * MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature,
0437: * MemberAvatar
0438: */
0439: public void update(
0440: int memberID, // primary key
0441: int memberEmailVisible, int memberNameVisible,
0442: Timestamp memberModifiedDate, int memberOption,
0443: int memberStatus, int memberMessageOption,
0444: int memberPostsPerPage, double memberTimeZone,
0445: String memberSkin, String memberLanguage,
0446: String memberFirstname, String memberLastname,
0447: int memberGender, Date memberBirthday,
0448: String memberAddress, String memberCity,
0449: String memberState, String memberCountry,
0450: String memberPhone, String memberMobile, String memberFax,
0451: String memberCareer, String memberHomepage,
0452: String memberYahoo, String memberAol, String memberIcq,
0453: String memberMsn, String memberCoolLink1,
0454: String memberCoolLink2) throws ObjectNotFoundException,
0455: DatabaseException {
0456:
0457: Connection connection = null;
0458: PreparedStatement statement = null;
0459: StringBuffer sql = new StringBuffer(512);
0460: sql
0461: .append("UPDATE "
0462: + TABLE_NAME
0463: + " 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 = ?");
0464: sql.append(" WHERE MemberID = ?");
0465: try {
0466: connection = DBUtils.getConnection();
0467: statement = connection.prepareStatement(sql.toString());
0468:
0469: // // column(s) to update
0470: statement.setInt(1, memberEmailVisible);
0471: statement.setInt(2, memberNameVisible);
0472: statement.setTimestamp(3, memberModifiedDate);
0473: statement.setInt(4, memberOption);
0474: statement.setInt(5, memberStatus);
0475: statement.setInt(6, memberMessageOption);
0476: statement.setInt(7, memberPostsPerPage);
0477: statement.setDouble(8, memberTimeZone);
0478: statement.setString(9, memberSkin);
0479: statement.setString(10, memberLanguage);
0480: statement.setString(11, memberFirstname);
0481: statement.setString(12, memberLastname);
0482: statement.setInt(13, memberGender);
0483: statement.setDate(14, memberBirthday);
0484: statement.setString(15, memberAddress);
0485: statement.setString(16, memberCity);
0486: statement.setString(17, memberState);
0487: statement.setString(18, memberCountry);
0488: statement.setString(19, memberPhone);
0489: statement.setString(20, memberMobile);
0490: statement.setString(21, memberFax);
0491: statement.setString(22, memberCareer);
0492: statement.setString(23, memberHomepage);
0493: statement.setString(24, memberYahoo);
0494: statement.setString(25, memberAol);
0495: statement.setString(26, memberIcq);
0496: statement.setString(27, memberMsn);
0497: statement.setString(28, memberCoolLink1);
0498: statement.setString(29, memberCoolLink2);
0499:
0500: // primary key column(s)
0501: statement.setInt(30, memberID);
0502:
0503: if (statement.executeUpdate() != 1) {
0504: throw new ObjectNotFoundException(
0505: "Cannot update table Member where primary key = ("
0506: + memberID + ").");
0507: }
0508: m_dirty = true;
0509: } catch (SQLException sqle) {
0510: log.error("Sql Execution Error!", sqle);
0511: throw new DatabaseException(
0512: "Error executing SQL in MemberDAOImplJDBC.update.");
0513: } finally {
0514: DBUtils.closeStatement(statement);
0515: DBUtils.closeConnection(connection);
0516: }
0517: }
0518:
0519: public boolean isSupportUpdateEmail() {
0520: return true;
0521: }
0522:
0523: /*
0524: * Included columns: MemberEmail
0525: * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmailVisible,
0526: * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
0527: * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
0528: * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
0529: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
0530: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
0531: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0532: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0533: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0534: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0535: */
0536: public void updateEmail(int memberID, // primary key
0537: String memberEmail) throws ObjectNotFoundException,
0538: DatabaseException, DuplicateKeyException {
0539:
0540: //@todo: use a more efficent method
0541: MemberBean bean = getMember(memberID); // @todo: comment or delete this line if no alternate key are included
0542:
0543: if (memberEmail.equalsIgnoreCase(bean.getMemberEmail()) == false) {
0544: // Member tries to change its alternate key <MemberEmail>, so we must check if it already exist
0545: try {
0546: findByAlternateKey_MemberEmail(memberEmail);
0547: throw new DuplicateKeyException(
0548: "Alternate key [MemberEmail] ("
0549: + memberEmail
0550: + ") already exists. Cannot update Member.");
0551: } catch (ObjectNotFoundException e) {
0552: //Otherwise we can go ahead
0553: }
0554: }
0555:
0556: Connection connection = null;
0557: PreparedStatement statement = null;
0558: StringBuffer sql = new StringBuffer(512);
0559: sql.append("UPDATE " + TABLE_NAME + " SET MemberEmail = ?");
0560: sql.append(" WHERE MemberID = ?");
0561: try {
0562: connection = DBUtils.getConnection();
0563: statement = connection.prepareStatement(sql.toString());
0564:
0565: // // column(s) to update
0566: statement.setString(1, memberEmail);
0567:
0568: // primary key column(s)
0569: statement.setInt(2, memberID);
0570:
0571: if (statement.executeUpdate() != 1) {
0572: throw new ObjectNotFoundException(
0573: "Cannot update table Member where primary key = ("
0574: + memberID + ").");
0575: }
0576: m_dirty = true;
0577: } catch (SQLException sqle) {
0578: log.error("Sql Execution Error!", sqle);
0579: throw new DatabaseException(
0580: "Error executing SQL in MemberDAOImplJDBC.updateEmail.");
0581: } finally {
0582: DBUtils.closeStatement(statement);
0583: DBUtils.closeConnection(connection);
0584: }
0585: }
0586:
0587: public boolean isSupportUpdatePassword() {
0588: return true;
0589: }
0590:
0591: /*
0592: * Included columns: MemberPassword
0593: * Excluded columns: MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible,
0594: * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
0595: * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
0596: * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
0597: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
0598: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
0599: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0600: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0601: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0602: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0603: */
0604: public void updatePassword(int memberID, // primary key
0605: String memberPassword, Timestamp memberPasswordExpireDate)
0606: throws ObjectNotFoundException, DatabaseException {
0607:
0608: Connection connection = null;
0609: PreparedStatement statement = null;
0610: StringBuffer sql = new StringBuffer(512);
0611: if (memberPasswordExpireDate == null) {
0612: sql.append("UPDATE " + TABLE_NAME
0613: + " SET MemberPassword = ?");
0614: sql.append(" WHERE MemberID = ?");
0615: } else {
0616: sql
0617: .append("UPDATE "
0618: + TABLE_NAME
0619: + " SET MemberPassword = ?, MemberPasswordExpireDate = ?");
0620: sql.append(" WHERE MemberID = ?");
0621: }
0622: try {
0623: connection = DBUtils.getConnection();
0624: statement = connection.prepareStatement(sql.toString());
0625:
0626: if (memberPasswordExpireDate == null) {
0627: // // column(s) to update
0628: statement.setString(1, memberPassword);
0629:
0630: // primary key column(s)
0631: statement.setInt(2, memberID);
0632: } else {
0633: // // column(s) to update
0634: statement.setString(1, memberPassword);
0635: statement.setTimestamp(2, memberPasswordExpireDate);
0636:
0637: // primary key column(s)
0638: statement.setInt(3, memberID);
0639: }
0640:
0641: if (statement.executeUpdate() != 1) {
0642: throw new ObjectNotFoundException(
0643: "Cannot update table Member where primary key = ("
0644: + memberID + ").");
0645: }
0646: m_dirty = true;
0647: } catch (SQLException sqle) {
0648: log.error("Sql Execution Error!", sqle);
0649: throw new DatabaseException(
0650: "Error executing SQL in MemberDAOImplJDBC.updatePassword.");
0651: } finally {
0652: DBUtils.closeStatement(statement);
0653: DBUtils.closeConnection(connection);
0654: }
0655: }
0656:
0657: public boolean isSupportUpdateTempPassword() {
0658: return true;
0659: }
0660:
0661: /*
0662: * Included columns: MemberTempPassword
0663: * Excluded columns: MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible,
0664: * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
0665: * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
0666: * MemberActivateCode, MemberPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
0667: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
0668: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
0669: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0670: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0671: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0672: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0673: */
0674: public void updateTempPassword(int memberID, // primary key
0675: String memberTempPassword) throws ObjectNotFoundException,
0676: DatabaseException {
0677:
0678: Connection connection = null;
0679: PreparedStatement statement = null;
0680: StringBuffer sql = new StringBuffer(512);
0681: sql.append("UPDATE " + TABLE_NAME
0682: + " SET MemberTempPassword = ?");
0683: sql.append(" WHERE MemberID = ?");
0684: try {
0685: connection = DBUtils.getConnection();
0686: statement = connection.prepareStatement(sql.toString());
0687:
0688: // // column(s) to update
0689: statement.setString(1, memberTempPassword);
0690:
0691: // primary key column(s)
0692: statement.setInt(2, memberID);
0693:
0694: if (statement.executeUpdate() != 1) {
0695: throw new ObjectNotFoundException(
0696: "Cannot update table Member where primary key = ("
0697: + memberID + ").");
0698: }
0699: m_dirty = true;
0700: } catch (SQLException sqle) {
0701: log.error("Sql Execution Error!", sqle);
0702: throw new DatabaseException(
0703: "Error executing SQL in MemberDAOImplJDBC.updateTempPassword.");
0704: } finally {
0705: DBUtils.closeStatement(statement);
0706: DBUtils.closeConnection(connection);
0707: }
0708: }
0709:
0710: public boolean isSupportUpdateActivateCode() {
0711: return true;
0712: }
0713:
0714: /*
0715: * Included columns: MemberActivateCode
0716: * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
0717: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
0718: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
0719: * MemberStatus, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
0720: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
0721: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
0722: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0723: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0724: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0725: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0726: */
0727: public void updateActivateCode(int memberID, // primary key
0728: String memberActivateCode) throws ObjectNotFoundException,
0729: DatabaseException {
0730:
0731: Connection connection = null;
0732: PreparedStatement statement = null;
0733: StringBuffer sql = new StringBuffer(512);
0734: sql.append("UPDATE " + TABLE_NAME
0735: + " SET MemberActivateCode = ?");
0736: sql.append(" WHERE MemberID = ?");
0737: try {
0738: connection = DBUtils.getConnection();
0739: statement = connection.prepareStatement(sql.toString());
0740:
0741: // // column(s) to update
0742: statement.setString(1, memberActivateCode);
0743:
0744: // primary key column(s)
0745: statement.setInt(2, memberID);
0746:
0747: if (statement.executeUpdate() != 1) {
0748: throw new ObjectNotFoundException(
0749: "Cannot update table Member where primary key = ("
0750: + memberID + ").");
0751: }
0752: m_dirty = true;
0753: } catch (SQLException sqle) {
0754: log.error("Sql Execution Error!", sqle);
0755: throw new DatabaseException(
0756: "Error executing SQL in MemberDAOImplJDBC.updateActivateCode.");
0757: } finally {
0758: DBUtils.closeStatement(statement);
0759: DBUtils.closeConnection(connection);
0760: }
0761: }
0762:
0763: public boolean isSupportUpdateAvatar() {
0764: return true;
0765: }
0766:
0767: /*
0768: * Included columns: MemberAvatar
0769: * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
0770: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
0771: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
0772: * MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption,
0773: * MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
0774: * MemberTitle, MemberTimeZone, MemberSignature, MemberSkin, MemberLanguage,
0775: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0776: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0777: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0778: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0779: */
0780: public void updateAvatar(int memberID, // primary key
0781: String memberAvatar) throws ObjectNotFoundException,
0782: DatabaseException {
0783:
0784: Connection connection = null;
0785: PreparedStatement statement = null;
0786: StringBuffer sql = new StringBuffer(512);
0787: sql.append("UPDATE " + TABLE_NAME + " SET MemberAvatar = ?");
0788: sql.append(" WHERE MemberID = ?");
0789: try {
0790: connection = DBUtils.getConnection();
0791: statement = connection.prepareStatement(sql.toString());
0792:
0793: // // column(s) to update
0794: statement.setString(1, memberAvatar);
0795:
0796: // primary key column(s)
0797: statement.setInt(2, memberID);
0798:
0799: if (statement.executeUpdate() != 1) {
0800: throw new ObjectNotFoundException(
0801: "Cannot update table Member where primary key = ("
0802: + memberID + ").");
0803: }
0804: m_dirty = true;
0805: } catch (SQLException sqle) {
0806: log.error("Sql Execution Error!", sqle);
0807: throw new DatabaseException(
0808: "Error executing SQL in MemberDAOImplJDBC.updateAvatar.");
0809: } finally {
0810: DBUtils.closeStatement(statement);
0811: DBUtils.closeConnection(connection);
0812: }
0813: }
0814:
0815: public boolean isSupportUpdateSignature() {
0816: return true;
0817: }
0818:
0819: /*
0820: * Included columns: MemberSignature
0821: * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
0822: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
0823: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
0824: * MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption,
0825: * MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
0826: * MemberTitle, MemberTimeZone, MemberAvatar, MemberSkin, MemberLanguage,
0827: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0828: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0829: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0830: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0831: */
0832: public void updateSignature(int memberID, // primary key
0833: String memberSignature) throws ObjectNotFoundException,
0834: DatabaseException {
0835:
0836: Connection connection = null;
0837: PreparedStatement statement = null;
0838: StringBuffer sql = new StringBuffer(512);
0839: sql.append("UPDATE " + TABLE_NAME + " SET MemberSignature = ?");
0840: sql.append(" WHERE MemberID = ?");
0841: try {
0842: connection = DBUtils.getConnection();
0843: statement = connection.prepareStatement(sql.toString());
0844:
0845: // // column(s) to update
0846: statement.setString(1, memberSignature);
0847:
0848: // primary key column(s)
0849: statement.setInt(2, memberID);
0850:
0851: if (statement.executeUpdate() != 1) {
0852: throw new ObjectNotFoundException(
0853: "Cannot update table Member where primary key = ("
0854: + memberID + ").");
0855: }
0856: m_dirty = true;
0857: } catch (SQLException sqle) {
0858: log.error("Sql Execution Error!", sqle);
0859: throw new DatabaseException(
0860: "Error executing SQL in MemberDAOImplJDBC.updateSignature.");
0861: } finally {
0862: DBUtils.closeStatement(statement);
0863: DBUtils.closeConnection(connection);
0864: }
0865: }
0866:
0867: public boolean isSupportUpdateTitle() {
0868: return true;
0869: }
0870:
0871: /*
0872: * Included columns: MemberTitle
0873: * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
0874: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
0875: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
0876: * MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption,
0877: * MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
0878: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
0879: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0880: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0881: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0882: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0883: */
0884: public void updateTitle(int memberID, // primary key
0885: String memberTitle) throws ObjectNotFoundException,
0886: DatabaseException {
0887:
0888: Connection connection = null;
0889: PreparedStatement statement = null;
0890: StringBuffer sql = new StringBuffer(512);
0891: sql.append("UPDATE " + TABLE_NAME + " SET MemberTitle = ?");
0892: sql.append(" WHERE MemberID = ?");
0893: try {
0894: connection = DBUtils.getConnection();
0895: statement = connection.prepareStatement(sql.toString());
0896:
0897: // // column(s) to update
0898: statement.setString(1, memberTitle);
0899:
0900: // primary key column(s)
0901: statement.setInt(2, memberID);
0902:
0903: if (statement.executeUpdate() != 1) {
0904: throw new ObjectNotFoundException(
0905: "Cannot update table Member where primary key = ("
0906: + memberID + ").");
0907: }
0908: m_dirty = true;
0909: } catch (SQLException sqle) {
0910: log.error("Sql Execution Error!", sqle);
0911: throw new DatabaseException(
0912: "Error executing SQL in MemberDAOImplJDBC.updateTitle.");
0913: } finally {
0914: DBUtils.closeStatement(statement);
0915: DBUtils.closeConnection(connection);
0916: }
0917: }
0918:
0919: public boolean isSupportUpdateLastLogon() {
0920: return true;
0921: }
0922:
0923: /*
0924: * Included columns: MemberLastLogon
0925: * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
0926: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
0927: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberOption, MemberStatus,
0928: * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
0929: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
0930: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
0931: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0932: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0933: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0934: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0935: */
0936: public void updateLastLogon(int memberID, // primary key
0937: Timestamp memberLastLogon, String memberLastIP)
0938: throws ObjectNotFoundException, DatabaseException {
0939:
0940: Connection connection = null;
0941: PreparedStatement statement = null;
0942: StringBuffer sql = new StringBuffer(512);
0943: sql.append("UPDATE " + TABLE_NAME
0944: + " SET MemberLastLogon = ?, MemberLastIP = ? ");
0945: sql.append(" WHERE MemberID = ?");
0946: try {
0947: connection = DBUtils.getConnection();
0948: statement = connection.prepareStatement(sql.toString());
0949:
0950: // // column(s) to update
0951: statement.setTimestamp(1, memberLastLogon);
0952: statement.setString(2, memberLastIP);
0953:
0954: // primary key column(s)
0955: statement.setInt(3, memberID);
0956:
0957: if (statement.executeUpdate() != 1) {
0958: throw new ObjectNotFoundException(
0959: "Cannot update table Member where primary key = ("
0960: + memberID + ").");
0961: }
0962: m_dirty = true;
0963: } catch (SQLException sqle) {
0964: log.error("Sql Execution Error!", sqle);
0965: throw new DatabaseException(
0966: "Error executing SQL in MemberDAOImplJDBC.updateLastLogon.");
0967: } finally {
0968: DBUtils.closeStatement(statement);
0969: DBUtils.closeConnection(connection);
0970: }
0971: }
0972:
0973: public boolean isSupportGetPassword() {
0974: return true;
0975: }
0976:
0977: /*
0978: * Included columns: MemberPassword
0979: * Excluded columns: MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible,
0980: * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
0981: * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
0982: * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
0983: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
0984: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
0985: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
0986: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
0987: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
0988: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
0989: */
0990: public String getPassword(int memberID)
0991: throws ObjectNotFoundException, DatabaseException {
0992:
0993: Connection connection = null;
0994: PreparedStatement statement = null;
0995: ResultSet resultSet = null;
0996: StringBuffer sql = new StringBuffer(512);
0997: sql.append("SELECT MemberPassword");
0998: sql.append(" FROM " + TABLE_NAME);
0999: sql.append(" WHERE MemberID = ?");
1000: try {
1001: connection = DBUtils.getConnection();
1002: statement = connection.prepareStatement(sql.toString());
1003: statement.setInt(1, memberID);
1004: resultSet = statement.executeQuery();
1005: if (!resultSet.next()) {
1006: throw new ObjectNotFoundException(
1007: "Cannot find the row in table Member where primary key = ("
1008: + memberID + ").");
1009: }
1010:
1011: String retValue = resultSet.getString("MemberPassword");
1012: if (retValue == null)
1013: retValue = ""; // hack for Oracle database
1014: return retValue;
1015: } catch (SQLException sqle) {
1016: log.error("Sql Execution Error!", sqle);
1017: throw new DatabaseException(
1018: "Error executing SQL in MemberDAOImplJDBC.getPassword(pk).");
1019: } finally {
1020: DBUtils.closeResultSet(resultSet);
1021: DBUtils.closeStatement(statement);
1022: DBUtils.closeConnection(connection);
1023: }
1024: }
1025:
1026: public boolean isSupportGetTempPassword() {
1027: return true;
1028: }
1029:
1030: /*
1031: * Included columns: MemberTempPassword
1032: * Excluded columns: MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible,
1033: * MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount,
1034: * MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption, MemberStatus,
1035: * MemberActivateCode, MemberPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1036: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
1037: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
1038: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
1039: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
1040: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
1041: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
1042: */
1043: public String getTempPassword(int memberID)
1044: throws ObjectNotFoundException, DatabaseException {
1045:
1046: Connection connection = null;
1047: PreparedStatement statement = null;
1048: ResultSet resultSet = null;
1049: StringBuffer sql = new StringBuffer(512);
1050: sql.append("SELECT MemberTempPassword");
1051: sql.append(" FROM " + TABLE_NAME);
1052: sql.append(" WHERE MemberID = ?");
1053: try {
1054: connection = DBUtils.getConnection();
1055: statement = connection.prepareStatement(sql.toString());
1056: statement.setInt(1, memberID);
1057: resultSet = statement.executeQuery();
1058: if (!resultSet.next()) {
1059: throw new ObjectNotFoundException(
1060: "Cannot find the row in table Member where primary key = ("
1061: + memberID + ").");
1062: }
1063:
1064: return StringUtil.getEmptyStringIfNull(resultSet
1065: .getString("MemberTempPassword"));
1066: } catch (SQLException sqle) {
1067: log.error("Sql Execution Error!", sqle);
1068: throw new DatabaseException(
1069: "Error executing SQL in MemberDAOImplJDBC.getTempPassword(pk).");
1070: } finally {
1071: DBUtils.closeResultSet(resultSet);
1072: DBUtils.closeStatement(statement);
1073: DBUtils.closeConnection(connection);
1074: }
1075: }
1076:
1077: public boolean isSupportGetActivateCode() {
1078: return true;
1079: }
1080:
1081: /*
1082: * Included columns: MemberActivateCode
1083: * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
1084: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
1085: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
1086: * MemberStatus, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1087: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
1088: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
1089: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
1090: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
1091: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
1092: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
1093: */
1094: public String getActivateCode(int memberID)
1095: throws ObjectNotFoundException, DatabaseException {
1096:
1097: Connection connection = null;
1098: PreparedStatement statement = null;
1099: ResultSet resultSet = null;
1100: StringBuffer sql = new StringBuffer(512);
1101: sql.append("SELECT MemberActivateCode");
1102: sql.append(" FROM " + TABLE_NAME);
1103: sql.append(" WHERE MemberID = ?");
1104: try {
1105: connection = DBUtils.getConnection();
1106: statement = connection.prepareStatement(sql.toString());
1107: statement.setInt(1, memberID);
1108: resultSet = statement.executeQuery();
1109: if (!resultSet.next()) {
1110: throw new ObjectNotFoundException(
1111: "Cannot find the row in table Member where primary key = ("
1112: + memberID + ").");
1113: }
1114:
1115: String retValue = resultSet.getString("MemberActivateCode");
1116: if (retValue == null)
1117: retValue = ""; // hack for Oracle database
1118: return retValue;
1119: } catch (SQLException sqle) {
1120: log.error("Sql Execution Error!", sqle);
1121: throw new DatabaseException(
1122: "Error executing SQL in MemberDAOImplJDBC.getActivateCode(pk).");
1123: } finally {
1124: DBUtils.closeResultSet(resultSet);
1125: DBUtils.closeStatement(statement);
1126: DBUtils.closeConnection(connection);
1127: }
1128: }
1129:
1130: //
1131: // /*
1132: // * Included columns: MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount,
1133: // * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
1134: // * MemberStatus, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount,
1135: // * MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone,
1136: // * MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname,
1137: // * MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity,
1138: // * MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax,
1139: // * MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq,
1140: // * MemberMsn, MemberCoolLink1, MemberCoolLink2
1141: // * As of 26 March: MemberActivateCode
1142: // * Excluded columns: MemberID, MemberPassword, MemberFirstIP, MemberFirstEmail,
1143: // * MemberTempPassword
1144: // */
1145: // public MemberBean getMember_forViewCurrentMember(int memberID)
1146: // throws ObjectNotFoundException, DatabaseException {
1147: //
1148: // Connection connection = null;
1149: // PreparedStatement statement = null;
1150: // ResultSet resultSet = null;
1151: // StringBuffer sql = new StringBuffer(512);
1152: // 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");
1153: // sql.append(" FROM " + TABLE_NAME);
1154: // sql.append(" WHERE MemberID = ?");
1155: // try {
1156: // connection = DBUtils.getConnection();
1157: // statement = connection.prepareStatement(sql.toString());
1158: // statement.setInt(1, memberID);
1159: // resultSet = statement.executeQuery();
1160: // if (!resultSet.next()) {
1161: // throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ").");
1162: // }
1163: //
1164: // MemberBean bean = new MemberBean();
1165: // // @todo: uncomment the following line(s) as needed
1166: // bean.setMemberID(memberID);
1167: // bean.setMemberName(resultSet.getString("MemberName"));
1168: // bean.setMemberEmail(resultSet.getString("MemberEmail"));
1169: // bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
1170: // bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
1171: // bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
1172: // bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
1173: // bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
1174: // bean.setMemberModifiedDate(resultSet.getTimestamp("MemberModifiedDate"));
1175: // bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
1176: // bean.setMemberLastIP(resultSet.getString("MemberLastIP"));
1177: // bean.setMemberOption(resultSet.getInt("MemberOption"));
1178: // bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1179: // bean.setMemberMessageCount(resultSet.getInt("MemberMessageCount"));
1180: // bean.setMemberMessageOption(resultSet.getInt("MemberMessageOption"));
1181: // bean.setMemberPostsPerPage(resultSet.getInt("MemberPostsPerPage"));
1182: // bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
1183: // bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
1184: // bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
1185: // bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
1186: // bean.setMemberTitle(resultSet.getString("MemberTitle"));
1187: // bean.setMemberTimeZone(resultSet.getDouble("MemberTimeZone"));
1188: // bean.setMemberSignature(resultSet.getString("MemberSignature"));
1189: // bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
1190: // bean.setMemberSkin(resultSet.getString("MemberSkin"));
1191: // bean.setMemberLanguage(resultSet.getString("MemberLanguage"));
1192: // bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
1193: // bean.setMemberLastname(resultSet.getString("MemberLastname"));
1194: // bean.setMemberGender(resultSet.getInt("MemberGender"));
1195: // bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1196: // bean.setMemberAddress(resultSet.getString("MemberAddress"));
1197: // bean.setMemberCity(resultSet.getString("MemberCity"));
1198: // bean.setMemberState(resultSet.getString("MemberState"));
1199: // bean.setMemberCountry(resultSet.getString("MemberCountry"));
1200: // bean.setMemberPhone(resultSet.getString("MemberPhone"));
1201: // bean.setMemberMobile(resultSet.getString("MemberMobile"));
1202: // bean.setMemberFax(resultSet.getString("MemberFax"));
1203: // bean.setMemberCareer(resultSet.getString("MemberCareer"));
1204: // bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
1205: // bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1206: // bean.setMemberAol(resultSet.getString("MemberAol"));
1207: // bean.setMemberIcq(resultSet.getString("MemberIcq"));
1208: // bean.setMemberMsn(resultSet.getString("MemberMsn"));
1209: // bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
1210: // bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
1211: // bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
1212: // return bean;
1213: // } catch(SQLException sqle) {
1214: // log.error("Sql Execution Error!", sqle);
1215: // throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMember_forViewCurrentMember(pk).");
1216: // } finally {
1217: // DBUtils.closeResultSet(resultSet);
1218: // DBUtils.closeStatement(statement);
1219: // DBUtils.closeConnection(connection);
1220: // }
1221: // }
1222: //
1223: // /*
1224: // * Included columns: MemberEmailVisible, MemberNameVisible, MemberOption, MemberStatus, MemberMessageOption,
1225: // * MemberPostsPerPage, MemberTimeZone, MemberSkin, MemberLanguage, MemberFirstname,
1226: // * MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity,
1227: // * MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax,
1228: // * MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq,
1229: // * MemberMsn, MemberCoolLink1, MemberCoolLink2
1230: // * As of 13 Jan 2005: MemberName : this column is for show current member in viewmember in Admin zone
1231: // * Excluded columns: MemberID, MemberPassword, MemberFirstEmail, MemberEmail,
1232: // * MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate,
1233: // * MemberModifiedDate, MemberLastLogon, MemberActivateCode, MemberTempPassword, MemberMessageCount,
1234: // * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
1235: // * MemberSignature, MemberAvatar
1236: // */
1237: // public MemberBean getMember_forEditCurrentMember(int memberID)
1238: // throws ObjectNotFoundException, DatabaseException {
1239: //
1240: // Connection connection = null;
1241: // PreparedStatement statement = null;
1242: // ResultSet resultSet = null;
1243: // StringBuffer sql = new StringBuffer(512);
1244: // 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");
1245: // sql.append(" FROM " + TABLE_NAME);
1246: // sql.append(" WHERE MemberID = ?");
1247: // try {
1248: // connection = DBUtils.getConnection();
1249: // statement = connection.prepareStatement(sql.toString());
1250: // statement.setInt(1, memberID);
1251: // resultSet = statement.executeQuery();
1252: // if (!resultSet.next()) {
1253: // throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ").");
1254: // }
1255: //
1256: // MemberBean bean = new MemberBean();
1257: // // @todo: uncomment the following line(s) as needed
1258: // bean.setMemberID(memberID);
1259: // bean.setMemberName(resultSet.getString("MemberName"));
1260: // bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
1261: // bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
1262: // bean.setMemberOption(resultSet.getInt("MemberOption"));
1263: // bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1264: // bean.setMemberMessageOption(resultSet.getInt("MemberMessageOption"));
1265: // bean.setMemberPostsPerPage(resultSet.getInt("MemberPostsPerPage"));
1266: // bean.setMemberTimeZone(resultSet.getDouble("MemberTimeZone"));
1267: // bean.setMemberSkin(resultSet.getString("MemberSkin"));
1268: // bean.setMemberLanguage(resultSet.getString("MemberLanguage"));
1269: // bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
1270: // bean.setMemberLastname(resultSet.getString("MemberLastname"));
1271: // bean.setMemberGender(resultSet.getInt("MemberGender"));
1272: // bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1273: // bean.setMemberAddress(resultSet.getString("MemberAddress"));
1274: // bean.setMemberCity(resultSet.getString("MemberCity"));
1275: // bean.setMemberState(resultSet.getString("MemberState"));
1276: // bean.setMemberCountry(resultSet.getString("MemberCountry"));
1277: // bean.setMemberPhone(resultSet.getString("MemberPhone"));
1278: // bean.setMemberMobile(resultSet.getString("MemberMobile"));
1279: // bean.setMemberFax(resultSet.getString("MemberFax"));
1280: // bean.setMemberCareer(resultSet.getString("MemberCareer"));
1281: // bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
1282: // bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1283: // bean.setMemberAol(resultSet.getString("MemberAol"));
1284: // bean.setMemberIcq(resultSet.getString("MemberIcq"));
1285: // bean.setMemberMsn(resultSet.getString("MemberMsn"));
1286: // bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
1287: // bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
1288: // return bean;
1289: // } catch(SQLException sqle) {
1290: // log.error("Sql Execution Error!", sqle);
1291: // throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMember_forEditCurrentMember(pk).");
1292: // } finally {
1293: // DBUtils.closeResultSet(resultSet);
1294: // DBUtils.closeStatement(statement);
1295: // DBUtils.closeConnection(connection);
1296: // }
1297: // }
1298:
1299: public boolean isSupportGetMember() {
1300: return true;
1301: }
1302:
1303: /*
1304: * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
1305: * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
1306: * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1307: * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
1308: * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
1309: * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
1310: * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
1311: * MemberCoolLink1, MemberCoolLink2
1312: * As of 29 Jun 2003: MemberActivateCode
1313: * As of 20 Oct 2004: MemberExpireDate
1314: * As of 10 Dec 2004: MemberFirstIP, MemberLastIP : for admin to view it
1315: * As of 4 Jan 2005: MemberFirstEmail : for admin to view it
1316: * As of 4 Jan 2005: MemberModifiedDate : for admin to view it
1317: * Excluded columns: MemberPassword,
1318: * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1319: * MemberTimeZone, MemberSkin
1320: */
1321: public MemberBean getMember(int memberID)
1322: throws ObjectNotFoundException, DatabaseException {
1323:
1324: Connection connection = null;
1325: PreparedStatement statement = null;
1326: ResultSet resultSet = null;
1327: StringBuffer sql = new StringBuffer(512);
1328: sql
1329: .append("SELECT MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberPasswordExpireDate, MemberLastLogon, MemberOption, MemberMessageOption, MemberPostsPerPage, MemberTimeZone, MemberSkin, MemberStatus, MemberMessageCount, 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");
1330: sql.append(" FROM " + TABLE_NAME);
1331: sql.append(" WHERE MemberID = ?");
1332: try {
1333: connection = DBUtils.getConnection();
1334: statement = connection.prepareStatement(sql.toString());
1335: statement.setInt(1, memberID);
1336: resultSet = statement.executeQuery();
1337: if (!resultSet.next()) {
1338: throw new ObjectNotFoundException(
1339: "Cannot find the row in table Member where primary key = ("
1340: + memberID + ").");
1341: }
1342:
1343: MemberBean bean = new MemberBean();
1344: // @todo: uncomment the following line(s) as needed
1345: //bean.setMemberID(memberID);
1346: bean.setMemberID(resultSet.getInt("MemberID"));
1347: bean.setMemberName(resultSet.getString("MemberName"));
1348: bean.setMemberFirstEmail(resultSet
1349: .getString("MemberFirstEmail"));
1350: bean.setMemberEmail(resultSet.getString("MemberEmail"));
1351: bean.setMemberEmailVisible(resultSet
1352: .getInt("MemberEmailVisible"));
1353: bean.setMemberNameVisible(resultSet
1354: .getInt("MemberNameVisible"));
1355: bean
1356: .setMemberViewCount(resultSet
1357: .getInt("MemberViewCount"));
1358: bean
1359: .setMemberPostCount(resultSet
1360: .getInt("MemberPostCount"));
1361: bean.setMemberCreationDate(resultSet
1362: .getTimestamp("MemberCreationDate"));
1363: bean.setMemberModifiedDate(resultSet
1364: .getTimestamp("MemberModifiedDate"));
1365: bean.setMemberExpireDate(resultSet
1366: .getTimestamp("MemberExpireDate"));
1367: bean.setMemberPasswordExpireDate(resultSet
1368: .getTimestamp("MemberPasswordExpireDate"));
1369: bean.setMemberLastLogon(resultSet
1370: .getTimestamp("MemberLastLogon"));
1371: bean.setMemberOption(resultSet.getInt("MemberOption"));
1372: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1373: bean.setMemberMessageCount(resultSet
1374: .getInt("MemberMessageCount"));
1375: bean.setMemberMessageOption(resultSet
1376: .getInt("MemberMessageOption"));
1377: bean.setMemberPostsPerPage(resultSet
1378: .getInt("MemberPostsPerPage"));
1379: bean
1380: .setMemberWarnCount(resultSet
1381: .getInt("MemberWarnCount"));
1382: bean
1383: .setMemberVoteCount(resultSet
1384: .getInt("MemberVoteCount"));
1385: bean.setMemberVoteTotalStars(resultSet
1386: .getInt("MemberVoteTotalStars"));
1387: bean.setMemberRewardPoints(resultSet
1388: .getInt("MemberRewardPoints"));
1389: bean.setMemberTitle(resultSet.getString("MemberTitle"));
1390: bean.setMemberTimeZone(resultSet
1391: .getDouble("MemberTimeZone"));
1392: bean.setMemberSignature(resultSet
1393: .getString("MemberSignature"));
1394: bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
1395: bean.setMemberSkin(resultSet.getString("MemberSkin"));
1396: bean.setMemberFirstname(resultSet
1397: .getString("MemberFirstname"));
1398: bean.setMemberLastname(resultSet
1399: .getString("MemberLastname"));
1400: bean.setMemberGender(resultSet.getInt("MemberGender"));
1401: bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
1402: bean.setMemberLanguage(resultSet
1403: .getString("MemberLanguage"));
1404: bean.setMemberAddress(resultSet.getString("MemberAddress"));
1405: bean.setMemberCity(resultSet.getString("MemberCity"));
1406: bean.setMemberState(resultSet.getString("MemberState"));
1407: bean.setMemberCountry(resultSet.getString("MemberCountry"));
1408: bean.setMemberPhone(resultSet.getString("MemberPhone"));
1409: bean.setMemberMobile(resultSet.getString("MemberMobile"));
1410: bean.setMemberFax(resultSet.getString("MemberFax"));
1411: bean.setMemberCareer(resultSet.getString("MemberCareer"));
1412: bean.setMemberHomepage(resultSet
1413: .getString("MemberHomepage"));
1414: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1415: bean.setMemberAol(resultSet.getString("MemberAol"));
1416: bean.setMemberIcq(resultSet.getString("MemberIcq"));
1417: bean.setMemberMsn(resultSet.getString("MemberMsn"));
1418: bean.setMemberCoolLink1(resultSet
1419: .getString("MemberCoolLink1"));
1420: bean.setMemberCoolLink2(resultSet
1421: .getString("MemberCoolLink2"));
1422: bean.setMemberActivateCode(resultSet
1423: .getString("MemberActivateCode"));
1424: bean.setMemberFirstIP(resultSet.getString("MemberFirstIP"));
1425: bean.setMemberLastIP(resultSet.getString("MemberLastIP"));
1426: return bean;
1427: } catch (SQLException sqle) {
1428: log.error("Sql Execution Error!", sqle);
1429: throw new DatabaseException(
1430: "Error executing SQL in MemberDAOImplJDBC.getMember_forPublic(pk).");
1431: } finally {
1432: DBUtils.closeResultSet(resultSet);
1433: DBUtils.closeStatement(statement);
1434: DBUtils.closeConnection(connection);
1435: }
1436: }
1437:
1438: public boolean isSupportGetNumberOfMembers() {
1439: return true;
1440: }
1441:
1442: /**
1443: * Returns number of members in the database. Virtual guest is included.
1444: * @return number of members
1445: * @throws DatabaseException
1446: */
1447: public int getNumberOfMembers() throws DatabaseException {
1448:
1449: Connection connection = null;
1450: PreparedStatement statement = null;
1451: ResultSet resultSet = null;
1452: StringBuffer sql = new StringBuffer(512);
1453: sql.append("SELECT Count(*)");
1454: sql.append(" FROM " + TABLE_NAME);
1455: //sql.append(" WHERE "); // @todo: uncomment as needed
1456: try {
1457: connection = DBUtils.getConnection();
1458: statement = connection.prepareStatement(sql.toString());
1459: resultSet = statement.executeQuery();
1460: AssertionUtil
1461: .doAssert(resultSet.next(),
1462: "Assertion in MemberDAOImplJDBC.getNumberOfMembers.");
1463: return resultSet.getInt(1);
1464: } catch (SQLException sqle) {
1465: log.error("Sql Execution Error!", sqle);
1466: throw new DatabaseException(
1467: "Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers.");
1468: } finally {
1469: DBUtils.closeResultSet(resultSet);
1470: DBUtils.closeStatement(statement);
1471: DBUtils.closeConnection(connection);
1472: }
1473: }
1474:
1475: public boolean isSupportGetNumberOfMembers_inMemberStatus() {
1476: return true;
1477: }
1478:
1479: /**
1480: * Returns number of members based on the memberStatus. Virtual guest is included.
1481: * @return number of members, based on the memberStatus
1482: * @throws DatabaseException
1483: */
1484: public int getNumberOfMembers_inMemberStatus(int memberStatus)
1485: throws DatabaseException {
1486:
1487: Connection connection = null;
1488: PreparedStatement statement = null;
1489: ResultSet resultSet = null;
1490: StringBuffer sql = new StringBuffer(512);
1491: sql.append("SELECT Count(*)");
1492: sql.append(" FROM " + TABLE_NAME);
1493: sql.append(" WHERE MemberStatus = ? ");
1494: try {
1495: connection = DBUtils.getConnection();
1496: statement = connection.prepareStatement(sql.toString());
1497: statement.setInt(1, memberStatus);
1498: resultSet = statement.executeQuery();
1499: AssertionUtil
1500: .doAssert(resultSet.next(),
1501: "Assertion in MemberDAOImplJDBC.getNumberOfMembers_inMemberStatus.");
1502: return resultSet.getInt(1);
1503: } catch (SQLException sqle) {
1504: log.error("Sql Execution Error!", sqle);
1505: throw new DatabaseException(
1506: "Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers_inMemberStatus.");
1507: } finally {
1508: DBUtils.closeResultSet(resultSet);
1509: DBUtils.closeStatement(statement);
1510: DBUtils.closeConnection(connection);
1511: }
1512: }
1513:
1514: public boolean isSupportGetNumberOfMembers_inActivationStatus() {
1515: return true;
1516: }
1517:
1518: /**
1519: * Returns number of members based on the memberActivateCode. Virtual guest is included.
1520: * @return number of members, based on the memberActivateCode
1521: * @throws DatabaseException
1522: */
1523: public int getNumberOfMembers_inActivationStatus(boolean activated)
1524: throws DatabaseException {
1525:
1526: Connection connection = null;
1527: PreparedStatement statement = null;
1528: ResultSet resultSet = null;
1529: StringBuffer sql = new StringBuffer(512);
1530: sql.append("SELECT Count(*)");
1531: sql.append(" FROM " + TABLE_NAME);
1532: if (activated) {
1533: sql.append(" WHERE MemberActivateCode = 'activated' ");
1534: } else {
1535: // use IS NULL to fix problem of Oracle (Thanks sssimon)
1536: sql
1537: .append(" WHERE (MemberActivateCode <> 'activated') OR (MemberActivateCode IS NULL) ");
1538: }
1539: try {
1540: connection = DBUtils.getConnection();
1541: statement = connection.prepareStatement(sql.toString());
1542: resultSet = statement.executeQuery();
1543: AssertionUtil
1544: .doAssert(resultSet.next(),
1545: "Assertion in MemberDAOImplJDBC.getNumberOfMembers_inActivationStatus.");
1546: return resultSet.getInt(1);
1547: } catch (SQLException sqle) {
1548: log.error("Sql Execution Error!", sqle);
1549: throw new DatabaseException(
1550: "Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers_inActivationStatus.");
1551: } finally {
1552: DBUtils.closeResultSet(resultSet);
1553: DBUtils.closeStatement(statement);
1554: DBUtils.closeConnection(connection);
1555: }
1556: }
1557:
1558: /************************************************
1559: * Customized methods come below
1560: ************************************************/
1561:
1562: public boolean isSupportGetMemberIDFromMemberName() {
1563: return true;
1564: }
1565:
1566: /* @todo check if this method work with other DBMS other than MySql (check case-sensitive) */
1567: public final int getMemberIDFromMemberName(String memberName)
1568: throws ObjectNotFoundException, DatabaseException {
1569:
1570: Connection connection = null;
1571: PreparedStatement statement = null;
1572: ResultSet resultSet = null;
1573: String sql = "SELECT MemberID FROM " + TABLE_NAME
1574: + " WHERE MemberName = ?";
1575: try {
1576: connection = DBUtils.getConnection();
1577: statement = connection.prepareStatement(sql);
1578: statement.setString(1, memberName);
1579: resultSet = statement.executeQuery();
1580: if (!resultSet.next()) {
1581: throw new ObjectNotFoundException(
1582: "Cannot find the row in table Member where MemberName = "
1583: + memberName);
1584: }
1585: return resultSet.getInt(1);
1586: } catch (SQLException sqle) {
1587: log.error("Sql Execution Error!", sqle);
1588: throw new DatabaseException(
1589: "Error executing SQL in MemberDAOImplJDBC.getMemberIDFromMemberName.");
1590: } finally {
1591: DBUtils.closeResultSet(resultSet);
1592: DBUtils.closeStatement(statement);
1593: DBUtils.closeConnection(connection);
1594: }
1595: }
1596:
1597: public boolean isSupportGetMemberIDFromMemberEmail() {
1598: return true;
1599: }
1600:
1601: /* @todo check if this method work with other DBMS other than MySql (check case-sensitive) */
1602: public final int getMemberIDFromMemberEmail(String memberEmail)
1603: throws ObjectNotFoundException, DatabaseException {
1604:
1605: Connection connection = null;
1606: PreparedStatement statement = null;
1607: ResultSet resultSet = null;
1608: String sql = "SELECT MemberID FROM " + TABLE_NAME
1609: + " WHERE MemberEmail = ?";
1610: try {
1611: connection = DBUtils.getConnection();
1612: statement = connection.prepareStatement(sql);
1613: statement.setString(1, memberEmail);
1614: resultSet = statement.executeQuery();
1615: if (!resultSet.next()) {
1616: throw new ObjectNotFoundException(
1617: "Cannot find the row in table Member where MemberEmail = "
1618: + memberEmail);
1619: }
1620: return resultSet.getInt(1);
1621: } catch (SQLException sqle) {
1622: log.error("Sql Execution Error!", sqle);
1623: throw new DatabaseException(
1624: "Error executing SQL in MemberDAOImplJDBC.getMemberIDFromMemberEmail.");
1625: } finally {
1626: DBUtils.closeResultSet(resultSet);
1627: DBUtils.closeStatement(statement);
1628: DBUtils.closeConnection(connection);
1629: }
1630: }
1631:
1632: public boolean isSupportGetMembers_withSortSupport_limit() {
1633: return true;
1634: }
1635:
1636: public Collection getMembers_withSortSupport_limit(int offset,
1637: int rowsToReturn, String sort, String order,
1638: int memberStatus) throws IllegalArgumentException,
1639: DatabaseException {
1640: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
1641: return getBeans_withSortSupport_limit_mysql(offset,
1642: rowsToReturn, sort, order, memberStatus);
1643: } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
1644: return getBeans_withSortSupport_limit_noscroll(offset,
1645: rowsToReturn, sort, order, memberStatus);
1646: }
1647: return getBeans_withSortSupport_limit_general(offset,
1648: rowsToReturn, sort, order, memberStatus);
1649: }
1650:
1651: /*
1652: * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
1653: * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
1654: * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1655: * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
1656: * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
1657: * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
1658: * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
1659: * MemberCoolLink1, MemberCoolLink2
1660: * as of 10 March, add MemberActivateCode
1661: * as of 19 Nov 2007, add MemberLanguage
1662: * Excluded columns: MemberPassword, MemberFirstEmail, MemberFirstIP, MemberLastIP, MemberModifiedDate,
1663: * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1664: * MemberTimeZone, MemberSkin
1665: */
1666: /**
1667: * This method support sorting and for PUBLIC view
1668: * @param memberStatus TODO
1669: */
1670: /* @todo fix bug that cannot prepare sort and order */
1671: private Collection getBeans_withSortSupport_limit_mysql(int offset,
1672: int rowsToReturn, String sort, String order,
1673: int memberStatus) throws IllegalArgumentException,
1674: DatabaseException {
1675:
1676: if (offset < 0)
1677: throw new IllegalArgumentException(
1678: "The offset < 0 is not allowed.");
1679: if (rowsToReturn <= 0)
1680: throw new IllegalArgumentException(
1681: "The rowsToReturn <= 0 is not allowed.");
1682: /*
1683: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1684: * MemberTitle,
1685: * MemberCity, MemberState,
1686: * MemberCountry, MemberCareer,
1687: */
1688: if ((!sort.equals("MemberID")) && (!sort.equals("MemberName"))
1689: && (!sort.equals("MemberFirstname"))
1690: && (!sort.equals("MemberLastname"))
1691: && (!sort.equals("MemberGender"))
1692: && (!sort.equals("MemberBirthday"))
1693: && (!sort.equals("MemberCreationDate"))
1694: && (!sort.equals("MemberLastLogon"))
1695: && (!sort.equals("MemberViewCount"))
1696: && (!sort.equals("MemberPostCount"))
1697: && (!sort.equals("MemberCountry"))
1698: && (!sort.equals("MemberEmail"))
1699: && (!sort.equals("MemberHomepage"))) {
1700: throw new IllegalArgumentException(
1701: "Cannot sort, reason: dont understand the criteria '"
1702: + sort + "'.");
1703: }
1704:
1705: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
1706: throw new IllegalArgumentException(
1707: "Cannot sort, reason: dont understand the order '"
1708: + order + "'.");
1709: }
1710:
1711: Connection connection = null;
1712: PreparedStatement statement = null;
1713: ResultSet resultSet = null;
1714: Collection retValue = new ArrayList();
1715: StringBuffer sql = new StringBuffer(512);
1716: sql
1717: .append("SELECT MemberID, MemberName, MemberEmail, MemberFirstEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, 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, MemberLanguage");
1718: sql.append(" FROM " + TABLE_NAME);
1719: if (memberStatus == MemberDAO.ALL_MEMBER_STATUS) {
1720: // do nothing
1721: } else if (memberStatus == MemberBean.MEMBER_STATUS_PENDING) {
1722: sql.append(" WHERE (MemberStatus = " + memberStatus + ")");
1723: } else {
1724: throw new IllegalArgumentException(
1725: "Cannot process MemberStatus = " + memberStatus);
1726: }
1727: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1728: sql.append(" LIMIT ?, ?");
1729:
1730: try {
1731: connection = DBUtils.getConnection();
1732: statement = connection.prepareStatement(sql.toString());
1733: statement.setInt(1, offset);
1734: statement.setInt(2, rowsToReturn);
1735: resultSet = statement.executeQuery();
1736: while (resultSet.next()) {
1737: MemberBean bean = new MemberBean();
1738: bean.setMemberID(resultSet.getInt("MemberID"));
1739: bean.setMemberName(resultSet.getString("MemberName"));
1740: bean.setMemberEmail(resultSet.getString("MemberEmail"));
1741: bean.setMemberFirstEmail(resultSet
1742: .getString("MemberFirstEmail"));
1743: bean.setMemberEmailVisible(resultSet
1744: .getInt("MemberEmailVisible"));
1745: bean.setMemberNameVisible(resultSet
1746: .getInt("MemberNameVisible"));
1747: bean.setMemberFirstIP(resultSet
1748: .getString("MemberFirstIP"));
1749: bean.setMemberLastIP(resultSet
1750: .getString("MemberLastIP"));
1751: bean.setMemberViewCount(resultSet
1752: .getInt("MemberViewCount"));
1753: bean.setMemberPostCount(resultSet
1754: .getInt("MemberPostCount"));
1755: bean.setMemberCreationDate(resultSet
1756: .getTimestamp("MemberCreationDate"));
1757: bean.setMemberModifiedDate(resultSet
1758: .getTimestamp("MemberModifiedDate"));
1759: bean.setMemberLastLogon(resultSet
1760: .getTimestamp("MemberLastLogon"));
1761: bean.setMemberOption(resultSet.getInt("MemberOption"));
1762: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1763: bean.setMemberWarnCount(resultSet
1764: .getInt("MemberWarnCount"));
1765: bean.setMemberVoteCount(resultSet
1766: .getInt("MemberVoteCount"));
1767: bean.setMemberVoteTotalStars(resultSet
1768: .getInt("MemberVoteTotalStars"));
1769: bean.setMemberRewardPoints(resultSet
1770: .getInt("MemberRewardPoints"));
1771: bean.setMemberTitle(resultSet.getString("MemberTitle"));
1772: bean.setMemberSignature(resultSet
1773: .getString("MemberSignature"));
1774: bean.setMemberAvatar(resultSet
1775: .getString("MemberAvatar"));
1776: bean.setMemberFirstname(resultSet
1777: .getString("MemberFirstname"));
1778: bean.setMemberLastname(resultSet
1779: .getString("MemberLastname"));
1780: bean.setMemberGender(resultSet.getInt("MemberGender"));
1781: bean.setMemberBirthday(resultSet
1782: .getDate("MemberBirthday"));
1783: bean.setMemberAddress(resultSet
1784: .getString("MemberAddress"));
1785: bean.setMemberCity(resultSet.getString("MemberCity"));
1786: bean.setMemberState(resultSet.getString("MemberState"));
1787: bean.setMemberCountry(resultSet
1788: .getString("MemberCountry"));
1789: bean.setMemberPhone(resultSet.getString("MemberPhone"));
1790: bean.setMemberMobile(resultSet
1791: .getString("MemberMobile"));
1792: bean.setMemberFax(resultSet.getString("MemberFax"));
1793: bean.setMemberCareer(resultSet
1794: .getString("MemberCareer"));
1795: bean.setMemberHomepage(resultSet
1796: .getString("MemberHomepage"));
1797: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1798: bean.setMemberAol(resultSet.getString("MemberAol"));
1799: bean.setMemberIcq(resultSet.getString("MemberIcq"));
1800: bean.setMemberMsn(resultSet.getString("MemberMsn"));
1801: bean.setMemberCoolLink1(resultSet
1802: .getString("MemberCoolLink1"));
1803: bean.setMemberCoolLink2(resultSet
1804: .getString("MemberCoolLink2"));
1805: bean.setMemberActivateCode(resultSet
1806: .getString("MemberActivateCode"));
1807: bean.setMemberLanguage(resultSet
1808: .getString("MemberLanguage"));
1809: retValue.add(bean);
1810: }
1811: return retValue;
1812: } catch (SQLException sqle) {
1813: log.error("Sql Execution Error!", sqle);
1814: throw new DatabaseException(
1815: "Error executing SQL in MemberDAOImplJDBC.getBeans_withSortSupport_limit_mysql.");
1816: } finally {
1817: DBUtils.closeResultSet(resultSet);
1818: DBUtils.closeStatement(statement);
1819: DBUtils.closeConnection(connection);
1820: }
1821: }
1822:
1823: /*
1824: * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
1825: * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
1826: * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1827: * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
1828: * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
1829: * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
1830: * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
1831: * MemberCoolLink1, MemberCoolLink2
1832: * as of 10 March, add MemberActivateCode
1833: * Excluded columns: MemberPassword, MemberFirstEmail, MemberFirstIP, MemberLastIP, MemberModifiedDate,
1834: * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
1835: * MemberTimeZone, MemberSkin, MemberLanguage
1836: */
1837: /**
1838: * This method support sorting and for PUBLIC view
1839: * @param memberStatus TODO
1840: */
1841: /* @todo fix bug that cannot prepare sort and order */
1842: private Collection getBeans_withSortSupport_limit_noscroll(
1843: int offset, int rowsToReturn, String sort, String order,
1844: int memberStatus) throws IllegalArgumentException,
1845: DatabaseException {
1846: if (offset < 0)
1847: throw new IllegalArgumentException(
1848: "The offset < 0 is not allowed.");
1849: if (rowsToReturn <= 0)
1850: throw new IllegalArgumentException(
1851: "The rowsToReturn <= 0 is not allowed.");
1852: /*
1853: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
1854: * MemberTitle,
1855: * MemberCity, MemberState,
1856: * MemberCountry, MemberCareer,
1857: */
1858: if ((!sort.equals("MemberID")) && (!sort.equals("MemberName"))
1859: && (!sort.equals("MemberFirstname"))
1860: && (!sort.equals("MemberLastname"))
1861: && (!sort.equals("MemberGender"))
1862: && (!sort.equals("MemberBirthday"))
1863: && (!sort.equals("MemberCreationDate"))
1864: && (!sort.equals("MemberLastLogon"))
1865: && (!sort.equals("MemberViewCount"))
1866: && (!sort.equals("MemberPostCount"))
1867: && (!sort.equals("MemberCountry"))
1868: && (!sort.equals("MemberEmai"))
1869: && (!sort.equals("MemberHomepage"))) {
1870: throw new IllegalArgumentException(
1871: "Cannot sort, reason: dont understand the criteria '"
1872: + sort + "'.");
1873: }
1874:
1875: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
1876: throw new IllegalArgumentException(
1877: "Cannot sort, reason: dont understand the order '"
1878: + order + "'.");
1879: }
1880:
1881: Connection connection = null;
1882: PreparedStatement statement = null;
1883: ResultSet resultSet = null;
1884: Collection retValue = new ArrayList();
1885: StringBuffer sql = new StringBuffer(512);
1886: sql
1887: .append("SELECT MemberID, MemberName, MemberEmail, MemberFirstEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, 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");
1888: sql.append(" FROM " + TABLE_NAME);
1889: if (memberStatus == MemberDAO.ALL_MEMBER_STATUS) {
1890: // do nothing
1891: } else if (memberStatus == MemberBean.MEMBER_STATUS_PENDING) {
1892: sql.append(" WHERE (MemberStatus = " + memberStatus + ")");
1893: } else {
1894: throw new IllegalArgumentException(
1895: "Cannot process MemberStatus = " + memberStatus);
1896: }
1897: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
1898:
1899: try {
1900: connection = DBUtils.getConnection();
1901: statement = connection.prepareStatement(sql.toString());
1902: statement.setMaxRows(offset + rowsToReturn);
1903: resultSet = statement.executeQuery();
1904: int rowIndex = -1;
1905: while (resultSet.next()) {
1906: rowIndex++;
1907: if (rowIndex < offset)
1908: continue;
1909: MemberBean bean = new MemberBean();
1910: bean.setMemberID(resultSet.getInt("MemberID"));
1911: bean.setMemberName(resultSet.getString("MemberName"));
1912: bean.setMemberEmail(resultSet.getString("MemberEmail"));
1913: bean.setMemberFirstEmail(resultSet
1914: .getString("MemberFirstEmail"));
1915: bean.setMemberEmailVisible(resultSet
1916: .getInt("MemberEmailVisible"));
1917: bean.setMemberNameVisible(resultSet
1918: .getInt("MemberNameVisible"));
1919: bean.setMemberFirstIP(resultSet
1920: .getString("MemberFirstIP"));
1921: bean.setMemberLastIP(resultSet
1922: .getString("MemberLastIP"));
1923: bean.setMemberViewCount(resultSet
1924: .getInt("MemberViewCount"));
1925: bean.setMemberPostCount(resultSet
1926: .getInt("MemberPostCount"));
1927: bean.setMemberCreationDate(resultSet
1928: .getTimestamp("MemberCreationDate"));
1929: bean.setMemberModifiedDate(resultSet
1930: .getTimestamp("MemberModifiedDate"));
1931: bean.setMemberLastLogon(resultSet
1932: .getTimestamp("MemberLastLogon"));
1933: bean.setMemberOption(resultSet.getInt("MemberOption"));
1934: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
1935: bean.setMemberWarnCount(resultSet
1936: .getInt("MemberWarnCount"));
1937: bean.setMemberVoteCount(resultSet
1938: .getInt("MemberVoteCount"));
1939: bean.setMemberVoteTotalStars(resultSet
1940: .getInt("MemberVoteTotalStars"));
1941: bean.setMemberRewardPoints(resultSet
1942: .getInt("MemberRewardPoints"));
1943: bean.setMemberTitle(resultSet.getString("MemberTitle"));
1944: bean.setMemberSignature(resultSet
1945: .getString("MemberSignature"));
1946: bean.setMemberAvatar(resultSet
1947: .getString("MemberAvatar"));
1948: bean.setMemberFirstname(resultSet
1949: .getString("MemberFirstname"));
1950: bean.setMemberLastname(resultSet
1951: .getString("MemberLastname"));
1952: bean.setMemberGender(resultSet.getInt("MemberGender"));
1953: bean.setMemberBirthday(resultSet
1954: .getDate("MemberBirthday"));
1955: bean.setMemberAddress(resultSet
1956: .getString("MemberAddress"));
1957: bean.setMemberCity(resultSet.getString("MemberCity"));
1958: bean.setMemberState(resultSet.getString("MemberState"));
1959: bean.setMemberCountry(resultSet
1960: .getString("MemberCountry"));
1961: bean.setMemberPhone(resultSet.getString("MemberPhone"));
1962: bean.setMemberMobile(resultSet
1963: .getString("MemberMobile"));
1964: bean.setMemberFax(resultSet.getString("MemberFax"));
1965: bean.setMemberCareer(resultSet
1966: .getString("MemberCareer"));
1967: bean.setMemberHomepage(resultSet
1968: .getString("MemberHomepage"));
1969: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
1970: bean.setMemberAol(resultSet.getString("MemberAol"));
1971: bean.setMemberIcq(resultSet.getString("MemberIcq"));
1972: bean.setMemberMsn(resultSet.getString("MemberMsn"));
1973: bean.setMemberCoolLink1(resultSet
1974: .getString("MemberCoolLink1"));
1975: bean.setMemberCoolLink2(resultSet
1976: .getString("MemberCoolLink2"));
1977: bean.setMemberActivateCode(resultSet
1978: .getString("MemberActivateCode"));
1979: retValue.add(bean);
1980: if (retValue.size() == rowsToReturn)
1981: break;// Fix the Sybase bug
1982: }
1983: return retValue;
1984: } catch (SQLException sqle) {
1985: log.error("Sql Execution Error!", sqle);
1986: throw new DatabaseException(
1987: "Error executing SQL in MemberDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
1988: } finally {
1989: DBUtils.closeResultSet(resultSet);
1990: DBUtils.resetStatement(statement);
1991: DBUtils.closeStatement(statement);
1992: DBUtils.closeConnection(connection);
1993: }
1994: }
1995:
1996: /*
1997: * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
1998: * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
1999: * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
2000: * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
2001: * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
2002: * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
2003: * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
2004: * MemberCoolLink1, MemberCoolLink2
2005: * as of 10 March, add MemberActivateCode
2006: * Excluded columns: MemberPassword, MemberFirstEmail, MemberFirstIP, MemberLastIP, MemberModifiedDate,
2007: * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
2008: * MemberTimeZone, MemberSkin, MemberLanguage
2009: */
2010: /**
2011: * This method support sorting and for PUBLIC view
2012: * @param memberStatus TODO
2013: */
2014: /* @todo fix bug that cannot prepare sort and order */
2015: private Collection getBeans_withSortSupport_limit_general(
2016: int offset, int rowsToReturn, String sort, String order,
2017: int memberStatus) throws IllegalArgumentException,
2018: DatabaseException {
2019:
2020: if (offset < 0)
2021: throw new IllegalArgumentException(
2022: "The offset < 0 is not allowed.");
2023: if (rowsToReturn <= 0)
2024: throw new IllegalArgumentException(
2025: "The rowsToReturn <= 0 is not allowed.");
2026: /*
2027: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
2028: * MemberTitle,
2029: * MemberCity, MemberState,
2030: * MemberCountry, MemberCareer,
2031: */
2032: if ((!sort.equals("MemberID")) && (!sort.equals("MemberName"))
2033: && (!sort.equals("MemberFirstname"))
2034: && (!sort.equals("MemberLastname"))
2035: && (!sort.equals("MemberGender"))
2036: && (!sort.equals("MemberBirthday"))
2037: && (!sort.equals("MemberCreationDate"))
2038: && (!sort.equals("MemberLastLogon"))
2039: && (!sort.equals("MemberViewCount"))
2040: && (!sort.equals("MemberPostCount"))
2041: && (!sort.equals("MemberCountry"))
2042: && (!sort.equals("MemberEmail"))
2043: && (!sort.equals("MemberHomepage"))) {
2044: throw new IllegalArgumentException(
2045: "Cannot sort, reason: dont understand the criteria '"
2046: + sort + "'.");
2047: }
2048:
2049: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
2050: throw new IllegalArgumentException(
2051: "Cannot sort, reason: dont understand the order '"
2052: + order + "'.");
2053: }
2054:
2055: Connection connection = null;
2056: PreparedStatement statement = null;
2057: ResultSet resultSet = null;
2058: Collection retValue = new ArrayList();
2059: StringBuffer sql = new StringBuffer(512);
2060: sql
2061: .append("SELECT MemberID, MemberName, MemberEmail, MemberFirstEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, 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");
2062: sql.append(" FROM " + TABLE_NAME);
2063: if (memberStatus == MemberDAO.ALL_MEMBER_STATUS) {
2064: // do nothing
2065: } else if (memberStatus == MemberBean.MEMBER_STATUS_PENDING) {
2066: sql.append(" WHERE (MemberStatus = " + memberStatus + ")");
2067: } else {
2068: throw new IllegalArgumentException(
2069: "Cannot process MemberStatus = " + memberStatus);
2070: }
2071: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
2072:
2073: try {
2074: connection = DBUtils.getConnection();
2075: statement = connection.prepareStatement(sql.toString(),
2076: ResultSet.TYPE_SCROLL_INSENSITIVE,
2077: ResultSet.CONCUR_READ_ONLY);
2078: statement.setMaxRows(offset + rowsToReturn);
2079: try {
2080: statement.setFetchSize(Math.min(rowsToReturn,
2081: DBUtils.MAX_FETCH_SIZE));
2082: } catch (SQLException sqle) {
2083: //do nothing, postgreSQL does not support this method
2084: }
2085: resultSet = statement.executeQuery();
2086: boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
2087: while (loop) {
2088: MemberBean bean = new MemberBean();
2089: bean.setMemberID(resultSet.getInt("MemberID"));
2090: bean.setMemberName(resultSet.getString("MemberName"));
2091: bean.setMemberEmail(resultSet.getString("MemberEmail"));
2092: bean.setMemberFirstEmail(resultSet
2093: .getString("MemberFirstEmail"));
2094: bean.setMemberEmailVisible(resultSet
2095: .getInt("MemberEmailVisible"));
2096: bean.setMemberNameVisible(resultSet
2097: .getInt("MemberNameVisible"));
2098: bean.setMemberFirstIP(resultSet
2099: .getString("MemberFirstIP"));
2100: bean.setMemberLastIP(resultSet
2101: .getString("MemberLastIP"));
2102: bean.setMemberViewCount(resultSet
2103: .getInt("MemberViewCount"));
2104: bean.setMemberPostCount(resultSet
2105: .getInt("MemberPostCount"));
2106: bean.setMemberCreationDate(resultSet
2107: .getTimestamp("MemberCreationDate"));
2108: bean.setMemberModifiedDate(resultSet
2109: .getTimestamp("MemberModifiedDate"));
2110: bean.setMemberLastLogon(resultSet
2111: .getTimestamp("MemberLastLogon"));
2112: bean.setMemberOption(resultSet.getInt("MemberOption"));
2113: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2114: bean.setMemberWarnCount(resultSet
2115: .getInt("MemberWarnCount"));
2116: bean.setMemberVoteCount(resultSet
2117: .getInt("MemberVoteCount"));
2118: bean.setMemberVoteTotalStars(resultSet
2119: .getInt("MemberVoteTotalStars"));
2120: bean.setMemberRewardPoints(resultSet
2121: .getInt("MemberRewardPoints"));
2122: bean.setMemberTitle(resultSet.getString("MemberTitle"));
2123: bean.setMemberSignature(resultSet
2124: .getString("MemberSignature"));
2125: bean.setMemberAvatar(resultSet
2126: .getString("MemberAvatar"));
2127: bean.setMemberFirstname(resultSet
2128: .getString("MemberFirstname"));
2129: bean.setMemberLastname(resultSet
2130: .getString("MemberLastname"));
2131: bean.setMemberGender(resultSet.getInt("MemberGender"));
2132: bean.setMemberBirthday(resultSet
2133: .getDate("MemberBirthday"));
2134: bean.setMemberAddress(resultSet
2135: .getString("MemberAddress"));
2136: bean.setMemberCity(resultSet.getString("MemberCity"));
2137: bean.setMemberState(resultSet.getString("MemberState"));
2138: bean.setMemberCountry(resultSet
2139: .getString("MemberCountry"));
2140: bean.setMemberPhone(resultSet.getString("MemberPhone"));
2141: bean.setMemberMobile(resultSet
2142: .getString("MemberMobile"));
2143: bean.setMemberFax(resultSet.getString("MemberFax"));
2144: bean.setMemberCareer(resultSet
2145: .getString("MemberCareer"));
2146: bean.setMemberHomepage(resultSet
2147: .getString("MemberHomepage"));
2148: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
2149: bean.setMemberAol(resultSet.getString("MemberAol"));
2150: bean.setMemberIcq(resultSet.getString("MemberIcq"));
2151: bean.setMemberMsn(resultSet.getString("MemberMsn"));
2152: bean.setMemberCoolLink1(resultSet
2153: .getString("MemberCoolLink1"));
2154: bean.setMemberCoolLink2(resultSet
2155: .getString("MemberCoolLink2"));
2156: bean.setMemberActivateCode(resultSet
2157: .getString("MemberActivateCode"));
2158: retValue.add(bean);
2159: if (retValue.size() == rowsToReturn)
2160: break;// Fix the Sybase bug
2161: loop = resultSet.next();
2162: }//while
2163: return retValue;
2164: } catch (SQLException sqle) {
2165: log.error("Sql Execution Error!", sqle);
2166: throw new DatabaseException(
2167: "Error executing SQL in MemberDAOImplJDBC.getBeans_withSortSupport_limit_general.");
2168: } finally {
2169: DBUtils.closeResultSet(resultSet);
2170: DBUtils.resetStatement(statement);
2171: DBUtils.closeStatement(statement);
2172: DBUtils.closeConnection(connection);
2173: }
2174: }
2175:
2176: /*
2177: * Included columns: MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible,
2178: * MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption,
2179: * MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
2180: * MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname,
2181: * MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState,
2182: * MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer,
2183: * MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn,
2184: * MemberCoolLink1, MemberCoolLink2
2185: * as of 10 March, add MemberActivateCode
2186: * Excluded columns: MemberPassword, MemberFirstEmail, MemberFirstIP, MemberLastIP, MemberModifiedDate,
2187: * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
2188: * MemberTimeZone, MemberSkin, MemberLanguage
2189: */
2190: public boolean isSupportGetEnableMembers_inActivationStatus() {
2191: return true;
2192: }
2193:
2194: /**
2195: * This method support sorting and for PUBLIC view
2196: */
2197: public Collection getEnableMembers_inActivationStatus(String kind)
2198: throws IllegalArgumentException, DatabaseException {
2199:
2200: Connection connection = null;
2201: PreparedStatement statement = null;
2202: ResultSet resultSet = null;
2203: Collection retValue = new ArrayList();
2204: StringBuffer sql = new StringBuffer(512);
2205:
2206: sql
2207: .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");
2208: sql.append(" FROM " + TABLE_NAME);
2209: if (kind.equals("activated")) {
2210: sql.append(" WHERE (MemberStatus = "
2211: + MemberBean.MEMBER_STATUS_ENABLE
2212: + ") AND (MemberActivateCode = 'activated') ");
2213: } else if (kind.equals("nonactivated")) {
2214: // use IS NULL to fix problem of Oracle (Thanks sssimon)
2215: sql
2216: .append(" WHERE (MemberStatus = "
2217: + MemberBean.MEMBER_STATUS_ENABLE
2218: + ") AND ((MemberActivateCode <> 'activated') OR (MemberActivateCode IS NULL))");
2219: } else if (kind.equals("all")) {
2220: sql.append(" WHERE (MemberStatus = "
2221: + MemberBean.MEMBER_STATUS_ENABLE + ")");
2222: } else {
2223: throw new IllegalArgumentException(
2224: "Cannot process activation kind = " + kind);
2225: }
2226:
2227: try {
2228: connection = DBUtils.getConnection();
2229: statement = connection.prepareStatement(sql.toString());
2230:
2231: resultSet = statement.executeQuery();
2232: while (resultSet.next()) {
2233: MemberBean bean = new MemberBean();
2234: bean.setMemberID(resultSet.getInt("MemberID"));
2235: bean.setMemberName(resultSet.getString("MemberName"));
2236: bean.setMemberEmail(resultSet.getString("MemberEmail"));
2237: bean.setMemberEmailVisible(resultSet
2238: .getInt("MemberEmailVisible"));
2239: bean.setMemberNameVisible(resultSet
2240: .getInt("MemberNameVisible"));
2241: bean.setMemberViewCount(resultSet
2242: .getInt("MemberViewCount"));
2243: bean.setMemberPostCount(resultSet
2244: .getInt("MemberPostCount"));
2245: bean.setMemberCreationDate(resultSet
2246: .getTimestamp("MemberCreationDate"));
2247: bean.setMemberLastLogon(resultSet
2248: .getTimestamp("MemberLastLogon"));
2249: bean.setMemberOption(resultSet.getInt("MemberOption"));
2250: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2251: bean.setMemberWarnCount(resultSet
2252: .getInt("MemberWarnCount"));
2253: bean.setMemberVoteCount(resultSet
2254: .getInt("MemberVoteCount"));
2255: bean.setMemberVoteTotalStars(resultSet
2256: .getInt("MemberVoteTotalStars"));
2257: bean.setMemberRewardPoints(resultSet
2258: .getInt("MemberRewardPoints"));
2259: bean.setMemberTitle(resultSet.getString("MemberTitle"));
2260: bean.setMemberSignature(resultSet
2261: .getString("MemberSignature"));
2262: bean.setMemberAvatar(resultSet
2263: .getString("MemberAvatar"));
2264: bean.setMemberFirstname(resultSet
2265: .getString("MemberFirstname"));
2266: bean.setMemberLastname(resultSet
2267: .getString("MemberLastname"));
2268: bean.setMemberGender(resultSet.getInt("MemberGender"));
2269: bean.setMemberBirthday(resultSet
2270: .getDate("MemberBirthday"));
2271: bean.setMemberAddress(resultSet
2272: .getString("MemberAddress"));
2273: bean.setMemberCity(resultSet.getString("MemberCity"));
2274: bean.setMemberState(resultSet.getString("MemberState"));
2275: bean.setMemberCountry(resultSet
2276: .getString("MemberCountry"));
2277: bean.setMemberPhone(resultSet.getString("MemberPhone"));
2278: bean.setMemberMobile(resultSet
2279: .getString("MemberMobile"));
2280: bean.setMemberFax(resultSet.getString("MemberFax"));
2281: bean.setMemberCareer(resultSet
2282: .getString("MemberCareer"));
2283: bean.setMemberHomepage(resultSet
2284: .getString("MemberHomepage"));
2285: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
2286: bean.setMemberAol(resultSet.getString("MemberAol"));
2287: bean.setMemberIcq(resultSet.getString("MemberIcq"));
2288: bean.setMemberMsn(resultSet.getString("MemberMsn"));
2289: bean.setMemberCoolLink1(resultSet
2290: .getString("MemberCoolLink1"));
2291: bean.setMemberCoolLink2(resultSet
2292: .getString("MemberCoolLink2"));
2293: bean.setMemberActivateCode(resultSet
2294: .getString("MemberActivateCode"));
2295: retValue.add(bean);
2296: }//while
2297: return retValue;
2298: } catch (SQLException sqle) {
2299: log.error("Sql Execution Error!", sqle);
2300: throw new DatabaseException(
2301: "Error executing SQL in MemberDAOImplJDBC.getEnableMembers_inActivationStatus.");
2302: } finally {
2303: DBUtils.closeResultSet(resultSet);
2304: DBUtils.resetStatement(statement);
2305: DBUtils.closeStatement(statement);
2306: DBUtils.closeConnection(connection);
2307: }
2308: }
2309:
2310: public boolean isSupportUpdateStatus() {
2311: return true;
2312: }
2313:
2314: /*
2315: * Included columns: MemberStatus
2316: * Excluded columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
2317: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
2318: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberOption,
2319: * MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage,
2320: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle,
2321: * MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage,
2322: * MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress,
2323: * MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile,
2324: * MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol,
2325: * MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2
2326: */
2327: public void updateStatus(int memberID, // primary key
2328: int memberStatus) throws ObjectNotFoundException,
2329: DatabaseException {
2330:
2331: Connection connection = null;
2332: PreparedStatement statement = null;
2333: StringBuffer sql = new StringBuffer(512);
2334: sql.append("UPDATE " + TABLE_NAME + " SET MemberStatus = ?");
2335: sql.append(" WHERE MemberID = ?");
2336: try {
2337: connection = DBUtils.getConnection();
2338: statement = connection.prepareStatement(sql.toString());
2339:
2340: // // column(s) to update
2341: statement.setInt(1, memberStatus);
2342:
2343: // primary key column(s)
2344: statement.setInt(2, memberID);
2345:
2346: if (statement.executeUpdate() != 1) {
2347: throw new ObjectNotFoundException(
2348: "Cannot update table Member where primary key = ("
2349: + memberID + ").");
2350: }
2351: setDirty(true);
2352: } catch (SQLException sqle) {
2353: log.error("Sql Execution Error!", sqle);
2354: throw new DatabaseException(
2355: "Error executing SQL in MemberDAOImplJDBC.updateStatus.");
2356: } finally {
2357: DBUtils.closeStatement(statement);
2358: DBUtils.closeConnection(connection);
2359: }
2360: }
2361:
2362: public boolean isSupportUpdatePostCount() {
2363: return true;
2364: }
2365:
2366: public void updatePostCount(int memberID, // primary key
2367: int memberPostCount) throws ObjectNotFoundException,
2368: DatabaseException {
2369:
2370: Connection connection = null;
2371: PreparedStatement statement = null;
2372: StringBuffer sql = new StringBuffer(512);
2373: sql.append("UPDATE " + TABLE_NAME + " SET MemberPostCount = ?");
2374: sql.append(" WHERE MemberID = ?");
2375: try {
2376: connection = DBUtils.getConnection();
2377: statement = connection.prepareStatement(sql.toString());
2378:
2379: // // column(s) to update
2380: statement.setInt(1, memberPostCount);
2381:
2382: // primary key column(s)
2383: statement.setInt(2, memberID);
2384:
2385: if (statement.executeUpdate() != 1) {
2386: throw new ObjectNotFoundException(
2387: "Cannot update table Member where primary key = ("
2388: + memberID + ").");
2389: }
2390: setDirty(true);
2391: } catch (SQLException sqle) {
2392: log.error("Sql Execution Error!", sqle);
2393: throw new DatabaseException(
2394: "Error executing SQL in MemberDAOImplJDBC.updatePostCount.");
2395: } finally {
2396: DBUtils.closeStatement(statement);
2397: DBUtils.closeConnection(connection);
2398: }
2399: }
2400:
2401: public boolean isSupportIncreaseViewCount() {
2402: return true;
2403: }
2404:
2405: /**
2406: * This method should be call only when we can make sure that memberID is in database
2407: */
2408: public void increaseViewCount(int memberID)
2409: throws DatabaseException, ObjectNotFoundException {
2410:
2411: Connection connection = null;
2412: PreparedStatement statement = null;
2413: String sql = "UPDATE "
2414: + TABLE_NAME
2415: + " SET MemberViewCount = MemberViewCount + 1 WHERE MemberID = ?";
2416: try {
2417: connection = DBUtils.getConnection();
2418: statement = connection.prepareStatement(sql);
2419: statement.setInt(1, memberID);
2420: if (statement.executeUpdate() != 1) {
2421: throw new ObjectNotFoundException(
2422: "Cannot update the MemberViewCount in table Member. Please contact Web site Administrator.");
2423: }
2424: //@todo: coi lai cho nay
2425: // ATTENTION !!!
2426: setDirty(true);
2427: } catch (SQLException sqle) {
2428: log.error("Sql Execution Error!", sqle);
2429: throw new DatabaseException(
2430: "Error executing SQL in MemberDAOImplJDBC.increaseViewCount.");
2431: } finally {
2432: DBUtils.closeStatement(statement);
2433: DBUtils.closeConnection(connection);
2434: }
2435: }
2436:
2437: public boolean isSupportIncreasePostCount() {
2438: return true;
2439: }
2440:
2441: /**
2442: * This method should be call only when we can make sure that memberID is in database
2443: */
2444: public void increasePostCount(int memberID)
2445: throws DatabaseException, ObjectNotFoundException {
2446:
2447: Connection connection = null;
2448: PreparedStatement statement = null;
2449: String sql = "UPDATE "
2450: + TABLE_NAME
2451: + " SET MemberPostCount = MemberPostCount + 1 WHERE MemberID = ?";
2452: try {
2453: connection = DBUtils.getConnection();
2454: statement = connection.prepareStatement(sql);
2455: statement.setInt(1, memberID);
2456: if (statement.executeUpdate() != 1) {
2457: throw new ObjectNotFoundException(
2458: "Cannot update the MemberPostCount in table Member. Please contact Web site Administrator.");
2459: }
2460: //@todo: coi lai cho nay
2461: // ATTENTION !!!
2462: setDirty(true);
2463: } catch (SQLException sqle) {
2464: log.error("Sql Execution Error!", sqle);
2465: throw new DatabaseException(
2466: "Error executing SQL in MemberDAOImplJDBC.increasePostCount.");
2467: } finally {
2468: DBUtils.closeStatement(statement);
2469: DBUtils.closeConnection(connection);
2470: }
2471: }
2472:
2473: public boolean isSupportGetMembers_inExpire_limit() {
2474: return true;
2475: }
2476:
2477: public Collection getMembers_inExpire_limit(Timestamp expireDate,
2478: int offset, int rowsToReturn, String sort, String order)
2479: throws IllegalArgumentException, DatabaseException {
2480:
2481: if (offset < 0)
2482: throw new IllegalArgumentException(
2483: "The offset < 0 is not allowed.");
2484: if (rowsToReturn <= 0)
2485: throw new IllegalArgumentException(
2486: "The rowsToReturn <= 0 is not allowed.");
2487:
2488: /*
2489: * MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints,
2490: * MemberTitle,
2491: * MemberCity, MemberState,
2492: * MemberCountry, MemberCareer,
2493: */
2494: if ((!sort.equals("MemberID")) && (!sort.equals("MemberName"))
2495: && (!sort.equals("MemberFirstname"))
2496: && (!sort.equals("MemberLastname"))
2497: && (!sort.equals("MemberGender"))
2498: && (!sort.equals("MemberBirthday"))
2499: && (!sort.equals("MemberCreationDate"))
2500: && (!sort.equals("MemberExpireDate"))
2501: && (!sort.equals("MemberLastLogon"))
2502: && (!sort.equals("MemberViewCount"))
2503: && (!sort.equals("MemberPostCount"))) {
2504: throw new IllegalArgumentException(
2505: "Cannot sort, reason: dont understand the criteria '"
2506: + sort + "'.");
2507: }
2508:
2509: if ((!order.equals("ASC")) && (!order.equals("DESC"))) {
2510: throw new IllegalArgumentException(
2511: "Cannot sort, reason: dont understand the order '"
2512: + order + "'.");
2513: }
2514:
2515: Connection connection = null;
2516: PreparedStatement statement = null;
2517: ResultSet resultSet = null;
2518: Collection retValue = new ArrayList();
2519: StringBuffer sql = new StringBuffer(512);
2520: sql
2521: .append("SELECT MemberID, MemberName, MemberCreationDate, MemberExpireDate, MemberStatus");
2522: sql.append(" FROM " + TABLE_NAME);
2523: sql
2524: .append(" WHERE MemberExpireDate <= ? OR MemberExpireDate IS NULL");
2525: sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
2526: try {
2527: connection = DBUtils.getConnection();
2528: statement = connection.prepareStatement(sql.toString(),
2529: ResultSet.TYPE_SCROLL_INSENSITIVE,
2530: ResultSet.CONCUR_READ_ONLY);
2531: statement.setMaxRows(offset + rowsToReturn);
2532: statement.setTimestamp(1, expireDate);
2533: try {
2534: statement.setFetchSize(Math.min(rowsToReturn,
2535: DBUtils.MAX_FETCH_SIZE));
2536: } catch (SQLException sqle) {
2537: //do nothing, postgreSQL does not support this method
2538: }
2539: resultSet = statement.executeQuery();
2540: boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
2541: while (loop) {
2542: MemberBean bean = new MemberBean();
2543: bean.setMemberID(resultSet.getInt("MemberID"));
2544: bean.setMemberName(resultSet.getString("MemberName"));
2545: bean.setMemberCreationDate(resultSet
2546: .getTimestamp("MemberCreationDate"));
2547: bean.setMemberExpireDate(resultSet
2548: .getTimestamp("MemberExpireDate"));
2549: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2550: retValue.add(bean);
2551: if (retValue.size() == rowsToReturn)
2552: break;// Fix the Sybase bug
2553: loop = resultSet.next();
2554: }
2555: return retValue;
2556: } catch (SQLException sqle) {
2557: log.error("Sql Execution Error!", sqle);
2558: throw new DatabaseException(
2559: "Error executing SQL in MemberDAOImplJDBC.getMembers_inExpire_limit.");
2560: } finally {
2561: DBUtils.closeResultSet(resultSet);
2562: DBUtils.resetStatement(statement);
2563: DBUtils.closeStatement(statement);
2564: DBUtils.closeConnection(connection);
2565: }
2566: }
2567:
2568: public boolean isSupportGetNumberOfMembers_inExpire() {
2569: return true;
2570: }
2571:
2572: public int getNumberOfMembers_inExpire(Timestamp expireDate)
2573: throws DatabaseException {
2574:
2575: Connection connection = null;
2576: PreparedStatement statement = null;
2577: ResultSet resultSet = null;
2578: StringBuffer sql = new StringBuffer(512);
2579: sql.append("SELECT Count(*)");
2580: sql.append(" FROM " + TABLE_NAME);
2581: sql
2582: .append(" WHERE MemberExpireDate <= ? OR MemberExpireDate IS NULL");
2583: try {
2584: connection = DBUtils.getConnection();
2585: statement = connection.prepareStatement(sql.toString());
2586: statement.setTimestamp(1, expireDate);
2587: resultSet = statement.executeQuery();
2588: AssertionUtil
2589: .doAssert(resultSet.next(),
2590: "Assertion in MemberDAOImplJDBC.getNumberOfMembers_inExpire.");
2591: return resultSet.getInt(1);
2592: } catch (SQLException sqle) {
2593: log.error("Sql Execution Error!", sqle);
2594: throw new DatabaseException(
2595: "Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers_inExpire.");
2596: } finally {
2597: DBUtils.closeResultSet(resultSet);
2598: DBUtils.closeStatement(statement);
2599: DBUtils.closeConnection(connection);
2600: }
2601: }
2602:
2603: public boolean isSupportUpdateMember_expireDate() {
2604: return true;
2605: }
2606:
2607: public void updateMember_expireDate(int memberID, // primary key
2608: Timestamp expireDate) throws ObjectNotFoundException,
2609: DatabaseException {
2610:
2611: Connection connection = null;
2612: PreparedStatement statement = null;
2613: StringBuffer sql = new StringBuffer(512);
2614: sql
2615: .append("UPDATE " + TABLE_NAME
2616: + " SET MemberExpireDate = ?");
2617: sql.append(" WHERE MemberID = ?");
2618: try {
2619: connection = DBUtils.getConnection();
2620: statement = connection.prepareStatement(sql.toString());
2621:
2622: // // column(s) to update
2623: statement.setTimestamp(1, expireDate);
2624:
2625: // primary key column(s)
2626: statement.setInt(2, memberID);
2627:
2628: if (statement.executeUpdate() != 1) {
2629: throw new ObjectNotFoundException(
2630: "Cannot update table Member where primary key = ("
2631: + memberID + ").");
2632: }
2633: m_dirty = true;
2634: } catch (SQLException sqle) {
2635: log.error("Sql Execution Error!", sqle);
2636: throw new DatabaseException(
2637: "Error executing SQL in MemberDAOImplJDBC.updateMember_expireDate.");
2638: } finally {
2639: DBUtils.closeStatement(statement);
2640: DBUtils.closeConnection(connection);
2641: }
2642: }
2643:
2644: public boolean isSupportGetMembers() {
2645: return true;
2646: }
2647:
2648: public Collection getMembers() throws DatabaseException {
2649:
2650: Connection connection = null;
2651: Statement statement = null;
2652: ResultSet resultSet = null;
2653: Collection retValue = new ArrayList();
2654: StringBuffer sql = new StringBuffer(512);
2655: sql
2656: .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");
2657: sql.append(" FROM " + TABLE_NAME);
2658:
2659: try {
2660: connection = DBUtils.getConnection();
2661: statement = connection.createStatement();
2662: resultSet = statement.executeQuery(sql.toString());
2663: while (resultSet.next()) {
2664: MemberBean bean = new MemberBean();
2665: bean.setMemberID(resultSet.getInt("MemberID"));
2666: bean.setMemberName(resultSet.getString("MemberName"));
2667: bean.setMemberEmail(resultSet.getString("MemberEmail"));
2668: bean.setMemberEmailVisible(resultSet
2669: .getInt("MemberEmailVisible"));
2670: bean.setMemberNameVisible(resultSet
2671: .getInt("MemberNameVisible"));
2672: bean.setMemberViewCount(resultSet
2673: .getInt("MemberViewCount"));
2674: bean.setMemberPostCount(resultSet
2675: .getInt("MemberPostCount"));
2676: bean.setMemberCreationDate(resultSet
2677: .getTimestamp("MemberCreationDate"));
2678: bean.setMemberLastLogon(resultSet
2679: .getTimestamp("MemberLastLogon"));
2680: bean.setMemberOption(resultSet.getInt("MemberOption"));
2681: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2682: bean.setMemberWarnCount(resultSet
2683: .getInt("MemberWarnCount"));
2684: bean.setMemberVoteCount(resultSet
2685: .getInt("MemberVoteCount"));
2686: bean.setMemberVoteTotalStars(resultSet
2687: .getInt("MemberVoteTotalStars"));
2688: bean.setMemberRewardPoints(resultSet
2689: .getInt("MemberRewardPoints"));
2690: bean.setMemberTitle(resultSet.getString("MemberTitle"));
2691: bean.setMemberSignature(resultSet
2692: .getString("MemberSignature"));
2693: bean.setMemberAvatar(resultSet
2694: .getString("MemberAvatar"));
2695: bean.setMemberFirstname(resultSet
2696: .getString("MemberFirstname"));
2697: bean.setMemberLastname(resultSet
2698: .getString("MemberLastname"));
2699: bean.setMemberGender(resultSet.getInt("MemberGender"));
2700: bean.setMemberBirthday(resultSet
2701: .getDate("MemberBirthday"));
2702: bean.setMemberAddress(resultSet
2703: .getString("MemberAddress"));
2704: bean.setMemberCity(resultSet.getString("MemberCity"));
2705: bean.setMemberState(resultSet.getString("MemberState"));
2706: bean.setMemberCountry(resultSet
2707: .getString("MemberCountry"));
2708: bean.setMemberPhone(resultSet.getString("MemberPhone"));
2709: bean.setMemberMobile(resultSet
2710: .getString("MemberMobile"));
2711: bean.setMemberFax(resultSet.getString("MemberFax"));
2712: bean.setMemberCareer(resultSet
2713: .getString("MemberCareer"));
2714: bean.setMemberHomepage(resultSet
2715: .getString("MemberHomepage"));
2716: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
2717: bean.setMemberAol(resultSet.getString("MemberAol"));
2718: bean.setMemberIcq(resultSet.getString("MemberIcq"));
2719: bean.setMemberMsn(resultSet.getString("MemberMsn"));
2720: bean.setMemberCoolLink1(resultSet
2721: .getString("MemberCoolLink1"));
2722: bean.setMemberCoolLink2(resultSet
2723: .getString("MemberCoolLink2"));
2724: bean.setMemberActivateCode(resultSet
2725: .getString("MemberActivateCode"));
2726: retValue.add(bean);
2727: }
2728: return retValue;
2729: } catch (SQLException sqle) {
2730: log.error("Sql Execution Error!", sqle);
2731: throw new DatabaseException(
2732: "Error executing SQL in MemberDAOImplJDBC.getMembers.");
2733: } finally {
2734: DBUtils.closeResultSet(resultSet);
2735: DBUtils.closeStatement(statement);
2736: DBUtils.closeConnection(connection);
2737: }
2738: }
2739:
2740: public boolean isSupportGetMaxMemberID() {
2741: return true;
2742: }
2743:
2744: public int getMaxMemberID() throws DatabaseException {
2745:
2746: Connection connection = null;
2747: PreparedStatement statement = null;
2748: ResultSet resultSet = null;
2749: StringBuffer sql = new StringBuffer(512);
2750: sql.append("SELECT MAX(MemberID)");
2751: sql.append(" FROM " + TABLE_NAME);
2752: try {
2753: connection = DBUtils.getConnection();
2754: statement = connection.prepareStatement(sql.toString());
2755: resultSet = statement.executeQuery();
2756: AssertionUtil.doAssert(resultSet.next(),
2757: "Assertion in MemberDAOImplJDBC.getMaxMemberID.");
2758: return resultSet.getInt(1);
2759: } catch (SQLException sqle) {
2760: log.error("Sql Execution Error!", sqle);
2761: throw new DatabaseException(
2762: "Error executing SQL in MemberDAOImplJDBC.getMaxMemberID.");
2763: } finally {
2764: DBUtils.closeResultSet(resultSet);
2765: DBUtils.closeStatement(statement);
2766: DBUtils.closeConnection(connection);
2767: }
2768: }
2769:
2770: public boolean isSupportGetMembersFromIDRange() {
2771: return true;
2772: }
2773:
2774: public Collection getMembers_fromIDRange(int fromID, int toID)
2775: throws IllegalArgumentException, DatabaseException {
2776:
2777: if (fromID < 0)
2778: throw new IllegalArgumentException(
2779: "The fromID < 0 is not allowed.");
2780: if (toID < fromID)
2781: throw new IllegalArgumentException(
2782: "toID < fromID is not allowed.");
2783:
2784: Connection connection = null;
2785: PreparedStatement statement = null;
2786: ResultSet resultSet = null;
2787: Collection retValue = new ArrayList();
2788: StringBuffer sql = new StringBuffer(512);
2789: sql
2790: .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");
2791: sql.append(" FROM " + TABLE_NAME);
2792: sql.append(" WHERE (MemberID >= ?) AND (MemberID <= ?)");
2793: sql.append(" ORDER BY MemberID ASC ");
2794: try {
2795: connection = DBUtils.getConnection();
2796: statement = connection.prepareStatement(sql.toString());
2797: statement.setInt(1, fromID);
2798: statement.setInt(2, toID);
2799: resultSet = statement.executeQuery();
2800: while (resultSet.next()) {
2801: MemberBean bean = new MemberBean();
2802: bean.setMemberID(resultSet.getInt("MemberID"));
2803: bean.setMemberName(resultSet.getString("MemberName"));
2804: bean.setMemberEmail(resultSet.getString("MemberEmail"));
2805: bean.setMemberEmailVisible(resultSet
2806: .getInt("MemberEmailVisible"));
2807: bean.setMemberNameVisible(resultSet
2808: .getInt("MemberNameVisible"));
2809: bean.setMemberViewCount(resultSet
2810: .getInt("MemberViewCount"));
2811: bean.setMemberPostCount(resultSet
2812: .getInt("MemberPostCount"));
2813: bean.setMemberCreationDate(resultSet
2814: .getTimestamp("MemberCreationDate"));
2815: bean.setMemberLastLogon(resultSet
2816: .getTimestamp("MemberLastLogon"));
2817: bean.setMemberOption(resultSet.getInt("MemberOption"));
2818: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2819: bean.setMemberWarnCount(resultSet
2820: .getInt("MemberWarnCount"));
2821: bean.setMemberVoteCount(resultSet
2822: .getInt("MemberVoteCount"));
2823: bean.setMemberVoteTotalStars(resultSet
2824: .getInt("MemberVoteTotalStars"));
2825: bean.setMemberRewardPoints(resultSet
2826: .getInt("MemberRewardPoints"));
2827: bean.setMemberTitle(resultSet.getString("MemberTitle"));
2828: bean.setMemberSignature(resultSet
2829: .getString("MemberSignature"));
2830: bean.setMemberAvatar(resultSet
2831: .getString("MemberAvatar"));
2832: bean.setMemberFirstname(resultSet
2833: .getString("MemberFirstname"));
2834: bean.setMemberLastname(resultSet
2835: .getString("MemberLastname"));
2836: bean.setMemberGender(resultSet.getInt("MemberGender"));
2837: bean.setMemberBirthday(resultSet
2838: .getDate("MemberBirthday"));
2839: bean.setMemberAddress(resultSet
2840: .getString("MemberAddress"));
2841: bean.setMemberCity(resultSet.getString("MemberCity"));
2842: bean.setMemberState(resultSet.getString("MemberState"));
2843: bean.setMemberCountry(resultSet
2844: .getString("MemberCountry"));
2845: bean.setMemberPhone(resultSet.getString("MemberPhone"));
2846: bean.setMemberMobile(resultSet
2847: .getString("MemberMobile"));
2848: bean.setMemberFax(resultSet.getString("MemberFax"));
2849: bean.setMemberCareer(resultSet
2850: .getString("MemberCareer"));
2851: bean.setMemberHomepage(resultSet
2852: .getString("MemberHomepage"));
2853: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
2854: bean.setMemberAol(resultSet.getString("MemberAol"));
2855: bean.setMemberIcq(resultSet.getString("MemberIcq"));
2856: bean.setMemberMsn(resultSet.getString("MemberMsn"));
2857: bean.setMemberCoolLink1(resultSet
2858: .getString("MemberCoolLink1"));
2859: bean.setMemberCoolLink2(resultSet
2860: .getString("MemberCoolLink2"));
2861: bean.setMemberActivateCode(resultSet
2862: .getString("MemberActivateCode"));
2863: retValue.add(bean);
2864: }
2865: return retValue;
2866: } catch (SQLException sqle) {
2867: log.error("Sql Execution Error!", sqle);
2868: throw new DatabaseException(
2869: "Error executing SQL in MemberDAOImplJDBC.getMembers_fromIDRange.");
2870: } finally {
2871: DBUtils.closeResultSet(resultSet);
2872: DBUtils.closeStatement(statement);
2873: DBUtils.closeConnection(connection);
2874: }
2875: }
2876:
2877: public boolean isSupportGetEnableMembers_inGroup() {
2878: return true;
2879: }
2880:
2881: public Collection getEnableMembers_inGroup(int groupID)
2882: throws DatabaseException {
2883:
2884: Connection connection = null;
2885: PreparedStatement statement = null;
2886: ResultSet resultSet = null;
2887: Collection retValue = new ArrayList();
2888: StringBuffer sql = new StringBuffer(512);
2889:
2890: sql
2891: .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");
2892: sql.append(" FROM " + TABLE_NAME + " m , "
2893: + MemberGroupDAO.TABLE_NAME + " mg");
2894: sql.append(" WHERE (MemberStatus = "
2895: + MemberBean.MEMBER_STATUS_ENABLE
2896: + ") AND (mg.MemberID = m.MemberID) AND GroupID = ?");
2897: try {
2898: connection = DBUtils.getConnection();
2899: statement = connection.prepareStatement(sql.toString());
2900: statement.setInt(1, groupID);
2901: resultSet = statement.executeQuery();
2902: while (resultSet.next()) {
2903: MemberBean bean = new MemberBean();
2904: bean.setMemberID(resultSet.getInt("MemberID"));
2905: bean.setMemberName(resultSet.getString("MemberName"));
2906: bean.setMemberEmail(resultSet.getString("MemberEmail"));
2907: bean.setMemberEmailVisible(resultSet
2908: .getInt("MemberEmailVisible"));
2909: bean.setMemberNameVisible(resultSet
2910: .getInt("MemberNameVisible"));
2911: bean.setMemberViewCount(resultSet
2912: .getInt("MemberViewCount"));
2913: bean.setMemberPostCount(resultSet
2914: .getInt("MemberPostCount"));
2915: bean.setMemberCreationDate(resultSet
2916: .getTimestamp("MemberCreationDate"));
2917: bean.setMemberLastLogon(resultSet
2918: .getTimestamp("MemberLastLogon"));
2919: bean.setMemberOption(resultSet.getInt("MemberOption"));
2920: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
2921: bean.setMemberWarnCount(resultSet
2922: .getInt("MemberWarnCount"));
2923: bean.setMemberVoteCount(resultSet
2924: .getInt("MemberVoteCount"));
2925: bean.setMemberVoteTotalStars(resultSet
2926: .getInt("MemberVoteTotalStars"));
2927: bean.setMemberRewardPoints(resultSet
2928: .getInt("MemberRewardPoints"));
2929: bean.setMemberTitle(resultSet.getString("MemberTitle"));
2930: bean.setMemberSignature(resultSet
2931: .getString("MemberSignature"));
2932: bean.setMemberAvatar(resultSet
2933: .getString("MemberAvatar"));
2934: bean.setMemberFirstname(resultSet
2935: .getString("MemberFirstname"));
2936: bean.setMemberLastname(resultSet
2937: .getString("MemberLastname"));
2938: bean.setMemberGender(resultSet.getInt("MemberGender"));
2939: bean.setMemberBirthday(resultSet
2940: .getDate("MemberBirthday"));
2941: bean.setMemberAddress(resultSet
2942: .getString("MemberAddress"));
2943: bean.setMemberCity(resultSet.getString("MemberCity"));
2944: bean.setMemberState(resultSet.getString("MemberState"));
2945: bean.setMemberCountry(resultSet
2946: .getString("MemberCountry"));
2947: bean.setMemberPhone(resultSet.getString("MemberPhone"));
2948: bean.setMemberMobile(resultSet
2949: .getString("MemberMobile"));
2950: bean.setMemberFax(resultSet.getString("MemberFax"));
2951: bean.setMemberCareer(resultSet
2952: .getString("MemberCareer"));
2953: bean.setMemberHomepage(resultSet
2954: .getString("MemberHomepage"));
2955: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
2956: bean.setMemberAol(resultSet.getString("MemberAol"));
2957: bean.setMemberIcq(resultSet.getString("MemberIcq"));
2958: bean.setMemberMsn(resultSet.getString("MemberMsn"));
2959: bean.setMemberCoolLink1(resultSet
2960: .getString("MemberCoolLink1"));
2961: bean.setMemberCoolLink2(resultSet
2962: .getString("MemberCoolLink2"));
2963: bean.setMemberActivateCode(resultSet
2964: .getString("MemberActivateCode"));
2965: retValue.add(bean);
2966: }//while
2967: return retValue;
2968: } catch (SQLException sqle) {
2969: log.error("Sql Execution Error!", sqle);
2970: throw new DatabaseException(
2971: "Error executing SQL in MemberDAOImplJDBC.getMembers_inGroup.");
2972: } finally {
2973: DBUtils.closeResultSet(resultSet);
2974: DBUtils.resetStatement(statement);
2975: DBUtils.closeStatement(statement);
2976: DBUtils.closeConnection(connection);
2977: }
2978: }
2979:
2980: public boolean isSupportGetForumsAuthorizedMembers() {
2981: return true;
2982: }
2983:
2984: public Collection getForumsAuthorizedMembers()
2985: throws DatabaseException {
2986:
2987: Connection connection = null;
2988: PreparedStatement statement = null;
2989: ResultSet resultSet = null;
2990: Collection retValue = new ArrayList();
2991: StringBuffer sql = new StringBuffer(512);
2992:
2993: sql
2994: .append("SELECT DISTINCT m.MemberID, MemberName, MemberEmail, MemberLastname, MemberFirstname, MemberCreationDate");
2995: sql.append(" FROM " + TABLE_NAME + " m , "
2996: + MemberForumDAO.TABLE_NAME + " mf");
2997: sql.append(" WHERE (m.MemberID = mf.MemberID) ");
2998: sql.append(" ORDER BY m.MemberID ASC");
2999: try {
3000: connection = DBUtils.getConnection();
3001: statement = connection.prepareStatement(sql.toString());
3002: resultSet = statement.executeQuery();
3003: while (resultSet.next()) {
3004: MemberBean bean = new MemberBean();
3005: bean.setMemberID(resultSet.getInt("MemberID"));
3006: bean.setMemberName(resultSet.getString("MemberName"));
3007: bean.setMemberEmail(resultSet.getString("MemberEmail"));
3008: bean.setMemberLastname(resultSet
3009: .getString("MemberLastname"));
3010: bean.setMemberFirstname(resultSet
3011: .getString("MemberFirstname"));
3012: bean.setMemberCreationDate(resultSet
3013: .getTimestamp("MemberCreationDate"));
3014: retValue.add(bean);
3015: }
3016: return retValue;
3017: } catch (SQLException sqle) {
3018: log.error("Sql Execution Error!", sqle);
3019: throw new DatabaseException(
3020: "Error executing SQL in MemberDAOImplJDBC.getForumsAuthorizedMembers");
3021: } finally {
3022: DBUtils.closeResultSet(resultSet);
3023: DBUtils.resetStatement(statement);
3024: DBUtils.closeStatement(statement);
3025: DBUtils.closeConnection(connection);
3026: }
3027: }
3028:
3029: public boolean isSupportGetAuthorizedMembers() {
3030: return true;
3031: }
3032:
3033: public Collection getAuthorizedMembers() throws DatabaseException {
3034:
3035: Connection connection = null;
3036: PreparedStatement statement = null;
3037: ResultSet resultSet = null;
3038: Collection retValue = new ArrayList();
3039: StringBuffer sql = new StringBuffer(512);
3040:
3041: sql
3042: .append("SELECT DISTINCT m.MemberID, MemberName, MemberEmail, MemberLastname, MemberFirstname, MemberCreationDate");
3043: sql.append(" FROM " + TABLE_NAME + " m , "
3044: + MemberPermissionDAO.TABLE_NAME + " mp");
3045: sql.append(" WHERE (m.MemberID = mp.MemberID) ");
3046: sql.append(" ORDER BY m.MemberID ASC");
3047: try {
3048: connection = DBUtils.getConnection();
3049: statement = connection.prepareStatement(sql.toString());
3050: resultSet = statement.executeQuery();
3051: while (resultSet.next()) {
3052: MemberBean bean = new MemberBean();
3053: bean.setMemberID(resultSet.getInt("MemberID"));
3054: bean.setMemberName(resultSet.getString("MemberName"));
3055: bean.setMemberEmail(resultSet.getString("MemberEmail"));
3056: bean.setMemberLastname(resultSet
3057: .getString("MemberLastname"));
3058: bean.setMemberFirstname(resultSet
3059: .getString("MemberFirstname"));
3060: bean.setMemberCreationDate(resultSet
3061: .getTimestamp("MemberCreationDate"));
3062: retValue.add(bean);
3063: }
3064: return retValue;
3065: } catch (SQLException sqle) {
3066: log.error("Sql Execution Error!", sqle);
3067: throw new DatabaseException(
3068: "Error executing SQL in MemberDAOImplJDBC.getAuthorizedMembers.");
3069: } finally {
3070: DBUtils.closeResultSet(resultSet);
3071: DBUtils.resetStatement(statement);
3072: DBUtils.closeStatement(statement);
3073: DBUtils.closeConnection(connection);
3074: }
3075: }
3076:
3077: public boolean isSupportGetNonActivatedNoPostMembers() {
3078: return true;
3079: }
3080:
3081: public Collection getNonActivatedNoPostMembers(Timestamp before)
3082: throws DatabaseException {
3083:
3084: Connection connection = null;
3085: PreparedStatement statement = null;
3086: ResultSet resultSet = null;
3087: Collection retValue = new ArrayList();
3088: StringBuffer sql = new StringBuffer(512);
3089:
3090: sql
3091: .append("SELECT MemberID, MemberName, MemberFirstName, MemberLastName, MemberEmail, MemberPostCount, MemberCreationDate, MemberStatus");
3092: sql.append(" FROM " + TABLE_NAME);
3093: sql
3094: .append(" WHERE ((MemberActivateCode <> 'activated') OR (MemberActivateCode IS NULL))");// not activated
3095: sql.append(" AND (MemberID <> 0) AND (MemberID <> 1) ");// not a guest, not Admin
3096: sql.append(" AND (MemberPostCount = 0) ");// with no posts
3097: sql.append(" AND (MemberCreationDate < ?) ");
3098: try {
3099: connection = DBUtils.getConnection();
3100: statement = connection.prepareStatement(sql.toString());
3101: statement.setTimestamp(1, before);
3102: resultSet = statement.executeQuery();
3103: while (resultSet.next()) {
3104: MemberBean bean = new MemberBean();
3105: bean.setMemberID(resultSet.getInt("MemberID"));
3106: bean.setMemberName(resultSet.getString("MemberName"));
3107: bean.setMemberFirstname(resultSet
3108: .getString("MemberFirstName"));
3109: bean.setMemberLastname(resultSet
3110: .getString("MemberLastName"));
3111: bean.setMemberEmail(resultSet.getString("MemberEmail"));
3112: bean.setMemberPostCount(resultSet
3113: .getInt("MemberPostCount"));
3114: bean.setMemberCreationDate(resultSet
3115: .getTimestamp("MemberCreationDate"));
3116: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
3117: retValue.add(bean);
3118: }
3119: return retValue;
3120: } catch (SQLException sqle) {
3121: log.error("Sql Execution Error!", sqle);
3122: throw new DatabaseException(
3123: "Error executing SQL in MemberDAOImplJDBC.getNonActivatedNoPostMembers");
3124: } finally {
3125: DBUtils.closeResultSet(resultSet);
3126: DBUtils.resetStatement(statement);
3127: DBUtils.closeStatement(statement);
3128: DBUtils.closeConnection(connection);
3129: }
3130: }
3131:
3132: }// end of class MemberDAOImplJDBC
|