001: /*
002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/GroupForumDAOImplJDBC.java,v 1.10 2007/10/09 11:09:20 lexuanttkhtn Exp $
003: * $Author: lexuanttkhtn $
004: * $Revision: 1.10 $
005: * $Date: 2007/10/09 11:09:20 $
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 org.apache.commons.logging.Log;
051: import org.apache.commons.logging.LogFactory;
052:
053: public class GroupForumDAOImplJDBC implements GroupForumDAO {
054:
055: private static Log log = LogFactory
056: .getLog(GroupForumDAOImplJDBC.class);
057:
058: // this variable will support caching if cache for this class is needed
059: private static boolean m_dirty = true;
060:
061: public GroupForumDAOImplJDBC() {
062: }
063:
064: protected static boolean isDirty() {
065: return m_dirty;
066: }
067:
068: protected static void setDirty(boolean dirty) {
069: m_dirty = dirty;
070: }
071:
072: public void findByPrimaryKey(int groupID, int forumID,
073: int permission) throws ObjectNotFoundException,
074: DatabaseException {
075:
076: Connection connection = null;
077: PreparedStatement statement = null;
078: ResultSet resultSet = null;
079: StringBuffer sql = new StringBuffer(512);
080: sql.append("SELECT GroupID, ForumID, Permission");
081: sql.append(" FROM " + TABLE_NAME);
082: sql
083: .append(" WHERE GroupID = ? AND ForumID = ? AND Permission = ?");
084: try {
085: connection = DBUtils.getConnection();
086: statement = connection.prepareStatement(sql.toString());
087: statement.setInt(1, groupID);
088: statement.setInt(2, forumID);
089: statement.setInt(3, permission);
090: resultSet = statement.executeQuery();
091: if (!resultSet.next()) {
092: throw new ObjectNotFoundException(
093: "Cannot find the primary key (" + groupID
094: + ", " + forumID + ", " + permission
095: + ") in table 'GroupForum'.");
096: }
097: } catch (SQLException sqle) {
098: log.error("Sql Execution Error!", sqle);
099: throw new DatabaseException(
100: "Error executing SQL in GroupForumDAOImplJDBC.findByPrimaryKey.");
101: } finally {
102: DBUtils.closeResultSet(resultSet);
103: DBUtils.closeStatement(statement);
104: DBUtils.closeConnection(connection);
105: }
106: }
107:
108: /*
109: * Included columns: GroupID, ForumID, Permission
110: * Excluded columns:
111: */
112: public void create(int groupID, int forumID, int permission)
113: throws CreateException, DatabaseException,
114: DuplicateKeyException, ForeignKeyNotFoundException {
115:
116: // @todo: comment this try-catch block if the needed columns dont have attribute 'include'
117: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
118: // However, if primary key is a auto_increament column, then you can safely delete this block
119: try {
120: //Check if primary key already exists
121: findByPrimaryKey(groupID, forumID, permission);
122: //If so, then we have to throw an exception
123: throw new DuplicateKeyException(
124: "Primary key already exists. Cannot create new GroupForum with the same [GroupID, ForumID, Permission] ("
125: + groupID
126: + ", "
127: + forumID
128: + ", "
129: + permission + ").");
130: } catch (ObjectNotFoundException e) {
131: //Otherwise we can go ahead
132: }
133:
134: try {
135: // @todo: modify the parameter list as needed
136: // You may have to regenerate this method if the needed columns dont have attribute 'include'
137: DAOFactory.getForumDAO().findByPrimaryKey(forumID);
138: } catch (ObjectNotFoundException e) {
139: throw new ForeignKeyNotFoundException(
140: "Foreign key refers to table 'Forum' does not exist. Cannot create new GroupForum.");
141: }
142:
143: try {
144: // @todo: modify the parameter list as needed
145: // You may have to regenerate this method if the needed columns dont have attribute 'include'
146: DAOFactory.getGroupsDAO().findByPrimaryKey(groupID);
147: } catch (ObjectNotFoundException e) {
148: throw new ForeignKeyNotFoundException(
149: "Foreign key refers to table 'Groups' does not exist. Cannot create new GroupForum.");
150: }
151:
152: Connection connection = null;
153: PreparedStatement statement = null;
154: StringBuffer sql = new StringBuffer(512);
155: sql.append("INSERT INTO " + TABLE_NAME
156: + " (GroupID, ForumID, Permission)");
157: sql.append(" VALUES (?, ?, ?)");
158: try {
159: connection = DBUtils.getConnection();
160: statement = connection.prepareStatement(sql.toString());
161:
162: statement.setInt(1, groupID);
163: statement.setInt(2, forumID);
164: statement.setInt(3, permission);
165:
166: if (statement.executeUpdate() != 1) {
167: throw new CreateException(
168: "Error adding a row into table 'GroupForum'.");
169: }
170: m_dirty = true;
171: } catch (SQLException sqle) {
172: log.error("Sql Execution Error!", sqle);
173: throw new DatabaseException(
174: "Error executing SQL in GroupForumDAOImplJDBC.create.");
175: } finally {
176: DBUtils.closeStatement(statement);
177: DBUtils.closeConnection(connection);
178: }
179: }
180:
181: public void delete(int groupID, int forumID, int permission)
182: throws DatabaseException, ObjectNotFoundException {
183:
184: Connection connection = null;
185: PreparedStatement statement = null;
186: StringBuffer sql = new StringBuffer(512);
187: sql.append("DELETE FROM " + TABLE_NAME);
188: sql
189: .append(" WHERE GroupID = ? AND ForumID = ? AND Permission = ?");
190:
191: try {
192: connection = DBUtils.getConnection();
193: statement = connection.prepareStatement(sql.toString());
194: statement.setInt(1, groupID);
195: statement.setInt(2, forumID);
196: statement.setInt(3, permission);
197: if (statement.executeUpdate() != 1) {
198: throw new ObjectNotFoundException(
199: "Cannot delete a row in table GroupForum where primary key = ("
200: + groupID + ", " + forumID + ", "
201: + permission + ").");
202: }
203: m_dirty = true;
204: } catch (SQLException sqle) {
205: log.error("Sql Execution Error!", sqle);
206: throw new DatabaseException(
207: "Error executing SQL in GroupForumDAOImplJDBC.delete.");
208: } finally {
209: DBUtils.closeStatement(statement);
210: DBUtils.closeConnection(connection);
211: }
212: }
213:
214: public void delete_inGroup(int groupID) throws DatabaseException {
215:
216: Connection connection = null;
217: PreparedStatement statement = null;
218: StringBuffer sql = new StringBuffer(512);
219: sql.append("DELETE FROM " + TABLE_NAME);
220: sql.append(" WHERE GroupID = ? ");
221:
222: try {
223: connection = DBUtils.getConnection();
224: statement = connection.prepareStatement(sql.toString());
225: statement.setInt(1, groupID);
226:
227: statement.executeUpdate();
228: m_dirty = true;
229: } catch (SQLException sqle) {
230: log.error("Sql Execution Error!", sqle);
231: throw new DatabaseException(
232: "Error executing SQL in GroupForumDAOImplJDBC.delete_inGroup.");
233: } finally {
234: DBUtils.closeStatement(statement);
235: DBUtils.closeConnection(connection);
236: }
237: }
238:
239: public void delete_inForum(int forumID) throws DatabaseException {
240:
241: Connection connection = null;
242: PreparedStatement statement = null;
243: StringBuffer sql = new StringBuffer(512);
244: sql.append("DELETE FROM " + TABLE_NAME);
245: sql.append(" WHERE ForumID = ? ");
246:
247: try {
248: connection = DBUtils.getConnection();
249: statement = connection.prepareStatement(sql.toString());
250: statement.setInt(1, forumID);
251:
252: statement.executeUpdate();
253: m_dirty = true;
254: } catch (SQLException sqle) {
255: log.error("Sql Execution Error!", sqle);
256: throw new DatabaseException(
257: "Error executing SQL in GroupForumDAOImplJDBC.delete_inForum.");
258: } finally {
259: DBUtils.closeStatement(statement);
260: DBUtils.closeConnection(connection);
261: }
262: }
263:
264: /************************************************
265: * Customized methods come below
266: ************************************************/
267:
268: /*
269: * Included columns: Permission
270: * Excluded columns: GroupID, ForumID
271: */
272: public Collection getBeans_inGroupForum(int groupID, int forumID)
273: throws DatabaseException {
274:
275: Connection connection = null;
276: PreparedStatement statement = null;
277: ResultSet resultSet = null;
278: Collection retValue = new ArrayList();
279: StringBuffer sql = new StringBuffer(512);
280: sql.append("SELECT Permission");
281: sql.append(" FROM " + TABLE_NAME);
282: sql.append(" WHERE GroupID = ? AND ForumID = ?"); // @todo: uncomment as needed
283: //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
284: try {
285: connection = DBUtils.getConnection();
286: statement = connection.prepareStatement(sql.toString());
287: statement.setInt(1, groupID);
288: statement.setInt(2, forumID);
289:
290: resultSet = statement.executeQuery();
291: while (resultSet.next()) {
292: GroupForumBean bean = new GroupForumBean();
293: bean.setGroupID(groupID);
294: bean.setForumID(forumID);
295: bean.setPermission(resultSet.getInt("Permission"));
296: retValue.add(bean);
297: }
298: return retValue;
299: } catch (SQLException sqle) {
300: log.error("Sql Execution Error!", sqle);
301: throw new DatabaseException(
302: "Error executing SQL in GroupForumDAOImplJDBC.getBeans_inGroupForum.");
303: } finally {
304: DBUtils.closeResultSet(resultSet);
305: DBUtils.closeStatement(statement);
306: DBUtils.closeConnection(connection);
307: }
308: }
309:
310: /*
311: * Included columns: GroupID, ForumID, Permission
312: * Excluded columns:
313: */
314: public Collection getBeans_inForum(int forumID)
315: throws DatabaseException {
316:
317: Connection connection = null;
318: PreparedStatement statement = null;
319: ResultSet resultSet = null;
320: Collection retValue = new ArrayList();
321: StringBuffer sql = new StringBuffer(512);
322: sql.append("SELECT GroupID, ForumID, Permission");
323: sql.append(" FROM " + TABLE_NAME);
324: sql.append(" WHERE ForumID = ? ");
325: sql.append(" ORDER BY GroupID ");
326: try {
327: connection = DBUtils.getConnection();
328: statement = connection.prepareStatement(sql.toString());
329: statement.setInt(1, forumID);
330:
331: resultSet = statement.executeQuery();
332: while (resultSet.next()) {
333: GroupForumBean bean = new GroupForumBean();
334: bean.setGroupID(resultSet.getInt("GroupID"));
335: bean.setForumID(resultSet.getInt("ForumID"));
336: bean.setPermission(resultSet.getInt("Permission"));
337: retValue.add(bean);
338: }
339: return retValue;
340: } catch (SQLException sqle) {
341: sqle.printStackTrace();
342: throw new DatabaseException(
343: "Error executing SQL in GroupForumDAOImplJDBC.getBeans_inForum.");
344: } finally {
345: DBUtils.closeResultSet(resultSet);
346: DBUtils.closeStatement(statement);
347: DBUtils.closeConnection(connection);
348: }
349: }
350:
351: /*
352: * Included columns: GroupID, ForumID, Permission
353: * Excluded columns:
354: */
355: public Collection getBeans_inGroup(int groupID)
356: throws DatabaseException {
357:
358: Connection connection = null;
359: PreparedStatement statement = null;
360: ResultSet resultSet = null;
361: Collection retValue = new ArrayList();
362: StringBuffer sql = new StringBuffer(512);
363: sql.append("SELECT GroupID, ForumID, Permission");
364: sql.append(" FROM " + TABLE_NAME);
365: sql.append(" WHERE GroupID = ? ");
366: sql.append(" ORDER BY ForumID ");
367:
368: try {
369: connection = DBUtils.getConnection();
370: statement = connection.prepareStatement(sql.toString());
371: statement.setInt(1, groupID);
372:
373: resultSet = statement.executeQuery();
374: while (resultSet.next()) {
375: GroupForumBean bean = new GroupForumBean();
376: bean.setGroupID(resultSet.getInt("GroupID"));
377: bean.setForumID(resultSet.getInt("ForumID"));
378: bean.setPermission(resultSet.getInt("Permission"));
379: retValue.add(bean);
380: }
381: return retValue;
382: } catch (SQLException sqle) {
383: sqle.printStackTrace();
384: throw new DatabaseException(
385: "Error executing SQL in GroupForumDAOImplJDBC.getBeans_inGroup.");
386: } finally {
387: DBUtils.closeResultSet(resultSet);
388: DBUtils.closeStatement(statement);
389: DBUtils.closeConnection(connection);
390: }
391: }
392:
393: /*
394: * Included columns: GroupID
395: * Excluded columns: Permission
396: */
397: public Collection getDistinctGroups() throws DatabaseException {
398:
399: Connection connection = null;
400: PreparedStatement statement = null;
401: ResultSet resultSet = null;
402: Collection retValue = new ArrayList();
403: StringBuffer sql = new StringBuffer(512);
404: sql.append("SELECT DISTINCT GroupID");
405: sql.append(" FROM " + TABLE_NAME);
406: sql.append(" ORDER BY GroupID ASC ");
407: try {
408: connection = DBUtils.getConnection();
409: statement = connection.prepareStatement(sql.toString());
410: resultSet = statement.executeQuery();
411: while (resultSet.next()) {
412: GroupForumBean bean = new GroupForumBean();
413: bean.setGroupID(resultSet.getInt("GroupID"));
414: retValue.add(bean);
415: }
416: return retValue;
417: } catch (SQLException sqle) {
418: log.error("Sql Execution Error!", sqle);
419: throw new DatabaseException(
420: "Error executing SQL in GroupForumDAOImplJDBC.getDistinctGroups.");
421: } finally {
422: DBUtils.closeResultSet(resultSet);
423: DBUtils.closeStatement(statement);
424: DBUtils.closeConnection(connection);
425: }
426: }
427: }// end of class GroupForumDAOImplJDBC
|