1 41 package com.mvnforum.db.jdbc; 42 43 import java.sql.*; 44 import java.sql.Date ; 45 import java.util.ArrayList ; 46 import java.util.Collection ; 47 48 import com.mvnforum.db.*; 49 import net.myvietnam.mvncore.db.DBUtils; 50 import net.myvietnam.mvncore.exception.*; 51 import net.myvietnam.mvncore.util.StringUtil; 52 import org.apache.commons.logging.Log; 53 import org.apache.commons.logging.LogFactory; 54 55 public class MemberDAOImplJDBC implements MemberDAO { 56 57 private static Log log = LogFactory.getLog(MemberDAOImplJDBC.class); 58 59 private static boolean m_dirty = true; 61 62 public MemberDAOImplJDBC() { 63 } 64 65 protected static boolean isDirty() { 66 return m_dirty; 67 } 68 69 protected static void setDirty(boolean dirty) { 70 m_dirty = dirty; 71 } 72 73 public void findByPrimaryKey(int memberID) 74 throws ObjectNotFoundException, DatabaseException { 75 76 Connection connection = null; 77 PreparedStatement statement = null; 78 ResultSet resultSet = null; 79 StringBuffer sql = new StringBuffer (512); 80 sql.append("SELECT MemberID"); 81 sql.append(" FROM " + TABLE_NAME); 82 sql.append(" WHERE MemberID = ?"); 83 try { 84 connection = DBUtils.getConnection(); 85 statement = connection.prepareStatement(sql.toString()); 86 statement.setInt(1, memberID); 87 resultSet = statement.executeQuery(); 88 if (!resultSet.next()) { 89 throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + ") in table 'Member'."); 90 } 91 } catch(SQLException sqle) { 92 log.error("Sql Execution Error!", sqle); 93 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByPrimaryKey."); 94 } finally { 95 DBUtils.closeResultSet(resultSet); 96 DBUtils.closeStatement(statement); 97 DBUtils.closeConnection(connection); 98 } 99 } 100 101 public void findByPrimaryKey2(int memberID, String memberName) 102 throws ObjectNotFoundException, DatabaseException { 103 104 Connection connection = null; 105 PreparedStatement statement = null; 106 ResultSet resultSet = null; 107 StringBuffer sql = new StringBuffer (512); 108 sql.append("SELECT MemberID, MemberName"); 109 sql.append(" FROM " + TABLE_NAME); 110 sql.append(" WHERE MemberID = ? AND MemberName = ?"); 111 try { 112 connection = DBUtils.getConnection(); 113 statement = connection.prepareStatement(sql.toString()); 114 statement.setInt(1, memberID); 115 statement.setString(2, memberName); 116 resultSet = statement.executeQuery(); 117 if (!resultSet.next()) { 118 throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + "," + memberName + ") in table 'Member'."); 119 } 120 } catch(SQLException sqle) { 121 log.error("Sql Execution Error!", sqle); 122 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByPrimaryKey2."); 123 } finally { 124 DBUtils.closeResultSet(resultSet); 125 DBUtils.closeStatement(statement); 126 DBUtils.closeConnection(connection); 127 } 128 } 129 130 public void findByAlternateKey_MemberName(String memberName) 131 throws ObjectNotFoundException, DatabaseException { 132 133 Connection connection = null; 134 PreparedStatement statement = null; 135 ResultSet resultSet = null; 136 StringBuffer sql = new StringBuffer (512); 137 sql.append("SELECT MemberName"); 138 sql.append(" FROM " + TABLE_NAME); 139 if ( (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) || 140 (DBUtils.getDatabaseType() == DBUtils.DATABASE_SAPDB) || 141 (DBUtils.getDatabaseType() == DBUtils.DATABASE_POSTGRESQL) ) { 142 sql.append(" WHERE lower(MemberName) = lower(?)"); 143 } else { 144 sql.append(" WHERE MemberName = ?"); 145 } 146 try { 147 connection = DBUtils.getConnection(); 148 statement = connection.prepareStatement(sql.toString()); 149 statement.setString(1, memberName); 150 resultSet = statement.executeQuery(); 151 if (!resultSet.next()) { 152 throw new ObjectNotFoundException("Cannot find the alternate key [MemberName] (" + memberName + ") in table 'Member'."); 153 } 154 } catch(SQLException sqle) { 155 log.error("Sql Execution Error!", sqle); 156 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberName."); 157 } finally { 158 DBUtils.closeResultSet(resultSet); 159 DBUtils.closeStatement(statement); 160 DBUtils.closeConnection(connection); 161 } 162 } 163 164 public void findByAlternateKey_MemberEmail(String memberEmail) 165 throws ObjectNotFoundException, DatabaseException { 166 167 Connection connection = null; 168 PreparedStatement statement = null; 169 ResultSet resultSet = null; 170 StringBuffer sql = new StringBuffer (512); 171 sql.append("SELECT MemberEmail"); 172 sql.append(" FROM " + TABLE_NAME); 173 if ( (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) || 174 (DBUtils.getDatabaseType() == DBUtils.DATABASE_SAPDB) || 175 (DBUtils.getDatabaseType() == DBUtils.DATABASE_POSTGRESQL) ) { 176 sql.append(" WHERE lower(MemberEmail) = lower(?)"); 177 } else { 178 sql.append(" WHERE MemberEmail = ?"); 179 } 180 try { 181 connection = DBUtils.getConnection(); 182 statement = connection.prepareStatement(sql.toString()); 183 statement.setString(1, memberEmail); 184 resultSet = statement.executeQuery(); 185 if (!resultSet.next()) { 186 throw new ObjectNotFoundException("Cannot find the alternate key [MemberEmail] (" + memberEmail + ") in table 'Member'."); 187 } 188 } catch(SQLException sqle) { 189 log.error("Sql Execution Error!", sqle); 190 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.findByAlternateKey_MemberEmail."); 191 } finally { 192 DBUtils.closeResultSet(resultSet); 193 DBUtils.closeStatement(statement); 194 DBUtils.closeConnection(connection); 195 } 196 } 197 198 205 public void deleteByPrimaryKey(int memberID) throws DatabaseException { 206 207 Connection connection = null; 208 PreparedStatement statement = null; 209 ResultSet resultSet = null; 210 StringBuffer sql = new StringBuffer (512); 211 sql.append("DELETE"); 212 sql.append(" FROM " + TABLE_NAME); 213 sql.append(" WHERE MemberID = ?"); 214 try { 215 connection = DBUtils.getConnection(); 216 statement = connection.prepareStatement(sql.toString()); 217 statement.setInt(1, memberID); 218 statement.executeUpdate(); 219 } catch(SQLException sqle) { 220 log.error("Sql Execution Error!", sqle); 221 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.deleteByPrimaryKey."); 222 } finally { 223 DBUtils.closeResultSet(resultSet); 224 DBUtils.closeStatement(statement); 225 DBUtils.closeConnection(connection); 226 } 227 } 228 229 242 public void create(String memberName, String memberPassword, String memberFirstEmail, 243 String memberEmail, int memberEmailVisible, int memberNameVisible, 244 String memberFirstIP, String memberLastIP, int memberViewCount, 245 int memberPostCount, Timestamp memberCreationDate, Timestamp memberModifiedDate, Timestamp memberExpireDate, 246 Timestamp memberLastLogon, int memberOption, int memberStatus, 247 String memberActivateCode, String memberTempPassword, int memberMessageCount, 248 int memberMessageOption, int memberPostsPerPage, int memberWarnCount, 249 int memberVoteCount, int memberVoteTotalStars, int memberRewardPoints, 250 String memberTitle, double memberTimeZone, String memberSignature, 251 String memberAvatar, String memberSkin, String memberLanguage, 252 String memberFirstname, String memberLastname, int memberGender, 253 Date memberBirthday, String memberAddress, String memberCity, 254 String memberState, String memberCountry, String memberPhone, 255 String memberMobile, String memberFax, String memberCareer, 256 String memberHomepage, String memberYahoo, String memberAol, 257 String memberIcq, String memberMsn, String memberCoolLink1, 258 String memberCoolLink2) 259 throws CreateException, DatabaseException, DuplicateKeyException { 260 261 try { 264 findByAlternateKey_MemberName(memberName); 266 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Member with the same [MemberName] (" + memberName + ")."); 268 } catch(ObjectNotFoundException e) { 269 } 271 272 try { 275 findByAlternateKey_MemberEmail(memberEmail); 277 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Member with the same [MemberEmail] (" + memberEmail + ")."); 279 } catch(ObjectNotFoundException e) { 280 } 282 283 Connection connection = null; 284 PreparedStatement statement = null; 285 StringBuffer sql = new StringBuffer (512); 286 sql.append("INSERT INTO " + TABLE_NAME + " (MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2)"); 287 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); 288 try { 289 connection = DBUtils.getConnection(); 290 statement = connection.prepareStatement(sql.toString()); 291 292 statement.setString(1, memberName); 293 statement.setString(2, memberPassword); 294 statement.setString(3, memberFirstEmail); 295 statement.setString(4, memberEmail); 296 statement.setInt(5, memberEmailVisible); 297 statement.setInt(6, memberNameVisible); 298 statement.setString(7, memberFirstIP); 299 statement.setString(8, memberLastIP); 300 statement.setInt(9, memberViewCount); 301 statement.setInt(10, memberPostCount); 302 statement.setTimestamp(11, memberCreationDate); 303 statement.setTimestamp(12, memberModifiedDate); 304 statement.setTimestamp(13, memberExpireDate); 305 statement.setTimestamp(14, memberLastLogon); 306 statement.setInt(15, memberOption); 307 statement.setInt(16, memberStatus); 308 statement.setString(17, memberActivateCode); 309 statement.setString(18, memberTempPassword); 310 statement.setInt(19, memberMessageCount); 311 statement.setInt(20, memberMessageOption); 312 statement.setInt(21, memberPostsPerPage); 313 statement.setInt(22, memberWarnCount); 314 statement.setInt(23, memberVoteCount); 315 statement.setInt(24, memberVoteTotalStars); 316 statement.setInt(25, memberRewardPoints); 317 statement.setString(26, memberTitle); 318 statement.setDouble(27, memberTimeZone); 319 statement.setString(28, memberSignature); 320 statement.setString(29, memberAvatar); 321 statement.setString(30, memberSkin); 322 statement.setString(31, memberLanguage); 323 statement.setString(32, memberFirstname); 324 statement.setString(33, memberLastname); 325 statement.setInt(34, memberGender); 326 statement.setDate(35, memberBirthday); 327 statement.setString(36, memberAddress); 328 statement.setString(37, memberCity); 329 statement.setString(38, memberState); 330 statement.setString(39, memberCountry); 331 statement.setString(40, memberPhone); 332 statement.setString(41, memberMobile); 333 statement.setString(42, memberFax); 334 statement.setString(43, memberCareer); 335 statement.setString(44, memberHomepage); 336 statement.setString(45, memberYahoo); 337 statement.setString(46, memberAol); 338 statement.setString(47, memberIcq); 339 statement.setString(48, memberMsn); 340 statement.setString(49, memberCoolLink1); 341 statement.setString(50, memberCoolLink2); 342 343 if (statement.executeUpdate() != 1) { 344 throw new CreateException("Error adding a row into table 'Member'."); 345 } 346 m_dirty = true; 347 } catch(SQLException sqle) { 348 log.error("Sql Execution Error!", sqle); 349 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.create."); 350 } finally { 351 DBUtils.closeStatement(statement); 352 DBUtils.closeConnection(connection); 353 } 354 } 355 356 369 public void update(int memberID, int memberEmailVisible, int memberNameVisible, Timestamp memberModifiedDate, 371 int memberOption, int memberStatus, int memberMessageOption, 372 int memberPostsPerPage, double memberTimeZone, String memberSkin, 373 String memberLanguage, String memberFirstname, String memberLastname, 374 int memberGender, Date memberBirthday, String memberAddress, 375 String memberCity, String memberState, String memberCountry, 376 String memberPhone, String memberMobile, String memberFax, 377 String memberCareer, String memberHomepage, String memberYahoo, 378 String memberAol, String memberIcq, String memberMsn, 379 String memberCoolLink1, String memberCoolLink2) 380 throws ObjectNotFoundException, DatabaseException { 381 382 Connection connection = null; 383 PreparedStatement statement = null; 384 StringBuffer sql = new StringBuffer (512); 385 sql.append("UPDATE " + TABLE_NAME + " SET MemberEmailVisible = ?, MemberNameVisible = ?, MemberModifiedDate = ?, MemberOption = ?, MemberStatus = ?, MemberMessageOption = ?, MemberPostsPerPage = ?, MemberTimeZone = ?, MemberSkin = ?, MemberLanguage = ?, MemberFirstname = ?, MemberLastname = ?, MemberGender = ?, MemberBirthday = ?, MemberAddress = ?, MemberCity = ?, MemberState = ?, MemberCountry = ?, MemberPhone = ?, MemberMobile = ?, MemberFax = ?, MemberCareer = ?, MemberHomepage = ?, MemberYahoo = ?, MemberAol = ?, MemberIcq = ?, MemberMsn = ?, MemberCoolLink1 = ?, MemberCoolLink2 = ?"); 386 sql.append(" WHERE MemberID = ?"); 387 try { 388 connection = DBUtils.getConnection(); 389 statement = connection.prepareStatement(sql.toString()); 390 391 statement.setInt(1, memberEmailVisible); 393 statement.setInt(2, memberNameVisible); 394 statement.setTimestamp(3, memberModifiedDate); 395 statement.setInt(4, memberOption); 396 statement.setInt(5, memberStatus); 397 statement.setInt(6, memberMessageOption); 398 statement.setInt(7, memberPostsPerPage); 399 statement.setDouble(8, memberTimeZone); 400 statement.setString(9, memberSkin); 401 statement.setString(10, memberLanguage); 402 statement.setString(11, memberFirstname); 403 statement.setString(12, memberLastname); 404 statement.setInt(13, memberGender); 405 statement.setDate(14, memberBirthday); 406 statement.setString(15, memberAddress); 407 statement.setString(16, memberCity); 408 statement.setString(17, memberState); 409 statement.setString(18, memberCountry); 410 statement.setString(19, memberPhone); 411 statement.setString(20, memberMobile); 412 statement.setString(21, memberFax); 413 statement.setString(22, memberCareer); 414 statement.setString(23, memberHomepage); 415 statement.setString(24, memberYahoo); 416 statement.setString(25, memberAol); 417 statement.setString(26, memberIcq); 418 statement.setString(27, memberMsn); 419 statement.setString(28, memberCoolLink1); 420 statement.setString(29, memberCoolLink2); 421 422 statement.setInt(30, memberID); 424 425 if (statement.executeUpdate() != 1) { 426 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 427 } 428 m_dirty = true; 429 } catch(SQLException sqle) { 430 log.error("Sql Execution Error!", sqle); 431 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.update."); 432 } finally { 433 DBUtils.closeStatement(statement); 434 DBUtils.closeConnection(connection); 435 } 436 } 437 438 451 public void updateEmail(int memberID, String memberEmail) 453 throws ObjectNotFoundException, DatabaseException, DuplicateKeyException { 454 455 MemberBean bean = getMember_forViewCurrentMember(memberID); 458 if ( !memberEmail.equals(bean.getMemberEmail()) ) { 459 try { 461 findByAlternateKey_MemberEmail(memberEmail); 462 throw new DuplicateKeyException("Alternate key [MemberEmail] (" + memberEmail + ")already exists. Cannot update Member."); 463 } catch(ObjectNotFoundException e) { 464 } 466 } 467 468 Connection connection = null; 469 PreparedStatement statement = null; 470 StringBuffer sql = new StringBuffer (512); 471 sql.append("UPDATE " + TABLE_NAME + " SET MemberEmail = ?"); 472 sql.append(" WHERE MemberID = ?"); 473 try { 474 connection = DBUtils.getConnection(); 475 statement = connection.prepareStatement(sql.toString()); 476 477 statement.setString(1, memberEmail); 479 480 statement.setInt(2, memberID); 482 483 if (statement.executeUpdate() != 1) { 484 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 485 } 486 m_dirty = true; 487 } catch(SQLException sqle) { 488 log.error("Sql Execution Error!", sqle); 489 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateEmail."); 490 } finally { 491 DBUtils.closeStatement(statement); 492 DBUtils.closeConnection(connection); 493 } 494 } 495 496 509 public void updatePassword(int memberID, String memberPassword) 511 throws ObjectNotFoundException, DatabaseException { 512 513 Connection connection = null; 514 PreparedStatement statement = null; 515 StringBuffer sql = new StringBuffer (512); 516 sql.append("UPDATE " + TABLE_NAME + " SET MemberPassword = ?"); 517 sql.append(" WHERE MemberID = ?"); 518 try { 519 connection = DBUtils.getConnection(); 520 statement = connection.prepareStatement(sql.toString()); 521 522 statement.setString(1, memberPassword); 524 525 statement.setInt(2, memberID); 527 528 if (statement.executeUpdate() != 1) { 529 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 530 } 531 m_dirty = true; 532 } catch(SQLException sqle) { 533 log.error("Sql Execution Error!", sqle); 534 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updatePassword."); 535 } finally { 536 DBUtils.closeStatement(statement); 537 DBUtils.closeConnection(connection); 538 } 539 } 540 541 554 public void updateTempPassword(int memberID, String memberTempPassword) 556 throws ObjectNotFoundException, DatabaseException { 557 558 Connection connection = null; 559 PreparedStatement statement = null; 560 StringBuffer sql = new StringBuffer (512); 561 sql.append("UPDATE " + TABLE_NAME + " SET MemberTempPassword = ?"); 562 sql.append(" WHERE MemberID = ?"); 563 try { 564 connection = DBUtils.getConnection(); 565 statement = connection.prepareStatement(sql.toString()); 566 567 statement.setString(1, memberTempPassword); 569 570 statement.setInt(2, memberID); 572 573 if (statement.executeUpdate() != 1) { 574 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 575 } 576 m_dirty = true; 577 } catch(SQLException sqle) { 578 log.error("Sql Execution Error!", sqle); 579 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateTempPassword."); 580 } finally { 581 DBUtils.closeStatement(statement); 582 DBUtils.closeConnection(connection); 583 } 584 } 585 586 599 public void updateActivateCode(int memberID, String memberActivateCode) 601 throws ObjectNotFoundException, DatabaseException { 602 603 Connection connection = null; 604 PreparedStatement statement = null; 605 StringBuffer sql = new StringBuffer (512); 606 sql.append("UPDATE " + TABLE_NAME + " SET MemberActivateCode = ?"); 607 sql.append(" WHERE MemberID = ?"); 608 try { 609 connection = DBUtils.getConnection(); 610 statement = connection.prepareStatement(sql.toString()); 611 612 statement.setString(1, memberActivateCode); 614 615 statement.setInt(2, memberID); 617 618 if (statement.executeUpdate() != 1) { 619 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 620 } 621 m_dirty = true; 622 } catch(SQLException sqle) { 623 log.error("Sql Execution Error!", sqle); 624 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateActivateCode."); 625 } finally { 626 DBUtils.closeStatement(statement); 627 DBUtils.closeConnection(connection); 628 } 629 } 630 631 644 public void updateAvatar(int memberID, String memberAvatar) 646 throws ObjectNotFoundException, DatabaseException { 647 648 Connection connection = null; 649 PreparedStatement statement = null; 650 StringBuffer sql = new StringBuffer (512); 651 sql.append("UPDATE " + TABLE_NAME + " SET MemberAvatar = ?"); 652 sql.append(" WHERE MemberID = ?"); 653 try { 654 connection = DBUtils.getConnection(); 655 statement = connection.prepareStatement(sql.toString()); 656 657 statement.setString(1, memberAvatar); 659 660 statement.setInt(2, memberID); 662 663 if (statement.executeUpdate() != 1) { 664 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 665 } 666 m_dirty = true; 667 } catch(SQLException sqle) { 668 log.error("Sql Execution Error!", sqle); 669 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateAvatar."); 670 } finally { 671 DBUtils.closeStatement(statement); 672 DBUtils.closeConnection(connection); 673 } 674 } 675 676 689 public void updateSignature(int memberID, String memberSignature) 691 throws ObjectNotFoundException, DatabaseException { 692 693 Connection connection = null; 694 PreparedStatement statement = null; 695 StringBuffer sql = new StringBuffer (512); 696 sql.append("UPDATE " + TABLE_NAME + " SET MemberSignature = ?"); 697 sql.append(" WHERE MemberID = ?"); 698 try { 699 connection = DBUtils.getConnection(); 700 statement = connection.prepareStatement(sql.toString()); 701 702 statement.setString(1, memberSignature); 704 705 statement.setInt(2, memberID); 707 708 if (statement.executeUpdate() != 1) { 709 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 710 } 711 m_dirty = true; 712 } catch(SQLException sqle) { 713 log.error("Sql Execution Error!", sqle); 714 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateSignature."); 715 } finally { 716 DBUtils.closeStatement(statement); 717 DBUtils.closeConnection(connection); 718 } 719 } 720 721 734 public void updateTitle(int memberID, String memberTitle) 736 throws ObjectNotFoundException, DatabaseException { 737 738 Connection connection = null; 739 PreparedStatement statement = null; 740 StringBuffer sql = new StringBuffer (512); 741 sql.append("UPDATE " + TABLE_NAME + " SET MemberTitle = ?"); 742 sql.append(" WHERE MemberID = ?"); 743 try { 744 connection = DBUtils.getConnection(); 745 statement = connection.prepareStatement(sql.toString()); 746 747 statement.setString(1, memberTitle); 749 750 statement.setInt(2, memberID); 752 753 if (statement.executeUpdate() != 1) { 754 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 755 } 756 m_dirty = true; 757 } catch(SQLException sqle) { 758 log.error("Sql Execution Error!", sqle); 759 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateTitle."); 760 } finally { 761 DBUtils.closeStatement(statement); 762 DBUtils.closeConnection(connection); 763 } 764 } 765 766 779 public void updateLastLogon(int memberID, Timestamp memberLastLogon, String memberLastIP) 781 throws ObjectNotFoundException, DatabaseException { 782 783 Connection connection = null; 784 PreparedStatement statement = null; 785 StringBuffer sql = new StringBuffer (512); 786 sql.append("UPDATE " + TABLE_NAME + " SET MemberLastLogon = ? , MemberLastIP = ? "); 787 sql.append(" WHERE MemberID = ?"); 788 try { 789 connection = DBUtils.getConnection(); 790 statement = connection.prepareStatement(sql.toString()); 791 792 statement.setTimestamp(1, memberLastLogon); 794 statement.setString(2, memberLastIP); 795 796 statement.setInt(3, memberID); 798 799 if (statement.executeUpdate() != 1) { 800 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 801 } 802 m_dirty = true; 803 } catch(SQLException sqle) { 804 log.error("Sql Execution Error!", sqle); 805 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateLastLogon."); 806 } finally { 807 DBUtils.closeStatement(statement); 808 DBUtils.closeConnection(connection); 809 } 810 } 811 812 825 public String getPassword(int memberID) 826 throws ObjectNotFoundException, DatabaseException { 827 828 Connection connection = null; 829 PreparedStatement statement = null; 830 ResultSet resultSet = null; 831 StringBuffer sql = new StringBuffer (512); 832 sql.append("SELECT MemberPassword"); 833 sql.append(" FROM " + TABLE_NAME); 834 sql.append(" WHERE MemberID = ?"); 835 try { 836 connection = DBUtils.getConnection(); 837 statement = connection.prepareStatement(sql.toString()); 838 statement.setInt(1, memberID); 839 resultSet = statement.executeQuery(); 840 if(!resultSet.next()) { 841 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ")."); 842 } 843 844 String retValue = resultSet.getString("MemberPassword"); 845 if (retValue == null) retValue = ""; return retValue; 847 } catch(SQLException sqle) { 848 log.error("Sql Execution Error!", sqle); 849 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getPassword(pk)."); 850 } finally { 851 DBUtils.closeResultSet(resultSet); 852 DBUtils.closeStatement(statement); 853 DBUtils.closeConnection(connection); 854 } 855 } 856 857 870 public String getTempPassword(int memberID) 871 throws ObjectNotFoundException, DatabaseException { 872 873 Connection connection = null; 874 PreparedStatement statement = null; 875 ResultSet resultSet = null; 876 StringBuffer sql = new StringBuffer (512); 877 sql.append("SELECT MemberTempPassword"); 878 sql.append(" FROM " + TABLE_NAME); 879 sql.append(" WHERE MemberID = ?"); 880 try { 881 connection = DBUtils.getConnection(); 882 statement = connection.prepareStatement(sql.toString()); 883 statement.setInt(1, memberID); 884 resultSet = statement.executeQuery(); 885 if(!resultSet.next()) { 886 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ")."); 887 } 888 889 return StringUtil.getEmptyStringIfNull(resultSet.getString("MemberTempPassword")); 890 } catch(SQLException sqle) { 891 log.error("Sql Execution Error!", sqle); 892 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getTempPassword(pk)."); 893 } finally { 894 DBUtils.closeResultSet(resultSet); 895 DBUtils.closeStatement(statement); 896 DBUtils.closeConnection(connection); 897 } 898 } 899 900 913 public String getActivateCode(int memberID) 914 throws ObjectNotFoundException, DatabaseException { 915 916 Connection connection = null; 917 PreparedStatement statement = null; 918 ResultSet resultSet = null; 919 StringBuffer sql = new StringBuffer (512); 920 sql.append("SELECT MemberActivateCode"); 921 sql.append(" FROM " + TABLE_NAME); 922 sql.append(" WHERE MemberID = ?"); 923 try { 924 connection = DBUtils.getConnection(); 925 statement = connection.prepareStatement(sql.toString()); 926 statement.setInt(1, memberID); 927 resultSet = statement.executeQuery(); 928 if(!resultSet.next()) { 929 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ")."); 930 } 931 932 String retValue = resultSet.getString("MemberActivateCode"); 933 if (retValue == null) retValue = ""; return retValue; 935 } catch(SQLException sqle) { 936 log.error("Sql Execution Error!", sqle); 937 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getActivateCode(pk)."); 938 } finally { 939 DBUtils.closeResultSet(resultSet); 940 DBUtils.closeStatement(statement); 941 DBUtils.closeConnection(connection); 942 } 943 } 944 945 959 public MemberBean getMember_forViewCurrentMember(int memberID) 960 throws ObjectNotFoundException, DatabaseException { 961 962 Connection connection = null; 963 PreparedStatement statement = null; 964 ResultSet resultSet = null; 965 StringBuffer sql = new StringBuffer (512); 966 sql.append("SELECT MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberLastLogon, MemberLastIP, MemberOption, MemberStatus, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode"); 967 sql.append(" FROM " + TABLE_NAME); 968 sql.append(" WHERE MemberID = ?"); 969 try { 970 connection = DBUtils.getConnection(); 971 statement = connection.prepareStatement(sql.toString()); 972 statement.setInt(1, memberID); 973 resultSet = statement.executeQuery(); 974 if(!resultSet.next()) { 975 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ")."); 976 } 977 978 MemberBean bean = new MemberBean(); 979 bean.setMemberID(memberID); 981 bean.setMemberName(resultSet.getString("MemberName")); 982 bean.setMemberEmail(resultSet.getString("MemberEmail")); 983 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); 984 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); 985 bean.setMemberViewCount(resultSet.getInt("MemberViewCount")); 986 bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); 987 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 988 bean.setMemberModifiedDate(resultSet.getTimestamp("MemberModifiedDate")); 989 bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon")); 990 bean.setMemberLastIP(resultSet.getString("MemberLastIP")); 991 bean.setMemberOption(resultSet.getInt("MemberOption")); 992 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 993 bean.setMemberMessageCount(resultSet.getInt("MemberMessageCount")); 994 bean.setMemberMessageOption(resultSet.getInt("MemberMessageOption")); 995 bean.setMemberPostsPerPage(resultSet.getInt("MemberPostsPerPage")); 996 bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount")); 997 bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount")); 998 bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars")); 999 bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints")); 1000 bean.setMemberTitle(resultSet.getString("MemberTitle")); 1001 bean.setMemberTimeZone(resultSet.getDouble("MemberTimeZone")); 1002 bean.setMemberSignature(resultSet.getString("MemberSignature")); 1003 bean.setMemberAvatar(resultSet.getString("MemberAvatar")); 1004 bean.setMemberSkin(resultSet.getString("MemberSkin")); 1005 bean.setMemberLanguage(resultSet.getString("MemberLanguage")); 1006 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 1007 bean.setMemberLastname(resultSet.getString("MemberLastname")); 1008 bean.setMemberGender(resultSet.getInt("MemberGender")); 1009 bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); 1010 bean.setMemberAddress(resultSet.getString("MemberAddress")); 1011 bean.setMemberCity(resultSet.getString("MemberCity")); 1012 bean.setMemberState(resultSet.getString("MemberState")); 1013 bean.setMemberCountry(resultSet.getString("MemberCountry")); 1014 bean.setMemberPhone(resultSet.getString("MemberPhone")); 1015 bean.setMemberMobile(resultSet.getString("MemberMobile")); 1016 bean.setMemberFax(resultSet.getString("MemberFax")); 1017 bean.setMemberCareer(resultSet.getString("MemberCareer")); 1018 bean.setMemberHomepage(resultSet.getString("MemberHomepage")); 1019 bean.setMemberYahoo(resultSet.getString("MemberYahoo")); 1020 bean.setMemberAol(resultSet.getString("MemberAol")); 1021 bean.setMemberIcq(resultSet.getString("MemberIcq")); 1022 bean.setMemberMsn(resultSet.getString("MemberMsn")); 1023 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); 1024 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); 1025 bean.setMemberActivateCode(resultSet.getString("MemberActivateCode")); 1026 return bean; 1027 } catch(SQLException sqle) { 1028 log.error("Sql Execution Error!", sqle); 1029 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMember_forViewCurrentMember(pk)."); 1030 } finally { 1031 DBUtils.closeResultSet(resultSet); 1032 DBUtils.closeStatement(statement); 1033 DBUtils.closeConnection(connection); 1034 } 1035 } 1036 1037 1051 public MemberBean getMember_forEditCurrentMember(int memberID) 1052 throws ObjectNotFoundException, DatabaseException { 1053 1054 Connection connection = null; 1055 PreparedStatement statement = null; 1056 ResultSet resultSet = null; 1057 StringBuffer sql = new StringBuffer (512); 1058 sql.append("SELECT MemberName, MemberEmailVisible, MemberNameVisible, MemberOption, MemberStatus, MemberMessageOption, MemberPostsPerPage, MemberTimeZone, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2"); 1059 sql.append(" FROM " + TABLE_NAME); 1060 sql.append(" WHERE MemberID = ?"); 1061 try { 1062 connection = DBUtils.getConnection(); 1063 statement = connection.prepareStatement(sql.toString()); 1064 statement.setInt(1, memberID); 1065 resultSet = statement.executeQuery(); 1066 if(!resultSet.next()) { 1067 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ")."); 1068 } 1069 1070 MemberBean bean = new MemberBean(); 1071 bean.setMemberID(memberID); 1073 bean.setMemberName(resultSet.getString("MemberName")); 1074 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); 1075 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); 1076 bean.setMemberOption(resultSet.getInt("MemberOption")); 1077 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 1078 bean.setMemberMessageOption(resultSet.getInt("MemberMessageOption")); 1079 bean.setMemberPostsPerPage(resultSet.getInt("MemberPostsPerPage")); 1080 bean.setMemberTimeZone(resultSet.getDouble("MemberTimeZone")); 1081 bean.setMemberSkin(resultSet.getString("MemberSkin")); 1082 bean.setMemberLanguage(resultSet.getString("MemberLanguage")); 1083 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 1084 bean.setMemberLastname(resultSet.getString("MemberLastname")); 1085 bean.setMemberGender(resultSet.getInt("MemberGender")); 1086 bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); 1087 bean.setMemberAddress(resultSet.getString("MemberAddress")); 1088 bean.setMemberCity(resultSet.getString("MemberCity")); 1089 bean.setMemberState(resultSet.getString("MemberState")); 1090 bean.setMemberCountry(resultSet.getString("MemberCountry")); 1091 bean.setMemberPhone(resultSet.getString("MemberPhone")); 1092 bean.setMemberMobile(resultSet.getString("MemberMobile")); 1093 bean.setMemberFax(resultSet.getString("MemberFax")); 1094 bean.setMemberCareer(resultSet.getString("MemberCareer")); 1095 bean.setMemberHomepage(resultSet.getString("MemberHomepage")); 1096 bean.setMemberYahoo(resultSet.getString("MemberYahoo")); 1097 bean.setMemberAol(resultSet.getString("MemberAol")); 1098 bean.setMemberIcq(resultSet.getString("MemberIcq")); 1099 bean.setMemberMsn(resultSet.getString("MemberMsn")); 1100 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); 1101 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); 1102 return bean; 1103 } catch(SQLException sqle) { 1104 log.error("Sql Execution Error!", sqle); 1105 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMember_forEditCurrentMember(pk)."); 1106 } finally { 1107 DBUtils.closeResultSet(resultSet); 1108 DBUtils.closeStatement(statement); 1109 DBUtils.closeConnection(connection); 1110 } 1111 } 1112 1113 1131 public MemberBean getMember_forPublic(int memberID) 1132 throws ObjectNotFoundException, DatabaseException { 1133 1134 Connection connection = null; 1135 PreparedStatement statement = null; 1136 ResultSet resultSet = null; 1137 StringBuffer sql = new StringBuffer (512); 1138 sql.append("SELECT MemberID, MemberName, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode, MemberFirstIP, MemberLastIP, MemberLanguage"); 1139 sql.append(" FROM " + TABLE_NAME); 1140 sql.append(" WHERE MemberID = ?"); 1141 try { 1142 connection = DBUtils.getConnection(); 1143 statement = connection.prepareStatement(sql.toString()); 1144 statement.setInt(1, memberID); 1145 resultSet = statement.executeQuery(); 1146 if(!resultSet.next()) { 1147 throw new ObjectNotFoundException("Cannot find the row in table Member where primary key = (" + memberID + ")."); 1148 } 1149 1150 MemberBean bean = new MemberBean(); 1151 bean.setMemberID(resultSet.getInt("MemberID")); 1154 bean.setMemberName(resultSet.getString("MemberName")); 1155 bean.setMemberFirstEmail(resultSet.getString("MemberFirstEmail")); 1156 bean.setMemberEmail(resultSet.getString("MemberEmail")); 1157 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); 1158 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); 1159 bean.setMemberViewCount(resultSet.getInt("MemberViewCount")); 1160 bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); 1161 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 1162 bean.setMemberModifiedDate(resultSet.getTimestamp("MemberModifiedDate")); 1163 bean.setMemberExpireDate(resultSet.getTimestamp("MemberExpireDate")); 1164 bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon")); 1165 bean.setMemberOption(resultSet.getInt("MemberOption")); 1166 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 1167 bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount")); 1168 bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount")); 1169 bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars")); 1170 bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints")); 1171 bean.setMemberTitle(resultSet.getString("MemberTitle")); 1172 bean.setMemberSignature(resultSet.getString("MemberSignature")); 1173 bean.setMemberAvatar(resultSet.getString("MemberAvatar")); 1174 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 1175 bean.setMemberLastname(resultSet.getString("MemberLastname")); 1176 bean.setMemberGender(resultSet.getInt("MemberGender")); 1177 bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); 1178 bean.setMemberLanguage(resultSet.getString("MemberLanguage")); 1179 bean.setMemberAddress(resultSet.getString("MemberAddress")); 1180 bean.setMemberCity(resultSet.getString("MemberCity")); 1181 bean.setMemberState(resultSet.getString("MemberState")); 1182 bean.setMemberCountry(resultSet.getString("MemberCountry")); 1183 bean.setMemberPhone(resultSet.getString("MemberPhone")); 1184 bean.setMemberMobile(resultSet.getString("MemberMobile")); 1185 bean.setMemberFax(resultSet.getString("MemberFax")); 1186 bean.setMemberCareer(resultSet.getString("MemberCareer")); 1187 bean.setMemberHomepage(resultSet.getString("MemberHomepage")); 1188 bean.setMemberYahoo(resultSet.getString("MemberYahoo")); 1189 bean.setMemberAol(resultSet.getString("MemberAol")); 1190 bean.setMemberIcq(resultSet.getString("MemberIcq")); 1191 bean.setMemberMsn(resultSet.getString("MemberMsn")); 1192 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); 1193 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); 1194 bean.setMemberActivateCode(resultSet.getString("MemberActivateCode")); 1195 bean.setMemberFirstIP(resultSet.getString("MemberFirstIP")); 1196 bean.setMemberLastIP(resultSet.getString("MemberLastIP")); 1197 return bean; 1198 } catch(SQLException sqle) { 1199 log.error("Sql Execution Error!", sqle); 1200 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMember_forPublic(pk)."); 1201 } finally { 1202 DBUtils.closeResultSet(resultSet); 1203 DBUtils.closeStatement(statement); 1204 DBUtils.closeConnection(connection); 1205 } 1206 } 1207 1208 1214 public int getNumberOfMembers() 1215 throws AssertionException, DatabaseException { 1216 1217 Connection connection = null; 1218 PreparedStatement statement = null; 1219 ResultSet resultSet = null; 1220 StringBuffer sql = new StringBuffer (512); 1221 sql.append("SELECT Count(*)"); 1222 sql.append(" FROM " + TABLE_NAME); 1223 try { 1225 connection = DBUtils.getConnection(); 1226 statement = connection.prepareStatement(sql.toString()); 1227 resultSet = statement.executeQuery(); 1228 if (!resultSet.next()) { 1229 throw new AssertionException("Assertion in MemberDAOImplJDBC.getNumberOfMembers."); 1230 } 1231 return resultSet.getInt(1); 1232 } catch(SQLException sqle) { 1233 log.error("Sql Execution Error!", sqle); 1234 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers."); 1235 } finally { 1236 DBUtils.closeResultSet(resultSet); 1237 DBUtils.closeStatement(statement); 1238 DBUtils.closeConnection(connection); 1239 } 1240 } 1241 1242 1248 public int getNumberOfMembers_inMemberStatus(int memberStatus) 1249 throws AssertionException, DatabaseException { 1250 1251 Connection connection = null; 1252 PreparedStatement statement = null; 1253 ResultSet resultSet = null; 1254 StringBuffer sql = new StringBuffer (512); 1255 sql.append("SELECT Count(*)"); 1256 sql.append(" FROM " + TABLE_NAME); 1257 sql.append(" WHERE MemberStatus = ? "); 1258 try { 1259 connection = DBUtils.getConnection(); 1260 statement = connection.prepareStatement(sql.toString()); 1261 statement.setInt(1, memberStatus); 1262 resultSet = statement.executeQuery(); 1263 if (!resultSet.next()) { 1264 throw new AssertionException("Assertion in MemberDAOImplJDBC.getNumberOfMembers_inMemberStatus."); 1265 } 1266 return resultSet.getInt(1); 1267 } catch(SQLException sqle) { 1268 log.error("Sql Execution Error!", sqle); 1269 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers_inMemberStatus."); 1270 } finally { 1271 DBUtils.closeResultSet(resultSet); 1272 DBUtils.closeStatement(statement); 1273 DBUtils.closeConnection(connection); 1274 } 1275 } 1276 1277 1283 public int getNumberOfMembers_inActivationStatus(boolean activated) 1284 throws AssertionException, DatabaseException { 1285 1286 Connection connection = null; 1287 PreparedStatement statement = null; 1288 ResultSet resultSet = null; 1289 StringBuffer sql = new StringBuffer (512); 1290 sql.append("SELECT Count(*)"); 1291 sql.append(" FROM " + TABLE_NAME); 1292 if (activated) { 1293 sql.append(" WHERE MemberActivateCode = 'activated' "); 1294 } else { 1295 sql.append(" WHERE (MemberActivateCode <> 'activated') OR (MemberActivateCode IS NULL) "); 1297 } 1298 try { 1299 connection = DBUtils.getConnection(); 1300 statement = connection.prepareStatement(sql.toString()); 1301 resultSet = statement.executeQuery(); 1302 if (!resultSet.next()) { 1303 throw new AssertionException("Assertion in MemberDAOImplJDBC.getNumberOfMembers_inActivationStatus."); 1304 } 1305 return resultSet.getInt(1); 1306 } catch(SQLException sqle) { 1307 log.error("Sql Execution Error!", sqle); 1308 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers_inActivationStatus."); 1309 } finally { 1310 DBUtils.closeResultSet(resultSet); 1311 DBUtils.closeStatement(statement); 1312 DBUtils.closeConnection(connection); 1313 } 1314 } 1315 1316 1317 1320 1321 1322 public final int getMemberIDFromMemberName(String memberName) 1323 throws ObjectNotFoundException, DatabaseException { 1324 1325 Connection connection = null; 1326 PreparedStatement statement = null; 1327 ResultSet resultSet = null; 1328 String sql = "SELECT MemberID FROM " + TABLE_NAME + " WHERE MemberName = ?"; 1329 try { 1330 connection = DBUtils.getConnection(); 1331 statement = connection.prepareStatement(sql); 1332 statement.setString(1, memberName); 1333 resultSet = statement.executeQuery(); 1334 if(!resultSet.next()) { 1335 throw new ObjectNotFoundException("Cannot find the row in table Member where MemberName = " + memberName); 1336 } 1337 return resultSet.getInt(1); 1338 } catch(SQLException sqle) { 1339 log.error("Sql Execution Error!", sqle); 1340 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMemberIDFromMemberName."); 1341 } finally { 1342 DBUtils.closeResultSet(resultSet); 1343 DBUtils.closeStatement(statement); 1344 DBUtils.closeConnection(connection); 1345 } 1346 } 1347 1348 1349 public final int getMemberIDFromMemberEmail(String memberEmail) 1350 throws ObjectNotFoundException, DatabaseException { 1351 1352 Connection connection = null; 1353 PreparedStatement statement = null; 1354 ResultSet resultSet = null; 1355 String sql = "SELECT MemberID FROM " + TABLE_NAME + " WHERE MemberEmail = ?"; 1356 try { 1357 connection = DBUtils.getConnection(); 1358 statement = connection.prepareStatement(sql); 1359 statement.setString(1, memberEmail); 1360 resultSet = statement.executeQuery(); 1361 if(!resultSet.next()) { 1362 throw new ObjectNotFoundException("Cannot find the row in table Member where MemberEmail = " + memberEmail); 1363 } 1364 return resultSet.getInt(1); 1365 } catch(SQLException sqle) { 1366 log.error("Sql Execution Error!", sqle); 1367 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMemberIDFromMemberEmail."); 1368 } finally { 1369 DBUtils.closeResultSet(resultSet); 1370 DBUtils.closeStatement(statement); 1371 DBUtils.closeConnection(connection); 1372 } 1373 } 1374 1375 public Collection getMembers_withSortSupport_limit(int offset, int rowsToReturn, String sort, String order) 1376 throws IllegalArgumentException , DatabaseException { 1377 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) { 1378 return getBeans_withSortSupport_limit_mysql(offset, rowsToReturn, sort, order); 1379 } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) { 1380 return getBeans_withSortSupport_limit_noscroll(offset, rowsToReturn, sort, order); 1381 } 1382 return getBeans_withSortSupport_limit_general(offset, rowsToReturn, sort, order); 1383 } 1384 1385 1399 1402 1403 private Collection getBeans_withSortSupport_limit_mysql(int offset, int rowsToReturn, String sort, String order) 1404 throws IllegalArgumentException , DatabaseException { 1405 1406 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed."); 1407 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed."); 1408 1414 if ((!sort.equals("MemberID")) && 1415 (!sort.equals("MemberName")) && 1416 (!sort.equals("MemberFirstname")) && 1417 (!sort.equals("MemberLastname")) && 1418 (!sort.equals("MemberGender")) && 1419 (!sort.equals("MemberBirthday")) && 1420 (!sort.equals("MemberCreationDate")) && 1421 (!sort.equals("MemberLastLogon")) && 1422 (!sort.equals("MemberViewCount")) && 1423 (!sort.equals("MemberPostCount")) ) { 1424 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the criteria '" + sort + "'."); 1425 } 1426 1427 if ((!order.equals("ASC")) && 1428 (!order.equals("DESC")) ) { 1429 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the order '" + order + "'."); 1430 } 1431 1432 Connection connection = null; 1433 PreparedStatement statement = null; 1434 ResultSet resultSet = null; 1435 Collection retValue = new ArrayList (); 1436 StringBuffer sql = new StringBuffer (512); 1437 sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode"); 1438 sql.append(" FROM " + TABLE_NAME); 1439 sql.append(" ORDER BY " + sort + " " + order); sql.append(" LIMIT ?, ?"); 1441 1442 try { 1443 connection = DBUtils.getConnection(); 1444 statement = connection.prepareStatement(sql.toString()); 1445 statement.setInt(1, offset); 1446 statement.setInt(2, rowsToReturn); 1447 resultSet = statement.executeQuery(); 1448 while (resultSet.next()) { 1449 MemberBean bean = new MemberBean(); 1450 bean.setMemberID(resultSet.getInt("MemberID")); 1451 bean.setMemberName(resultSet.getString("MemberName")); 1452 bean.setMemberEmail(resultSet.getString("MemberEmail")); 1453 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); 1454 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); 1455 bean.setMemberViewCount(resultSet.getInt("MemberViewCount")); 1456 bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); 1457 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 1458 bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon")); 1459 bean.setMemberOption(resultSet.getInt("MemberOption")); 1460 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 1461 bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount")); 1462 bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount")); 1463 bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars")); 1464 bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints")); 1465 bean.setMemberTitle(resultSet.getString("MemberTitle")); 1466 bean.setMemberSignature(resultSet.getString("MemberSignature")); 1467 bean.setMemberAvatar(resultSet.getString("MemberAvatar")); 1468 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 1469 bean.setMemberLastname(resultSet.getString("MemberLastname")); 1470 bean.setMemberGender(resultSet.getInt("MemberGender")); 1471 bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); 1472 bean.setMemberAddress(resultSet.getString("MemberAddress")); 1473 bean.setMemberCity(resultSet.getString("MemberCity")); 1474 bean.setMemberState(resultSet.getString("MemberState")); 1475 bean.setMemberCountry(resultSet.getString("MemberCountry")); 1476 bean.setMemberPhone(resultSet.getString("MemberPhone")); 1477 bean.setMemberMobile(resultSet.getString("MemberMobile")); 1478 bean.setMemberFax(resultSet.getString("MemberFax")); 1479 bean.setMemberCareer(resultSet.getString("MemberCareer")); 1480 bean.setMemberHomepage(resultSet.getString("MemberHomepage")); 1481 bean.setMemberYahoo(resultSet.getString("MemberYahoo")); 1482 bean.setMemberAol(resultSet.getString("MemberAol")); 1483 bean.setMemberIcq(resultSet.getString("MemberIcq")); 1484 bean.setMemberMsn(resultSet.getString("MemberMsn")); 1485 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); 1486 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); 1487 bean.setMemberActivateCode(resultSet.getString("MemberActivateCode")); 1488 retValue.add(bean); 1489 } 1490 return retValue; 1491 } catch(SQLException sqle) { 1492 log.error("Sql Execution Error!", sqle); 1493 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getBeans_withSortSupport_limit_mysql."); 1494 } finally { 1495 DBUtils.closeResultSet(resultSet); 1496 DBUtils.closeStatement(statement); 1497 DBUtils.closeConnection(connection); 1498 } 1499 } 1500 1501 1515 1518 1519 private Collection getBeans_withSortSupport_limit_noscroll(int offset, int rowsToReturn, String sort, String order) 1520 throws IllegalArgumentException , DatabaseException { 1521 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed."); 1522 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed."); 1523 1529 if ((!sort.equals("MemberID")) && 1530 (!sort.equals("MemberName")) && 1531 (!sort.equals("MemberFirstname")) && 1532 (!sort.equals("MemberLastname")) && 1533 (!sort.equals("MemberGender")) && 1534 (!sort.equals("MemberBirthday")) && 1535 (!sort.equals("MemberCreationDate")) && 1536 (!sort.equals("MemberLastLogon")) && 1537 (!sort.equals("MemberViewCount")) && 1538 (!sort.equals("MemberPostCount")) ) { 1539 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the criteria '" + sort + "'."); 1540 } 1541 1542 if ((!order.equals("ASC")) && 1543 (!order.equals("DESC")) ) { 1544 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the order '" + order + "'."); 1545 } 1546 1547 Connection connection = null; 1548 PreparedStatement statement = null; 1549 ResultSet resultSet = null; 1550 Collection retValue = new ArrayList (); 1551 StringBuffer sql = new StringBuffer (512); 1552 sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode"); 1553 sql.append(" FROM " + TABLE_NAME); 1554 sql.append(" ORDER BY " + sort + " " + order); 1556 try { 1557 connection = DBUtils.getConnection(); 1558 statement = connection.prepareStatement(sql.toString()); 1559 statement.setMaxRows(offset + rowsToReturn); 1560 resultSet = statement.executeQuery(); 1561 int rowIndex = -1; 1562 while (resultSet.next()) { 1563 rowIndex++; 1564 if (rowIndex < offset) continue; 1565 MemberBean bean = new MemberBean(); 1566 bean.setMemberID(resultSet.getInt("MemberID")); 1567 bean.setMemberName(resultSet.getString("MemberName")); 1568 bean.setMemberEmail(resultSet.getString("MemberEmail")); 1569 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); 1570 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); 1571 bean.setMemberViewCount(resultSet.getInt("MemberViewCount")); 1572 bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); 1573 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 1574 bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon")); 1575 bean.setMemberOption(resultSet.getInt("MemberOption")); 1576 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 1577 bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount")); 1578 bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount")); 1579 bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars")); 1580 bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints")); 1581 bean.setMemberTitle(resultSet.getString("MemberTitle")); 1582 bean.setMemberSignature(resultSet.getString("MemberSignature")); 1583 bean.setMemberAvatar(resultSet.getString("MemberAvatar")); 1584 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 1585 bean.setMemberLastname(resultSet.getString("MemberLastname")); 1586 bean.setMemberGender(resultSet.getInt("MemberGender")); 1587 bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); 1588 bean.setMemberAddress(resultSet.getString("MemberAddress")); 1589 bean.setMemberCity(resultSet.getString("MemberCity")); 1590 bean.setMemberState(resultSet.getString("MemberState")); 1591 bean.setMemberCountry(resultSet.getString("MemberCountry")); 1592 bean.setMemberPhone(resultSet.getString("MemberPhone")); 1593 bean.setMemberMobile(resultSet.getString("MemberMobile")); 1594 bean.setMemberFax(resultSet.getString("MemberFax")); 1595 bean.setMemberCareer(resultSet.getString("MemberCareer")); 1596 bean.setMemberHomepage(resultSet.getString("MemberHomepage")); 1597 bean.setMemberYahoo(resultSet.getString("MemberYahoo")); 1598 bean.setMemberAol(resultSet.getString("MemberAol")); 1599 bean.setMemberIcq(resultSet.getString("MemberIcq")); 1600 bean.setMemberMsn(resultSet.getString("MemberMsn")); 1601 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); 1602 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); 1603 bean.setMemberActivateCode(resultSet.getString("MemberActivateCode")); 1604 retValue.add(bean); 1605 if (retValue.size() == rowsToReturn) break; } 1607 return retValue; 1608 } catch(SQLException sqle) { 1609 log.error("Sql Execution Error!", sqle); 1610 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getBeans_withSortSupport_limit_noscroll."); 1611 } finally { 1612 DBUtils.closeResultSet(resultSet); 1613 DBUtils.resetStatement(statement); 1614 DBUtils.closeStatement(statement); 1615 DBUtils.closeConnection(connection); 1616 } 1617 } 1618 1619 1633 1636 1637 private Collection getBeans_withSortSupport_limit_general(int offset, int rowsToReturn, String sort, String order) 1638 throws IllegalArgumentException , DatabaseException { 1639 1640 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed."); 1641 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed."); 1642 1648 if ((!sort.equals("MemberID")) && 1649 (!sort.equals("MemberName")) && 1650 (!sort.equals("MemberFirstname")) && 1651 (!sort.equals("MemberLastname")) && 1652 (!sort.equals("MemberGender")) && 1653 (!sort.equals("MemberBirthday")) && 1654 (!sort.equals("MemberCreationDate")) && 1655 (!sort.equals("MemberLastLogon")) && 1656 (!sort.equals("MemberViewCount")) && 1657 (!sort.equals("MemberPostCount")) ) { 1658 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the criteria '" + sort + "'."); 1659 } 1660 1661 if ((!order.equals("ASC")) && 1662 (!order.equals("DESC")) ) { 1663 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the order '" + order + "'."); 1664 } 1665 1666 Connection connection = null; 1667 PreparedStatement statement = null; 1668 ResultSet resultSet = null; 1669 Collection retValue = new ArrayList (); 1670 StringBuffer sql = new StringBuffer (512); 1671 sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode"); 1672 sql.append(" FROM " + TABLE_NAME); 1673 sql.append(" ORDER BY " + sort + " " + order); 1675 try { 1676 connection = DBUtils.getConnection(); 1677 statement = connection.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 1678 statement.setMaxRows(offset + rowsToReturn); 1679 try { 1680 statement.setFetchSize(rowsToReturn); 1681 } catch (SQLException sqle) { 1682 } 1684 resultSet = statement.executeQuery(); 1685 boolean loop = resultSet.absolute(offset + 1); while (loop) { 1687 MemberBean bean = new MemberBean(); 1688 bean.setMemberID(resultSet.getInt("MemberID")); 1689 bean.setMemberName(resultSet.getString("MemberName")); 1690 bean.setMemberEmail(resultSet.getString("MemberEmail")); 1691 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); 1692 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); 1693 bean.setMemberViewCount(resultSet.getInt("MemberViewCount")); 1694 bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); 1695 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 1696 bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon")); 1697 bean.setMemberOption(resultSet.getInt("MemberOption")); 1698 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 1699 bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount")); 1700 bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount")); 1701 bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars")); 1702 bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints")); 1703 bean.setMemberTitle(resultSet.getString("MemberTitle")); 1704 bean.setMemberSignature(resultSet.getString("MemberSignature")); 1705 bean.setMemberAvatar(resultSet.getString("MemberAvatar")); 1706 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 1707 bean.setMemberLastname(resultSet.getString("MemberLastname")); 1708 bean.setMemberGender(resultSet.getInt("MemberGender")); 1709 bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); 1710 bean.setMemberAddress(resultSet.getString("MemberAddress")); 1711 bean.setMemberCity(resultSet.getString("MemberCity")); 1712 bean.setMemberState(resultSet.getString("MemberState")); 1713 bean.setMemberCountry(resultSet.getString("MemberCountry")); 1714 bean.setMemberPhone(resultSet.getString("MemberPhone")); 1715 bean.setMemberMobile(resultSet.getString("MemberMobile")); 1716 bean.setMemberFax(resultSet.getString("MemberFax")); 1717 bean.setMemberCareer(resultSet.getString("MemberCareer")); 1718 bean.setMemberHomepage(resultSet.getString("MemberHomepage")); 1719 bean.setMemberYahoo(resultSet.getString("MemberYahoo")); 1720 bean.setMemberAol(resultSet.getString("MemberAol")); 1721 bean.setMemberIcq(resultSet.getString("MemberIcq")); 1722 bean.setMemberMsn(resultSet.getString("MemberMsn")); 1723 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); 1724 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); 1725 bean.setMemberActivateCode(resultSet.getString("MemberActivateCode")); 1726 retValue.add(bean); 1727 if (retValue.size() == rowsToReturn) break; loop = resultSet.next(); 1729 } return retValue; 1731 } catch(SQLException sqle) { 1732 log.error("Sql Execution Error!", sqle); 1733 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getBeans_withSortSupport_limit_general."); 1734 } finally { 1735 DBUtils.closeResultSet(resultSet); 1736 DBUtils.resetStatement(statement); 1737 DBUtils.closeStatement(statement); 1738 DBUtils.closeConnection(connection); 1739 } 1740 } 1741 1742 1756 1759 public Collection getEnableMembers_inActivationStatus(String kind) 1760 throws IllegalArgumentException , DatabaseException { 1761 1762 Connection connection = null; 1763 PreparedStatement statement = null; 1764 ResultSet resultSet = null; 1765 Collection retValue = new ArrayList (); 1766 StringBuffer sql = new StringBuffer (512); 1767 1768 sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode"); 1769 sql.append(" FROM " + TABLE_NAME); 1770 if (kind.equals("activated")) { 1771 sql.append(" WHERE (MemberStatus = 0) AND (MemberActivateCode = 'activated') "); 1772 } else if (kind.equals("nonactivated")) { 1773 sql.append(" WHERE (MemberStatus = 0) AND ((MemberActivateCode <> 'activated') OR (MemberActivateCode IS NULL))"); 1775 } else if (kind.equals("all")) { 1776 sql.append(" WHERE (MemberStatus = 0)"); 1777 } else { 1778 throw new IllegalArgumentException ("Cannot process activation kind = " + kind); 1779 } 1780 1781 try { 1782 connection = DBUtils.getConnection(); 1783 statement = connection.prepareStatement(sql.toString()); 1784 1785 resultSet = statement.executeQuery(); 1786 while (resultSet.next()) { 1787 MemberBean bean = new MemberBean(); 1788 bean.setMemberID(resultSet.getInt("MemberID")); 1789 bean.setMemberName(resultSet.getString("MemberName")); 1790 bean.setMemberEmail(resultSet.getString("MemberEmail")); 1791 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); 1792 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); 1793 bean.setMemberViewCount(resultSet.getInt("MemberViewCount")); 1794 bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); 1795 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 1796 bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon")); 1797 bean.setMemberOption(resultSet.getInt("MemberOption")); 1798 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 1799 bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount")); 1800 bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount")); 1801 bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars")); 1802 bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints")); 1803 bean.setMemberTitle(resultSet.getString("MemberTitle")); 1804 bean.setMemberSignature(resultSet.getString("MemberSignature")); 1805 bean.setMemberAvatar(resultSet.getString("MemberAvatar")); 1806 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 1807 bean.setMemberLastname(resultSet.getString("MemberLastname")); 1808 bean.setMemberGender(resultSet.getInt("MemberGender")); 1809 bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); 1810 bean.setMemberAddress(resultSet.getString("MemberAddress")); 1811 bean.setMemberCity(resultSet.getString("MemberCity")); 1812 bean.setMemberState(resultSet.getString("MemberState")); 1813 bean.setMemberCountry(resultSet.getString("MemberCountry")); 1814 bean.setMemberPhone(resultSet.getString("MemberPhone")); 1815 bean.setMemberMobile(resultSet.getString("MemberMobile")); 1816 bean.setMemberFax(resultSet.getString("MemberFax")); 1817 bean.setMemberCareer(resultSet.getString("MemberCareer")); 1818 bean.setMemberHomepage(resultSet.getString("MemberHomepage")); 1819 bean.setMemberYahoo(resultSet.getString("MemberYahoo")); 1820 bean.setMemberAol(resultSet.getString("MemberAol")); 1821 bean.setMemberIcq(resultSet.getString("MemberIcq")); 1822 bean.setMemberMsn(resultSet.getString("MemberMsn")); 1823 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); 1824 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); 1825 bean.setMemberActivateCode(resultSet.getString("MemberActivateCode")); 1826 retValue.add(bean); 1827 } return retValue; 1829 } catch(SQLException sqle) { 1830 log.error("Sql Execution Error!", sqle); 1831 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getEnableMembers_inActivationStatus."); 1832 } finally { 1833 DBUtils.closeResultSet(resultSet); 1834 DBUtils.resetStatement(statement); 1835 DBUtils.closeStatement(statement); 1836 DBUtils.closeConnection(connection); 1837 } 1838 } 1839 1840 1841 1854 public void updateStatus(int memberID, int memberStatus) 1856 throws ObjectNotFoundException, DatabaseException { 1857 1858 Connection connection = null; 1859 PreparedStatement statement = null; 1860 StringBuffer sql = new StringBuffer (512); 1861 sql.append("UPDATE " + TABLE_NAME + " SET MemberStatus = ?"); 1862 sql.append(" WHERE MemberID = ?"); 1863 try { 1864 connection = DBUtils.getConnection(); 1865 statement = connection.prepareStatement(sql.toString()); 1866 1867 statement.setInt(1, memberStatus); 1869 1870 statement.setInt(2, memberID); 1872 1873 if (statement.executeUpdate() != 1) { 1874 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 1875 } 1876 setDirty(true); 1877 } catch(SQLException sqle) { 1878 log.error("Sql Execution Error!", sqle); 1879 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateStatus."); 1880 } finally { 1881 DBUtils.closeStatement(statement); 1882 DBUtils.closeConnection(connection); 1883 } 1884 } 1885 1886 public void updatePostCount(int memberID, int memberPostCount) 1888 throws ObjectNotFoundException, DatabaseException { 1889 1890 Connection connection = null; 1891 PreparedStatement statement = null; 1892 StringBuffer sql = new StringBuffer (512); 1893 sql.append("UPDATE " + TABLE_NAME + " SET MemberPostCount = ?"); 1894 sql.append(" WHERE MemberID = ?"); 1895 try { 1896 connection = DBUtils.getConnection(); 1897 statement = connection.prepareStatement(sql.toString()); 1898 1899 statement.setInt(1, memberPostCount); 1901 1902 statement.setInt(2, memberID); 1904 1905 if (statement.executeUpdate() != 1) { 1906 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 1907 } 1908 setDirty(true); 1909 } catch(SQLException sqle) { 1910 log.error("Sql Execution Error!", sqle); 1911 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updatePostCount."); 1912 } finally { 1913 DBUtils.closeStatement(statement); 1914 DBUtils.closeConnection(connection); 1915 } 1916 } 1917 1918 1921 public void increaseViewCount(int memberID) 1922 throws DatabaseException, ObjectNotFoundException { 1923 1924 Connection connection = null; 1925 PreparedStatement statement = null; 1926 String sql = "UPDATE " + TABLE_NAME + " SET MemberViewCount = MemberViewCount + 1 WHERE MemberID = ?"; 1927 try { 1928 connection = DBUtils.getConnection(); 1929 statement = connection.prepareStatement(sql); 1930 statement.setInt(1, memberID); 1931 if (statement.executeUpdate() != 1) { 1932 throw new ObjectNotFoundException("Cannot update the MemberViewCount in table Member. Please contact Web site Administrator."); 1933 } 1934 setDirty(true); 1937 } catch (SQLException sqle) { 1938 log.error("Sql Execution Error!", sqle); 1939 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.increaseViewCount."); 1940 } finally { 1941 DBUtils.closeStatement(statement); 1942 DBUtils.closeConnection(connection); 1943 } 1944 } 1945 1946 1949 public void increasePostCount(int memberID) 1950 throws DatabaseException, ObjectNotFoundException { 1951 1952 Connection connection = null; 1953 PreparedStatement statement = null; 1954 String sql = "UPDATE " + TABLE_NAME + " SET MemberPostCount = MemberPostCount + 1 WHERE MemberID = ?"; 1955 try { 1956 connection = DBUtils.getConnection(); 1957 statement = connection.prepareStatement(sql); 1958 statement.setInt(1, memberID); 1959 if (statement.executeUpdate() != 1) { 1960 throw new ObjectNotFoundException("Cannot update the MemberPostCount in table Member. Please contact Web site Administrator."); 1961 } 1962 setDirty(true); 1965 } catch (SQLException sqle) { 1966 log.error("Sql Execution Error!", sqle); 1967 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.increasePostCount."); 1968 } finally { 1969 DBUtils.closeStatement(statement); 1970 DBUtils.closeConnection(connection); 1971 } 1972 } 1973 1974 public Collection getMembers_inExpire_limit(Timestamp expireDate, int offset, int rowsToReturn, String sort, String order) 1975 throws IllegalArgumentException , DatabaseException { 1976 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed."); 1977 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed."); 1978 1979 1985 if ((!sort.equals("MemberID")) && 1986 (!sort.equals("MemberName")) && 1987 (!sort.equals("MemberFirstname")) && 1988 (!sort.equals("MemberLastname")) && 1989 (!sort.equals("MemberGender")) && 1990 (!sort.equals("MemberBirthday")) && 1991 (!sort.equals("MemberCreationDate")) && 1992 (!sort.equals("MemberExpireDate")) && 1993 (!sort.equals("MemberLastLogon")) && 1994 (!sort.equals("MemberViewCount")) && 1995 (!sort.equals("MemberPostCount")) ) { 1996 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the criteria '" + sort + "'."); 1997 } 1998 1999 if ((!order.equals("ASC")) && 2000 (!order.equals("DESC")) ) { 2001 throw new IllegalArgumentException ("Cannot sort, reason: dont understand the order '" + order + "'."); 2002 } 2003 2004 Connection connection = null; 2005 PreparedStatement statement = null; 2006 ResultSet resultSet = null; 2007 Collection retValue = new ArrayList (); 2008 StringBuffer sql = new StringBuffer (512); 2009 sql.append("SELECT MemberID, MemberName, MemberCreationDate, MemberExpireDate, MemberStatus"); 2010 sql.append(" FROM " + TABLE_NAME); 2011 sql.append(" WHERE MemberExpireDate <= ? OR MemberExpireDate IS NULL"); 2012 sql.append(" ORDER BY " + sort + " " + order); sql.append(" LIMIT ?, ?"); 2014 try { 2015 connection = DBUtils.getConnection(); 2016 statement = connection.prepareStatement(sql.toString()); 2017 statement.setTimestamp(1, expireDate); 2018 statement.setInt(2, offset); 2019 statement.setInt(3, rowsToReturn); 2020 resultSet = statement.executeQuery(); 2021 while (resultSet.next()) { 2022 MemberBean bean = new MemberBean(); 2023 bean.setMemberID(resultSet.getInt("MemberID")); 2024 bean.setMemberName(resultSet.getString("MemberName")); 2025 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 2026 bean.setMemberExpireDate(resultSet.getTimestamp("MemberExpireDate")); 2027 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 2028 retValue.add(bean); 2029 } 2030 return retValue; 2031 } catch(SQLException sqle) { 2032 log.error("Sql Execution Error!", sqle); 2033 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMembers_inExpire_limit."); 2034 } finally { 2035 DBUtils.closeResultSet(resultSet); 2036 DBUtils.closeStatement(statement); 2037 DBUtils.closeConnection(connection); 2038 } 2039 } 2040 2041 public int getNumberOfMembers_inExpire(Timestamp expireDate) 2042 throws AssertionException, DatabaseException { 2043 2044 Connection connection = null; 2045 PreparedStatement statement = null; 2046 ResultSet resultSet = null; 2047 StringBuffer sql = new StringBuffer (512); 2048 sql.append("SELECT Count(*)"); 2049 sql.append(" FROM " + TABLE_NAME); 2050 sql.append(" WHERE MemberExpireDate <= ? OR MemberExpireDate IS NULL"); 2051 try { 2052 connection = DBUtils.getConnection(); 2053 statement = connection.prepareStatement(sql.toString()); 2054 statement.setTimestamp(1, expireDate); 2055 resultSet = statement.executeQuery(); 2056 if (!resultSet.next()) { 2057 throw new AssertionException("Assertion in MemberDAOImplJDBC.getNumberOfMembers_inExpire."); 2058 } 2059 return resultSet.getInt(1); 2060 } catch(SQLException sqle) { 2061 log.error("Sql Execution Error!", sqle); 2062 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNumberOfMembers_inExpire."); 2063 } finally { 2064 DBUtils.closeResultSet(resultSet); 2065 DBUtils.closeStatement(statement); 2066 DBUtils.closeConnection(connection); 2067 } 2068 } 2069 2070 public void updateMember_expireDate(int memberID, Timestamp expireDate) 2072 throws ObjectNotFoundException, DatabaseException { 2073 2074 Connection connection = null; 2075 PreparedStatement statement = null; 2076 StringBuffer sql = new StringBuffer (512); 2077 sql.append("UPDATE " + TABLE_NAME + " SET MemberExpireDate = ?"); 2078 sql.append(" WHERE MemberID = ?"); 2079 try { 2080 connection = DBUtils.getConnection(); 2081 statement = connection.prepareStatement(sql.toString()); 2082 2083 statement.setTimestamp(1, expireDate); 2085 2086 statement.setInt(2, memberID); 2088 2089 if (statement.executeUpdate() != 1) { 2090 throw new ObjectNotFoundException("Cannot update table Member where primary key = (" + memberID + ")."); 2091 } 2092 m_dirty = true; 2093 } catch(SQLException sqle) { 2094 log.error("Sql Execution Error!", sqle); 2095 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.updateMember_expireDate."); 2096 } finally { 2097 DBUtils.closeStatement(statement); 2098 DBUtils.closeConnection(connection); 2099 } 2100 } 2101 2102 public Collection getMembers() 2103 throws DatabaseException { 2104 2105 Connection connection = null; 2106 Statement statement = null; 2107 ResultSet resultSet = null; 2108 Collection retValue = new ArrayList (); 2109 StringBuffer sql = new StringBuffer (512); 2110 sql.append("SELECT MemberID, MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode"); 2111 sql.append(" FROM " + TABLE_NAME); 2112 2113 try { 2114 connection = DBUtils.getConnection(); 2115 statement = connection.createStatement(); 2116 resultSet = statement.executeQuery(sql.toString()); 2117 while (resultSet.next()) { 2118 MemberBean bean = new MemberBean(); 2119 bean.setMemberID(resultSet.getInt("MemberID")); 2120 bean.setMemberName(resultSet.getString("MemberName")); 2121 bean.setMemberEmail(resultSet.getString("MemberEmail")); 2122 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); 2123 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); 2124 bean.setMemberViewCount(resultSet.getInt("MemberViewCount")); 2125 bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); 2126 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 2127 bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon")); 2128 bean.setMemberOption(resultSet.getInt("MemberOption")); 2129 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 2130 bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount")); 2131 bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount")); 2132 bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars")); 2133 bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints")); 2134 bean.setMemberTitle(resultSet.getString("MemberTitle")); 2135 bean.setMemberSignature(resultSet.getString("MemberSignature")); 2136 bean.setMemberAvatar(resultSet.getString("MemberAvatar")); 2137 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 2138 bean.setMemberLastname(resultSet.getString("MemberLastname")); 2139 bean.setMemberGender(resultSet.getInt("MemberGender")); 2140 bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); 2141 bean.setMemberAddress(resultSet.getString("MemberAddress")); 2142 bean.setMemberCity(resultSet.getString("MemberCity")); 2143 bean.setMemberState(resultSet.getString("MemberState")); 2144 bean.setMemberCountry(resultSet.getString("MemberCountry")); 2145 bean.setMemberPhone(resultSet.getString("MemberPhone")); 2146 bean.setMemberMobile(resultSet.getString("MemberMobile")); 2147 bean.setMemberFax(resultSet.getString("MemberFax")); 2148 bean.setMemberCareer(resultSet.getString("MemberCareer")); 2149 bean.setMemberHomepage(resultSet.getString("MemberHomepage")); 2150 bean.setMemberYahoo(resultSet.getString("MemberYahoo")); 2151 bean.setMemberAol(resultSet.getString("MemberAol")); 2152 bean.setMemberIcq(resultSet.getString("MemberIcq")); 2153 bean.setMemberMsn(resultSet.getString("MemberMsn")); 2154 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); 2155 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); 2156 bean.setMemberActivateCode(resultSet.getString("MemberActivateCode")); 2157 retValue.add(bean); 2158 } 2159 return retValue; 2160 } catch(SQLException sqle) { 2161 log.error("Sql Execution Error!", sqle); 2162 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMembers."); 2163 } finally { 2164 DBUtils.closeResultSet(resultSet); 2165 DBUtils.closeStatement(statement); 2166 DBUtils.closeConnection(connection); 2167 } 2168 } 2169 2170 public Collection getEnableMembers_inGroup(int groupID) 2171 throws DatabaseException { 2172 2173 Connection connection = null; 2174 PreparedStatement statement = null; 2175 ResultSet resultSet = null; 2176 Collection retValue = new ArrayList (); 2177 StringBuffer sql = new StringBuffer (512); 2178 2179 sql.append("SELECT m.MemberID, m.MemberName, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberViewCount, MemberPostCount, MemberCreationDate, MemberLastLogon, MemberOption, MemberStatus, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberSignature, MemberAvatar, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2, MemberActivateCode"); 2180 sql.append(" FROM " + TABLE_NAME + " m , " + MemberGroupDAO.TABLE_NAME + " mg"); 2181 sql.append(" WHERE (MemberStatus = 0) AND (mg.MemberID = m.MemberID) AND GroupID = ?"); 2182 try { 2183 connection = DBUtils.getConnection(); 2184 statement = connection.prepareStatement(sql.toString()); 2185 statement.setInt(1, groupID); 2186 resultSet = statement.executeQuery(); 2187 while (resultSet.next()) { 2188 MemberBean bean = new MemberBean(); 2189 bean.setMemberID(resultSet.getInt("MemberID")); 2190 bean.setMemberName(resultSet.getString("MemberName")); 2191 bean.setMemberEmail(resultSet.getString("MemberEmail")); 2192 bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); 2193 bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); 2194 bean.setMemberViewCount(resultSet.getInt("MemberViewCount")); 2195 bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); 2196 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 2197 bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon")); 2198 bean.setMemberOption(resultSet.getInt("MemberOption")); 2199 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 2200 bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount")); 2201 bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount")); 2202 bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars")); 2203 bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints")); 2204 bean.setMemberTitle(resultSet.getString("MemberTitle")); 2205 bean.setMemberSignature(resultSet.getString("MemberSignature")); 2206 bean.setMemberAvatar(resultSet.getString("MemberAvatar")); 2207 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 2208 bean.setMemberLastname(resultSet.getString("MemberLastname")); 2209 bean.setMemberGender(resultSet.getInt("MemberGender")); 2210 bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); 2211 bean.setMemberAddress(resultSet.getString("MemberAddress")); 2212 bean.setMemberCity(resultSet.getString("MemberCity")); 2213 bean.setMemberState(resultSet.getString("MemberState")); 2214 bean.setMemberCountry(resultSet.getString("MemberCountry")); 2215 bean.setMemberPhone(resultSet.getString("MemberPhone")); 2216 bean.setMemberMobile(resultSet.getString("MemberMobile")); 2217 bean.setMemberFax(resultSet.getString("MemberFax")); 2218 bean.setMemberCareer(resultSet.getString("MemberCareer")); 2219 bean.setMemberHomepage(resultSet.getString("MemberHomepage")); 2220 bean.setMemberYahoo(resultSet.getString("MemberYahoo")); 2221 bean.setMemberAol(resultSet.getString("MemberAol")); 2222 bean.setMemberIcq(resultSet.getString("MemberIcq")); 2223 bean.setMemberMsn(resultSet.getString("MemberMsn")); 2224 bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); 2225 bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); 2226 bean.setMemberActivateCode(resultSet.getString("MemberActivateCode")); 2227 retValue.add(bean); 2228 } return retValue; 2230 } catch(SQLException sqle) { 2231 log.error("Sql Execution Error!", sqle); 2232 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getMembers_inGroup."); 2233 } finally { 2234 DBUtils.closeResultSet(resultSet); 2235 DBUtils.resetStatement(statement); 2236 DBUtils.closeStatement(statement); 2237 DBUtils.closeConnection(connection); 2238 } 2239 } 2240 2241 public Collection getForumsAuthorizedMembers() throws DatabaseException { 2242 2243 Connection connection = null; 2244 PreparedStatement statement = null; 2245 ResultSet resultSet = null; 2246 Collection retValue = new ArrayList (); 2247 StringBuffer sql = new StringBuffer (512); 2248 2249 sql.append("SELECT DISTINCT m.MemberID, MemberName, MemberEmail, MemberLastname, MemberFirstname, MemberCreationDate"); 2250 sql.append(" FROM " + TABLE_NAME + " m , " + MemberForumDAO.TABLE_NAME + " mf"); 2251 sql.append(" WHERE (m.MemberID = mf.MemberID) "); 2252 sql.append(" ORDER BY m.MemberID ASC"); 2253 try { 2254 connection = DBUtils.getConnection(); 2255 statement = connection.prepareStatement(sql.toString()); 2256 resultSet = statement.executeQuery(); 2257 while (resultSet.next()) { 2258 MemberBean bean = new MemberBean(); 2259 bean.setMemberID(resultSet.getInt("MemberID")); 2260 bean.setMemberName(resultSet.getString("MemberName")); 2261 bean.setMemberEmail(resultSet.getString("MemberEmail")); 2262 bean.setMemberLastname(resultSet.getString("MemberLastname")); 2263 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 2264 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 2265 retValue.add(bean); 2266 } 2267 return retValue; 2268 } catch(SQLException sqle) { 2269 log.error("Sql Execution Error!", sqle); 2270 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getForumsAuthorizedMembers"); 2271 } finally { 2272 DBUtils.closeResultSet(resultSet); 2273 DBUtils.resetStatement(statement); 2274 DBUtils.closeStatement(statement); 2275 DBUtils.closeConnection(connection); 2276 } 2277 } 2278 2279 public Collection getAuthorizedMembers() throws DatabaseException { 2280 2281 Connection connection = null; 2282 PreparedStatement statement = null; 2283 ResultSet resultSet = null; 2284 Collection retValue = new ArrayList (); 2285 StringBuffer sql = new StringBuffer (512); 2286 2287 sql.append("SELECT DISTINCT m.MemberID, MemberName, MemberEmail, MemberLastname, MemberFirstname, MemberCreationDate"); 2288 sql.append(" FROM " + TABLE_NAME + " m , " + MemberPermissionDAO.TABLE_NAME + " mp"); 2289 sql.append(" WHERE (m.MemberID = mp.MemberID) "); 2290 sql.append(" ORDER BY m.MemberID ASC"); 2291 try { 2292 connection = DBUtils.getConnection(); 2293 statement = connection.prepareStatement(sql.toString()); 2294 resultSet = statement.executeQuery(); 2295 while (resultSet.next()) { 2296 MemberBean bean = new MemberBean(); 2297 bean.setMemberID(resultSet.getInt("MemberID")); 2298 bean.setMemberName(resultSet.getString("MemberName")); 2299 bean.setMemberEmail(resultSet.getString("MemberEmail")); 2300 bean.setMemberLastname(resultSet.getString("MemberLastname")); 2301 bean.setMemberFirstname(resultSet.getString("MemberFirstname")); 2302 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 2303 retValue.add(bean); 2304 } 2305 return retValue; 2306 } catch(SQLException sqle) { 2307 log.error("Sql Execution Error!", sqle); 2308 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getAuthorizedMembers."); 2309 } finally { 2310 DBUtils.closeResultSet(resultSet); 2311 DBUtils.resetStatement(statement); 2312 DBUtils.closeStatement(statement); 2313 DBUtils.closeConnection(connection); 2314 } 2315 } 2316 public Collection getNonActivatedNoPostMembers(Timestamp before) 2317 throws DatabaseException { 2318 2319 Connection connection = null; 2320 PreparedStatement statement = null; 2321 ResultSet resultSet = null; 2322 Collection retValue = new ArrayList (); 2323 StringBuffer sql = new StringBuffer (512); 2324 2325 sql.append("SELECT MemberID, MemberName, MemberFirstName, MemberLastName, MemberEmail, MemberPostCount, MemberCreationDate, MemberStatus"); 2326 sql.append(" FROM " + TABLE_NAME); 2327 sql.append(" WHERE ((MemberActivateCode <> 'activated') OR (MemberActivateCode IS NULL))"); sql.append(" AND (MemberID <> 0) AND (MemberID <> 1) "); sql.append(" AND (MemberPostCount = 0) "); sql.append(" AND (MemberCreationDate < ?) "); 2331 try { 2332 connection = DBUtils.getConnection(); 2333 statement = connection.prepareStatement(sql.toString()); 2334 statement.setTimestamp(1, before); 2335 resultSet = statement.executeQuery(); 2336 while (resultSet.next()) { 2337 MemberBean bean = new MemberBean(); 2338 bean.setMemberID(resultSet.getInt("MemberID")); 2339 bean.setMemberName(resultSet.getString("MemberName")); 2340 bean.setMemberFirstname(resultSet.getString("MemberFirstName")); 2341 bean.setMemberLastname(resultSet.getString("MemberLastName")); 2342 bean.setMemberEmail(resultSet.getString("MemberEmail")); 2343 bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); 2344 bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); 2345 bean.setMemberStatus(resultSet.getInt("MemberStatus")); 2346 retValue.add(bean); 2347 } 2348 return retValue; 2349 } catch(SQLException sqle) { 2350 log.error("Sql Execution Error!", sqle); 2351 throw new DatabaseException("Error executing SQL in MemberDAOImplJDBC.getNonActivatedNoPostMembers"); 2352 } finally { 2353 DBUtils.closeResultSet(resultSet); 2354 DBUtils.resetStatement(statement); 2355 DBUtils.closeStatement(statement); 2356 DBUtils.closeConnection(connection); 2357 } 2358 } 2359 2360} | Popular Tags |