|
1
41 package com.mvnforum.db.jdbc;
42
43 import java.sql.*;
44 import java.util.ArrayList  ;
45 import java.util.Collection ;
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 GroupForumDAOImplJDBC implements GroupForumDAO {
54
55 private static Log log = LogFactory.getLog(GroupForumDAOImplJDBC.class);
56
57 private static boolean m_dirty = true;
59
60 public GroupForumDAOImplJDBC() {
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 forumID, int permission)
72 throws ObjectNotFoundException, DatabaseException {
73
74 Connection connection = null;
75 PreparedStatement statement = null;
76 ResultSet resultSet = null;
77 StringBuffer sql = new StringBuffer (512);
78 sql.append("SELECT GroupID, ForumID, Permission");
79 sql.append(" FROM " + TABLE_NAME);
80 sql.append(" WHERE GroupID = ? AND ForumID = ? AND Permission = ?");
81 try {
82 connection = DBUtils.getConnection();
83 statement = connection.prepareStatement(sql.toString());
84 statement.setInt(1, groupID);
85 statement.setInt(2, forumID);
86 statement.setInt(3, permission);
87 resultSet = statement.executeQuery();
88 if (!resultSet.next()) {
89 throw new ObjectNotFoundException("Cannot find the primary key (" + groupID + ", " + forumID + ", " + permission + ") in table 'GroupForum'.");
90 }
91 } catch(SQLException sqle) {
92 log.error("Sql Execution Error!", sqle);
93 throw new DatabaseException("Error executing SQL in GroupForumDAOImplJDBC.findByPrimaryKey.");
94 } finally {
95 DBUtils.closeResultSet(resultSet);
96 DBUtils.closeStatement(statement);
97 DBUtils.closeConnection(connection);
98 }
99 }
100
101
105 public void create(int groupID, int forumID, int permission)
106 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {
107
108 try {
112 findByPrimaryKey(groupID, forumID, permission);
114 throw new DuplicateKeyException("Primary key already exists. Cannot create new GroupForum with the same [GroupID, ForumID, Permission] (" + groupID + ", " + forumID + ", " + permission + ").");
116 } catch(ObjectNotFoundException e) {
117 }
119
120 try {
121 DAOFactory.getForumDAO().findByPrimaryKey(forumID);
124 } catch(ObjectNotFoundException e) {
125 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot create new GroupForum.");
126 }
127
128 try {
129 DAOFactory.getGroupsDAO().findByPrimaryKey(groupID);
132 } catch(ObjectNotFoundException e) {
133 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Groups' does not exist. Cannot create new GroupForum.");
134 }
135
136 Connection connection = null;
137 PreparedStatement statement = null;
138 StringBuffer sql = new StringBuffer (512);
139 sql.append("INSERT INTO " + TABLE_NAME + " (GroupID, ForumID, Permission)");
140 sql.append(" VALUES (?, ?, ?)");
141 try {
142 connection = DBUtils.getConnection();
143 statement = connection.prepareStatement(sql.toString());
144
145 statement.setInt(1, groupID);
146 statement.setInt(2, forumID);
147 statement.setInt(3, permission);
148
149 if (statement.executeUpdate() != 1) {
150 throw new CreateException("Error adding a row into table 'GroupForum'.");
151 }
152 m_dirty = true;
153 } catch(SQLException sqle) {
154 log.error("Sql Execution Error!", sqle);
155 throw new DatabaseException("Error executing SQL in GroupForumDAOImplJDBC.create.");
156 } finally {
157 DBUtils.closeStatement(statement);
158 DBUtils.closeConnection(connection);
159 }
160 }
161
162 public void delete(int groupID, int forumID, int permission)
163 throws DatabaseException, ObjectNotFoundException {
164
165 Connection connection = null;
166 PreparedStatement statement = null;
167 StringBuffer sql = new StringBuffer (512);
168 sql.append("DELETE FROM " + TABLE_NAME);
169 sql.append(" WHERE GroupID = ? AND ForumID = ? AND Permission = ?");
170
171 try {
172 connection = DBUtils.getConnection();
173 statement = connection.prepareStatement(sql.toString());
174 statement.setInt(1, groupID);
175 statement.setInt(2, forumID);
176 statement.setInt(3, permission);
177 if (statement.executeUpdate() != 1) {
178 throw new ObjectNotFoundException("Cannot delete a row in table GroupForum where primary key = (" + groupID + ", " + forumID + ", " + permission + ").");
179 }
180 m_dirty = true;
181 } catch(SQLException sqle) {
182 log.error("Sql Execution Error!", sqle);
183 throw new DatabaseException("Error executing SQL in GroupForumDAOImplJDBC.delete.");
184 } finally {
185 DBUtils.closeStatement(statement);
186 DBUtils.closeConnection(connection);
187 }
188 }
189
190 public void delete_inGroup(int groupID)
191 throws DatabaseException {
192
193 Connection connection = null;
194 PreparedStatement statement = null;
195 StringBuffer sql = new StringBuffer (512);
196 sql.append("DELETE FROM " + TABLE_NAME);
197 sql.append(" WHERE GroupID = ? ");
198
199 try {
200 connection = DBUtils.getConnection();
201 statement = connection.prepareStatement(sql.toString());
202 statement.setInt(1, groupID);
203
204 statement.executeUpdate();
205 m_dirty = true;
206 } catch(SQLException sqle) {
207 log.error("Sql Execution Error!", sqle);
208 throw new DatabaseException("Error executing SQL in GroupForumDAOImplJDBC.delete_inGroup.");
209 } finally {
210 DBUtils.closeStatement(statement);
211 DBUtils.closeConnection(connection);
212 }
213 }
214
215 public void delete_inForum(int forumID)
216 throws DatabaseException {
217
218 Connection connection = null;
219 PreparedStatement statement = null;
220 StringBuffer sql = new StringBuffer (512);
221 sql.append("DELETE FROM " + TABLE_NAME);
222 sql.append(" WHERE ForumID = ? ");
223
224 try {
225 connection = DBUtils.getConnection();
226 statement = connection.prepareStatement(sql.toString());
227 statement.setInt(1, forumID);
228
229 statement.executeUpdate();
230 m_dirty = true;
231 } catch(SQLException sqle) {
232 log.error("Sql Execution Error!", sqle);
233 throw new DatabaseException("Error executing SQL in GroupForumDAOImplJDBC.delete_inForum.");
234 } finally {
235 DBUtils.closeStatement(statement);
236 DBUtils.closeConnection(connection);
237 }
238 }
239
240
241
244
245
249 public Collection getBeans_inGroupForum(int groupID, int forumID)
250 throws DatabaseException {
251
252 Connection connection = null;
253 PreparedStatement statement = null;
254 ResultSet resultSet = null;
255 Collection retValue = new ArrayList ();
256 StringBuffer sql = new StringBuffer (512);
257 sql.append("SELECT Permission");
258 sql.append(" FROM " + TABLE_NAME);
259 sql.append(" WHERE GroupID = ? AND ForumID = ?"); try {
262 connection = DBUtils.getConnection();
263 statement = connection.prepareStatement(sql.toString());
264 statement.setInt(1, groupID);
265 statement.setInt(2, forumID);
266
267 resultSet = statement.executeQuery();
268 while (resultSet.next()) {
269 GroupForumBean bean = new GroupForumBean();
270 bean.setGroupID(groupID);
271 bean.setForumID(forumID);
272 bean.setPermission(resultSet.getInt("Permission"));
273 retValue.add(bean);
274 }
275 return retValue;
276 } catch(SQLException sqle) {
277 log.error("Sql Execution Error!", sqle);
278 throw new DatabaseException("Error executing SQL in GroupForumDAOImplJDBC.getBeans_inGroupForum.");
279 } finally {
280 DBUtils.closeResultSet(resultSet);
281 DBUtils.closeStatement(statement);
282 DBUtils.closeConnection(connection);
283 }
284 }
285
286
290 public Collection getBeans_inForum(int forumID)
291 throws DatabaseException {
292
293 Connection connection = null;
294 PreparedStatement statement = null;
295 ResultSet resultSet = null;
296 Collection retValue = new ArrayList ();
297 StringBuffer sql = new StringBuffer (512);
298 sql.append("SELECT GroupID, ForumID, Permission");
299 sql.append(" FROM " + TABLE_NAME);
300 sql.append(" WHERE ForumID = ? ");
301 sql.append(" ORDER BY GroupID ");
302 try {
303 connection = DBUtils.getConnection();
304 statement = connection.prepareStatement(sql.toString());
305 statement.setInt(1, forumID);
306
307 resultSet = statement.executeQuery();
308 while (resultSet.next()) {
309 GroupForumBean bean = new GroupForumBean();
310 bean.setGroupID(resultSet.getInt("GroupID"));
311 bean.setForumID(resultSet.getInt("ForumID"));
312 bean.setPermission(resultSet.getInt("Permission"));
313 retValue.add(bean);
314 }
315 return retValue;
316 } catch (SQLException sqle) {
317 sqle.printStackTrace();
318 throw new DatabaseException("Error executing SQL in GroupForumDAOImplJDBC.getBeans_inForum.");
319 } finally {
320 DBUtils.closeResultSet(resultSet);
321 DBUtils.closeStatement(statement);
322 DBUtils.closeConnection(connection);
323 }
324 }
325
326
330 public Collection getBeans_inGroup(int groupID)
331 throws DatabaseException {
332
333 Connection connection = null;
334 PreparedStatement statement = null;
335 ResultSet resultSet = null;
336 Collection retValue = new ArrayList ();
337 StringBuffer sql = new StringBuffer (512);
338 sql.append("SELECT GroupID, ForumID, Permission");
339 sql.append(" FROM " + TABLE_NAME);
340 sql.append(" WHERE GroupID = ? ");
341 sql.append(" ORDER BY ForumID ");
342
343 try {
344 connection = DBUtils.getConnection();
345 statement = connection.prepareStatement(sql.toString());
346 statement.setInt(1, groupID);
347
348 resultSet = statement.executeQuery();
349 while (resultSet.next()) {
350 GroupForumBean bean = new GroupForumBean();
351 bean.setGroupID(resultSet.getInt("GroupID"));
352 bean.setForumID(resultSet.getInt("ForumID"));
353 bean.setPermission(resultSet.getInt("Permission"));
354 retValue.add(bean);
355 }
356 return retValue;
357 } catch (SQLException sqle) {
358 sqle.printStackTrace();
359 throw new DatabaseException("Error executing SQL in GroupForumDAOImplJDBC.getBeans_inGroup.");
360 } finally {
361 DBUtils.closeResultSet(resultSet);
362 DBUtils.closeStatement(statement);
363 DBUtils.closeConnection(connection);
364 }
365 }
366
367
371 public Collection getDistinctGroups()
372 throws DatabaseException {
373
374 Connection connection = null;
375 PreparedStatement statement = null;
376 ResultSet resultSet = null;
377 Collection retValue = new ArrayList ();
378 StringBuffer sql = new StringBuffer (512);
379 sql.append("SELECT DISTINCT GroupID");
380 sql.append(" FROM " + TABLE_NAME);
381 sql.append(" ORDER BY GroupID ASC ");
382 try {
383 connection = DBUtils.getConnection();
384 statement = connection.prepareStatement(sql.toString());
385 resultSet = statement.executeQuery();
386 while (resultSet.next()) {
387 GroupForumBean bean = new GroupForumBean();
388 bean.setGroupID(resultSet.getInt("GroupID"));
389 retValue.add(bean);
390 }
391 return retValue;
392 } catch(SQLException sqle) {
393 log.error("Sql Execution Error!", sqle);
394 throw new DatabaseException("Error executing SQL in GroupForumDAOImplJDBC.getDistinctGroups.");
395 } finally {
396 DBUtils.closeResultSet(resultSet);
397 DBUtils.closeStatement(statement);
398 DBUtils.closeConnection(connection);
399 }
400 }
401 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.