alvinalexander.com | career | drupal | java | mac | mysql | perl | scala | uml | unix  

What this is

This file is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.

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

 

new blog posts

 

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.