|
What this is
Other links
The source code/* * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/MemberGroupDAOImplJDBC.java,v 1.10 2005/01/18 11:52:20 minhnn Exp $ * $Author: minhnn $ * $Revision: 1.10 $ * $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 MemberGroupDAOImplJDBC implements MemberGroupDAO { private static Log log = LogFactory.getLog(MemberGroupDAOImplJDBC.class); // this variable will support caching if cache for this class is needed private static boolean m_dirty = true; public MemberGroupDAOImplJDBC() { } protected static boolean isDirty() { return m_dirty; } protected static void setDirty(boolean dirty) { m_dirty = dirty; } public void findByPrimaryKey(int groupID, int memberID) throws ObjectNotFoundException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT GroupID, MemberID"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE GroupID = ? AND MemberID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, groupID); statement.setInt(2, memberID); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new ObjectNotFoundException("Cannot find the primary key (" + groupID + ", " + memberID + ") in table 'MemberGroup'."); } } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.findByPrimaryKey."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public void isTutor(int memberID) throws ObjectNotFoundException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append(" SELECT * FROM " + TABLE_NAME + " membergroup , " + GroupsDAO.TABLE_NAME + " groups "); sql.append(" WHERE membergroup.GroupID = groups.GroupID"); sql.append(" AND membergroup.MemberID = ?"); sql.append(" AND groups.GroupName = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, memberID); statement.setString(2, GroupsDAO.TUTOR_GROUP_NAME); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + ") in table 'MemberGroup'."); } } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.isTutor."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: GroupID, MemberID, MemberName, Privilege, CreationDate, * ModifiedDate * Excluded columns: */ public void create(int groupID, String memberName, int privilege, Timestamp creationDate, Timestamp modifiedDate) throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException { int memberID = 0; 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().findByAlternateKey_MemberName(memberName);//redundant ??? memberID = DAOFactory.getMemberDAO().getMemberIDFromMemberName(memberName); } catch(ObjectNotFoundException e) { throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new MemberGroup."); } // @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(groupID, memberID); //If so, then we have to throw an exception throw new DuplicateKeyException("Primary key already exists. Cannot create new MemberGroup with the same [GroupID, MemberID] (" + groupID + ", " + memberID + ")."); } 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.getGroupsDAO().findByPrimaryKey(groupID); } catch(ObjectNotFoundException e) { throw new ForeignKeyNotFoundException("Foreign key refers to table 'Groups' does not exist. Cannot create new MemberGroup."); } Connection connection = null; PreparedStatement statement = null; StringBuffer sql = new StringBuffer(512); sql.append("INSERT INTO " + TABLE_NAME + " (GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate)"); sql.append(" VALUES (?, ?, ?, ?, ?, ?)"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, groupID); statement.setInt(2, memberID); statement.setString(3, memberName); statement.setInt(4, privilege); statement.setTimestamp(5, creationDate); statement.setTimestamp(6, modifiedDate); if (statement.executeUpdate() != 1) { throw new CreateException("Error adding a row into table 'MemberGroup'."); } m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.create."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public void delete(int groupID, 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 GroupID = ? AND MemberID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, groupID); statement.setInt(2, memberID); if (statement.executeUpdate() != 1) { throw new ObjectNotFoundException("Cannot delete a row in table MemberGroup where primary key = (" + groupID + ", " + memberID + ")."); } m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.delete."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public void delete_inGroup(int groupID) throws DatabaseException { Connection connection = null; PreparedStatement statement = null; StringBuffer sql = new StringBuffer(512); sql.append("DELETE FROM " + TABLE_NAME); sql.append(" WHERE GroupID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, groupID); statement.executeUpdate(); m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.delete_inGroup."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public void delete_inMember(int memberID) throws DatabaseException { 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); statement.executeUpdate(); m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.delete_inMember."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /************************************************ * Customized methods come below ************************************************/ /* * Included columns: MemberID, MemberName, Privilege, CreationDate, ModifiedDate * Excluded columns: GroupID */ public Collection getBeans_inGroup(int groupID) 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, Privilege, CreationDate, ModifiedDate"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE GroupID = ?"); sql.append(" ORDER BY MemberID ASC "); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, groupID); resultSet = statement.executeQuery(); while (resultSet.next()) { MemberGroupBean bean = new MemberGroupBean(); bean.setGroupID(groupID); bean.setMemberID(resultSet.getInt("MemberID")); bean.setMemberName(resultSet.getString("MemberName")); bean.setPrivilege(resultSet.getInt("Privilege")); bean.setCreationDate(resultSet.getTimestamp("CreationDate")); bean.setModifiedDate(resultSet.getTimestamp("ModifiedDate")); retValue.add(bean); } return retValue; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.getBeans_inGroup."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public int getNumberOfBeans_inGroup(int groupID) 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 GroupID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, groupID); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new AssertionException("Assertion in MemberGroupDAOImplJDBC.getNumberOfBeans_inGroup."); } return resultSet.getInt(1); } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.getNumberOfBeans_inGroup."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: GroupID, MemberID, MemberName, Privilege, CreationDate, * ModifiedDate * Excluded columns: */ // huumai: only support MySQL //@todo: check if need support for other database??? public Collection getBeans_limit(int groupID, 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 GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE GroupID = ?"); // @todo: uncomment as needed //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, groupID); statement.setInt(2, offset); statement.setInt(3, rowsToReturn); resultSet = statement.executeQuery(); while (resultSet.next()) { MemberGroupBean bean = new MemberGroupBean(); bean.setGroupID(resultSet.getInt("GroupID")); bean.setMemberID(resultSet.getInt("MemberID")); bean.setMemberName(resultSet.getString("MemberName")); bean.setPrivilege(resultSet.getInt("Privilege")); bean.setCreationDate(resultSet.getTimestamp("CreationDate")); bean.setModifiedDate(resultSet.getTimestamp("ModifiedDate")); retValue.add(bean); } return retValue; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in membergroupWebHelper.getBeans_limit."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } }// end of class MemberGroupDAOImplJDBC |
... 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.