|
What this is
Other links
The source code/* * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/MemberTutorDAOImplJDBC.java,v 1.14 2005/01/18 11:52:20 minhnn Exp $ * $Author: minhnn $ * $Revision: 1.14 $ * $Date: 2005/01/18 11:52:20 $ * * ==================================================================== * * Copyright (C) 2002-2005 by MyVietnam.net * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or any later version. * * All copyright notices regarding mvnForum MUST remain intact * in the scripts and in the outputted HTML. * The "powered by" text/logo with a link back to * http://www.mvnForum.com and http://www.MyVietnam.net in the * footer of the pages MUST remain visible when the pages * are viewed on the internet or intranet. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. * * Support can be obtained from support forums at: * http://www.mvnForum.com/mvnforum/index * * Correspondence and Marketing Questions can be sent to: * info@MyVietnam.net * * @author: Minh Nguyen minhnn@MyVietnam.net * @author: Mai Nguyen mai.nh@MyVietnam.net */ package com.mvnforum.db.jdbc; import java.sql.*; import java.util.ArrayList; import java.util.Collection; import com.mvnforum.db.*; import net.myvietnam.mvncore.db.DBUtils; import net.myvietnam.mvncore.exception.*; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; public class MemberTutorDAOImplJDBC implements MemberTutorDAO { private static Log log = LogFactory.getLog(MemberTutorDAOImplJDBC.class); // this variable will support caching if cache for this class is needed private static boolean m_dirty = true; public MemberTutorDAOImplJDBC() { } protected static boolean isDirty() { return m_dirty; } protected static void setDirty(boolean dirty) { m_dirty = dirty; } // This function should be in MemberDAOImplJDBC /* * Included columns: 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 * as of 10 March, add MemberActivateCode * Excluded columns: MemberPassword, MemberFirstEmail, MemberFirstIP, MemberLastIP, MemberModifiedDate, * MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, * MemberTimeZone, MemberSkin, MemberLanguage */ public Collection getBeans(int offset, int rowsToReturn) throws DatabaseException { if (DBUtils.getDatabaseType() != DBUtils.DATABASE_MYSQL) { throw new IllegalStateException("Currently only support MySql"); } Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; Collection retValue = new ArrayList(); StringBuffer sql = new StringBuffer(512); sql.append("SELECT " + MemberDAO.TABLE_NAME + ".* FROM " + MemberDAO.TABLE_NAME); sql.append(" LEFT JOIN " + TABLE_NAME + " ON " + MemberDAO.TABLE_NAME + ".MemberID = " + TABLE_NAME + ".MemberID "); sql.append(" WHERE " + TABLE_NAME + ".MemberID IS NULL"); sql.append(" LIMIT ?, ? "); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, offset); statement.setInt(2, rowsToReturn); resultSet = statement.executeQuery(); while (resultSet.next()) { MemberBean bean = new MemberBean(); bean.setMemberID(resultSet.getInt("MemberID")); bean.setMemberName(resultSet.getString("MemberName")); bean.setMemberEmail(resultSet.getString("MemberEmail")); bean.setMemberEmailVisible(resultSet.getInt("MemberEmailVisible")); bean.setMemberNameVisible(resultSet.getInt("MemberNameVisible")); bean.setMemberViewCount(resultSet.getInt("MemberViewCount")); bean.setMemberPostCount(resultSet.getInt("MemberPostCount")); bean.setMemberCreationDate(resultSet.getTimestamp("MemberCreationDate")); bean.setMemberLastLogon(resultSet.getTimestamp("MemberLastLogon")); bean.setMemberOption(resultSet.getInt("MemberOption")); bean.setMemberStatus(resultSet.getInt("MemberStatus")); bean.setMemberWarnCount(resultSet.getInt("MemberWarnCount")); bean.setMemberVoteCount(resultSet.getInt("MemberVoteCount")); bean.setMemberVoteTotalStars(resultSet.getInt("MemberVoteTotalStars")); bean.setMemberRewardPoints(resultSet.getInt("MemberRewardPoints")); bean.setMemberTitle(resultSet.getString("MemberTitle")); bean.setMemberSignature(resultSet.getString("MemberSignature")); bean.setMemberAvatar(resultSet.getString("MemberAvatar")); bean.setMemberFirstname(resultSet.getString("MemberFirstname")); bean.setMemberLastname(resultSet.getString("MemberLastname")); bean.setMemberGender(resultSet.getInt("MemberGender")); bean.setMemberBirthday(resultSet.getDate("MemberBirthday")); bean.setMemberAddress(resultSet.getString("MemberAddress")); bean.setMemberCity(resultSet.getString("MemberCity")); bean.setMemberState(resultSet.getString("MemberState")); bean.setMemberCountry(resultSet.getString("MemberCountry")); bean.setMemberPhone(resultSet.getString("MemberPhone")); bean.setMemberMobile(resultSet.getString("MemberMobile")); bean.setMemberFax(resultSet.getString("MemberFax")); bean.setMemberCareer(resultSet.getString("MemberCareer")); bean.setMemberHomepage(resultSet.getString("MemberHomepage")); bean.setMemberYahoo(resultSet.getString("MemberYahoo")); bean.setMemberAol(resultSet.getString("MemberAol")); bean.setMemberIcq(resultSet.getString("MemberIcq")); bean.setMemberMsn(resultSet.getString("MemberMsn")); bean.setMemberCoolLink1(resultSet.getString("MemberCoolLink1")); bean.setMemberCoolLink2(resultSet.getString("MemberCoolLink2")); bean.setMemberActivateCode(resultSet.getString("MemberActivateCode")); retValue.add(bean); } return retValue; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.getBeans."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public void findByPrimaryKey(int memberID) throws ObjectNotFoundException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT MemberID"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE MemberID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, memberID); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + ") in table 'MemberTutor'."); } } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.findByPrimaryKey."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* public void findByPrimaryKey2(int memberID, int tutorID) throws DatabaseException, DuplicateKeyException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT MemberID"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE MemberID = ? and TutorID= ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, memberID); statement.setInt(2, tutorID); resultSet = statement.executeQuery(); if (resultSet.next()) { throw new DuplicateKeyException("Cannot find the primary key (" + memberID + ") in table 'MemberTutor'."); } } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.findByPrimaryKey2."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } }*/ public void findByAlternateKey_MemberName(String memberName) throws ObjectNotFoundException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT MemberName"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE MemberName = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setString(1, memberName); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new ObjectNotFoundException("Cannot find the alternate key [MemberName] (" + memberName + ") in table 'MemberTutor'."); } } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.findByAlternateKey_MemberName."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* This method is used to specify collection of member belong to one tutor * */ public Collection getMembers_inTutor(int tutorID) throws DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; Collection retValue = new ArrayList(); StringBuffer sql = new StringBuffer(512); sql.append("SELECT MemberID, MemberName, TutorID, TutorName, CreationDate, ExpireDate, RelationType, RelationOption, RelationStatus"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE TutorID = ?"); //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, tutorID); resultSet = statement.executeQuery(); while (resultSet.next()) { MemberTutorBean bean = new MemberTutorBean(); bean.setMemberID(resultSet.getInt("MemberID")); bean.setMemberName(resultSet.getString("MemberName")); bean.setTutorID(resultSet.getInt("TutorID")); bean.setTutorName(resultSet.getString("TutorName")); bean.setCreationDate(resultSet.getTimestamp("CreationDate")); bean.setExpireDate(resultSet.getTimestamp("ExpireDate")); bean.setRelationType(resultSet.getInt("RelationType")); bean.setRelationOption(resultSet.getInt("RelationOption")); bean.setRelationStatus(resultSet.getInt("RelationStatus")); retValue.add(bean); } return retValue; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.getMembers_inTutor."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: MemberID, MemberName, TutorID, TutorName, CreationDate, * ExpireDate, RelationType, RelationOption, RelationStatus * Excluded columns: */ public void create(int memberID, String memberName, int tutorID, String tutorName, Timestamp creationDate, Timestamp expireDate, int relationType, int relationOption, int relationStatus) throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException { // @todo: comment this try-catch block if the needed columns dont have attribute 'include' // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on // However, if primary key is a auto_increament column, then you can safely delete this block try { //Check if primary key already exists findByPrimaryKey(memberID); //If so, then we have to throw an exception throw new DuplicateKeyException("Primary key already exists. Cannot create new MemberTutor with the same [MemberID] (" + memberID + ")."); } catch(ObjectNotFoundException e) { //Otherwise we can go ahead } // @todo: Comment this try-catch block if the needed columns dont have attribute 'include' // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on try { //Check if alternate key already exists findByAlternateKey_MemberName(memberName); //If so, then we have to throw an exception throw new DuplicateKeyException("Alternate key already exists. Cannot create new MemberTutor with the same [MemberName] (" + memberName + ")."); } catch(ObjectNotFoundException e) { //Otherwise we can go ahead } try { // @todo: modify the parameter list as needed // You may have to regenerate this method if the needed columns dont have attribute 'include' DAOFactory.getMemberDAO().findByPrimaryKey(memberID); } catch(ObjectNotFoundException e) { throw new ForeignKeyNotFoundException("Foreign key [MemberID] refers to table 'Member' does not exist. Cannot create new MemberTutor."); } try { // @todo: modify the parameter list as needed // You may have to regenerate this method if the needed columns dont have attribute 'include' DAOFactory.getMemberDAO().findByPrimaryKey(tutorID); } catch(ObjectNotFoundException e) { throw new ForeignKeyNotFoundException("Foreign key [TutorID] refers to table 'Member' does not exist. Cannot create new MemberTutor."); } Connection connection = null; PreparedStatement statement = null; StringBuffer sql = new StringBuffer(512); sql.append("INSERT INTO " + TABLE_NAME + " (MemberID, MemberName, TutorID, TutorName, CreationDate, ExpireDate, RelationType, RelationOption, RelationStatus)"); sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, memberID); statement.setString(2, memberName); statement.setInt(3, tutorID); statement.setString(4, tutorName); statement.setTimestamp(5, creationDate); statement.setTimestamp(6, expireDate); statement.setInt(7, relationType); statement.setInt(8, relationOption); statement.setInt(9, relationStatus); if (statement.executeUpdate() != 1) { throw new CreateException("Error adding a row into table 'MemberTutor'."); } m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.create."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public void delete(int memberID) throws DatabaseException, ObjectNotFoundException { Connection connection = null; PreparedStatement statement = null; StringBuffer sql = new StringBuffer(512); sql.append("DELETE FROM " + TABLE_NAME); sql.append(" WHERE MemberID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, memberID); if (statement.executeUpdate() != 1) { throw new ObjectNotFoundException("Cannot delete a row in table MemberTutor where primary key = (" + memberID + ")."); } m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.delete."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public void deleteMembers_inTutor(int tutorID) throws DatabaseException { Connection connection = null; PreparedStatement statement = null; StringBuffer sql = new StringBuffer(512); sql.append("DELETE FROM " + TABLE_NAME); sql.append(" WHERE TutorID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, tutorID); statement.executeUpdate(); m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.deleteMembers_inTutor."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: MemberID, MemberName, TutorID, TutorName, CreationDate, * ExpireDate, RelationType, RelationOption, RelationStatus * Excluded columns: */ public MemberTutorBean getBean(int memberID) throws ObjectNotFoundException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT MemberID, MemberName, TutorID, TutorName, CreationDate, ExpireDate, RelationType, RelationOption, RelationStatus"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE MemberID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, memberID); resultSet = statement.executeQuery(); if(!resultSet.next()) { throw new ObjectNotFoundException("Cannot find the row in table MemberTutor where primary key = (" + memberID + ")."); } MemberTutorBean bean = new MemberTutorBean(); // @todo: uncomment the following line(s) as needed //bean.setMemberID(memberID); bean.setMemberID(resultSet.getInt("MemberID")); bean.setMemberName(resultSet.getString("MemberName")); bean.setTutorID(resultSet.getInt("TutorID")); bean.setTutorName(resultSet.getString("TutorName")); bean.setCreationDate(resultSet.getTimestamp("CreationDate")); bean.setExpireDate(resultSet.getTimestamp("ExpireDate")); bean.setRelationType(resultSet.getInt("RelationType")); bean.setRelationOption(resultSet.getInt("RelationOption")); bean.setRelationStatus(resultSet.getInt("RelationStatus")); return bean; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.getBean(pk)."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: MemberID, MemberName, TutorID, TutorName, CreationDate, * ExpireDate, RelationType, RelationOption, RelationStatus * Excluded columns: */ public Collection getBeans_inTutor_limit(int tutorID, int offset, int rowsToReturn) throws IllegalArgumentException, DatabaseException { if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed."); if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed."); Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; Collection retValue = new ArrayList(); StringBuffer sql = new StringBuffer(512); sql.append("SELECT MemberID, MemberName, TutorID, TutorName, CreationDate, ExpireDate, RelationType, RelationOption, RelationStatus"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE TutorID = ?"); //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed sql.append(" LIMIT ?, ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, tutorID); statement.setInt(2, offset); statement.setInt(3, rowsToReturn); resultSet = statement.executeQuery(); while (resultSet.next()) { MemberTutorBean bean = new MemberTutorBean(); bean.setMemberID(resultSet.getInt("MemberID")); bean.setMemberName(resultSet.getString("MemberName")); bean.setTutorID(resultSet.getInt("TutorID")); bean.setTutorName(resultSet.getString("TutorName")); bean.setCreationDate(resultSet.getTimestamp("CreationDate")); bean.setExpireDate(resultSet.getTimestamp("ExpireDate")); bean.setRelationType(resultSet.getInt("RelationType")); bean.setRelationOption(resultSet.getInt("RelationOption")); bean.setRelationStatus(resultSet.getInt("RelationStatus")); retValue.add(bean); } return retValue; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.getBeans_inTutor_limit."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public int getNumberOfMembers() throws AssertionException, DatabaseException { if (DBUtils.getDatabaseType() != DBUtils.DATABASE_MYSQL) { throw new IllegalStateException("Currently only support MySql"); } Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT COUNT(" + MemberDAO.TABLE_NAME + ".MemberID) FROM " + MemberDAO.TABLE_NAME); sql.append(" LEFT JOIN " + TABLE_NAME + " ON " + MemberDAO.TABLE_NAME + ".MemberID = " + TABLE_NAME + ".MemberID "); sql.append(" WHERE " + TABLE_NAME + ".MemberID IS NULL"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new AssertionException("Assertion in MemberTutorDAOImplJDBC.getNumberOfMember."); } return resultSet.getInt(1); } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.getNumberOfMembers."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public int getNumberOfBeans_inTutor(int tutorID) throws AssertionException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT Count(*)"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE TutorID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, tutorID); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new AssertionException("Assertion in MemberTutorDAOImplJDBC.getNumberOfBeans_inTutor."); } return resultSet.getInt(1); } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.getNumberOfBeans_inTutor."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: MemberID, MemberName, TutorID, TutorName, CreationDate, * ExpireDate, RelationType, RelationOption, RelationStatus * Excluded columns: */ public Collection getBeans_inExpire_limit(Timestamp expireDate, int offset, int rowsToReturn) throws IllegalArgumentException, DatabaseException { if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed."); if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed."); Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; Collection retValue = new ArrayList(); StringBuffer sql = new StringBuffer(512); sql.append("SELECT MemberID, MemberName, TutorID, TutorName, CreationDate, ExpireDate, RelationType, RelationOption, RelationStatus"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE ExpireDate <= ?"); //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed sql.append(" LIMIT ?, ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setTimestamp(1, expireDate); statement.setInt(2, offset); statement.setInt(3, rowsToReturn); resultSet = statement.executeQuery(); while (resultSet.next()) { MemberTutorBean bean = new MemberTutorBean(); bean.setMemberID(resultSet.getInt("MemberID")); bean.setMemberName(resultSet.getString("MemberName")); bean.setTutorID(resultSet.getInt("TutorID")); bean.setTutorName(resultSet.getString("TutorName")); bean.setCreationDate(resultSet.getTimestamp("CreationDate")); bean.setExpireDate(resultSet.getTimestamp("ExpireDate")); bean.setRelationType(resultSet.getInt("RelationType")); bean.setRelationOption(resultSet.getInt("RelationOption")); bean.setRelationStatus(resultSet.getInt("RelationStatus")); retValue.add(bean); } return retValue; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.getBeans_inExpire_limit."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public int getNumberOfBeans_inExpire(Timestamp expireDate) throws AssertionException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT Count(*)"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE ExpireDate <= ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setTimestamp(1, expireDate); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new AssertionException("Assertion in MemberTutorDAOImplJDBC.getNumberOfBeans_inExpire."); } return resultSet.getInt(1); } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.getNumberOfBeans_inExpire."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: MemberID, CreationDate, ExpireDate * Excluded columns: MemberName, TutorID, TutorName, RelationType, RelationOption, * RelationStatus */ public void updateMember_expireDate(int memberID, // primary key Timestamp expireDate) throws ObjectNotFoundException, DatabaseException/*, DuplicateKeyException*/ { //MemberTutorBean bean = getBean(memberID); // @todo: comment or delete this line if no alternate key are included Connection connection = null; PreparedStatement statement = null; StringBuffer sql = new StringBuffer(512); sql.append("UPDATE " + TABLE_NAME + " SET ExpireDate = ?"); sql.append(" WHERE MemberID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); // // column(s) to update statement.setTimestamp(1, expireDate); // primary key column(s) statement.setInt(2, memberID); if (statement.executeUpdate() != 1) { throw new ObjectNotFoundException("Cannot update table MemberTutor where primary key = (" + memberID + ")."); } m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberTutorDAOImplJDBC.updateMember_expireDate."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } }// end of class MemberTutorDAOImplJDBC |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.