KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > mvnforum > db > jdbc > MemberGroupDAOImplJDBC


1 /*
2  * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/MemberGroupDAOImplJDBC.java,v 1.13 2006/04/14 17:05:26 minhnn Exp $
3  * $Author: minhnn $
4  * $Revision: 1.13 $
5  * $Date: 2006/04/14 17:05:26 $
6  *
7  * ====================================================================
8  *
9  * Copyright (C) 2002-2006 by MyVietnam.net
10  *
11  * All copyright notices regarding mvnForum MUST remain
12  * intact in the scripts and in the outputted HTML.
13  * The "powered by" text/logo with a link back to
14  * http://www.mvnForum.com and http://www.MyVietnam.net in
15  * the footer of the pages MUST remain visible when the pages
16  * are viewed on the internet or intranet.
17  *
18  * This program is free software; you can redistribute it and/or modify
19  * it under the terms of the GNU General Public License as published by
20  * the Free Software Foundation; either version 2 of the License, or
21  * any later version.
22  *
23  * This program is distributed in the hope that it will be useful,
24  * but WITHOUT ANY WARRANTY; without even the implied warranty of
25  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
26  * GNU General Public License for more details.
27  *
28  * You should have received a copy of the GNU General Public License
29  * along with this program; if not, write to the Free Software
30  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
31  *
32  * Support can be obtained from support forums at:
33  * http://www.mvnForum.com/mvnforum/index
34  *
35  * Correspondence and Marketing Questions can be sent to:
36  * info at MyVietnam net
37  *
38  * @author: Minh Nguyen
39  * @author: Mai Nguyen
40  */

41 package com.mvnforum.db.jdbc;
42
43 import java.sql.*;
44 import java.util.ArrayList JavaDoc;
45 import java.util.Collection JavaDoc;
46
47 import com.mvnforum.db.*;
48 import net.myvietnam.mvncore.db.DBUtils;
49 import net.myvietnam.mvncore.exception.*;
50 import org.apache.commons.logging.Log;
51 import org.apache.commons.logging.LogFactory;
52
53 public class MemberGroupDAOImplJDBC implements MemberGroupDAO {
54
55     private static Log log = LogFactory.getLog(MemberGroupDAOImplJDBC.class);
56
57     // this variable will support caching if cache for this class is needed
58
private static boolean m_dirty = true;
59
60     public MemberGroupDAOImplJDBC() {
61     }
62
63     protected static boolean isDirty() {
64         return m_dirty;
65     }
66
67     protected static void setDirty(boolean dirty) {
68         m_dirty = dirty;
69     }
70
71     public void findByPrimaryKey(int groupID, int memberID)
72         throws ObjectNotFoundException, DatabaseException {
73
74         Connection connection = null;
75         PreparedStatement statement = null;
76         ResultSet resultSet = null;
77         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
78         sql.append("SELECT GroupID, MemberID");
79         sql.append(" FROM " + TABLE_NAME);
80         sql.append(" WHERE GroupID = ? AND MemberID = ?");
81         try {
82             connection = DBUtils.getConnection();
83             statement = connection.prepareStatement(sql.toString());
84             statement.setInt(1, groupID);
85             statement.setInt(2, memberID);
86             resultSet = statement.executeQuery();
87             if (!resultSet.next()) {
88                 throw new ObjectNotFoundException("Cannot find the primary key (" + groupID + ", " + memberID + ") in table 'MemberGroup'.");
89             }
90         } catch(SQLException sqle) {
91             log.error("Sql Execution Error!", sqle);
92             throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.findByPrimaryKey.");
93         } finally {
94             DBUtils.closeResultSet(resultSet);
95             DBUtils.closeStatement(statement);
96             DBUtils.closeConnection(connection);
97         }
98     }
99
100     public void isTutor(int memberID)
101         throws ObjectNotFoundException, DatabaseException {
102
103         Connection connection = null;
104         PreparedStatement statement = null;
105         ResultSet resultSet = null;
106         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
107
108         sql.append(" SELECT * FROM " + TABLE_NAME + " membergroup , " + GroupsDAO.TABLE_NAME + " groups ");
109         sql.append(" WHERE membergroup.GroupID = groups.GroupID");
110         sql.append(" AND membergroup.MemberID = ?");
111         sql.append(" AND groups.GroupName = ?");
112         try {
113             connection = DBUtils.getConnection();
114             statement = connection.prepareStatement(sql.toString());
115             statement.setInt(1, memberID);
116             statement.setString(2, GroupsDAO.TUTOR_GROUP_NAME);
117             resultSet = statement.executeQuery();
118             if (!resultSet.next()) {
119                 throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + ") in table 'MemberGroup'.");
120             }
121         } catch(SQLException sqle) {
122             log.error("Sql Execution Error!", sqle);
123             throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.isTutor.");
124         } finally {
125             DBUtils.closeResultSet(resultSet);
126             DBUtils.closeStatement(statement);
127             DBUtils.closeConnection(connection);
128         }
129     }
130
131     /*
132      * Included columns: GroupID, MemberID, MemberName, Privilege, CreationDate,
133      * ModifiedDate
134      * Excluded columns:
135      */

136     public void create(int groupID, String JavaDoc memberName,
137                        int privilege, Timestamp creationDate, Timestamp modifiedDate)
138         throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {
139
140         int memberID = 0;
141         try {
142             // @todo: modify the parameter list as needed
143
// You may have to regenerate this method if the needed columns dont have attribute 'include'
144
DAOFactory.getMemberDAO().findByAlternateKey_MemberName(memberName);//redundant ???
145
memberID = DAOFactory.getMemberDAO().getMemberIDFromMemberName(memberName);
146         } catch(ObjectNotFoundException e) {
147             throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new MemberGroup.");
148         }
149
150         // @todo: comment this try-catch block if the needed columns dont have attribute 'include'
151
// If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
152
// However, if primary key is a auto_increament column, then you can safely delete this block
153
try {
154             //Check if primary key already exists
155
findByPrimaryKey(groupID, memberID);
156             //If so, then we have to throw an exception
157
throw new DuplicateKeyException("Primary key already exists. Cannot create new MemberGroup with the same [GroupID, MemberID] (" + groupID + ", " + memberID + ").");
158         } catch(ObjectNotFoundException e) {
159             //Otherwise we can go ahead
160
}
161
162         try {
163             // @todo: modify the parameter list as needed
164
// You may have to regenerate this method if the needed columns dont have attribute 'include'
165
DAOFactory.getGroupsDAO().findByPrimaryKey(groupID);
166         } catch(ObjectNotFoundException e) {
167             throw new ForeignKeyNotFoundException("Foreign key refers to table 'Groups' does not exist. Cannot create new MemberGroup.");
168         }
169
170         Connection connection = null;
171         PreparedStatement statement = null;
172         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
173         sql.append("INSERT INTO " + TABLE_NAME + " (GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate)");
174         sql.append(" VALUES (?, ?, ?, ?, ?, ?)");
175         try {
176             connection = DBUtils.getConnection();
177             statement = connection.prepareStatement(sql.toString());
178
179             statement.setInt(1, groupID);
180             statement.setInt(2, memberID);
181             statement.setString(3, memberName);
182             statement.setInt(4, privilege);
183             statement.setTimestamp(5, creationDate);
184             statement.setTimestamp(6, modifiedDate);
185
186             if (statement.executeUpdate() != 1) {
187                 throw new CreateException("Error adding a row into table 'MemberGroup'.");
188             }
189             m_dirty = true;
190         } catch(SQLException sqle) {
191             log.error("Sql Execution Error!", sqle);
192             throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.create.");
193         } finally {
194             DBUtils.closeStatement(statement);
195             DBUtils.closeConnection(connection);
196         }
197     }
198
199     public void delete(int groupID, int memberID)
200         throws DatabaseException, ObjectNotFoundException {
201
202         Connection connection = null;
203         PreparedStatement statement = null;
204         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
205         sql.append("DELETE FROM " + TABLE_NAME);
206         sql.append(" WHERE GroupID = ? AND MemberID = ?");
207
208         try {
209             connection = DBUtils.getConnection();
210             statement = connection.prepareStatement(sql.toString());
211             statement.setInt(1, groupID);
212             statement.setInt(2, memberID);
213             if (statement.executeUpdate() != 1) {
214                 throw new ObjectNotFoundException("Cannot delete a row in table MemberGroup where primary key = (" + groupID + ", " + memberID + ").");
215             }
216             m_dirty = true;
217         } catch(SQLException sqle) {
218             log.error("Sql Execution Error!", sqle);
219             throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.delete.");
220         } finally {
221             DBUtils.closeStatement(statement);
222             DBUtils.closeConnection(connection);
223         }
224     }
225
226     public void delete_inGroup(int groupID)
227         throws DatabaseException {
228
229         Connection connection = null;
230         PreparedStatement statement = null;
231         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
232         sql.append("DELETE FROM " + TABLE_NAME);
233         sql.append(" WHERE GroupID = ?");
234
235         try {
236             connection = DBUtils.getConnection();
237             statement = connection.prepareStatement(sql.toString());
238             statement.setInt(1, groupID);
239
240             statement.executeUpdate();
241             m_dirty = true;
242         } catch(SQLException sqle) {
243             log.error("Sql Execution Error!", sqle);
244             throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.delete_inGroup.");
245         } finally {
246             DBUtils.closeStatement(statement);
247             DBUtils.closeConnection(connection);
248         }
249     }
250
251     public void delete_inMember(int memberID)
252         throws DatabaseException {
253
254         Connection connection = null;
255         PreparedStatement statement = null;
256         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
257         sql.append("DELETE FROM " + TABLE_NAME);
258         sql.append(" WHERE MemberID = ?");
259
260         try {
261             connection = DBUtils.getConnection();
262             statement = connection.prepareStatement(sql.toString());
263             statement.setInt(1, memberID);
264
265             statement.executeUpdate();
266             m_dirty = true;
267         } catch(SQLException sqle) {
268             log.error("Sql Execution Error!", sqle);
269             throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.delete_inMember.");
270         } finally {
271             DBUtils.closeStatement(statement);
272             DBUtils.closeConnection(connection);
273         }
274     }
275
276 /************************************************
277  * Customized methods come below
278  ************************************************/

279     /*
280      * Included columns: MemberID, MemberName, Privilege, CreationDate, ModifiedDate
281      * Excluded columns: GroupID
282      */

283     public Collection JavaDoc getBeans_inGroup(int groupID)
284         throws DatabaseException {
285
286         Connection connection = null;
287         PreparedStatement statement = null;
288         ResultSet resultSet = null;
289         Collection JavaDoc retValue = new ArrayList JavaDoc();
290         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
291         sql.append("SELECT MemberID, MemberName, Privilege, CreationDate, ModifiedDate");
292         sql.append(" FROM " + TABLE_NAME);
293         sql.append(" WHERE GroupID = ?");
294         sql.append(" ORDER BY MemberID ASC ");
295         try {
296             connection = DBUtils.getConnection();
297             statement = connection.prepareStatement(sql.toString());
298             statement.setInt(1, groupID);
299             resultSet = statement.executeQuery();
300             while (resultSet.next()) {
301                 MemberGroupBean bean = new MemberGroupBean();
302                 bean.setGroupID(groupID);
303                 bean.setMemberID(resultSet.getInt("MemberID"));
304                 bean.setMemberName(resultSet.getString("MemberName"));
305                 bean.setPrivilege(resultSet.getInt("Privilege"));
306                 bean.setCreationDate(resultSet.getTimestamp("CreationDate"));
307                 bean.setModifiedDate(resultSet.getTimestamp("ModifiedDate"));
308                 retValue.add(bean);
309             }
310             return retValue;
311         } catch(SQLException sqle) {
312             log.error("Sql Execution Error!", sqle);
313             throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.getBeans_inGroup.");
314         } finally {
315             DBUtils.closeResultSet(resultSet);
316             DBUtils.closeStatement(statement);
317             DBUtils.closeConnection(connection);
318         }
319     }
320
321     public int getNumberOfBeans_inGroup(int groupID)
322         throws AssertionException, DatabaseException {
323
324         Connection connection = null;
325         PreparedStatement statement = null;
326         ResultSet resultSet = null;
327         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
328         sql.append("SELECT Count(*)");
329         sql.append(" FROM " + TABLE_NAME);
330         sql.append(" WHERE GroupID = ?");
331         try {
332             connection = DBUtils.getConnection();
333             statement = connection.prepareStatement(sql.toString());
334             statement.setInt(1, groupID);
335             resultSet = statement.executeQuery();
336             if (!resultSet.next()) {
337                 throw new AssertionException("Assertion in MemberGroupDAOImplJDBC.getNumberOfBeans_inGroup.");
338             }
339             return resultSet.getInt(1);
340         } catch(SQLException sqle) {
341             log.error("Sql Execution Error!", sqle);
342             throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.getNumberOfBeans_inGroup.");
343         } finally {
344             DBUtils.closeResultSet(resultSet);
345             DBUtils.closeStatement(statement);
346             DBUtils.closeConnection(connection);
347         }
348     }
349
350     /*
351      * Included columns: GroupID, MemberID, MemberName, Privilege, CreationDate,
352      * ModifiedDate
353      * Excluded columns:
354      */

355     // huumai: only support MySQL
356
//@todo: check if need support for other database???
357
public Collection JavaDoc getBeans_limit(int groupID, int offset, int rowsToReturn)
358         throws IllegalArgumentException JavaDoc, DatabaseException {
359         if (offset < 0) throw new IllegalArgumentException JavaDoc("The offset < 0 is not allowed.");
360         if (rowsToReturn <= 0) throw new IllegalArgumentException JavaDoc("The rowsToReturn <= 0 is not allowed.");
361
362         Connection connection = null;
363         PreparedStatement statement = null;
364         ResultSet resultSet = null;
365         Collection JavaDoc retValue = new ArrayList JavaDoc();
366         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
367         sql.append("SELECT GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate");
368         sql.append(" FROM " + TABLE_NAME);
369         sql.append(" WHERE GroupID = ?"); // @todo: uncomment as needed
370
//sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
371
sql.append(" LIMIT ?, ?");
372         try {
373             connection = DBUtils.getConnection();
374             statement = connection.prepareStatement(sql.toString());
375             statement.setInt(1, groupID);
376             statement.setInt(2, offset);
377             statement.setInt(3, rowsToReturn);
378             resultSet = statement.executeQuery();
379             while (resultSet.next()) {
380                 MemberGroupBean bean = new MemberGroupBean();
381                 bean.setGroupID(resultSet.getInt("GroupID"));
382                 bean.setMemberID(resultSet.getInt("MemberID"));
383                 bean.setMemberName(resultSet.getString("MemberName"));
384                 bean.setPrivilege(resultSet.getInt("Privilege"));
385                 bean.setCreationDate(resultSet.getTimestamp("CreationDate"));
386                 bean.setModifiedDate(resultSet.getTimestamp("ModifiedDate"));
387                 retValue.add(bean);
388             }
389             return retValue;
390         } catch(SQLException sqle) {
391             log.error("Sql Execution Error!", sqle);
392             throw new DatabaseException("Error executing SQL in MemberGroupDAOImplJDBC.getBeans_limit.");
393         } finally {
394             DBUtils.closeResultSet(resultSet);
395             DBUtils.closeStatement(statement);
396             DBUtils.closeConnection(connection);
397         }
398     }
399
400 }// end of class MemberGroupDAOImplJDBC
401
Popular Tags