001: /*
002: * $Header: /cvsroot/mvnforum/mvnforum/contrib/phpbb2mvnforum/src/org/mvnforum/phpbb2mvnforum/db/jdbc/MemberDAOImplJDBC.java,v 1.7 2007/12/18 04:58:44 minhnn Exp $
003: * $Author: minhnn $
004: * $Revision: 1.7 $
005: * $Date: 2007/12/18 04:58:44 $
006: *
007: * ====================================================================
008: *
009: * Copyright (C) 2002-2007 by MyVietnam.net
010: *
011: * All copyright notices regarding mvnForum MUST remain
012: * intact in the scripts and in the outputted HTML.
013: * The "powered by" text/logo with a link back to
014: * http://www.mvnForum.com and http://www.MyVietnam.net in
015: * the footer of the pages MUST remain visible when the pages
016: * are viewed on the internet or intranet.
017: *
018: * This program is free software; you can redistribute it and/or modify
019: * it under the terms of the GNU General Public License as published by
020: * the Free Software Foundation; either version 2 of the License, or
021: * any later version.
022: *
023: * This program is distributed in the hope that it will be useful,
024: * but WITHOUT ANY WARRANTY; without even the implied warranty of
025: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
026: * GNU General Public License for more details.
027: *
028: * You should have received a copy of the GNU General Public License
029: * along with this program; if not, write to the Free Software
030: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
031: *
032: * Support can be obtained from support forums at:
033: * http://www.mvnForum.com/mvnforum/index
034: *
035: * Correspondence and Marketing Questions can be sent to:
036: * info at MyVietnam net
037: *
038: * @author:
039: */
040: package org.mvnforum.phpbb2mvnforum.db.jdbc;
041:
042: import java.sql.Connection;
043: import java.sql.Date;
044: import java.sql.PreparedStatement;
045: import java.sql.ResultSet;
046: import java.sql.SQLException;
047: import java.sql.Timestamp;
048:
049: import net.myvietnam.mvncore.exception.CreateException;
050: import net.myvietnam.mvncore.exception.DatabaseException;
051: import net.myvietnam.mvncore.exception.DuplicateKeyException;
052: import net.myvietnam.mvncore.exception.ObjectNotFoundException;
053:
054: import org.mvnforum.phpbb2mvnforum.db.MemberBean;
055: import org.mvnforum.phpbb2mvnforum.db.MemberDAO;
056: import org.mvnforum.util.DBUtils;
057:
058: public class MemberDAOImplJDBC implements MemberDAO {
059:
060: public void findByPrimaryKey(int memberID)
061: throws ObjectNotFoundException, DatabaseException {
062:
063: Connection connection = null;
064: PreparedStatement statement = null;
065: ResultSet resultSet = null;
066:
067: StringBuffer sql = new StringBuffer(512);
068: sql.append("SELECT MemberID");
069: sql.append(" FROM " + TABLE_NAME);
070: sql.append(" WHERE MemberID = ?");
071:
072: try {
073: connection = DBUtils.getMvnConnection();
074: statement = connection.prepareStatement(sql.toString());
075: statement.setInt(1, memberID);
076: resultSet = statement.executeQuery();
077: if (!resultSet.next()) {
078: throw new ObjectNotFoundException(
079: "Cannot find the primary key (" + memberID
080: + ") in table 'Member'.");
081: }
082: } catch (SQLException sqle) {
083: throw new DatabaseException(
084: "Error executing SQL in MemberDAOImplJDBC.findByPrimaryKey.");
085: } finally {
086: DBUtils.closeResultSet(resultSet);
087: DBUtils.closeStatement(statement);
088: DBUtils.closeConnection(connection);
089: }
090: }
091:
092: public void findByPrimaryKey2(int memberID, String memberName)
093: throws ObjectNotFoundException, DatabaseException {
094: // TODO Auto-generated method stub
095: }
096:
097: public void findByAlternateKey_MemberEmail(String memberEmail)
098: throws ObjectNotFoundException, DatabaseException {
099:
100: Connection connection = null;
101: PreparedStatement statement = null;
102: ResultSet resultSet = null;
103:
104: StringBuffer sql = new StringBuffer(512);
105: sql.append("SELECT MemberEmail");
106: sql.append(" FROM " + TABLE_NAME);
107: sql.append(" WHERE MemberEmail = ?");
108:
109: try {
110: connection = DBUtils.getMvnConnection();
111: statement = connection.prepareStatement(sql.toString());
112: statement.setString(1, memberEmail);
113: resultSet = statement.executeQuery();
114:
115: if (!resultSet.next()) {
116: throw new ObjectNotFoundException(
117: "Cannot find the alternate key [MemberEmail] ("
118: + memberEmail + ") in table 'Member'.");
119: }
120: } catch (SQLException sqle) {
121: throw new DatabaseException(
122: "Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberEmail.");
123: } finally {
124: DBUtils.closeResultSet(resultSet);
125: DBUtils.closeStatement(statement);
126: DBUtils.closeConnection(connection);
127: }
128: }
129:
130: public void findByAlternateKey_MemberName(String memberName)
131: throws ObjectNotFoundException, DatabaseException {
132:
133: Connection connection = null;
134: PreparedStatement statement = null;
135: ResultSet resultSet = null;
136:
137: StringBuffer sql = new StringBuffer(512);
138: sql.append("SELECT MemberName");
139: sql.append(" FROM " + TABLE_NAME);
140: sql.append(" WHERE MemberName = ?");
141:
142: try {
143: connection = DBUtils.getMvnConnection();
144: statement = connection.prepareStatement(sql.toString());
145: statement.setString(1, memberName);
146: resultSet = statement.executeQuery();
147: if (!resultSet.next()) {
148: throw new ObjectNotFoundException(
149: "Cannot find the alternate key [MemberName] ("
150: + memberName + ") in table 'Member'.");
151: }
152: } catch (SQLException sqle) {
153: throw new DatabaseException(
154: "Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberName.");
155: } finally {
156: DBUtils.closeResultSet(resultSet);
157: DBUtils.closeStatement(statement);
158: DBUtils.closeConnection(connection);
159: }
160: }
161:
162: public void deleteByPrimaryKey(int memberID)
163: throws DatabaseException {
164: // TODO Auto-generated method stub
165:
166: }
167:
168: /*
169: * Included columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
170: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
171: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon,
172: * MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount,
173: * MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars,
174: * MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar,
175: * MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender,
176: * MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry,
177: * MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage,
178: * MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1,
179: * MemberCoolLink2
180: * Excluded columns:
181: */
182: public void create(int memberID, String memberName,
183: String memberPassword, String memberFirstEmail,
184: String memberEmail, int memberEmailVisible,
185: int memberNameVisible, String memberFirstIP,
186: String memberLastIP, int memberViewCount,
187: int memberPostCount, Timestamp memberCreationDate,
188: Timestamp memberModifiedDate, Timestamp memberExpireDate,
189: Timestamp memberLastLogon, int memberOption,
190: int memberStatus, String memberActivateCode,
191: String memberTempPassword, int memberMessageCount,
192: int memberMessageOption, int memberPostsPerPage,
193: int memberWarnCount, int memberVoteCount,
194: int memberVoteTotalStars, int memberRewardPoints,
195: String memberTitle, double memberTimeZone,
196: String memberSignature, String memberAvatar,
197: String memberSkin, String memberLanguage,
198: String memberFirstname, String memberLastname,
199: int memberGender, Date memberBirthday,
200: String memberAddress, String memberCity,
201: String memberState, String memberCountry,
202: String memberPhone, String memberMobile, String memberFax,
203: String memberCareer, String memberHomepage,
204: String memberYahoo, String memberAol, String memberIcq,
205: String memberMsn, String memberCoolLink1,
206: String memberCoolLink2) throws CreateException,
207: DatabaseException, DuplicateKeyException {
208:
209: // @todo: comment this try-catch block if the needed columns dont have attribute 'include'
210: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
211: // However, if primary key is a auto_increament column, then you can safely delete this block
212: try {
213: //Check if primary key already exists
214: findByPrimaryKey(memberID);
215: //If so, then we have to throw an exception
216: throw new DuplicateKeyException(
217: "Primary key already exists. Cannot create new member with the same [MemberID] ("
218: + memberID + ").");
219: } catch (ObjectNotFoundException e) {
220: //Otherwise we can go ahead
221: }
222:
223: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
224: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
225: try {
226: //Check if alternate key already exists
227: findByAlternateKey_MemberEmail(memberEmail);
228: //If so, then we have to throw an exception
229: throw new DuplicateKeyException(
230: "Alternate key already exists. Cannot create new member with the same [MemberEmail] ("
231: + memberEmail + ").");
232: } catch (ObjectNotFoundException e) {
233: //Otherwise we can go ahead
234: }
235:
236: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
237: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
238: try {
239: //Check if alternate key already exists
240: findByAlternateKey_MemberName(memberName);
241: //If so, then we have to throw an exception
242: throw new DuplicateKeyException(
243: "Alternate key already exists. Cannot create new member with the same [MemberName] ("
244: + memberName + ").");
245: } catch (ObjectNotFoundException e) {
246: //Otherwise we can go ahead
247: }
248:
249: Connection connection = null;
250: PreparedStatement statement = null;
251: StringBuffer sql = new StringBuffer(512);
252: sql
253: .append("INSERT INTO "
254: + TABLE_NAME
255: + " (MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2)");
256: sql
257: .append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
258: try {
259: connection = DBUtils.getMvnConnection();
260: statement = connection.prepareStatement(sql.toString());
261:
262: statement.setInt(1, memberID);
263: statement.setString(2, memberName);
264: statement.setString(3, memberPassword);
265: statement.setString(4, memberFirstEmail);
266: statement.setString(5, memberEmail);
267: statement.setInt(6, memberEmailVisible);
268: statement.setInt(7, memberNameVisible);
269: statement.setString(8, memberFirstIP);
270: statement.setString(9, memberLastIP);
271: statement.setInt(10, memberViewCount);
272: statement.setInt(11, memberPostCount);
273: statement.setTimestamp(12, memberCreationDate);
274: statement.setTimestamp(13, memberModifiedDate);
275: statement.setTimestamp(14, memberExpireDate);
276: statement.setTimestamp(15, memberLastLogon);
277: statement.setInt(16, memberOption);
278: statement.setInt(17, memberStatus);
279: statement.setString(18, memberActivateCode);
280: statement.setString(19, memberTempPassword);
281: statement.setInt(20, memberMessageCount);
282: statement.setInt(21, memberMessageOption);
283: statement.setInt(22, memberPostsPerPage);
284: statement.setInt(23, memberWarnCount);
285: statement.setInt(24, memberVoteCount);
286: statement.setInt(25, memberVoteTotalStars);
287: statement.setInt(26, memberRewardPoints);
288: statement.setString(27, memberTitle);
289: statement.setDouble(28, memberTimeZone);
290: statement.setString(29, memberSignature);
291: statement.setString(30, memberAvatar);
292: statement.setString(31, memberSkin);
293: statement.setString(32, memberLanguage);
294: statement.setString(33, memberFirstname);
295: statement.setString(34, memberLastname);
296: statement.setInt(35, memberGender);
297: statement.setDate(36, memberBirthday);
298: statement.setString(37, memberAddress);
299: statement.setString(38, memberCity);
300: statement.setString(39, memberState);
301: statement.setString(40, memberCountry);
302: statement.setString(41, memberPhone);
303: statement.setString(42, memberMobile);
304: statement.setString(43, memberFax);
305: statement.setString(44, memberCareer);
306: statement.setString(45, memberHomepage);
307: statement.setString(46, memberYahoo);
308: statement.setString(47, memberAol);
309: statement.setString(48, memberIcq);
310: statement.setString(49, memberMsn);
311: statement.setString(50, memberCoolLink1);
312: statement.setString(51, memberCoolLink2);
313:
314: if (statement.executeUpdate() != 1) {
315: throw new CreateException(
316: "Error adding a row into table 'member'.");
317: }
318: } catch (SQLException sqle) {
319: throw new DatabaseException(
320: "Error executing SQL in memberDAOImplJDBC.create.");
321: } finally {
322: DBUtils.closeStatement(statement);
323: DBUtils.closeConnection(connection);
324: }
325: }
326:
327: /*
328: * Included columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
329: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
330: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon,
331: * MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount,
332: * MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars,
333: * MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar,
334: * MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender,
335: * MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry,
336: * MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage,
337: * MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1,
338: * MemberCoolLink2
339: * Excluded columns:
340: */
341: public MemberBean getBean(int memberID)
342: throws ObjectNotFoundException, DatabaseException {
343:
344: Connection connection = null;
345: PreparedStatement statement = null;
346: ResultSet resultSet = null;
347: StringBuffer sql = new StringBuffer(512);
348: sql
349: .append("SELECT MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2");
350: sql.append(" FROM " + TABLE_NAME);
351: sql.append(" WHERE MemberID = ?");
352: try {
353: connection = DBUtils.getMvnConnection();
354: statement = connection.prepareStatement(sql.toString());
355: statement.setInt(1, memberID);
356: resultSet = statement.executeQuery();
357: if (!resultSet.next()) {
358: throw new ObjectNotFoundException(
359: "Cannot find the row in table Member where primary key = ("
360: + memberID + ").");
361: }
362:
363: MemberBean bean = new MemberBean();
364: // @todo: uncomment the following line(s) as needed
365: //bean.setMemberID(memberID);
366: bean.setMemberID(resultSet.getInt("MemberID"));
367: bean.setMemberName(resultSet.getString("MemberName"));
368: //bean.setMemberPassword(resultSet.getString("MemberPassword"));
369: bean.setMemberFirstEmail(resultSet
370: .getString("MemberFirstEmail"));
371: bean.setMemberEmail(resultSet.getString("MemberEmail"));
372: bean.setMemberEmailVisible(resultSet
373: .getInt("MemberEmailVisible"));
374: bean.setMemberNameVisible(resultSet
375: .getInt("MemberNameVisible"));
376: bean.setMemberFirstIP(resultSet.getString("MemberFirstIP"));
377: bean.setMemberLastIP(resultSet.getString("MemberLastIP"));
378: bean
379: .setMemberViewCount(resultSet
380: .getInt("MemberViewCount"));
381: bean
382: .setMemberPostCount(resultSet
383: .getInt("MemberPostCount"));
384: bean.setMemberCreationDate(resultSet
385: .getTimestamp("MemberCreationDate"));
386: bean.setMemberModifiedDate(resultSet
387: .getTimestamp("MemberModifiedDate"));
388: bean.setMemberExpireDate(resultSet
389: .getTimestamp("MemberExpireDate"));
390: bean.setMemberLastLogon(resultSet
391: .getTimestamp("MemberLastLogon"));
392: bean.setMemberOption(resultSet.getInt("MemberOption"));
393: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
394: bean.setMemberActivateCode(resultSet
395: .getString("MemberActivateCode"));
396: //bean.setMemberTempPassword(resultSet.getString("MemberTempPassword"));
397: bean.setMemberMessageCount(resultSet
398: .getInt("MemberMessageCount"));
399: bean.setMemberMessageOption(resultSet
400: .getInt("MemberMessageOption"));
401: bean.setMemberPostsPerPage(resultSet
402: .getInt("MemberPostsPerPage"));
403: bean
404: .setMemberWarnCount(resultSet
405: .getInt("MemberWarnCount"));
406: bean
407: .setMemberVoteCount(resultSet
408: .getInt("MemberVoteCount"));
409: bean.setMemberVoteTotalStars(resultSet
410: .getInt("MemberVoteTotalStars"));
411: bean.setMemberRewardPoints(resultSet
412: .getInt("MemberRewardPoints"));
413: bean.setMemberTitle(resultSet.getString("MemberTitle"));
414: bean.setMemberTimeZone(resultSet
415: .getDouble("MemberTimeZone"));
416: bean.setMemberSignature(resultSet
417: .getString("MemberSignature"));
418: bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
419: bean.setMemberSkin(resultSet.getString("MemberSkin"));
420: bean.setMemberLanguage(resultSet
421: .getString("MemberLanguage"));
422: bean.setMemberFirstname(resultSet
423: .getString("MemberFirstname"));
424: bean.setMemberLastname(resultSet
425: .getString("MemberLastname"));
426: bean.setMemberGender(resultSet.getInt("MemberGender"));
427: bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
428: bean.setMemberAddress(resultSet.getString("MemberAddress"));
429: bean.setMemberCity(resultSet.getString("MemberCity"));
430: bean.setMemberState(resultSet.getString("MemberState"));
431: bean.setMemberCountry(resultSet.getString("MemberCountry"));
432: bean.setMemberPhone(resultSet.getString("MemberPhone"));
433: bean.setMemberMobile(resultSet.getString("MemberMobile"));
434: bean.setMemberFax(resultSet.getString("MemberFax"));
435: bean.setMemberCareer(resultSet.getString("MemberCareer"));
436: bean.setMemberHomepage(resultSet
437: .getString("MemberHomepage"));
438: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
439: bean.setMemberAol(resultSet.getString("MemberAol"));
440: bean.setMemberIcq(resultSet.getString("MemberIcq"));
441: bean.setMemberMsn(resultSet.getString("MemberMsn"));
442: bean.setMemberCoolLink1(resultSet
443: .getString("MemberCoolLink1"));
444: bean.setMemberCoolLink2(resultSet
445: .getString("MemberCoolLink2"));
446: return bean;
447: } catch (SQLException sqle) {
448: throw new DatabaseException(
449: "Error executing SQL in MemberDAOImplJDBC.getBean(pk).");
450: } finally {
451: DBUtils.closeResultSet(resultSet);
452: DBUtils.closeStatement(statement);
453: DBUtils.closeConnection(connection);
454: }
455: }
456:
457: /*
458: * Included columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
459: * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
460: * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon,
461: * MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount,
462: * MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars,
463: * MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar,
464: * MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender,
465: * MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry,
466: * MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage,
467: * MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1,
468: * MemberCoolLink2
469: * Excluded columns:
470: */
471: public void update(
472: int memberID, // primary key
473: String memberName, String memberPassword,
474: String memberFirstEmail, String memberEmail,
475: int memberEmailVisible, int memberNameVisible,
476: String memberFirstIP, String memberLastIP,
477: int memberViewCount, int memberPostCount,
478: Timestamp memberCreationDate, Timestamp memberModifiedDate,
479: Timestamp memberExpireDate, Timestamp memberLastLogon,
480: int memberOption, int memberStatus,
481: String memberActivateCode, String memberTempPassword,
482: int memberMessageCount, int memberMessageOption,
483: int memberPostsPerPage, int memberWarnCount,
484: int memberVoteCount, int memberVoteTotalStars,
485: int memberRewardPoints, String memberTitle,
486: double memberTimeZone, String memberSignature,
487: String memberAvatar, String memberSkin,
488: String memberLanguage, String memberFirstname,
489: String memberLastname, int memberGender,
490: Date memberBirthday, String memberAddress,
491: String memberCity, String memberState,
492: String memberCountry, String memberPhone,
493: String memberMobile, String memberFax, String memberCareer,
494: String memberHomepage, String memberYahoo,
495: String memberAol, String memberIcq, String memberMsn,
496: String memberCoolLink1, String memberCoolLink2)
497: throws ObjectNotFoundException,
498: DatabaseException/*, DuplicateKeyException*/,
499: DuplicateKeyException {
500:
501: MemberBean bean = getBean(memberID); // @todo: comment or delete this line if no alternate key are included
502:
503: if (!memberEmail.equals(bean.getMemberEmail())) {
504: // member tries to change its alternate key [MemberEmail], so we must check if it already exist
505: try {
506: findByAlternateKey_MemberEmail(memberEmail);
507: throw new DuplicateKeyException(
508: "Alternate key [MemberEmail] ("
509: + memberEmail
510: + ") already exists. Cannot update member.");
511: } catch (ObjectNotFoundException e) {
512: //Otherwise we can go ahead
513: }
514: }
515:
516: Connection connection = null;
517: PreparedStatement statement = null;
518: StringBuffer sql = new StringBuffer(512);
519: sql
520: .append("UPDATE "
521: + TABLE_NAME
522: + " SET MemberName = ?, MemberPassword = ?, MemberFirstEmail = ?, MemberEmail = ?, MemberEmailVisible = ?, MemberNameVisible = ?, MemberFirstIP = ?, MemberLastIP = ?, MemberViewCount = ?, MemberPostCount = ?, MemberCreationDate = ?, MemberModifiedDate = ?, MemberExpireDate = ?, MemberLastLogon = ?, MemberOption = ?, MemberStatus = ?, MemberActivateCode = ?, MemberTempPassword = ?, MemberMessageCount = ?, MemberMessageOption = ?, MemberPostsPerPage = ?, MemberWarnCount = ?, MemberVoteCount = ?, MemberVoteTotalStars = ?, MemberRewardPoints = ?, MemberTitle = ?, MemberTimeZone = ?, MemberSignature = ?, MemberAvatar = ?, MemberSkin = ?, MemberLanguage = ?, MemberFirstname = ?, MemberLastname = ?, MemberGender = ?, MemberBirthday = ?, MemberAddress = ?, MemberCity = ?, MemberState = ?, MemberCountry = ?, MemberPhone = ?, MemberMobile = ?, MemberFax = ?, MemberCareer = ?, MemberHomepage = ?, MemberYahoo = ?, MemberAol = ?, MemberIcq = ?, MemberMsn = ?, MemberCoolLink1 = ?, MemberCoolLink2 = ?");
523: sql.append(" WHERE MemberID = ?");
524: try {
525: connection = DBUtils.getMvnConnection();
526: statement = connection.prepareStatement(sql.toString());
527:
528: // // column(s) to update
529: statement.setString(1, memberName);
530: statement.setString(2, memberPassword);
531: statement.setString(3, memberFirstEmail);
532: statement.setString(4, memberEmail);
533: statement.setInt(5, memberEmailVisible);
534: statement.setInt(6, memberNameVisible);
535: statement.setString(7, memberFirstIP);
536: statement.setString(8, memberLastIP);
537: statement.setInt(9, memberViewCount);
538: statement.setInt(10, memberPostCount);
539: statement.setTimestamp(11, memberCreationDate);
540: statement.setTimestamp(12, memberModifiedDate);
541: statement.setTimestamp(13, memberExpireDate);
542: statement.setTimestamp(14, memberLastLogon);
543: statement.setInt(15, memberOption);
544: statement.setInt(16, memberStatus);
545: statement.setString(17, memberActivateCode);
546: statement.setString(18, memberTempPassword);
547: statement.setInt(19, memberMessageCount);
548: statement.setInt(20, memberMessageOption);
549: statement.setInt(21, memberPostsPerPage);
550: statement.setInt(22, memberWarnCount);
551: statement.setInt(23, memberVoteCount);
552: statement.setInt(24, memberVoteTotalStars);
553: statement.setInt(25, memberRewardPoints);
554: statement.setString(26, memberTitle);
555: statement.setDouble(27, memberTimeZone);
556: statement.setString(28, memberSignature);
557: statement.setString(29, memberAvatar);
558: statement.setString(30, memberSkin);
559: statement.setString(31, memberLanguage);
560: statement.setString(32, memberFirstname);
561: statement.setString(33, memberLastname);
562: statement.setInt(34, memberGender);
563: statement.setDate(35, memberBirthday);
564: statement.setString(36, memberAddress);
565: statement.setString(37, memberCity);
566: statement.setString(38, memberState);
567: statement.setString(39, memberCountry);
568: statement.setString(40, memberPhone);
569: statement.setString(41, memberMobile);
570: statement.setString(42, memberFax);
571: statement.setString(43, memberCareer);
572: statement.setString(44, memberHomepage);
573: statement.setString(45, memberYahoo);
574: statement.setString(46, memberAol);
575: statement.setString(47, memberIcq);
576: statement.setString(48, memberMsn);
577: statement.setString(49, memberCoolLink1);
578: statement.setString(50, memberCoolLink2);
579:
580: // primary key column(s)
581: statement.setInt(51, memberID);
582:
583: if (statement.executeUpdate() != 1) {
584: throw new ObjectNotFoundException(
585: "Cannot update table member where primary key = ("
586: + memberID + ").");
587: }
588: } catch (SQLException sqle) {
589: throw new DatabaseException(
590: "Error executing SQL in memberDAOImplJDBC.update.");
591: } finally {
592: DBUtils.closeStatement(statement);
593: DBUtils.closeConnection(connection);
594: }
595: }
596:
597: public String getMemberNameFromMemberID(int memberID)
598: throws ObjectNotFoundException, DatabaseException {
599: // TODO Auto-generated method stub
600: Connection connection = null;
601: PreparedStatement statement = null;
602: ResultSet resultSet = null;
603: StringBuffer sql = new StringBuffer(512);
604: sql.append("SELECT MemberName");
605: sql.append(" FROM " + TABLE_NAME);
606: sql.append(" WHERE MemberID = ?");
607: try {
608: connection = DBUtils.getMvnConnection();
609: statement = connection.prepareStatement(sql.toString());
610: statement.setInt(1, memberID);
611: resultSet = statement.executeQuery();
612: if (!resultSet.next()) {
613: throw new ObjectNotFoundException(
614: "Cannot find the row in table member where primary key = ("
615: + memberID + ").");
616: }
617: return resultSet.getString("MemberName");
618: } catch (SQLException sqle) {
619: throw new DatabaseException(
620: "Error executing SQL in memberDAOImplJDBC.getBean(pk).");
621: } finally {
622: DBUtils.closeResultSet(resultSet);
623: DBUtils.closeStatement(statement);
624: DBUtils.closeConnection(connection);
625: }
626: }
627:
628: public MemberBean getMemberFromMemberName(String memberName)
629: throws ObjectNotFoundException, DatabaseException {
630: // TODO Auto-generated method stub
631: Connection connection = null;
632: PreparedStatement statement = null;
633: ResultSet resultSet = null;
634: StringBuffer sql = new StringBuffer(512);
635: sql
636: .append("SELECT MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2");
637: sql.append(" FROM " + TABLE_NAME);
638: sql.append(" WHERE MemberName = ?");
639: try {
640: connection = DBUtils.getMvnConnection();
641: statement = connection.prepareStatement(sql.toString());
642: statement.setString(1, memberName);
643: resultSet = statement.executeQuery();
644: if (!resultSet.next()) {
645: throw new ObjectNotFoundException(
646: "Cannot find the row in table member where primary key = ("
647: + memberName + ").");
648: }
649:
650: MemberBean bean = new MemberBean();
651: bean.setMemberID(resultSet.getInt("MemberID"));
652: bean.setMemberName(resultSet.getString("MemberName"));
653: bean.setMemberPassword(resultSet
654: .getString("MemberPassword"));
655: bean.setMemberFirstEmail(resultSet
656: .getString("MemberFirstEmail"));
657: bean.setMemberEmail(resultSet.getString("MemberEmail"));
658: bean.setMemberEmailVisible(resultSet
659: .getInt("MemberEmailVisible"));
660: bean.setMemberNameVisible(resultSet
661: .getInt("MemberNameVisible"));
662: bean.setMemberFirstIP(resultSet.getString("MemberFirstIP"));
663: bean.setMemberLastIP(resultSet.getString("MemberLastIP"));
664: bean
665: .setMemberViewCount(resultSet
666: .getInt("MemberViewCount"));
667: bean
668: .setMemberPostCount(resultSet
669: .getInt("MemberPostCount"));
670: bean.setMemberCreationDate(resultSet
671: .getTimestamp("MemberCreationDate"));
672: bean.setMemberModifiedDate(resultSet
673: .getTimestamp("MemberModifiedDate"));
674: bean.setMemberExpireDate(resultSet
675: .getTimestamp("MemberExpireDate"));
676: bean.setMemberLastLogon(resultSet
677: .getTimestamp("MemberLastLogon"));
678: bean.setMemberOption(resultSet.getInt("MemberOption"));
679: bean.setMemberStatus(resultSet.getInt("MemberStatus"));
680: bean.setMemberActivateCode(resultSet
681: .getString("MemberActivateCode"));
682: bean.setMemberTempPassword(resultSet
683: .getString("MemberTempPassword"));
684: bean.setMemberMessageCount(resultSet
685: .getInt("MemberMessageCount"));
686: bean.setMemberMessageOption(resultSet
687: .getInt("MemberMessageOption"));
688: bean.setMemberPostsPerPage(resultSet
689: .getInt("MemberPostsPerPage"));
690: bean
691: .setMemberWarnCount(resultSet
692: .getInt("MemberWarnCount"));
693: bean
694: .setMemberVoteCount(resultSet
695: .getInt("MemberVoteCount"));
696: bean.setMemberVoteTotalStars(resultSet
697: .getInt("MemberVoteTotalStars"));
698: bean.setMemberRewardPoints(resultSet
699: .getInt("MemberRewardPoints"));
700: bean.setMemberTitle(resultSet.getString("MemberTitle"));
701: bean.setMemberTimeZone(resultSet
702: .getDouble("MemberTimeZone"));
703: bean.setMemberSignature(resultSet
704: .getString("MemberSignature"));
705: bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
706: bean.setMemberSkin(resultSet.getString("MemberSkin"));
707: bean.setMemberLanguage(resultSet
708: .getString("MemberLanguage"));
709: bean.setMemberFirstname(resultSet
710: .getString("MemberFirstname"));
711: bean.setMemberLastname(resultSet
712: .getString("MemberLastname"));
713: bean.setMemberGender(resultSet.getInt("MemberGender"));
714: bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
715: bean.setMemberAddress(resultSet.getString("MemberAddress"));
716: bean.setMemberCity(resultSet.getString("MemberCity"));
717: bean.setMemberState(resultSet.getString("MemberState"));
718: bean.setMemberCountry(resultSet.getString("MemberCountry"));
719: bean.setMemberPhone(resultSet.getString("MemberPhone"));
720: bean.setMemberMobile(resultSet.getString("MemberMobile"));
721: bean.setMemberFax(resultSet.getString("MemberFax"));
722: bean.setMemberCareer(resultSet.getString("MemberCareer"));
723: bean.setMemberHomepage(resultSet
724: .getString("MemberHomepage"));
725: bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
726: bean.setMemberAol(resultSet.getString("MemberAol"));
727: bean.setMemberIcq(resultSet.getString("MemberIcq"));
728: bean.setMemberMsn(resultSet.getString("MemberMsn"));
729: bean.setMemberCoolLink1(resultSet
730: .getString("MemberCoolLink1"));
731: bean.setMemberCoolLink2(resultSet
732: .getString("MemberCoolLink2"));
733: return bean;
734: } catch (SQLException sqle) {
735: throw new DatabaseException(
736: "Error executing SQL in memberDAOImplJDBC.getBean(pk).");
737: } finally {
738: DBUtils.closeResultSet(resultSet);
739: DBUtils.closeStatement(statement);
740: DBUtils.closeConnection(connection);
741: }
742: }
743:
744: public void create(String memberName, String memberPassword,
745: String memberFirstEmail, String memberEmail,
746: int memberEmailVisible, int memberNameVisible,
747: String memberFirstIP, String memberLastIP,
748: int memberViewCount, int memberPostCount,
749: Timestamp memberCreationDate, Timestamp memberModifiedDate,
750: Timestamp memberExpireDate, Timestamp memberLastLogon,
751: int memberOption, int memberStatus,
752: String memberActivateCode, String memberTempPassword,
753: int memberMessageCount, int memberMessageOption,
754: int memberPostsPerPage, int memberWarnCount,
755: int memberVoteCount, int memberVoteTotalStars,
756: int memberRewardPoints, String memberTitle,
757: double memberTimeZone, String memberSignature,
758: String memberAvatar, String memberSkin,
759: String memberLanguage, String memberFirstname,
760: String memberLastname, int memberGender,
761: Date memberBirthday, String memberAddress,
762: String memberCity, String memberState,
763: String memberCountry, String memberPhone,
764: String memberMobile, String memberFax, String memberCareer,
765: String memberHomepage, String memberYahoo,
766: String memberAol, String memberIcq, String memberMsn,
767: String memberCoolLink1, String memberCoolLink2)
768: throws CreateException, DatabaseException,
769: DuplicateKeyException {
770: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
771: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
772: try {
773: //Check if alternate key already exists
774: findByAlternateKey_MemberName(memberName);
775: //If so, then we have to throw an exception
776: throw new DuplicateKeyException(
777: "Alternate key already exists. Cannot create new Member with the same [MemberName] ("
778: + memberName + ").");
779: } catch (ObjectNotFoundException e) {
780: //Otherwise we can go ahead
781: }
782:
783: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
784: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
785: try {
786: //Check if alternate key already exists
787: findByAlternateKey_MemberEmail(memberEmail);
788: //If so, then we have to throw an exception
789: throw new DuplicateKeyException(
790: "Alternate key already exists. Cannot create new Member with the same [MemberEmail] ("
791: + memberEmail + ").");
792: } catch (ObjectNotFoundException e) {
793: //Otherwise we can go ahead
794: }
795:
796: Connection connection = null;
797: PreparedStatement statement = null;
798: StringBuffer sql = new StringBuffer(512);
799: sql
800: .append("INSERT INTO "
801: + TABLE_NAME
802: + " (MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2)");
803: sql
804: .append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
805: try {
806: connection = DBUtils.getMvnConnection();
807: statement = connection.prepareStatement(sql.toString());
808:
809: statement.setString(1, memberName);
810: statement.setString(2, memberPassword);
811: statement.setString(3, memberFirstEmail);
812: statement.setString(4, memberEmail);
813: statement.setInt(5, memberEmailVisible);
814: statement.setInt(6, memberNameVisible);
815: statement.setString(7, memberFirstIP);
816: statement.setString(8, memberLastIP);
817: statement.setInt(9, memberViewCount);
818: statement.setInt(10, memberPostCount);
819: statement.setTimestamp(11, memberCreationDate);
820: statement.setTimestamp(12, memberModifiedDate);
821: statement.setTimestamp(13, memberExpireDate);
822: statement.setTimestamp(14, memberLastLogon);
823: statement.setInt(15, memberOption);
824: statement.setInt(16, memberStatus);
825: statement.setString(17, memberActivateCode);
826: statement.setString(18, memberTempPassword);
827: statement.setInt(19, memberMessageCount);
828: statement.setInt(20, memberMessageOption);
829: statement.setInt(21, memberPostsPerPage);
830: statement.setInt(22, memberWarnCount);
831: statement.setInt(23, memberVoteCount);
832: statement.setInt(24, memberVoteTotalStars);
833: statement.setInt(25, memberRewardPoints);
834: statement.setString(26, memberTitle);
835: statement.setDouble(27, memberTimeZone);
836: statement.setString(28, memberSignature);
837: statement.setString(29, memberAvatar);
838: statement.setString(30, memberSkin);
839: statement.setString(31, memberLanguage);
840: statement.setString(32, memberFirstname);
841: statement.setString(33, memberLastname);
842: statement.setInt(34, memberGender);
843: statement.setDate(35, memberBirthday);
844: statement.setString(36, memberAddress);
845: statement.setString(37, memberCity);
846: statement.setString(38, memberState);
847: statement.setString(39, memberCountry);
848: statement.setString(40, memberPhone);
849: statement.setString(41, memberMobile);
850: statement.setString(42, memberFax);
851: statement.setString(43, memberCareer);
852: statement.setString(44, memberHomepage);
853: statement.setString(45, memberYahoo);
854: statement.setString(46, memberAol);
855: statement.setString(47, memberIcq);
856: statement.setString(48, memberMsn);
857: statement.setString(49, memberCoolLink1);
858: statement.setString(50, memberCoolLink2);
859:
860: if (statement.executeUpdate() != 1) {
861: throw new CreateException(
862: "Error adding a row into table 'Member'.");
863: }
864: } catch (SQLException sqle) {
865: throw new DatabaseException(
866: "Error executing SQL in MemberDAOImplJDBC.create.");
867: } finally {
868: DBUtils.closeStatement(statement);
869: DBUtils.closeConnection(connection);
870: }
871:
872: }
873: }
|