1 package org.mvnforum.phpbb2mvnforum.db.jdbc; 2 3 import java.sql.Connection ; 4 import java.sql.Date ; 5 import java.sql.PreparedStatement ; 6 import java.sql.ResultSet ; 7 import java.sql.SQLException ; 8 import java.sql.Timestamp ; 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 connection = null; 25 PreparedStatement statement = null; 26 ResultSet resultSet = null; 27 28 StringBuffer sql = new StringBuffer (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 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 memberName) 51 throws ObjectNotFoundException, DatabaseException { 52 } 54 55 public void findByAlternateKey_MemberEmail(String memberEmail) 56 throws ObjectNotFoundException, DatabaseException { 57 58 Connection connection = null; 59 PreparedStatement statement = null; 60 ResultSet resultSet = null; 61 62 StringBuffer sql = new StringBuffer (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 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 memberName) 87 throws ObjectNotFoundException, DatabaseException { 88 89 Connection connection = null; 90 PreparedStatement statement = null; 91 ResultSet resultSet = null; 92 93 StringBuffer sql = new StringBuffer (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 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 120 } 121 122 136 public void create(int memberID, String memberName, String memberPassword, 137 String memberFirstEmail, String memberEmail, int memberEmailVisible, 138 int memberNameVisible, String memberFirstIP, String memberLastIP, 139 int memberViewCount, int memberPostCount, Timestamp memberCreationDate, 140 Timestamp memberModifiedDate, Timestamp memberExpireDate, Timestamp memberLastLogon, 141 int memberOption, int memberStatus, String memberActivateCode, 142 String memberTempPassword, int memberMessageCount, int memberMessageOption, 143 int memberPostsPerPage, int memberWarnCount, int memberVoteCount, 144 int memberVoteTotalStars, int memberRewardPoints, String memberTitle, 145 double memberTimeZone, String memberSignature, String memberAvatar, 146 String memberSkin, String memberLanguage, String memberFirstname, 147 String memberLastname, int memberGender, Date memberBirthday, 148 String memberAddress, String memberCity, String memberState, 149 String memberCountry, String memberPhone, String memberMobile, 150 String memberFax, String memberCareer, String memberHomepage, 151 String memberYahoo, String memberAol, String memberIcq, 152 String memberMsn, String memberCoolLink1, String memberCoolLink2) 153 throws CreateException, DatabaseException, DuplicateKeyException { 154 155 try { 159 findByPrimaryKey(memberID); 161 throw new DuplicateKeyException("Primary key already exists. Cannot create new member with the same [MemberID] (" + memberID + ")."); 163 } catch(ObjectNotFoundException e) { 164 } 166 167 try { 170 findByAlternateKey_MemberEmail(memberEmail); 172 throw new DuplicateKeyException("Alternate key already exists. Cannot create new member with the same [MemberEmail] (" + memberEmail + ")."); 174 } catch(ObjectNotFoundException e) { 175 } 177 178 try { 181 findByAlternateKey_MemberName(memberName); 183 throw new DuplicateKeyException("Alternate key already exists. Cannot create new member with the same [MemberName] (" + memberName + ")."); 185 } catch(ObjectNotFoundException e) { 186 } 188 189 Connection connection = null; 190 PreparedStatement statement = null; 191 StringBuffer sql = new StringBuffer (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 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 277 public MemberBean getBean(int memberID) 278 throws ObjectNotFoundException, DatabaseException { 279 280 Connection connection = null; 281 PreparedStatement statement = null; 282 ResultSet resultSet = null; 283 StringBuffer sql = new StringBuffer (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 bean.setMemberID(resultSet.getInt("MemberID")); 302 bean.setMemberName(resultSet.getString("MemberName")); 303 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.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 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 376 public void update(int memberID, String memberName, String memberPassword, String memberFirstEmail, 378 String memberEmail, int memberEmailVisible, int memberNameVisible, 379 String memberFirstIP, String memberLastIP, int memberViewCount, 380 int memberPostCount, Timestamp memberCreationDate, Timestamp memberModifiedDate, 381 Timestamp memberExpireDate, Timestamp memberLastLogon, int memberOption, 382 int memberStatus, String memberActivateCode, String memberTempPassword, 383 int memberMessageCount, int memberMessageOption, int memberPostsPerPage, 384 int memberWarnCount, int memberVoteCount, int memberVoteTotalStars, 385 int memberRewardPoints, String memberTitle, double memberTimeZone, 386 String memberSignature, String memberAvatar, String memberSkin, 387 String memberLanguage, String memberFirstname, String memberLastname, 388 int memberGender, Date memberBirthday, String memberAddress, 389 String memberCity, String memberState, String memberCountry, 390 String memberPhone, String memberMobile, String memberFax, 391 String memberCareer, String memberHomepage, String memberYahoo, 392 String memberAol, String memberIcq, String memberMsn, 393 String memberCoolLink1, String memberCoolLink2) 394 throws ObjectNotFoundException, DatabaseException, DuplicateKeyException { 395 396 MemberBean bean = getBean(memberID); 398 if ( !memberEmail.equals(bean.getMemberEmail()) ) { 399 try { 401 findByAlternateKey_MemberEmail(memberEmail); 402 throw new DuplicateKeyException("Alternate key [MemberEmail] (" + memberEmail + ")already exists. Cannot update member."); 403 } catch(ObjectNotFoundException e) { 404 } 406 } 407 408 Connection connection = null; 409 PreparedStatement statement = null; 410 StringBuffer sql = new StringBuffer (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 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 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 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 getMemberNameFromMemberID(int memberID) 484 throws ObjectNotFoundException, DatabaseException { 485 Connection connection = null; 487 PreparedStatement statement = null; 488 ResultSet resultSet = null; 489 StringBuffer sql = new StringBuffer (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 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 memberName) throws ObjectNotFoundException, DatabaseException { 512 Connection connection = null; 514 PreparedStatement statement = null; 515 ResultSet resultSet = null; 516 StringBuffer sql = new StringBuffer (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 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 memberName, String memberPassword, String memberFirstEmail, String memberEmail, int memberEmailVisible, 592 int memberNameVisible, String memberFirstIP, String memberLastIP, int memberViewCount, int memberPostCount, 593 Timestamp memberCreationDate, Timestamp memberModifiedDate, Timestamp memberExpireDate, Timestamp memberLastLogon, 594 int memberOption, int memberStatus, String memberActivateCode, String memberTempPassword, int memberMessageCount, 595 int memberMessageOption, int memberPostsPerPage, int memberWarnCount, int memberVoteCount, int memberVoteTotalStars, 596 int memberRewardPoints, String memberTitle, double memberTimeZone, String memberSignature, String memberAvatar, String memberSkin, 597 String memberLanguage, String memberFirstname, String memberLastname, int memberGender, Date memberBirthday, 598 String memberAddress, String memberCity, String memberState, String memberCountry, String memberPhone, String memberMobile, 599 String memberFax, String memberCareer, String memberHomepage, String memberYahoo, String memberAol, String memberIcq, 600 String memberMsn, String memberCoolLink1, String memberCoolLink2) 601 throws CreateException, DatabaseException, DuplicateKeyException { 602 try { 605 findByAlternateKey_MemberName(memberName); 607 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Member with the same [MemberName] (" + memberName + ")."); 609 } catch(ObjectNotFoundException e) { 610 } 612 613 try { 616 findByAlternateKey_MemberEmail(memberEmail); 618 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Member with the same [MemberEmail] (" + memberEmail + ")."); 620 } catch(ObjectNotFoundException e) { 621 } 623 624 Connection connection = null; 625 PreparedStatement statement = null; 626 StringBuffer sql = new StringBuffer (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 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 |