|
1
41 package com.mvnforum.db.jdbc;
42
43 import java.io.StringReader  ;
44 import java.sql.*;
45 import java.util.ArrayList ;
46 import java.util.Collection ;
47
48 import com.mvnforum.db.*;
49 import net.myvietnam.mvncore.db.DBUtils;
50 import net.myvietnam.mvncore.exception.*;
51 import org.apache.commons.logging.Log;
52 import org.apache.commons.logging.LogFactory;
53
54 public class GroupsDAOImplJDBC implements GroupsDAO {
55
56 private static Log log = LogFactory.getLog(GroupsDAOImplJDBC.class);
57
58 private static boolean m_dirty = true;
60
61 public GroupsDAOImplJDBC() {
62 }
63
64 protected static boolean isDirty() {
65 return m_dirty;
66 }
67
68 protected static void setDirty(boolean dirty) {
69 m_dirty = dirty;
70 }
71
72 public void findByPrimaryKey(int groupID)
73 throws ObjectNotFoundException, DatabaseException {
74
75 Connection connection = null;
76 PreparedStatement statement = null;
77 ResultSet resultSet = null;
78 StringBuffer sql = new StringBuffer (512);
79 sql.append("SELECT GroupID");
80 sql.append(" FROM " + TABLE_NAME);
81 sql.append(" WHERE GroupID = ?");
82 try {
83 connection = DBUtils.getConnection();
84 statement = connection.prepareStatement(sql.toString());
85 statement.setInt(1, groupID);
86 resultSet = statement.executeQuery();
87 if (!resultSet.next()) {
88 throw new ObjectNotFoundException("Cannot find the primary key (" + groupID + ") in table 'Groups'.");
89 }
90 } catch(SQLException sqle) {
91 log.error("Sql Execution Error!", sqle);
92 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.findByPrimaryKey.");
93 } finally {
94 DBUtils.closeResultSet(resultSet);
95 DBUtils.closeStatement(statement);
96 DBUtils.closeConnection(connection);
97 }
98 }
99
100 public void findByAlternateKey_GroupName(String groupName)
101 throws ObjectNotFoundException, DatabaseException {
102
103 Connection connection = null;
104 PreparedStatement statement = null;
105 ResultSet resultSet = null;
106 StringBuffer sql = new StringBuffer (512);
107 sql.append("SELECT GroupName");
108 sql.append(" FROM " + TABLE_NAME);
109 sql.append(" WHERE GroupName = ?");
110 try {
111 connection = DBUtils.getConnection();
112 statement = connection.prepareStatement(sql.toString());
113 statement.setString(1, groupName);
114 resultSet = statement.executeQuery();
115 if (!resultSet.next()) {
116 throw new ObjectNotFoundException("Cannot find the alternate key [GroupName] (" + groupName + ") in table 'Groups'.");
117 }
118 } catch(SQLException sqle) {
119 log.error("Sql Execution Error!", sqle);
120 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.findByAlternateKey_GroupName.");
121 } finally {
122 DBUtils.closeResultSet(resultSet);
123 DBUtils.closeStatement(statement);
124 DBUtils.closeConnection(connection);
125 }
126 }
127
128
133
137 public void create(String groupOwnerName, String groupName,
138 String groupDesc, int groupOption, Timestamp groupCreationDate,
139 Timestamp groupModifiedDate)
140 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {
141
142 int groupOwnerID = 0;
144 try {
147 findByAlternateKey_GroupName(groupName);
149 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Groups with the same [GroupName] (" + groupName + ").");
151 } catch(ObjectNotFoundException e) {
152 }
154
155 try {
156 groupOwnerID = 0;
159 if ((groupOwnerName!=null) && (groupOwnerName.length()>0)) { DAOFactory.getMemberDAO().findByAlternateKey_MemberName(groupOwnerName);
161 try {
162 groupOwnerID = DAOFactory.getMemberDAO().getMemberIDFromMemberName(groupOwnerName);
163 } catch (ObjectNotFoundException ex) {
164 throw new ObjectNotFoundException("ASSERTION: This should never happen.");
166 }
167 }
168 } catch(ObjectNotFoundException e) {
169 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Groups.");
170 }
171
172 Connection connection = null;
173 PreparedStatement statement = null;
174 StringBuffer sql = new StringBuffer (512);
175 sql.append("INSERT INTO " + TABLE_NAME + " (GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate)");
176 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?)");
177 try {
178 connection = DBUtils.getConnection();
179 statement = connection.prepareStatement(sql.toString());
180
181 statement.setInt(1, groupOwnerID);
182 statement.setString(2, groupOwnerName);
183 statement.setString(3, groupName);
184 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
185 statement.setCharacterStream(4, new StringReader (groupDesc), groupDesc.length());
186 } else {
187 statement.setString(4, groupDesc);
188 }
189 statement.setInt(5, groupOption);
190 statement.setTimestamp(6, groupCreationDate);
191 statement.setTimestamp(7, groupModifiedDate);
192
193 if (statement.executeUpdate() != 1) {
194 throw new CreateException("Error adding a row into table 'Groups'.");
195 }
196 m_dirty = true;
197 } catch(SQLException sqle) {
198 log.error("Sql Execution Error!", sqle);
199 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.create.");
200 } finally {
201 DBUtils.closeStatement(statement);
202 DBUtils.closeConnection(connection);
203 }
204 }
205
206 public void delete(int groupID)
207 throws DatabaseException, ObjectNotFoundException {
208
209 Connection connection = null;
210 PreparedStatement statement = null;
211 StringBuffer sql = new StringBuffer (512);
212 sql.append("DELETE FROM " + TABLE_NAME);
213 sql.append(" WHERE GroupID = ?");
214
215 try {
216 connection = DBUtils.getConnection();
217 statement = connection.prepareStatement(sql.toString());
218 statement.setInt(1, groupID);
219 if (statement.executeUpdate() != 1) {
220 throw new ObjectNotFoundException("Cannot delete a row in table Groups where primary key = (" + groupID + ").");
221 }
222 m_dirty = true;
223 } catch(SQLException sqle) {
224 log.error("Sql Execution Error!", sqle);
225 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.delete.");
226 } finally {
227 DBUtils.closeStatement(statement);
228 DBUtils.closeConnection(connection);
229 }
230 }
231
232
236 public void update(int groupID, String groupName, String groupDesc, Timestamp groupModifiedDate)
238 throws ObjectNotFoundException, DatabaseException, DuplicateKeyException {
239
240 GroupsBean bean = getGroup(groupID);
242 if ( !groupName.equals(bean.getGroupName()) ) {
243 try {
245 findByAlternateKey_GroupName(groupName);
246 throw new DuplicateKeyException("Alternate key [GroupName] (" + groupName + ")already exists. Cannot update Groups.");
247 } catch(ObjectNotFoundException e) {
248 }
250 }
251
252 Connection connection = null;
253 PreparedStatement statement = null;
254 StringBuffer sql = new StringBuffer (512);
255 sql.append("UPDATE " + TABLE_NAME + " SET GroupName = ?, GroupDesc = ?, GroupModifiedDate = ?");
256 sql.append(" WHERE GroupID = ?");
257 try {
258 connection = DBUtils.getConnection();
259 statement = connection.prepareStatement(sql.toString());
260
261 statement.setString(1, groupName);
263 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
264 statement.setCharacterStream(2, new StringReader (groupDesc), groupDesc.length());
265 } else {
266 statement.setString(2, groupDesc);
267 }
268 statement.setTimestamp(3, groupModifiedDate);
269
270 statement.setInt(4, groupID);
272
273 if (statement.executeUpdate() != 1) {
274 throw new ObjectNotFoundException("Cannot update table Groups where primary key = (" + groupID + ").");
275 }
276 m_dirty = true;
277 } catch(SQLException sqle) {
278 log.error("Sql Execution Error!", sqle);
279 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.update.");
280 } finally {
281 DBUtils.closeStatement(statement);
282 DBUtils.closeConnection(connection);
283 }
284 }
285
286
290 public void updateOwner(int groupID, String groupOwnerName, Timestamp groupModifiedDate)
292 throws ObjectNotFoundException, DatabaseException, ForeignKeyNotFoundException {
293
294 int groupOwnerID = 0;
296
298 try {
299 groupOwnerID = 0;
302 if ((groupOwnerName!=null) && (groupOwnerName.length()>0)) { DAOFactory.getMemberDAO().findByAlternateKey_MemberName(groupOwnerName);
304 try {
305 groupOwnerID = DAOFactory.getMemberDAO().getMemberIDFromMemberName(groupOwnerName);
306 } catch (ObjectNotFoundException ex) {
307 throw new ObjectNotFoundException("ASSERTION: This should never happen.");
309 }
310 }
311 } catch(ObjectNotFoundException e) {
312 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot update table 'Groups'.");
313 }
314
315 Connection connection = null;
316 PreparedStatement statement = null;
317 StringBuffer sql = new StringBuffer (512);
318 sql.append("UPDATE " + TABLE_NAME + " SET GroupOwnerID = ?, GroupOwnerName = ?, GroupModifiedDate = ?");
319 sql.append(" WHERE GroupID = ?");
320 try {
321 connection = DBUtils.getConnection();
322 statement = connection.prepareStatement(sql.toString());
323
324 statement.setInt(1, groupOwnerID);
326 statement.setString(2, groupOwnerName);
327 statement.setTimestamp(3, groupModifiedDate);
328
329 statement.setInt(4, groupID);
331
332 if (statement.executeUpdate() != 1) {
333 throw new ObjectNotFoundException("Cannot update table Groups where primary key = (" + groupID + ").");
334 }
335 m_dirty = true;
336 } catch(SQLException sqle) {
337 log.error("Sql Execution Error!", sqle);
338 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.updateOwner.");
339 } finally {
340 DBUtils.closeStatement(statement);
341 DBUtils.closeConnection(connection);
342 }
343 }
344
345
350 public GroupsBean getGroup(int groupID)
351 throws ObjectNotFoundException, DatabaseException {
352
353 Connection connection = null;
354 PreparedStatement statement = null;
355 ResultSet resultSet = null;
356 StringBuffer sql = new StringBuffer (512);
357 sql.append("SELECT GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate");
358 sql.append(" FROM " + TABLE_NAME);
359 sql.append(" WHERE GroupID = ?");
360 try {
361 connection = DBUtils.getConnection();
362 statement = connection.prepareStatement(sql.toString());
363 statement.setInt(1, groupID);
364 resultSet = statement.executeQuery();
365 if(!resultSet.next()) {
366 throw new ObjectNotFoundException("Cannot find the row in table Groups where primary key = (" + groupID + ").");
367 }
368
369 GroupsBean bean = new GroupsBean();
370 bean.setGroupID(groupID);
371 bean.setGroupOwnerID(resultSet.getInt("GroupOwnerID"));
372 bean.setGroupOwnerName(resultSet.getString("GroupOwnerName"));
373 bean.setGroupName(resultSet.getString("GroupName"));
374 bean.setGroupDesc(resultSet.getString("GroupDesc"));
375 bean.setGroupOption(resultSet.getInt("GroupOption"));
376 bean.setGroupCreationDate(resultSet.getTimestamp("GroupCreationDate"));
377 bean.setGroupModifiedDate(resultSet.getTimestamp("GroupModifiedDate"));
378 return bean;
379 } catch(SQLException sqle) {
380 log.error("Sql Execution Error!", sqle);
381 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getGroup(pk).");
382 } finally {
383 DBUtils.closeResultSet(resultSet);
384 DBUtils.closeStatement(statement);
385 DBUtils.closeConnection(connection);
386 }
387 }
388
389 public Collection getMyGroups(int memberID)
390 throws DatabaseException {
391
392 Connection connection = null;
393 PreparedStatement statement = null;
394 ResultSet resultSet = null;
395 StringBuffer sql = new StringBuffer (512);
396 ArrayList retValue = new ArrayList ();
397 sql.append("SELECT g.GroupID, g.GroupName, g.GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate");
398 sql.append(" FROM " + TABLE_NAME).append(" g, ").append(MemberGroupDAO.TABLE_NAME).append(" mg");
399 sql.append(" WHERE (g.GroupID = mg.GroupID) AND (mg.MemberID = ?)");
400 sql.append(" ORDER BY g.GroupID ASC");
401
402 try {
403 connection = DBUtils.getConnection();
404 statement = connection.prepareStatement(sql.toString());
405 statement.setInt(1, memberID);
406 resultSet = statement.executeQuery();
407 while(resultSet.next()) {
408 GroupsBean bean = new GroupsBean();
409 bean.setGroupID(resultSet.getInt("GroupID"));
410 bean.setGroupName(resultSet.getString("GroupName"));
411 bean.setGroupDesc(resultSet.getString("GroupDesc"));
412 bean.setGroupOption(resultSet.getInt("GroupOption"));
413 bean.setGroupCreationDate(resultSet.getTimestamp("GroupCreationDate"));
414 bean.setGroupModifiedDate(resultSet.getTimestamp("GroupModifiedDate"));
415 retValue.add(bean);
416 }
417 return retValue;
418 } catch(SQLException sqle) {
419 log.error("Sql Execution Error!", sqle);
420 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getMyGroups(memberID).");
421 } finally {
422 DBUtils.closeResultSet(resultSet);
423 DBUtils.closeStatement(statement);
424 DBUtils.closeConnection(connection);
425 }
426 }
427
428
433 public Collection getGroups()
434 throws DatabaseException {
435
436 Connection connection = null;
437 PreparedStatement statement = null;
438 ResultSet resultSet = null;
439 Collection retValue = new ArrayList ();
440 StringBuffer sql = new StringBuffer (512);
441 sql.append("SELECT GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate");
442 sql.append(" FROM " + TABLE_NAME);
443 sql.append(" ORDER BY GroupID ASC"); try {
447 connection = DBUtils.getConnection();
448 statement = connection.prepareStatement(sql.toString());
449 resultSet = statement.executeQuery();
450 while (resultSet.next()) {
451 GroupsBean bean = new GroupsBean();
452 bean.setGroupID(resultSet.getInt("GroupID"));
453 bean.setGroupOwnerID(resultSet.getInt("GroupOwnerID"));
454 bean.setGroupOwnerName(resultSet.getString("GroupOwnerName"));
455 bean.setGroupName(resultSet.getString("GroupName"));
456 bean.setGroupDesc(resultSet.getString("GroupDesc"));
457 bean.setGroupOption(resultSet.getInt("GroupOption"));
458 bean.setGroupCreationDate(resultSet.getTimestamp("GroupCreationDate"));
459 bean.setGroupModifiedDate(resultSet.getTimestamp("GroupModifiedDate"));
460 retValue.add(bean);
461 }
462 return retValue;
463 } catch(SQLException sqle) {
464 log.error("Sql Execution Error!", sqle);
465 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getBeans.");
466 } finally {
467 DBUtils.closeResultSet(resultSet);
468 DBUtils.closeStatement(statement);
469 DBUtils.closeConnection(connection);
470 }
471 }
472
473 public int getNumberOfGroups()
474 throws AssertionException, DatabaseException {
475
476 Connection connection = null;
477 PreparedStatement statement = null;
478 ResultSet resultSet = null;
479 StringBuffer sql = new StringBuffer (512);
480 sql.append("SELECT Count(*)");
481 sql.append(" FROM " + TABLE_NAME);
482 try {
484 connection = DBUtils.getConnection();
485 statement = connection.prepareStatement(sql.toString());
486 resultSet = statement.executeQuery();
487 if (!resultSet.next()) {
488 throw new AssertionException("Assertion in GroupsDAOImplJDBC.getNumberOfGroups.");
489 }
490 return resultSet.getInt(1);
491 } catch(SQLException sqle) {
492 log.error("Sql Execution Error!", sqle);
493 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getNumberOfGroups.");
494 } finally {
495 DBUtils.closeResultSet(resultSet);
496 DBUtils.closeStatement(statement);
497 DBUtils.closeConnection(connection);
498 }
499 }
500
501
504
505
506 public int getGroupIDFromGroupName(String groupName)
507 throws ObjectNotFoundException, DatabaseException {
508
509 Connection connection = null;
510 PreparedStatement statement = null;
511 ResultSet resultSet = null;
512 String sql = "SELECT GroupID FROM " + TABLE_NAME + " WHERE GroupName = ?";
513 try {
514 connection = DBUtils.getConnection();
515 statement = connection.prepareStatement(sql);
516 statement.setString(1, groupName);
517 resultSet = statement.executeQuery();
518 if(!resultSet.next()) {
519 throw new ObjectNotFoundException("Cannot find the row in table Group where GroupName = " + groupName);
520 }
521 return resultSet.getInt(1);
522 } catch(SQLException sqle) {
523 log.error("Sql Execution Error!", sqle);
524 throw new DatabaseException("Error executing SQL in GroupsDAOImplJDBC.getGroupIDFromGroupName.");
525 } finally {
526 DBUtils.closeResultSet(resultSet);
527 DBUtils.closeStatement(statement);
528 DBUtils.closeConnection(connection);
529 }
530 }
531
532 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.