001: /*
002: * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/GroupsDAOImplJDBC.java,v 1.26 2007/12/17 09:09:39 minhnn Exp $
003: * $Author: minhnn $
004: * $Revision: 1.26 $
005: * $Date: 2007/12/17 09:09:39 $
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.io.StringReader;
044: import java.sql.*;
045: import java.util.ArrayList;
046: import java.util.Collection;
047:
048: import com.mvnforum.db.*;
049: import net.myvietnam.mvncore.db.DBUtils;
050: import net.myvietnam.mvncore.exception.*;
051: import net.myvietnam.mvncore.util.AssertionUtil;
052:
053: import org.apache.commons.logging.Log;
054: import org.apache.commons.logging.LogFactory;
055:
056: public class GroupsDAOImplJDBC implements GroupsDAO {
057:
058: private static Log log = LogFactory.getLog(GroupsDAOImplJDBC.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 GroupsDAOImplJDBC() {
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)
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");
082: sql.append(" FROM " + TABLE_NAME);
083: sql.append(" WHERE GroupID = ?");
084: try {
085: connection = DBUtils.getConnection();
086: statement = connection.prepareStatement(sql.toString());
087: statement.setInt(1, groupID);
088: resultSet = statement.executeQuery();
089: if (!resultSet.next()) {
090: throw new ObjectNotFoundException(
091: "Cannot find the primary key (" + groupID
092: + ") in table 'Groups'.");
093: }
094: } catch (SQLException sqle) {
095: log.error("Sql Execution Error!", sqle);
096: throw new DatabaseException(
097: "Error executing SQL in GroupsDAOImplJDBC.findByPrimaryKey.");
098: } finally {
099: DBUtils.closeResultSet(resultSet);
100: DBUtils.closeStatement(statement);
101: DBUtils.closeConnection(connection);
102: }
103: }
104:
105: public void findByAlternateKey_GroupName(String groupName)
106: throws ObjectNotFoundException, DatabaseException {
107:
108: Connection connection = null;
109: PreparedStatement statement = null;
110: ResultSet resultSet = null;
111: StringBuffer sql = new StringBuffer(512);
112: sql.append("SELECT GroupName");
113: sql.append(" FROM " + TABLE_NAME);
114: if (DBUtils.isCaseSensitiveDatebase()) {
115: sql.append(" WHERE lower(GroupName) = lower(?)");
116: } else {
117: sql.append(" WHERE GroupName = ?");
118: }
119: try {
120: connection = DBUtils.getConnection();
121: statement = connection.prepareStatement(sql.toString());
122: statement.setString(1, groupName);
123: resultSet = statement.executeQuery();
124: if (!resultSet.next()) {
125: throw new ObjectNotFoundException(
126: "Cannot find the alternate key [GroupName] ("
127: + groupName + ") in table 'Groups'.");
128: }
129: } catch (SQLException sqle) {
130: log.error("Sql Execution Error!", sqle);
131: throw new DatabaseException(
132: "Error executing SQL in GroupsDAOImplJDBC.findByAlternateKey_GroupName.");
133: } finally {
134: DBUtils.closeResultSet(resultSet);
135: DBUtils.closeStatement(statement);
136: DBUtils.closeConnection(connection);
137: }
138: }
139:
140: /*
141: * Included columns: GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption,
142: * GroupCreationDate, GroupModifiedDate
143: * Excluded columns: GroupID
144: */
145: /**
146: * NOTE: This is a customized method, it get groupOwnerID from groupOwnerName
147: * and I remove the groupOwnerID from the parameter list
148: */
149: public void create(String groupOwnerName, String groupName,
150: String groupDesc, int groupOption,
151: Timestamp groupCreationDate, Timestamp groupModifiedDate)
152: throws CreateException, DatabaseException,
153: DuplicateKeyException, ForeignKeyNotFoundException {
154:
155: int groupOwnerID = 0;// MUST init to 0, or this method will be wrong
156:
157: // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
158: // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
159: try {
160: //Check if alternate key already exists
161: findByAlternateKey_GroupName(groupName);
162: //If so, then we have to throw an exception
163: throw new DuplicateKeyException(
164: "Alternate key already exists. Cannot create new Groups with the same [GroupName] ("
165: + groupName + ").");
166: } catch (ObjectNotFoundException e) {
167: //Otherwise we can go ahead
168: }
169:
170: try {
171: // @todo: modify the parameter list as needed
172: // You may have to regenerate this method if the needed columns dont have attribute 'include'
173: groupOwnerID = 0;
174: if ((groupOwnerName != null)
175: && (groupOwnerName.length() > 0)) {// have group owner
176: DAOFactory.getMemberDAO()
177: .findByAlternateKey_MemberName(groupOwnerName);
178: try {
179: groupOwnerID = DAOFactory.getMemberDAO()
180: .getMemberIDFromMemberName(groupOwnerName);
181: } catch (ObjectNotFoundException ex) {
182: // This exception should never be thrown
183: throw new ObjectNotFoundException(
184: "ASSERTION: This should never happen.");
185: }
186: }
187: } catch (ObjectNotFoundException e) {
188: throw new ForeignKeyNotFoundException(
189: "Foreign key refers to table 'Member' does not exist. Cannot create new Groups.");
190: }
191:
192: Connection connection = null;
193: PreparedStatement statement = null;
194: StringBuffer sql = new StringBuffer(512);
195: sql
196: .append("INSERT INTO "
197: + TABLE_NAME
198: + " (GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate)");
199: sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?)");
200: try {
201: connection = DBUtils.getConnection();
202: statement = connection.prepareStatement(sql.toString());
203:
204: statement.setInt(1, groupOwnerID);
205: statement.setString(2, groupOwnerName);
206: statement.setString(3, groupName);
207: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
208: statement.setCharacterStream(4, new StringReader(
209: groupDesc), groupDesc.length());
210: } else {
211: statement.setString(4, groupDesc);
212: }
213: statement.setInt(5, groupOption);
214: statement.setTimestamp(6, groupCreationDate);
215: statement.setTimestamp(7, groupModifiedDate);
216:
217: if (statement.executeUpdate() != 1) {
218: throw new CreateException(
219: "Error adding a row into table 'Groups'.");
220: }
221: m_dirty = true;
222: } catch (SQLException sqle) {
223: log.error("Sql Execution Error!", sqle);
224: throw new DatabaseException(
225: "Error executing SQL in GroupsDAOImplJDBC.create.");
226: } finally {
227: DBUtils.closeStatement(statement);
228: DBUtils.closeConnection(connection);
229: }
230: }
231:
232: public void delete(int groupID) throws DatabaseException,
233: ObjectNotFoundException {
234:
235: Connection connection = null;
236: PreparedStatement statement = null;
237: StringBuffer sql = new StringBuffer(512);
238: sql.append("DELETE FROM " + TABLE_NAME);
239: sql.append(" WHERE GroupID = ?");
240:
241: try {
242: connection = DBUtils.getConnection();
243: statement = connection.prepareStatement(sql.toString());
244: statement.setInt(1, groupID);
245: if (statement.executeUpdate() != 1) {
246: throw new ObjectNotFoundException(
247: "Cannot delete a row in table Groups where primary key = ("
248: + groupID + ").");
249: }
250: m_dirty = true;
251: } catch (SQLException sqle) {
252: log.error("Sql Execution Error!", sqle);
253: throw new DatabaseException(
254: "Error executing SQL in GroupsDAOImplJDBC.delete.");
255: } finally {
256: DBUtils.closeStatement(statement);
257: DBUtils.closeConnection(connection);
258: }
259: }
260:
261: /*
262: * Included columns: GroupName, GroupDesc, GroupModifiedDate
263: * Excluded columns: GroupID, GroupOwnerID, GroupOption, GroupCreationDate
264: */
265: public void update(
266: int groupID, // primary key
267: String groupName, String groupDesc,
268: Timestamp groupModifiedDate)
269: throws ObjectNotFoundException, DatabaseException,
270: DuplicateKeyException {
271:
272: GroupsBean bean = getGroup(groupID); // @todo: comment or delete this line if no alternate key are included
273:
274: if (groupName.equalsIgnoreCase(bean.getGroupName()) == false) {
275: // Groups tries to change its alternate key <GroupName>, so we must check if it already exist
276: try {
277: findByAlternateKey_GroupName(groupName);
278: throw new DuplicateKeyException(
279: "Alternate key [GroupName] ("
280: + groupName
281: + ") already exists. Cannot update Groups.");
282: } catch (ObjectNotFoundException e) {
283: //Otherwise we can go ahead
284: }
285: }
286:
287: Connection connection = null;
288: PreparedStatement statement = null;
289: StringBuffer sql = new StringBuffer(512);
290: sql
291: .append("UPDATE "
292: + TABLE_NAME
293: + " SET GroupName = ?, GroupDesc = ?, GroupModifiedDate = ?");
294: sql.append(" WHERE GroupID = ?");
295: try {
296: connection = DBUtils.getConnection();
297: statement = connection.prepareStatement(sql.toString());
298:
299: // // column(s) to update
300: statement.setString(1, groupName);
301: if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
302: statement.setCharacterStream(2, new StringReader(
303: groupDesc), groupDesc.length());
304: } else {
305: statement.setString(2, groupDesc);
306: }
307: statement.setTimestamp(3, groupModifiedDate);
308:
309: // primary key column(s)
310: statement.setInt(4, groupID);
311:
312: if (statement.executeUpdate() != 1) {
313: throw new ObjectNotFoundException(
314: "Cannot update table Groups where primary key = ("
315: + groupID + ").");
316: }
317: m_dirty = true;
318: } catch (SQLException sqle) {
319: log.error("Sql Execution Error!", sqle);
320: throw new DatabaseException(
321: "Error executing SQL in GroupsDAOImplJDBC.update.");
322: } finally {
323: DBUtils.closeStatement(statement);
324: DBUtils.closeConnection(connection);
325: }
326: }
327:
328: /*
329: * Included columns: GroupOwnerID, GroupOwnerName, GroupModifiedDate
330: * Excluded columns: GroupID, GroupName, GroupDesc, GroupOption, GroupCreationDate
331: */
332: public void updateOwner(int groupID, // primary key
333: String groupOwnerName, Timestamp groupModifiedDate)
334: throws ObjectNotFoundException, DatabaseException,
335: ForeignKeyNotFoundException {
336:
337: int groupOwnerID = 0;//MUST init to 0
338:
339: //GroupsBean bean = getGroup(groupID); // @todo: comment or delete this line if no alternate key are included
340:
341: try {
342: // @todo: modify the parameter list as needed
343: // If this method does not change the foreign key columns, you can comment this block of code.
344: groupOwnerID = 0;
345: if ((groupOwnerName != null)
346: && (groupOwnerName.length() > 0)) {// have group owner
347: groupOwnerName = DAOFactory.getMemberDAO()
348: .findByAlternateKey_MemberName(groupOwnerName);
349: try {
350: groupOwnerID = DAOFactory.getMemberDAO()
351: .getMemberIDFromMemberName(groupOwnerName);
352: } catch (ObjectNotFoundException ex) {
353: // This exception should never be thrown
354: throw new ObjectNotFoundException(
355: "ASSERTION: This should never happen.");
356: }
357: }
358: } catch (ObjectNotFoundException e) {
359: throw new ForeignKeyNotFoundException(
360: "Foreign key refers to table 'Member' does not exist. Cannot update table 'Groups'.");
361: }
362:
363: Connection connection = null;
364: PreparedStatement statement = null;
365: StringBuffer sql = new StringBuffer(512);
366: sql
367: .append("UPDATE "
368: + TABLE_NAME
369: + " SET GroupOwnerID = ?, GroupOwnerName = ?, GroupModifiedDate = ?");
370: sql.append(" WHERE GroupID = ?");
371: try {
372: connection = DBUtils.getConnection();
373: statement = connection.prepareStatement(sql.toString());
374:
375: // // column(s) to update
376: statement.setInt(1, groupOwnerID);
377: statement.setString(2, groupOwnerName);
378: statement.setTimestamp(3, groupModifiedDate);
379:
380: // primary key column(s)
381: statement.setInt(4, groupID);
382:
383: if (statement.executeUpdate() != 1) {
384: throw new ObjectNotFoundException(
385: "Cannot update table Groups where primary key = ("
386: + groupID + ").");
387: }
388: m_dirty = true;
389: } catch (SQLException sqle) {
390: log.error("Sql Execution Error!", sqle);
391: throw new DatabaseException(
392: "Error executing SQL in GroupsDAOImplJDBC.updateOwner.");
393: } finally {
394: DBUtils.closeStatement(statement);
395: DBUtils.closeConnection(connection);
396: }
397: }
398:
399: /*
400: * Included columns: GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption,
401: * GroupCreationDate, GroupModifiedDate
402: * Excluded columns: GroupID
403: */
404: public GroupsBean getGroup(int groupID)
405: throws ObjectNotFoundException, DatabaseException {
406:
407: Connection connection = null;
408: PreparedStatement statement = null;
409: ResultSet resultSet = null;
410: StringBuffer sql = new StringBuffer(512);
411: sql
412: .append("SELECT GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate");
413: sql.append(" FROM " + TABLE_NAME);
414: sql.append(" WHERE GroupID = ?");
415: try {
416: connection = DBUtils.getConnection();
417: statement = connection.prepareStatement(sql.toString());
418: statement.setInt(1, groupID);
419: resultSet = statement.executeQuery();
420: if (!resultSet.next()) {
421: throw new ObjectNotFoundException(
422: "Cannot find the row in table Groups where primary key = ("
423: + groupID + ").");
424: }
425:
426: GroupsBean bean = new GroupsBean();
427: bean.setGroupID(groupID);
428: bean.setGroupOwnerID(resultSet.getInt("GroupOwnerID"));
429: bean.setGroupOwnerName(resultSet
430: .getString("GroupOwnerName"));
431: bean.setGroupName(resultSet.getString("GroupName"));
432: bean.setGroupDesc(resultSet.getString("GroupDesc"));
433: bean.setGroupOption(resultSet.getInt("GroupOption"));
434: bean.setGroupCreationDate(resultSet
435: .getTimestamp("GroupCreationDate"));
436: bean.setGroupModifiedDate(resultSet
437: .getTimestamp("GroupModifiedDate"));
438: return bean;
439: } catch (SQLException sqle) {
440: log.error("Sql Execution Error!", sqle);
441: throw new DatabaseException(
442: "Error executing SQL in GroupsDAOImplJDBC.getGroup(pk).");
443: } finally {
444: DBUtils.closeResultSet(resultSet);
445: DBUtils.closeStatement(statement);
446: DBUtils.closeConnection(connection);
447: }
448: }
449:
450: public Collection getMyGroups(int memberID)
451: throws DatabaseException {
452:
453: Connection connection = null;
454: PreparedStatement statement = null;
455: ResultSet resultSet = null;
456: StringBuffer sql = new StringBuffer(512);
457: ArrayList retValue = new ArrayList();
458: sql
459: .append("SELECT g.GroupID, g.GroupName, g.GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate");
460: sql.append(" FROM " + TABLE_NAME).append(" g, ").append(
461: MemberGroupDAO.TABLE_NAME).append(" mg");
462: sql
463: .append(" WHERE (g.GroupID = mg.GroupID) AND (mg.MemberID = ?)");
464: sql.append(" ORDER BY g.GroupID ASC");
465:
466: try {
467: connection = DBUtils.getConnection();
468: statement = connection.prepareStatement(sql.toString());
469: statement.setInt(1, memberID);
470: resultSet = statement.executeQuery();
471: while (resultSet.next()) {
472: GroupsBean bean = new GroupsBean();
473: bean.setGroupID(resultSet.getInt("GroupID"));
474: bean.setGroupName(resultSet.getString("GroupName"));
475: bean.setGroupDesc(resultSet.getString("GroupDesc"));
476: bean.setGroupOption(resultSet.getInt("GroupOption"));
477: bean.setGroupCreationDate(resultSet
478: .getTimestamp("GroupCreationDate"));
479: bean.setGroupModifiedDate(resultSet
480: .getTimestamp("GroupModifiedDate"));
481: retValue.add(bean);
482: }
483: return retValue;
484: } catch (SQLException sqle) {
485: log.error("Sql Execution Error!", sqle);
486: throw new DatabaseException(
487: "Error executing SQL in GroupsDAOImplJDBC.getMyGroups(memberID).");
488: } finally {
489: DBUtils.closeResultSet(resultSet);
490: DBUtils.closeStatement(statement);
491: DBUtils.closeConnection(connection);
492: }
493: }
494:
495: /*
496: * Included columns: GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,
497: * GroupOption, GroupCreationDate, GroupModifiedDate
498: * Excluded columns:
499: */
500: public Collection getGroups() throws DatabaseException {
501:
502: Connection connection = null;
503: PreparedStatement statement = null;
504: ResultSet resultSet = null;
505: Collection retValue = new ArrayList();
506: StringBuffer sql = new StringBuffer(512);
507: sql
508: .append("SELECT GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate");
509: sql.append(" FROM " + TABLE_NAME);
510: //sql.append(" WHERE "); // @todo: uncomment as needed
511: //minhnn: @todo: it should be GroupID, not GroupName
512: sql.append(" ORDER BY GroupID ASC");// must sort be id to show number of members in group
513: try {
514: connection = DBUtils.getConnection();
515: statement = connection.prepareStatement(sql.toString());
516: resultSet = statement.executeQuery();
517: while (resultSet.next()) {
518: GroupsBean bean = new GroupsBean();
519: bean.setGroupID(resultSet.getInt("GroupID"));
520: bean.setGroupOwnerID(resultSet.getInt("GroupOwnerID"));
521: bean.setGroupOwnerName(resultSet
522: .getString("GroupOwnerName"));
523: bean.setGroupName(resultSet.getString("GroupName"));
524: bean.setGroupDesc(resultSet.getString("GroupDesc"));
525: bean.setGroupOption(resultSet.getInt("GroupOption"));
526: bean.setGroupCreationDate(resultSet
527: .getTimestamp("GroupCreationDate"));
528: bean.setGroupModifiedDate(resultSet
529: .getTimestamp("GroupModifiedDate"));
530: retValue.add(bean);
531: }
532: return retValue;
533: } catch (SQLException sqle) {
534: log.error("Sql Execution Error!", sqle);
535: throw new DatabaseException(
536: "Error executing SQL in GroupsDAOImplJDBC.getBeans.");
537: } finally {
538: DBUtils.closeResultSet(resultSet);
539: DBUtils.closeStatement(statement);
540: DBUtils.closeConnection(connection);
541: }
542: }
543:
544: public int getNumberOfGroups() throws DatabaseException {
545:
546: Connection connection = null;
547: PreparedStatement statement = null;
548: ResultSet resultSet = null;
549: StringBuffer sql = new StringBuffer(512);
550: sql.append("SELECT Count(*)");
551: sql.append(" FROM " + TABLE_NAME);
552: //sql.append(" WHERE "); // @todo: uncomment as needed
553: try {
554: connection = DBUtils.getConnection();
555: statement = connection.prepareStatement(sql.toString());
556: resultSet = statement.executeQuery();
557: AssertionUtil
558: .doAssert(resultSet.next(),
559: "Assertion in GroupsDAOImplJDBC.getNumberOfGroups.");
560: return resultSet.getInt(1);
561: } catch (SQLException sqle) {
562: log.error("Sql Execution Error!", sqle);
563: throw new DatabaseException(
564: "Error executing SQL in GroupsDAOImplJDBC.getNumberOfGroups.");
565: } finally {
566: DBUtils.closeResultSet(resultSet);
567: DBUtils.closeStatement(statement);
568: DBUtils.closeConnection(connection);
569: }
570: }
571:
572: /************************************************
573: * Customized methods come below
574: ************************************************/
575:
576: /* @todo check if this method work with other DBMS other than MySql (check case-sensitive) */
577: public int getGroupIDFromGroupName(String groupName)
578: throws ObjectNotFoundException, DatabaseException {
579:
580: Connection connection = null;
581: PreparedStatement statement = null;
582: ResultSet resultSet = null;
583: String sql = "SELECT GroupID FROM " + TABLE_NAME
584: + " WHERE GroupName = ?";
585: try {
586: connection = DBUtils.getConnection();
587: statement = connection.prepareStatement(sql);
588: statement.setString(1, groupName);
589: resultSet = statement.executeQuery();
590: if (!resultSet.next()) {
591: throw new ObjectNotFoundException(
592: "Cannot find the row in table Group where GroupName = "
593: + groupName);
594: }
595: return resultSet.getInt(1);
596: } catch (SQLException sqle) {
597: log.error("Sql Execution Error!", sqle);
598: throw new DatabaseException(
599: "Error executing SQL in GroupsDAOImplJDBC.getGroupIDFromGroupName.");
600: } finally {
601: DBUtils.closeResultSet(resultSet);
602: DBUtils.closeStatement(statement);
603: DBUtils.closeConnection(connection);
604: }
605: }
606:
607: }// end of class GroupsDAOImplJDBC
|