001: /*
002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/MemberGroupDAOImplJDBC.java,v 1.19 2007/11/09 09:14:37 minhnn Exp $
003: * $Author: minhnn $
004: * $Revision: 1.19 $
005: * $Date: 2007/11/09 09:14:37 $
006: *
007: * ====================================================================
008: *
009: * Copyright (C) 2002-2007 by MyVietnam.net
010: *
011: * All copyright notices regarding mvnForum MUST remain
012: * intact in the scripts and in the outputted HTML.
013: * The "powered by" text/logo with a link back to
014: * http://www.mvnForum.com and http://www.MyVietnam.net in
015: * the footer of the pages MUST remain visible when the pages
016: * are viewed on the internet or intranet.
017: *
018: * This program is free software; you can redistribute it and/or modify
019: * it under the terms of the GNU General Public License as published by
020: * the Free Software Foundation; either version 2 of the License, or
021: * any later version.
022: *
023: * This program is distributed in the hope that it will be useful,
024: * but WITHOUT ANY WARRANTY; without even the implied warranty of
025: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
026: * GNU General Public License for more details.
027: *
028: * You should have received a copy of the GNU General Public License
029: * along with this program; if not, write to the Free Software
030: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
031: *
032: * Support can be obtained from support forums at:
033: * http://www.mvnForum.com/mvnforum/index
034: *
035: * Correspondence and Marketing Questions can be sent to:
036: * info at MyVietnam net
037: *
038: * @author: Minh Nguyen
039: * @author: Mai Nguyen
040: */
041: package com.mvnforum.db.jdbc;
042:
043: import java.sql.*;
044: import java.util.ArrayList;
045: import java.util.Collection;
046:
047: import com.mvnforum.db.*;
048: import net.myvietnam.mvncore.db.DBUtils;
049: import net.myvietnam.mvncore.exception.*;
050: import net.myvietnam.mvncore.util.AssertionUtil;
051:
052: import org.apache.commons.logging.Log;
053: import org.apache.commons.logging.LogFactory;
054:
055: public class MemberGroupDAOImplJDBC implements MemberGroupDAO {
056:
057: private static Log log = LogFactory
058: .getLog(MemberGroupDAOImplJDBC.class);
059:
060: // this variable will support caching if cache for this class is needed
061: private static boolean m_dirty = true;
062:
063: public MemberGroupDAOImplJDBC() {
064: }
065:
066: protected static boolean isDirty() {
067: return m_dirty;
068: }
069:
070: protected static void setDirty(boolean dirty) {
071: m_dirty = dirty;
072: }
073:
074: public void findByPrimaryKey(int groupID, int memberID)
075: throws ObjectNotFoundException, DatabaseException {
076:
077: Connection connection = null;
078: PreparedStatement statement = null;
079: ResultSet resultSet = null;
080: StringBuffer sql = new StringBuffer(512);
081: sql.append("SELECT GroupID, MemberID");
082: sql.append(" FROM " + TABLE_NAME);
083: sql.append(" WHERE GroupID = ? AND MemberID = ?");
084: try {
085: connection = DBUtils.getConnection();
086: statement = connection.prepareStatement(sql.toString());
087: statement.setInt(1, groupID);
088: statement.setInt(2, memberID);
089: resultSet = statement.executeQuery();
090: if (!resultSet.next()) {
091: throw new ObjectNotFoundException(
092: "Cannot find the primary key (" + groupID
093: + ", " + memberID
094: + ") in table 'MemberGroup'.");
095: }
096: } catch (SQLException sqle) {
097: log.error("Sql Execution Error!", sqle);
098: throw new DatabaseException(
099: "Error executing SQL in MemberGroupDAOImplJDBC.findByPrimaryKey.");
100: } finally {
101: DBUtils.closeResultSet(resultSet);
102: DBUtils.closeStatement(statement);
103: DBUtils.closeConnection(connection);
104: }
105: }
106:
107: /*
108: * Included columns: GroupID, MemberID, MemberName, Privilege, CreationDate,
109: * ModifiedDate
110: * Excluded columns:
111: */
112: public void create(int groupID, String memberName, int privilege,
113: Timestamp creationDate, Timestamp modifiedDate)
114: throws CreateException, DatabaseException,
115: DuplicateKeyException, ForeignKeyNotFoundException {
116:
117: int memberID = 0;
118: try {
119: // @todo: modify the parameter list as needed
120: // You may have to regenerate this method if the needed columns dont have attribute 'include'
121: memberName = DAOFactory.getMemberDAO()
122: .findByAlternateKey_MemberName(memberName);//redundant ???
123: memberID = DAOFactory.getMemberDAO()
124: .getMemberIDFromMemberName(memberName);
125: } catch (ObjectNotFoundException e) {
126: throw new ForeignKeyNotFoundException(
127: "Foreign key refers to table 'Member' does not exist. Cannot create new MemberGroup.");
128: }
129:
130: // @todo: comment this try-catch block if the needed columns dont have attribute 'include'
131: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
132: // However, if primary key is a auto_increament column, then you can safely delete this block
133: try {
134: //Check if primary key already exists
135: findByPrimaryKey(groupID, memberID);
136: //If so, then we have to throw an exception
137: throw new DuplicateKeyException(
138: "Primary key already exists. Cannot create new MemberGroup with the same [GroupID, MemberID] ("
139: + groupID + ", " + memberID + ").");
140: } catch (ObjectNotFoundException e) {
141: //Otherwise we can go ahead
142: }
143:
144: try {
145: // @todo: modify the parameter list as needed
146: // You may have to regenerate this method if the needed columns dont have attribute 'include'
147: DAOFactory.getGroupsDAO().findByPrimaryKey(groupID);
148: } catch (ObjectNotFoundException e) {
149: throw new ForeignKeyNotFoundException(
150: "Foreign key refers to table 'Groups' does not exist. Cannot create new MemberGroup.");
151: }
152:
153: Connection connection = null;
154: PreparedStatement statement = null;
155: StringBuffer sql = new StringBuffer(512);
156: sql
157: .append("INSERT INTO "
158: + TABLE_NAME
159: + " (GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate)");
160: sql.append(" VALUES (?, ?, ?, ?, ?, ?)");
161: try {
162: connection = DBUtils.getConnection();
163: statement = connection.prepareStatement(sql.toString());
164:
165: statement.setInt(1, groupID);
166: statement.setInt(2, memberID);
167: statement.setString(3, memberName);
168: statement.setInt(4, privilege);
169: statement.setTimestamp(5, creationDate);
170: statement.setTimestamp(6, modifiedDate);
171:
172: if (statement.executeUpdate() != 1) {
173: throw new CreateException(
174: "Error adding a row into table 'MemberGroup'.");
175: }
176: m_dirty = true;
177: } catch (SQLException sqle) {
178: log.error("Sql Execution Error!", sqle);
179: throw new DatabaseException(
180: "Error executing SQL in MemberGroupDAOImplJDBC.create.");
181: } finally {
182: DBUtils.closeStatement(statement);
183: DBUtils.closeConnection(connection);
184: }
185: }
186:
187: public void delete(int groupID, int memberID)
188: throws DatabaseException, ObjectNotFoundException {
189:
190: Connection connection = null;
191: PreparedStatement statement = null;
192: StringBuffer sql = new StringBuffer(512);
193: sql.append("DELETE FROM " + TABLE_NAME);
194: sql.append(" WHERE GroupID = ? AND MemberID = ?");
195:
196: try {
197: connection = DBUtils.getConnection();
198: statement = connection.prepareStatement(sql.toString());
199: statement.setInt(1, groupID);
200: statement.setInt(2, memberID);
201: if (statement.executeUpdate() != 1) {
202: throw new ObjectNotFoundException(
203: "Cannot delete a row in table MemberGroup where primary key = ("
204: + groupID + ", " + memberID + ").");
205: }
206: m_dirty = true;
207: } catch (SQLException sqle) {
208: log.error("Sql Execution Error!", sqle);
209: throw new DatabaseException(
210: "Error executing SQL in MemberGroupDAOImplJDBC.delete.");
211: } finally {
212: DBUtils.closeStatement(statement);
213: DBUtils.closeConnection(connection);
214: }
215: }
216:
217: public void delete_inGroup(int groupID) throws DatabaseException {
218:
219: Connection connection = null;
220: PreparedStatement statement = null;
221: StringBuffer sql = new StringBuffer(512);
222: sql.append("DELETE FROM " + TABLE_NAME);
223: sql.append(" WHERE GroupID = ?");
224:
225: try {
226: connection = DBUtils.getConnection();
227: statement = connection.prepareStatement(sql.toString());
228: statement.setInt(1, groupID);
229:
230: statement.executeUpdate();
231: m_dirty = true;
232: } catch (SQLException sqle) {
233: log.error("Sql Execution Error!", sqle);
234: throw new DatabaseException(
235: "Error executing SQL in MemberGroupDAOImplJDBC.delete_inGroup.");
236: } finally {
237: DBUtils.closeStatement(statement);
238: DBUtils.closeConnection(connection);
239: }
240: }
241:
242: public void delete_inMember(int memberID) throws DatabaseException {
243:
244: Connection connection = null;
245: PreparedStatement statement = null;
246: StringBuffer sql = new StringBuffer(512);
247: sql.append("DELETE FROM " + TABLE_NAME);
248: sql.append(" WHERE MemberID = ?");
249:
250: try {
251: connection = DBUtils.getConnection();
252: statement = connection.prepareStatement(sql.toString());
253: statement.setInt(1, memberID);
254:
255: statement.executeUpdate();
256: m_dirty = true;
257: } catch (SQLException sqle) {
258: log.error("Sql Execution Error!", sqle);
259: throw new DatabaseException(
260: "Error executing SQL in MemberGroupDAOImplJDBC.delete_inMember.");
261: } finally {
262: DBUtils.closeStatement(statement);
263: DBUtils.closeConnection(connection);
264: }
265: }
266:
267: /************************************************
268: * Customized methods come below
269: ************************************************/
270: /*
271: * Included columns: MemberID, MemberName, Privilege, CreationDate, ModifiedDate
272: * Excluded columns: GroupID
273: */
274: public Collection getBeans_inGroup(int groupID)
275: throws DatabaseException {
276:
277: Connection connection = null;
278: PreparedStatement statement = null;
279: ResultSet resultSet = null;
280: Collection retValue = new ArrayList();
281: StringBuffer sql = new StringBuffer(512);
282: sql
283: .append("SELECT MemberID, MemberName, Privilege, CreationDate, ModifiedDate");
284: sql.append(" FROM " + TABLE_NAME);
285: sql.append(" WHERE GroupID = ?");
286: sql.append(" ORDER BY MemberID ASC ");
287: try {
288: connection = DBUtils.getConnection();
289: statement = connection.prepareStatement(sql.toString());
290: statement.setInt(1, groupID);
291: resultSet = statement.executeQuery();
292: while (resultSet.next()) {
293: MemberGroupBean bean = new MemberGroupBean();
294: bean.setGroupID(groupID);
295: bean.setMemberID(resultSet.getInt("MemberID"));
296: bean.setMemberName(resultSet.getString("MemberName"));
297: bean.setPrivilege(resultSet.getInt("Privilege"));
298: bean.setCreationDate(resultSet
299: .getTimestamp("CreationDate"));
300: bean.setModifiedDate(resultSet
301: .getTimestamp("ModifiedDate"));
302: retValue.add(bean);
303: }
304: return retValue;
305: } catch (SQLException sqle) {
306: log.error("Sql Execution Error!", sqle);
307: throw new DatabaseException(
308: "Error executing SQL in MemberGroupDAOImplJDBC.getBeans_inGroup.");
309: } finally {
310: DBUtils.closeResultSet(resultSet);
311: DBUtils.closeStatement(statement);
312: DBUtils.closeConnection(connection);
313: }
314: }
315:
316: public int getNumberOfBeans_inGroup(int groupID)
317: throws DatabaseException {
318:
319: Connection connection = null;
320: PreparedStatement statement = null;
321: ResultSet resultSet = null;
322: StringBuffer sql = new StringBuffer(512);
323: sql.append("SELECT Count(*)");
324: sql.append(" FROM " + TABLE_NAME);
325: sql.append(" WHERE GroupID = ?");
326: try {
327: connection = DBUtils.getConnection();
328: statement = connection.prepareStatement(sql.toString());
329: statement.setInt(1, groupID);
330: resultSet = statement.executeQuery();
331: AssertionUtil
332: .doAssert(resultSet.next(),
333: "Assertion in MemberGroupDAOImplJDBC.getNumberOfBeans_inGroup.");
334: return resultSet.getInt(1);
335: } catch (SQLException sqle) {
336: log.error("Sql Execution Error!", sqle);
337: throw new DatabaseException(
338: "Error executing SQL in MemberGroupDAOImplJDBC.getNumberOfBeans_inGroup.");
339: } finally {
340: DBUtils.closeResultSet(resultSet);
341: DBUtils.closeStatement(statement);
342: DBUtils.closeConnection(connection);
343: }
344: }
345:
346: /*
347: * Included columns: GroupID, MemberID, MemberName, Privilege, CreationDate,
348: * ModifiedDate
349: * Excluded columns:
350: */
351: // huumai: only support MySQL
352: //@todo: check if need support for other database???
353: public Collection getBeans_limit(int groupID, int offset,
354: int rowsToReturn) throws IllegalArgumentException,
355: DatabaseException {
356: if (offset < 0)
357: throw new IllegalArgumentException(
358: "The offset < 0 is not allowed.");
359: if (rowsToReturn <= 0)
360: throw new IllegalArgumentException(
361: "The rowsToReturn <= 0 is not allowed.");
362:
363: Connection connection = null;
364: PreparedStatement statement = null;
365: ResultSet resultSet = null;
366: Collection retValue = new ArrayList();
367: StringBuffer sql = new StringBuffer(512);
368: sql
369: .append("SELECT GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate");
370: sql.append(" FROM " + TABLE_NAME);
371: sql.append(" WHERE GroupID = ?"); // @todo: uncomment as needed
372: //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
373: sql.append(" LIMIT ?, ?");
374: try {
375: connection = DBUtils.getConnection();
376: statement = connection.prepareStatement(sql.toString());
377: statement.setInt(1, groupID);
378: statement.setInt(2, offset);
379: statement.setInt(3, rowsToReturn);
380: resultSet = statement.executeQuery();
381: while (resultSet.next()) {
382: MemberGroupBean bean = new MemberGroupBean();
383: bean.setGroupID(resultSet.getInt("GroupID"));
384: bean.setMemberID(resultSet.getInt("MemberID"));
385: bean.setMemberName(resultSet.getString("MemberName"));
386: bean.setPrivilege(resultSet.getInt("Privilege"));
387: bean.setCreationDate(resultSet
388: .getTimestamp("CreationDate"));
389: bean.setModifiedDate(resultSet
390: .getTimestamp("ModifiedDate"));
391: retValue.add(bean);
392: }
393: return retValue;
394: } catch (SQLException sqle) {
395: log.error("Sql Execution Error!", sqle);
396: throw new DatabaseException(
397: "Error executing SQL in MemberGroupDAOImplJDBC.getBeans_limit.");
398: } finally {
399: DBUtils.closeResultSet(resultSet);
400: DBUtils.closeStatement(statement);
401: DBUtils.closeConnection(connection);
402: }
403: }
404:
405: }// end of class MemberGroupDAOImplJDBC
|