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 MemberGroupDAOImplJDBC implements MemberGroupDAO { 54 55 private static Log log = LogFactory.getLog(MemberGroupDAOImplJDBC.class); 56 57 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 sql = new StringBuffer (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 sql = new StringBuffer (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 136 public void create(int groupID, String memberName, 137 int privilege, Timestamp creationDate, Timestamp modifiedDate) 138 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException { 139 140 int memberID = 0; 141 try { 142 DAOFactory.getMemberDAO().findByAlternateKey_MemberName(memberName); 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 try { 154 findByPrimaryKey(groupID, memberID); 156 throw new DuplicateKeyException("Primary key already exists. Cannot create new MemberGroup with the same [GroupID, MemberID] (" + groupID + ", " + memberID + ")."); 158 } catch(ObjectNotFoundException e) { 159 } 161 162 try { 163 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 sql = new StringBuffer (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 sql = new StringBuffer (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 sql = new StringBuffer (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 sql = new StringBuffer (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 279 283 public Collection getBeans_inGroup(int groupID) 284 throws DatabaseException { 285 286 Connection connection = null; 287 PreparedStatement statement = null; 288 ResultSet resultSet = null; 289 Collection retValue = new ArrayList (); 290 StringBuffer sql = new StringBuffer (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 sql = new StringBuffer (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 355 public Collection getBeans_limit(int groupID, int offset, int rowsToReturn) 358 throws IllegalArgumentException , DatabaseException { 359 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed."); 360 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed."); 361 362 Connection connection = null; 363 PreparedStatement statement = null; 364 ResultSet resultSet = null; 365 Collection retValue = new ArrayList (); 366 StringBuffer sql = new StringBuffer (512); 367 sql.append("SELECT GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate"); 368 sql.append(" FROM " + TABLE_NAME); 369 sql.append(" WHERE GroupID = ?"); 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 } | Popular Tags |