|
What this is
Other links
The source code/* * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/GroupsDAOImplJDBC.java,v 1.15 2005/02/15 19:33:07 minhnn Exp $ * $Author: minhnn $ * $Revision: 1.15 $ * $Date: 2005/02/15 19:33:07 $ * * ==================================================================== * * 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.io.StringReader; 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 GroupsDAOImplJDBC implements GroupsDAO { private static Log log = LogFactory.getLog(GroupsDAOImplJDBC.class); // this variable will support caching if cache for this class is needed private static boolean m_dirty = true; public GroupsDAOImplJDBC() { } protected static boolean isDirty() { return m_dirty; } protected static void setDirty(boolean dirty) { m_dirty = dirty; } public void findByPrimaryKey(int groupID) throws ObjectNotFoundException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT GroupID"); 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 ObjectNotFoundException("Cannot find the primary key (" + groupID + ") in table 'Groups'."); } } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.findByPrimaryKey."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public void findByAlternateKey_GroupName(String groupName) throws ObjectNotFoundException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT GroupName"); sql.append(" FROM " + TABLE_NAME); sql.append(" WHERE GroupName = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setString(1, groupName); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new ObjectNotFoundException("Cannot find the alternate key [GroupName] (" + groupName + ") in table 'Groups'."); } } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.findByAlternateKey_GroupName."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, * GroupCreationDate, GroupModifiedDate * Excluded columns: GroupID */ /** * NOTE: This is a customized method, it get groupOwnerID from groupOwnerName * and I remove the groupOwnerID from the parameter list */ public void create(String groupOwnerName, String groupName, String groupDesc, int groupOption, Timestamp groupCreationDate, Timestamp groupModifiedDate) throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException { int groupOwnerID = 0;// MUST init to 0, or this method will be wrong // @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_GroupName(groupName); //If so, then we have to throw an exception throw new DuplicateKeyException("Alternate key already exists. Cannot create new Groups with the same [GroupName] (" + groupName + ")."); } 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' groupOwnerID = 0; if ((groupOwnerName!=null) && (groupOwnerName.length()>0)) {// have group owner DAOFactory.getMemberDAO().findByAlternateKey_MemberName(groupOwnerName); try { groupOwnerID = DAOFactory.getMemberDAO().getMemberIDFromMemberName(groupOwnerName); } catch (ObjectNotFoundException ex) { // This exception should never be thrown throw new ObjectNotFoundException("ASSERTION: This should never happen."); } } } catch(ObjectNotFoundException e) { throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Groups."); } Connection connection = null; PreparedStatement statement = null; StringBuffer sql = new StringBuffer(512); sql.append("INSERT INTO " + TABLE_NAME + " (GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate)"); sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?)"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, groupOwnerID); statement.setString(2, groupOwnerName); statement.setString(3, groupName); if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) { statement.setCharacterStream(4, new StringReader(groupDesc), groupDesc.length()); } else { statement.setString(4, groupDesc); } statement.setInt(5, groupOption); statement.setTimestamp(6, groupCreationDate); statement.setTimestamp(7, groupModifiedDate); if (statement.executeUpdate() != 1) { throw new CreateException("Error adding a row into table 'Groups'."); } m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.create."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public void delete(int groupID) throws DatabaseException, ObjectNotFoundException { 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); if (statement.executeUpdate() != 1) { throw new ObjectNotFoundException("Cannot delete a row in table Groups where primary key = (" + groupID + ")."); } m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.delete."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: GroupName, GroupDesc, GroupModifiedDate * Excluded columns: GroupID, GroupOwnerID, GroupOption, GroupCreationDate */ public void update(int groupID, // primary key String groupName, String groupDesc, Timestamp groupModifiedDate) throws ObjectNotFoundException, DatabaseException, DuplicateKeyException { GroupsBean bean = getGroup(groupID); // @todo: comment or delete this line if no alternate key are included if ( !groupName.equals(bean.getGroupName()) ) { // Groups tries to change its alternate key <GroupName>, so we must check if it already exist try { findByAlternateKey_GroupName(groupName); throw new DuplicateKeyException("Alternate key [GroupName] (" + groupName + ")already exists. Cannot update Groups."); } catch(ObjectNotFoundException e) { //Otherwise we can go ahead } } Connection connection = null; PreparedStatement statement = null; StringBuffer sql = new StringBuffer(512); sql.append("UPDATE " + TABLE_NAME + " SET GroupName = ?, GroupDesc = ?, GroupModifiedDate = ?"); sql.append(" WHERE GroupID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); // // column(s) to update statement.setString(1, groupName); if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) { statement.setCharacterStream(2, new StringReader(groupDesc), groupDesc.length()); } else { statement.setString(2, groupDesc); } statement.setTimestamp(3, groupModifiedDate); // primary key column(s) statement.setInt(4, groupID); if (statement.executeUpdate() != 1) { throw new ObjectNotFoundException("Cannot update table Groups where primary key = (" + groupID + ")."); } m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.update."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: GroupOwnerID, GroupOwnerName, GroupModifiedDate * Excluded columns: GroupID, GroupName, GroupDesc, GroupOption, GroupCreationDate */ public void updateOwner(int groupID, // primary key String groupOwnerName, Timestamp groupModifiedDate) throws ObjectNotFoundException, DatabaseException, ForeignKeyNotFoundException { int groupOwnerID = 0;//MUST init to 0 //GroupsBean bean = getGroup(groupID); // @todo: comment or delete this line if no alternate key are included try { // @todo: modify the parameter list as needed // If this method does not change the foreign key columns, you can comment this block of code. groupOwnerID = 0; if ((groupOwnerName!=null) && (groupOwnerName.length()>0)) {// have group owner DAOFactory.getMemberDAO().findByAlternateKey_MemberName(groupOwnerName); try { groupOwnerID = DAOFactory.getMemberDAO().getMemberIDFromMemberName(groupOwnerName); } catch (ObjectNotFoundException ex) { // This exception should never be thrown throw new ObjectNotFoundException("ASSERTION: This should never happen."); } } } catch(ObjectNotFoundException e) { throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot update table 'Groups'."); } Connection connection = null; PreparedStatement statement = null; StringBuffer sql = new StringBuffer(512); sql.append("UPDATE " + TABLE_NAME + " SET GroupOwnerID = ?, GroupOwnerName = ?, GroupModifiedDate = ?"); sql.append(" WHERE GroupID = ?"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); // // column(s) to update statement.setInt(1, groupOwnerID); statement.setString(2, groupOwnerName); statement.setTimestamp(3, groupModifiedDate); // primary key column(s) statement.setInt(4, groupID); if (statement.executeUpdate() != 1) { throw new ObjectNotFoundException("Cannot update table Groups where primary key = (" + groupID + ")."); } m_dirty = true; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.updateOwner."); } finally { DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, * GroupCreationDate, GroupModifiedDate * Excluded columns: GroupID */ public GroupsBean getGroup(int groupID) throws ObjectNotFoundException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); sql.append("SELECT GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate"); 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 ObjectNotFoundException("Cannot find the row in table Groups where primary key = (" + groupID + ")."); } GroupsBean bean = new GroupsBean(); bean.setGroupID(groupID); bean.setGroupOwnerID(resultSet.getInt("GroupOwnerID")); bean.setGroupOwnerName(resultSet.getString("GroupOwnerName")); bean.setGroupName(resultSet.getString("GroupName")); bean.setGroupDesc(resultSet.getString("GroupDesc")); bean.setGroupOption(resultSet.getInt("GroupOption")); bean.setGroupCreationDate(resultSet.getTimestamp("GroupCreationDate")); bean.setGroupModifiedDate(resultSet.getTimestamp("GroupModifiedDate")); return bean; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getGroup(pk)."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public Collection getMyGroups(int memberID) throws DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; StringBuffer sql = new StringBuffer(512); ArrayList retValue = new ArrayList(); sql.append("SELECT g.GroupID, g.GroupName, g.GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate"); sql.append(" FROM " + TABLE_NAME).append(" g, ").append(MemberGroupDAO.TABLE_NAME).append(" mg"); sql.append(" WHERE (g.GroupID = mg.GroupID) AND (mg.MemberID = ?)"); sql.append(" ORDER BY g.GroupID ASC"); try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); statement.setInt(1, memberID); resultSet = statement.executeQuery(); while(resultSet.next()) { GroupsBean bean = new GroupsBean(); bean.setGroupID(resultSet.getInt("GroupID")); bean.setGroupName(resultSet.getString("GroupName")); bean.setGroupDesc(resultSet.getString("GroupDesc")); bean.setGroupOption(resultSet.getInt("GroupOption")); bean.setGroupCreationDate(resultSet.getTimestamp("GroupCreationDate")); bean.setGroupModifiedDate(resultSet.getTimestamp("GroupModifiedDate")); retValue.add(bean); } return retValue; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getMyGroups(memberID)."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /* * Included columns: GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, * GroupOption, GroupCreationDate, GroupModifiedDate * Excluded columns: */ public Collection getGroups() throws DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; Collection retValue = new ArrayList(); StringBuffer sql = new StringBuffer(512); sql.append("SELECT GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate"); sql.append(" FROM " + TABLE_NAME); //sql.append(" WHERE "); // @todo: uncomment as needed //minhnn: @todo: it should be GroupID, not GroupName sql.append(" ORDER BY GroupID ASC");// must sort be id to show number of members in group try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); resultSet = statement.executeQuery(); while (resultSet.next()) { GroupsBean bean = new GroupsBean(); bean.setGroupID(resultSet.getInt("GroupID")); bean.setGroupOwnerID(resultSet.getInt("GroupOwnerID")); bean.setGroupOwnerName(resultSet.getString("GroupOwnerName")); bean.setGroupName(resultSet.getString("GroupName")); bean.setGroupDesc(resultSet.getString("GroupDesc")); bean.setGroupOption(resultSet.getInt("GroupOption")); bean.setGroupCreationDate(resultSet.getTimestamp("GroupCreationDate")); bean.setGroupModifiedDate(resultSet.getTimestamp("GroupModifiedDate")); retValue.add(bean); } return retValue; } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getBeans."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } public int getNumberOfGroups() 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 "); // @todo: uncomment as needed try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql.toString()); resultSet = statement.executeQuery(); if (!resultSet.next()) { throw new AssertionException("Assertion in GroupsDAOImplJDBC.getNumberOfGroups."); } return resultSet.getInt(1); } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getNumberOfGroups."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } /************************************************ * Customized methods come below ************************************************/ /* @todo check if this method work with other DBMS other than MySql (check case-sensitive) */ public int getGroupIDFromGroupName(String groupName) throws ObjectNotFoundException, DatabaseException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; String sql = "SELECT GroupID FROM " + TABLE_NAME + " WHERE GroupName = ?"; try { connection = DBUtils.getConnection(); statement = connection.prepareStatement(sql); statement.setString(1, groupName); resultSet = statement.executeQuery(); if(!resultSet.next()) { throw new ObjectNotFoundException("Cannot find the row in table Group where GroupName = " + groupName); } return resultSet.getInt(1); } catch(SQLException sqle) { log.error("Sql Execution Error!", sqle); throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getGroupIDFromGroupName."); } finally { DBUtils.closeResultSet(resultSet); DBUtils.closeStatement(statement); DBUtils.closeConnection(connection); } } }// end of class GroupsDAOImplJDBC |
... 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.