KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > mvnforum > phpbb2mvnforum > db > jdbc > MemberDAOImplJDBC


1 package org.mvnforum.phpbb2mvnforum.db.jdbc;
2
3 import java.sql.Connection JavaDoc;
4 import java.sql.Date JavaDoc;
5 import java.sql.PreparedStatement JavaDoc;
6 import java.sql.ResultSet JavaDoc;
7 import java.sql.SQLException JavaDoc;
8 import java.sql.Timestamp JavaDoc;
9
10 import net.myvietnam.mvncore.exception.CreateException;
11 import net.myvietnam.mvncore.exception.DatabaseException;
12 import net.myvietnam.mvncore.exception.DuplicateKeyException;
13 import net.myvietnam.mvncore.exception.ObjectNotFoundException;
14
15 import org.mvnforum.phpbb2mvnforum.db.MemberBean;
16 import org.mvnforum.phpbb2mvnforum.db.MemberDAO;
17 import org.mvnforum.util.DBUtils;
18
19 public class MemberDAOImplJDBC implements MemberDAO{
20
21     public void findByPrimaryKey(int memberID)
22         throws ObjectNotFoundException, DatabaseException {
23
24         Connection JavaDoc connection = null;
25         PreparedStatement JavaDoc statement = null;
26         ResultSet JavaDoc resultSet = null;
27
28         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
29         sql.append("SELECT MemberID");
30         sql.append(" FROM " + TABLE_NAME);
31         sql.append(" WHERE MemberID = ?");
32
33         try {
34             connection = DBUtils.getMvnConnection();
35             statement = connection.prepareStatement(sql.toString());
36             statement.setInt(1, memberID);
37             resultSet = statement.executeQuery();
38             if (!resultSet.next()) {
39                 throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + ") in table 'Member'.");
40             }
41         } catch (SQLException JavaDoc sqle) {
42             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByPrimaryKey.");
43         } finally {
44             DBUtils.closeResultSet(resultSet);
45             DBUtils.closeStatement(statement);
46             DBUtils.closeConnection(connection);
47         }
48     }
49
50     public void findByPrimaryKey2(int memberID, String JavaDoc memberName)
51         throws ObjectNotFoundException, DatabaseException {
52         // TODO Auto-generated method stub
53
}
54
55     public void findByAlternateKey_MemberEmail(String JavaDoc memberEmail)
56         throws ObjectNotFoundException, DatabaseException {
57
58         Connection JavaDoc connection = null;
59         PreparedStatement JavaDoc statement = null;
60         ResultSet JavaDoc resultSet = null;
61
62         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
63         sql.append("SELECT MemberEmail");
64         sql.append(" FROM " + TABLE_NAME);
65         sql.append(" WHERE MemberEmail = ?");
66
67         try {
68             connection = DBUtils.getMvnConnection();
69             statement = connection.prepareStatement(sql.toString());
70             statement.setString(1, memberEmail);
71             resultSet = statement.executeQuery();
72
73             if (!resultSet.next()) {
74                 throw new ObjectNotFoundException("Cannot find the alternate key [MemberEmail] (" + memberEmail
75                         + ") in table 'Member'.");
76             }
77         } catch (SQLException JavaDoc sqle) {
78             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberEmail.");
79         } finally {
80             DBUtils.closeResultSet(resultSet);
81             DBUtils.closeStatement(statement);
82             DBUtils.closeConnection(connection);
83         }
84     }
85
86     public void findByAlternateKey_MemberName(String JavaDoc memberName)
87         throws ObjectNotFoundException, DatabaseException {
88
89         Connection JavaDoc connection = null;
90         PreparedStatement JavaDoc statement = null;
91         ResultSet JavaDoc resultSet = null;
92
93         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
94         sql.append("SELECT MemberName");
95         sql.append(" FROM " + TABLE_NAME);
96         sql.append(" WHERE MemberName = ?");
97
98         try {
99             connection = DBUtils.getMvnConnection();
100             statement = connection.prepareStatement(sql.toString());
101             statement.setString(1, memberName);
102             resultSet = statement.executeQuery();
103             if (!resultSet.next()) {
104                 throw new ObjectNotFoundException("Cannot find the alternate key [MemberName] (" + memberName
105                         + ") in table 'Member'.");
106             }
107         } catch (SQLException JavaDoc sqle) {
108             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberName.");
109         } finally {
110             DBUtils.closeResultSet(resultSet);
111             DBUtils.closeStatement(statement);
112             DBUtils.closeConnection(connection);
113         }
114     }
115
116     public void deleteByPrimaryKey(int memberID)
117         throws DatabaseException {
118         // TODO Auto-generated method stub
119

120     }
121
122     /*
123      * Included columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
124      * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
125      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon,
126      * MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount,
127      * MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars,
128      * MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar,
129      * MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender,
130      * MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry,
131      * MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage,
132      * MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1,
133      * MemberCoolLink2
134      * Excluded columns:
135      */

136     public void create(int memberID, String JavaDoc memberName, String JavaDoc memberPassword,
137                         String JavaDoc memberFirstEmail, String JavaDoc memberEmail, int memberEmailVisible,
138                         int memberNameVisible, String JavaDoc memberFirstIP, String JavaDoc memberLastIP,
139                         int memberViewCount, int memberPostCount, Timestamp JavaDoc memberCreationDate,
140                         Timestamp JavaDoc memberModifiedDate, Timestamp JavaDoc memberExpireDate, Timestamp JavaDoc memberLastLogon,
141                         int memberOption, int memberStatus, String JavaDoc memberActivateCode,
142                         String JavaDoc memberTempPassword, int memberMessageCount, int memberMessageOption,
143                         int memberPostsPerPage, int memberWarnCount, int memberVoteCount,
144                         int memberVoteTotalStars, int memberRewardPoints, String JavaDoc memberTitle,
145                         double memberTimeZone, String JavaDoc memberSignature, String JavaDoc memberAvatar,
146                         String JavaDoc memberSkin, String JavaDoc memberLanguage, String JavaDoc memberFirstname,
147                         String JavaDoc memberLastname, int memberGender, Date JavaDoc memberBirthday,
148                         String JavaDoc memberAddress, String JavaDoc memberCity, String JavaDoc memberState,
149                         String JavaDoc memberCountry, String JavaDoc memberPhone, String JavaDoc memberMobile,
150                         String JavaDoc memberFax, String JavaDoc memberCareer, String JavaDoc memberHomepage,
151                         String JavaDoc memberYahoo, String JavaDoc memberAol, String JavaDoc memberIcq,
152                         String JavaDoc memberMsn, String JavaDoc memberCoolLink1, String JavaDoc memberCoolLink2)
153         throws CreateException, DatabaseException, DuplicateKeyException {
154
155         // @todo: comment this try-catch block if the needed columns dont have attribute 'include'
156
// If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
157
// However, if primary key is a auto_increament column, then you can safely delete this block
158
try {
159             //Check if primary key already exists
160
findByPrimaryKey(memberID);
161             //If so, then we have to throw an exception
162
throw new DuplicateKeyException("Primary key already exists. Cannot create new member with the same [MemberID] (" + memberID + ").");
163         } catch(ObjectNotFoundException e) {
164             //Otherwise we can go ahead
165
}
166
167         // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
168
// If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
169
try {
170             //Check if alternate key already exists
171
findByAlternateKey_MemberEmail(memberEmail);
172             //If so, then we have to throw an exception
173
throw new DuplicateKeyException("Alternate key already exists. Cannot create new member with the same [MemberEmail] (" + memberEmail + ").");
174         } catch(ObjectNotFoundException e) {
175             //Otherwise we can go ahead
176
}
177
178         // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
179
// If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
180
try {
181             //Check if alternate key already exists
182
findByAlternateKey_MemberName(memberName);
183             //If so, then we have to throw an exception
184
throw new DuplicateKeyException("Alternate key already exists. Cannot create new member with the same [MemberName] (" + memberName + ").");
185         } catch(ObjectNotFoundException e) {
186             //Otherwise we can go ahead
187
}
188
189         Connection JavaDoc connection = null;
190         PreparedStatement JavaDoc statement = null;
191         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
192         sql.append("INSERT INTO " + TABLE_NAME + " (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)");
193         sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
194         try {
195             connection = DBUtils.getMvnConnection();
196             statement = connection.prepareStatement(sql.toString());
197
198             statement.setInt(1, memberID);
199             statement.setString(2, memberName);
200             statement.setString(3, memberPassword);
201             statement.setString(4, memberFirstEmail);
202             statement.setString(5, memberEmail);
203             statement.setInt(6, memberEmailVisible);
204             statement.setInt(7, memberNameVisible);
205             statement.setString(8, memberFirstIP);
206             statement.setString(9, memberLastIP);
207             statement.setInt(10, memberViewCount);
208             statement.setInt(11, memberPostCount);
209             statement.setTimestamp(12, memberCreationDate);
210             statement.setTimestamp(13, memberModifiedDate);
211             statement.setTimestamp(14, memberExpireDate);
212             statement.setTimestamp(15, memberLastLogon);
213             statement.setInt(16, memberOption);
214             statement.setInt(17, memberStatus);
215             statement.setString(18, memberActivateCode);
216             statement.setString(19, memberTempPassword);
217             statement.setInt(20, memberMessageCount);
218             statement.setInt(21, memberMessageOption);
219             statement.setInt(22, memberPostsPerPage);
220             statement.setInt(23, memberWarnCount);
221             statement.setInt(24, memberVoteCount);
222             statement.setInt(25, memberVoteTotalStars);
223             statement.setInt(26, memberRewardPoints);
224             statement.setString(27, memberTitle);
225             statement.setDouble(28, memberTimeZone);
226             statement.setString(29, memberSignature);
227             statement.setString(30, memberAvatar);
228             statement.setString(31, memberSkin);
229             statement.setString(32, memberLanguage);
230             statement.setString(33, memberFirstname);
231             statement.setString(34, memberLastname);
232             statement.setInt(35, memberGender);
233             statement.setDate(36, memberBirthday);
234             statement.setString(37, memberAddress);
235             statement.setString(38, memberCity);
236             statement.setString(39, memberState);
237             statement.setString(40, memberCountry);
238             statement.setString(41, memberPhone);
239             statement.setString(42, memberMobile);
240             statement.setString(43, memberFax);
241             statement.setString(44, memberCareer);
242             statement.setString(45, memberHomepage);
243             statement.setString(46, memberYahoo);
244             statement.setString(47, memberAol);
245             statement.setString(48, memberIcq);
246             statement.setString(49, memberMsn);
247             statement.setString(50, memberCoolLink1);
248             statement.setString(51, memberCoolLink2);
249
250             if (statement.executeUpdate() != 1) {
251                 throw new CreateException("Error adding a row into table 'member'.");
252             }
253         } catch(SQLException JavaDoc sqle) {
254             throw new DatabaseException("Error executing SQL in memberDAOImplJDBC.create.");
255         } finally {
256             DBUtils.closeStatement(statement);
257             DBUtils.closeConnection(connection);
258         }
259     }
260
261
262
263     /*
264      * Included columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
265      * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
266      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon,
267      * MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount,
268      * MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars,
269      * MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar,
270      * MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender,
271      * MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry,
272      * MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage,
273      * MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1,
274      * MemberCoolLink2
275      * Excluded columns:
276      */

277     public MemberBean getBean(int memberID)
278         throws ObjectNotFoundException, DatabaseException {
279
280         Connection JavaDoc connection = null;
281         PreparedStatement JavaDoc statement = null;
282         ResultSet JavaDoc resultSet = null;
283         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
284         sql
285                 .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");
286         sql.append(" FROM " + TABLE_NAME);
287         sql.append(" WHERE MemberID = ?");
288         try {
289             connection = DBUtils.getMvnConnection();
290             statement = connection.prepareStatement(sql.toString());
291             statement.setInt(1, memberID);
292             resultSet = statement.executeQuery();
293             if (!resultSet.next()) {
294                 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = ("
295                         + memberID + ").");
296             }
297
298             MemberBean bean = new MemberBean();
299             // @todo: uncomment the following line(s) as needed
300
//bean.setMemberID(memberID);
301
bean.setMemberID(resultSet.getInt("MemberID"));
302             bean.setMemberName(resultSet.getString("MemberName"));
303             //bean.setMemberPassword(resultSet.getString("MemberPassword"));
304
bean.setMemberFirstEmail(resultSet.getString("MemberFirstEmail"));
305             bean.setMemberEmail(resultSet.getString("MemberEmail"));
306             bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
307             bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
308             bean.setMemberFirstIP(resultSet.getString("MemberFirstIP"));
309             bean.setMemberLastIP(resultSet.getString("MemberLastIP"));
310             bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
311             bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
312             bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
313             bean.setMemberModifiedDate(resultSet.getTimestamp("MemberModifiedDate"));
314             bean.setMemberExpireDate(resultSet.getTimestamp("MemberExpireDate"));
315             bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
316             bean.setMemberOption(resultSet.getInt("MemberOption"));
317             bean.setMemberStatus(resultSet.getInt("MemberStatus"));
318             bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
319             //bean.setMemberTempPassword(resultSet.getString("MemberTempPassword"));
320
bean.setMemberMessageCount(resultSet.getInt("MemberMessageCount"));
321             bean.setMemberMessageOption(resultSet.getInt("MemberMessageOption"));
322             bean.setMemberPostsPerPage(resultSet.getInt("MemberPostsPerPage"));
323             bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
324             bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
325             bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
326             bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
327             bean.setMemberTitle(resultSet.getString("MemberTitle"));
328             bean.setMemberTimeZone(resultSet.getDouble("MemberTimeZone"));
329             bean.setMemberSignature(resultSet.getString("MemberSignature"));
330             bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
331             bean.setMemberSkin(resultSet.getString("MemberSkin"));
332             bean.setMemberLanguage(resultSet.getString("MemberLanguage"));
333             bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
334             bean.setMemberLastname(resultSet.getString("MemberLastname"));
335             bean.setMemberGender(resultSet.getInt("MemberGender"));
336             bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
337             bean.setMemberAddress(resultSet.getString("MemberAddress"));
338             bean.setMemberCity(resultSet.getString("MemberCity"));
339             bean.setMemberState(resultSet.getString("MemberState"));
340             bean.setMemberCountry(resultSet.getString("MemberCountry"));
341             bean.setMemberPhone(resultSet.getString("MemberPhone"));
342             bean.setMemberMobile(resultSet.getString("MemberMobile"));
343             bean.setMemberFax(resultSet.getString("MemberFax"));
344             bean.setMemberCareer(resultSet.getString("MemberCareer"));
345             bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
346             bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
347             bean.setMemberAol(resultSet.getString("MemberAol"));
348             bean.setMemberIcq(resultSet.getString("MemberIcq"));
349             bean.setMemberMsn(resultSet.getString("MemberMsn"));
350             bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
351             bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
352             return bean;
353         } catch (SQLException JavaDoc sqle) {
354             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getBean(pk).");
355         } finally {
356             DBUtils.closeResultSet(resultSet);
357             DBUtils.closeStatement(statement);
358             DBUtils.closeConnection(connection);
359         }
360     }
361     
362     /*
363      * Included columns: MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail,
364      * MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount,
365      * MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon,
366      * MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount,
367      * MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars,
368      * MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar,
369      * MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender,
370      * MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry,
371      * MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage,
372      * MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1,
373      * MemberCoolLink2
374      * Excluded columns:
375      */

376     public void update(int memberID, // primary key
377
String JavaDoc memberName, String JavaDoc memberPassword, String JavaDoc memberFirstEmail,
378                         String JavaDoc memberEmail, int memberEmailVisible, int memberNameVisible,
379                         String JavaDoc memberFirstIP, String JavaDoc memberLastIP, int memberViewCount,
380                         int memberPostCount, Timestamp JavaDoc memberCreationDate, Timestamp JavaDoc memberModifiedDate,
381                         Timestamp JavaDoc memberExpireDate, Timestamp JavaDoc memberLastLogon, int memberOption,
382                         int memberStatus, String JavaDoc memberActivateCode, String JavaDoc memberTempPassword,
383                         int memberMessageCount, int memberMessageOption, int memberPostsPerPage,
384                         int memberWarnCount, int memberVoteCount, int memberVoteTotalStars,
385                         int memberRewardPoints, String JavaDoc memberTitle, double memberTimeZone,
386                         String JavaDoc memberSignature, String JavaDoc memberAvatar, String JavaDoc memberSkin,
387                         String JavaDoc memberLanguage, String JavaDoc memberFirstname, String JavaDoc memberLastname,
388                         int memberGender, Date JavaDoc memberBirthday, String JavaDoc memberAddress,
389                         String JavaDoc memberCity, String JavaDoc memberState, String JavaDoc memberCountry,
390                         String JavaDoc memberPhone, String JavaDoc memberMobile, String JavaDoc memberFax,
391                         String JavaDoc memberCareer, String JavaDoc memberHomepage, String JavaDoc memberYahoo,
392                         String JavaDoc memberAol, String JavaDoc memberIcq, String JavaDoc memberMsn,
393                         String JavaDoc memberCoolLink1, String JavaDoc memberCoolLink2)
394         throws ObjectNotFoundException, DatabaseException/*, DuplicateKeyException*/, DuplicateKeyException {
395
396         MemberBean bean = getBean(memberID); // @todo: comment or delete this line if no alternate key are included
397

398         if ( !memberEmail.equals(bean.getMemberEmail()) ) {
399             // member tries to change its alternate key [MemberEmail], so we must check if it already exist
400
try {
401                 findByAlternateKey_MemberEmail(memberEmail);
402                 throw new DuplicateKeyException("Alternate key [MemberEmail] (" + memberEmail + ")already exists. Cannot update member.");
403             } catch(ObjectNotFoundException e) {
404                 //Otherwise we can go ahead
405
}
406         }
407
408         Connection JavaDoc connection = null;
409         PreparedStatement JavaDoc statement = null;
410         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
411         sql.append("UPDATE " + TABLE_NAME + " 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 = ?");
412         sql.append(" WHERE MemberID = ?");
413         try {
414             connection = DBUtils.getMvnConnection();
415             statement = connection.prepareStatement(sql.toString());
416
417             // // column(s) to update
418
statement.setString(1, memberName);
419             statement.setString(2, memberPassword);
420             statement.setString(3, memberFirstEmail);
421             statement.setString(4, memberEmail);
422             statement.setInt(5, memberEmailVisible);
423             statement.setInt(6, memberNameVisible);
424             statement.setString(7, memberFirstIP);
425             statement.setString(8, memberLastIP);
426             statement.setInt(9, memberViewCount);
427             statement.setInt(10, memberPostCount);
428             statement.setTimestamp(11, memberCreationDate);
429             statement.setTimestamp(12, memberModifiedDate);
430             statement.setTimestamp(13, memberExpireDate);
431             statement.setTimestamp(14, memberLastLogon);
432             statement.setInt(15, memberOption);
433             statement.setInt(16, memberStatus);
434             statement.setString(17, memberActivateCode);
435             statement.setString(18, memberTempPassword);
436             statement.setInt(19, memberMessageCount);
437             statement.setInt(20, memberMessageOption);
438             statement.setInt(21, memberPostsPerPage);
439             statement.setInt(22, memberWarnCount);
440             statement.setInt(23, memberVoteCount);
441             statement.setInt(24, memberVoteTotalStars);
442             statement.setInt(25, memberRewardPoints);
443             statement.setString(26, memberTitle);
444             statement.setDouble(27, memberTimeZone);
445             statement.setString(28, memberSignature);
446             statement.setString(29, memberAvatar);
447             statement.setString(30, memberSkin);
448             statement.setString(31, memberLanguage);
449             statement.setString(32, memberFirstname);
450             statement.setString(33, memberLastname);
451             statement.setInt(34, memberGender);
452             statement.setDate(35, memberBirthday);
453             statement.setString(36, memberAddress);
454             statement.setString(37, memberCity);
455             statement.setString(38, memberState);
456             statement.setString(39, memberCountry);
457             statement.setString(40, memberPhone);
458             statement.setString(41, memberMobile);
459             statement.setString(42, memberFax);
460             statement.setString(43, memberCareer);
461             statement.setString(44, memberHomepage);
462             statement.setString(45, memberYahoo);
463             statement.setString(46, memberAol);
464             statement.setString(47, memberIcq);
465             statement.setString(48, memberMsn);
466             statement.setString(49, memberCoolLink1);
467             statement.setString(50, memberCoolLink2);
468
469             // primary key column(s)
470
statement.setInt(51, memberID);
471
472             if (statement.executeUpdate() != 1) {
473                 throw new ObjectNotFoundException("Cannot update table member where primary key = (" + memberID + ").");
474             }
475         } catch(SQLException JavaDoc sqle) {
476             throw new DatabaseException("Error executing SQL in memberDAOImplJDBC.update.");
477         } finally {
478             DBUtils.closeStatement(statement);
479             DBUtils.closeConnection(connection);
480         }
481     }
482
483     public String JavaDoc getMemberNameFromMemberID(int memberID)
484         throws ObjectNotFoundException, DatabaseException {
485         // TODO Auto-generated method stub
486
Connection JavaDoc connection = null;
487         PreparedStatement JavaDoc statement = null;
488         ResultSet JavaDoc resultSet = null;
489         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
490         sql.append("SELECT MemberName");
491         sql.append(" FROM " + TABLE_NAME);
492         sql.append(" WHERE MemberID = ?");
493         try {
494             connection = DBUtils.getMvnConnection();
495             statement = connection.prepareStatement(sql.toString());
496             statement.setInt(1, memberID);
497             resultSet = statement.executeQuery();
498             if(!resultSet.next()) {
499                 throw new ObjectNotFoundException("Cannot find the row in table member where primary key = (" + memberID + ").");
500             }
501             return resultSet.getString("MemberName");
502         } catch(SQLException JavaDoc sqle) {
503             throw new DatabaseException("Error executing SQL in memberDAOImplJDBC.getBean(pk).");
504         } finally {
505             DBUtils.closeResultSet(resultSet);
506             DBUtils.closeStatement(statement);
507             DBUtils.closeConnection(connection);
508         }
509     }
510
511     public MemberBean getMemberFromMemberName(String JavaDoc memberName) throws ObjectNotFoundException, DatabaseException {
512         // TODO Auto-generated method stub
513
Connection JavaDoc connection = null;
514             PreparedStatement JavaDoc statement = null;
515             ResultSet JavaDoc resultSet = null;
516             StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
517             sql.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");
518             sql.append(" FROM " + TABLE_NAME);
519             sql.append(" WHERE MemberName = ?");
520             try {
521                 connection = DBUtils.getMvnConnection();
522                 statement = connection.prepareStatement(sql.toString());
523                 statement.setString(1, memberName);
524                 resultSet = statement.executeQuery();
525                 if(!resultSet.next()) {
526                     throw new ObjectNotFoundException("Cannot find the row in table member where primary key = (" + memberName + ").");
527                 }
528
529                 MemberBean bean = new MemberBean();
530                 bean.setMemberID(resultSet.getInt("MemberID"));
531                 bean.setMemberName(resultSet.getString("MemberName"));
532                 bean.setMemberPassword(resultSet.getString("MemberPassword"));
533                 bean.setMemberFirstEmail(resultSet.getString("MemberFirstEmail"));
534                 bean.setMemberEmail(resultSet.getString("MemberEmail"));
535                 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible"));
536                 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible"));
537                 bean.setMemberFirstIP(resultSet.getString("MemberFirstIP"));
538                 bean.setMemberLastIP(resultSet.getString("MemberLastIP"));
539                 bean.setMemberViewCount(resultSet.getInt("MemberViewCount"));
540                 bean.setMemberPostCount(resultSet.getInt("MemberPostCount"));
541                 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate"));
542                 bean.setMemberModifiedDate(resultSet.getTimestamp("MemberModifiedDate"));
543                 bean.setMemberExpireDate(resultSet.getTimestamp("MemberExpireDate"));
544                 bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon"));
545                 bean.setMemberOption(resultSet.getInt("MemberOption"));
546                 bean.setMemberStatus(resultSet.getInt("MemberStatus"));
547                 bean.setMemberActivateCode(resultSet.getString("MemberActivateCode"));
548                 bean.setMemberTempPassword(resultSet.getString("MemberTempPassword"));
549                 bean.setMemberMessageCount(resultSet.getInt("MemberMessageCount"));
550                 bean.setMemberMessageOption(resultSet.getInt("MemberMessageOption"));
551                 bean.setMemberPostsPerPage(resultSet.getInt("MemberPostsPerPage"));
552                 bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount"));
553                 bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount"));
554                 bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars"));
555                 bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints"));
556                 bean.setMemberTitle(resultSet.getString("MemberTitle"));
557                 bean.setMemberTimeZone(resultSet.getDouble("MemberTimeZone"));
558                 bean.setMemberSignature(resultSet.getString("MemberSignature"));
559                 bean.setMemberAvatar(resultSet.getString("MemberAvatar"));
560                 bean.setMemberSkin(resultSet.getString("MemberSkin"));
561                 bean.setMemberLanguage(resultSet.getString("MemberLanguage"));
562                 bean.setMemberFirstname(resultSet.getString("MemberFirstname"));
563                 bean.setMemberLastname(resultSet.getString("MemberLastname"));
564                 bean.setMemberGender(resultSet.getInt("MemberGender"));
565                 bean.setMemberBirthday(resultSet.getDate("MemberBirthday"));
566                 bean.setMemberAddress(resultSet.getString("MemberAddress"));
567                 bean.setMemberCity(resultSet.getString("MemberCity"));
568                 bean.setMemberState(resultSet.getString("MemberState"));
569                 bean.setMemberCountry(resultSet.getString("MemberCountry"));
570                 bean.setMemberPhone(resultSet.getString("MemberPhone"));
571                 bean.setMemberMobile(resultSet.getString("MemberMobile"));
572                 bean.setMemberFax(resultSet.getString("MemberFax"));
573                 bean.setMemberCareer(resultSet.getString("MemberCareer"));
574                 bean.setMemberHomepage(resultSet.getString("MemberHomepage"));
575                 bean.setMemberYahoo(resultSet.getString("MemberYahoo"));
576                 bean.setMemberAol(resultSet.getString("MemberAol"));
577                 bean.setMemberIcq(resultSet.getString("MemberIcq"));
578                 bean.setMemberMsn(resultSet.getString("MemberMsn"));
579                 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1"));
580                 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2"));
581                 return bean;
582             } catch(SQLException JavaDoc sqle) {
583                 throw new DatabaseException("Error executing SQL in memberDAOImplJDBC.getBean(pk).");
584             } finally {
585                 DBUtils.closeResultSet(resultSet);
586                 DBUtils.closeStatement(statement);
587                 DBUtils.closeConnection(connection);
588             }
589         }
590
591     public void create(String JavaDoc memberName, String JavaDoc memberPassword, String JavaDoc memberFirstEmail, String JavaDoc memberEmail, int memberEmailVisible,
592             int memberNameVisible, String JavaDoc memberFirstIP, String JavaDoc memberLastIP, int memberViewCount, int memberPostCount,
593             Timestamp JavaDoc memberCreationDate, Timestamp JavaDoc memberModifiedDate, Timestamp JavaDoc memberExpireDate, Timestamp JavaDoc memberLastLogon,
594             int memberOption, int memberStatus, String JavaDoc memberActivateCode, String JavaDoc memberTempPassword, int memberMessageCount,
595             int memberMessageOption, int memberPostsPerPage, int memberWarnCount, int memberVoteCount, int memberVoteTotalStars,
596             int memberRewardPoints, String JavaDoc memberTitle, double memberTimeZone, String JavaDoc memberSignature, String JavaDoc memberAvatar, String JavaDoc memberSkin,
597             String JavaDoc memberLanguage, String JavaDoc memberFirstname, String JavaDoc memberLastname, int memberGender, Date JavaDoc memberBirthday,
598             String JavaDoc memberAddress, String JavaDoc memberCity, String JavaDoc memberState, String JavaDoc memberCountry, String JavaDoc memberPhone, String JavaDoc memberMobile,
599             String JavaDoc memberFax, String JavaDoc memberCareer, String JavaDoc memberHomepage, String JavaDoc memberYahoo, String JavaDoc memberAol, String JavaDoc memberIcq,
600             String JavaDoc memberMsn, String JavaDoc memberCoolLink1, String JavaDoc memberCoolLink2)
601         throws CreateException, DatabaseException, DuplicateKeyException {
602         // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
603
// If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
604
try {
605             //Check if alternate key already exists
606
findByAlternateKey_MemberName(memberName);
607             //If so, then we have to throw an exception
608
throw new DuplicateKeyException("Alternate key already exists. Cannot create new Member with the same [MemberName] (" + memberName + ").");
609         } catch(ObjectNotFoundException e) {
610             //Otherwise we can go ahead
611
}
612
613         // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
614
// If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
615
try {
616             //Check if alternate key already exists
617
findByAlternateKey_MemberEmail(memberEmail);
618             //If so, then we have to throw an exception
619
throw new DuplicateKeyException("Alternate key already exists. Cannot create new Member with the same [MemberEmail] (" + memberEmail + ").");
620         } catch(ObjectNotFoundException e) {
621             //Otherwise we can go ahead
622
}
623
624         Connection JavaDoc connection = null;
625         PreparedStatement JavaDoc statement = null;
626         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
627         sql.append("INSERT INTO " + TABLE_NAME + " (MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2)");
628         sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
629         try {
630             connection = DBUtils.getMvnConnection();
631             statement = connection.prepareStatement(sql.toString());
632
633             statement.setString(1, memberName);
634             statement.setString(2, memberPassword);
635             statement.setString(3, memberFirstEmail);
636             statement.setString(4, memberEmail);
637             statement.setInt(5, memberEmailVisible);
638             statement.setInt(6, memberNameVisible);
639             statement.setString(7, memberFirstIP);
640             statement.setString(8, memberLastIP);
641             statement.setInt(9, memberViewCount);
642             statement.setInt(10, memberPostCount);
643             statement.setTimestamp(11, memberCreationDate);
644             statement.setTimestamp(12, memberModifiedDate);
645             statement.setTimestamp(13, memberExpireDate);
646             statement.setTimestamp(14, memberLastLogon);
647             statement.setInt(15, memberOption);
648             statement.setInt(16, memberStatus);
649             statement.setString(17, memberActivateCode);
650             statement.setString(18, memberTempPassword);
651             statement.setInt(19, memberMessageCount);
652             statement.setInt(20, memberMessageOption);
653             statement.setInt(21, memberPostsPerPage);
654             statement.setInt(22, memberWarnCount);
655             statement.setInt(23, memberVoteCount);
656             statement.setInt(24, memberVoteTotalStars);
657             statement.setInt(25, memberRewardPoints);
658             statement.setString(26, memberTitle);
659             statement.setDouble(27, memberTimeZone);
660             statement.setString(28, memberSignature);
661             statement.setString(29, memberAvatar);
662             statement.setString(30, memberSkin);
663             statement.setString(31, memberLanguage);
664             statement.setString(32, memberFirstname);
665             statement.setString(33, memberLastname);
666             statement.setInt(34, memberGender);
667             statement.setDate(35, memberBirthday);
668             statement.setString(36, memberAddress);
669             statement.setString(37, memberCity);
670             statement.setString(38, memberState);
671             statement.setString(39, memberCountry);
672             statement.setString(40, memberPhone);
673             statement.setString(41, memberMobile);
674             statement.setString(42, memberFax);
675             statement.setString(43, memberCareer);
676             statement.setString(44, memberHomepage);
677             statement.setString(45, memberYahoo);
678             statement.setString(46, memberAol);
679             statement.setString(47, memberIcq);
680             statement.setString(48, memberMsn);
681             statement.setString(49, memberCoolLink1);
682             statement.setString(50, memberCoolLink2);
683
684             if (statement.executeUpdate() != 1) {
685                 throw new CreateException("Error adding a row into table 'Member'.");
686             }
687         } catch(SQLException JavaDoc sqle) {
688             throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.create.");
689         } finally {
690             DBUtils.closeStatement(statement);
691             DBUtils.closeConnection(connection);
692         }
693
694       
695     }
696 }
697
Popular Tags