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 MemberForumDAOImplJDBC implements MemberForumDAO { 54 55 private static Log log = LogFactory.getLog(MemberForumDAOImplJDBC.class); 56 57 private static boolean m_dirty = true; 59 60 public MemberForumDAOImplJDBC() { 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 memberID, 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 MemberID, ForumID, Permission"); 79 sql.append(" FROM " + TABLE_NAME); 80 sql.append(" WHERE MemberID = ? AND ForumID = ? AND Permission = ?"); 81 try { 82 connection = DBUtils.getConnection(); 83 statement = connection.prepareStatement(sql.toString()); 84 statement.setInt(1, memberID); 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 (" + memberID + ", " + forumID + ", " + permission + ") in table 'MemberForum'."); 90 } 91 } catch(SQLException sqle) { 92 log.error("Sql Execution Error!", sqle); 93 throw new DatabaseException("Error executing SQL in MemberForumDAOImplJDBC.findByPrimaryKey."); 94 } finally { 95 DBUtils.closeResultSet(resultSet); 96 DBUtils.closeStatement(statement); 97 DBUtils.closeConnection(connection); 98 } 99 } 100 101 105 public void create(int memberID, int forumID, int permission) 106 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException { 107 108 try { 112 findByPrimaryKey(memberID, forumID, permission); 114 throw new DuplicateKeyException("Primary key already exists. Cannot create new MemberForum with the same [MemberID, ForumID, Permission] (" + memberID + ", " + forumID + ", " + permission + ")."); 116 } catch(ObjectNotFoundException e) { 117 } 119 120 if (memberID!=0) { 121 try { 122 DAOFactory.getMemberDAO().findByPrimaryKey(memberID); 125 } catch(ObjectNotFoundException e) { 126 throw new ForeignKeyNotFoundException("Foreign key refers to table 'mvnforumMember' does not exist. Cannot create new MemberForum."); 127 } 128 } 129 130 try { 131 DAOFactory.getForumDAO().findByPrimaryKey(forumID); 134 } catch(ObjectNotFoundException e) { 135 throw new ForeignKeyNotFoundException("Foreign key refers to table 'mvnforumForum' does not exist. Cannot create new MemberForum."); 136 } 137 138 Connection connection = null; 139 PreparedStatement statement = null; 140 StringBuffer sql = new StringBuffer (512); 141 sql.append("INSERT INTO " + TABLE_NAME + " (MemberID, ForumID, Permission)"); 142 sql.append(" VALUES (?, ?, ?)"); 143 try { 144 connection = DBUtils.getConnection(); 145 statement = connection.prepareStatement(sql.toString()); 146 147 statement.setInt(1, memberID); 148 statement.setInt(2, forumID); 149 statement.setInt(3, permission); 150 151 if (statement.executeUpdate() != 1) { 152 throw new CreateException("Error adding a row into table 'MemberForum'."); 153 } 154 m_dirty = true; 155 } catch(SQLException sqle) { 156 log.error("Sql Execution Error!", sqle); 157 throw new DatabaseException("Error executing SQL in MemberForumDAOImplJDBC.create."); 158 } finally { 159 DBUtils.closeStatement(statement); 160 DBUtils.closeConnection(connection); 161 } 162 } 163 164 public void delete(int memberID, int forumID, int permission) 165 throws DatabaseException, ObjectNotFoundException { 166 167 Connection connection = null; 168 PreparedStatement statement = null; 169 StringBuffer sql = new StringBuffer (512); 170 sql.append("DELETE FROM " + TABLE_NAME); 171 sql.append(" WHERE MemberID = ? AND ForumID = ? AND Permission = ?"); 172 173 try { 174 connection = DBUtils.getConnection(); 175 statement = connection.prepareStatement(sql.toString()); 176 statement.setInt(1, memberID); 177 statement.setInt(2, forumID); 178 statement.setInt(3, permission); 179 if (statement.executeUpdate() != 1) { 180 throw new ObjectNotFoundException("Cannot delete a row in table MemberForum where primary key = (" + memberID + ", " + forumID + ", " + permission + ")."); 181 } 182 m_dirty = true; 183 } catch(SQLException sqle) { 184 log.error("Sql Execution Error!", sqle); 185 throw new DatabaseException("Error executing SQL in MemberForumDAOImplJDBC.delete."); 186 } finally { 187 DBUtils.closeStatement(statement); 188 DBUtils.closeConnection(connection); 189 } 190 } 191 192 public void delete_inMember(int memberID) 193 throws DatabaseException { 194 195 Connection connection = null; 196 PreparedStatement statement = null; 197 StringBuffer sql = new StringBuffer (512); 198 sql.append("DELETE FROM " + TABLE_NAME); 199 sql.append(" WHERE MemberID = ?"); 200 201 try { 202 connection = DBUtils.getConnection(); 203 statement = connection.prepareStatement(sql.toString()); 204 statement.setInt(1, memberID); 205 statement.executeUpdate(); 206 m_dirty = true; 207 } catch(SQLException sqle) { 208 log.error("Sql Execution Error!", sqle); 209 throw new DatabaseException("Error executing SQL in MemberForumDAOImplJDBC.delete_inMember."); 210 } finally { 211 DBUtils.closeStatement(statement); 212 DBUtils.closeConnection(connection); 213 } 214 } 215 216 public void delete_inForum(int forumID) 217 throws DatabaseException { 218 219 Connection connection = null; 220 PreparedStatement statement = null; 221 StringBuffer sql = new StringBuffer (512); 222 sql.append("DELETE FROM " + TABLE_NAME); 223 sql.append(" WHERE ForumID = ? "); 224 225 try { 226 connection = DBUtils.getConnection(); 227 statement = connection.prepareStatement(sql.toString()); 228 statement.setInt(1, forumID); 229 230 statement.executeUpdate(); 231 m_dirty = true; 232 } catch(SQLException sqle) { 233 log.error("Sql Execution Error!", sqle); 234 throw new DatabaseException("Error executing SQL in MemberForumDAOImplJDBC.delete_inForum."); 235 } finally { 236 DBUtils.closeStatement(statement); 237 DBUtils.closeConnection(connection); 238 } 239 } 240 241 245 public Collection getBeans_inMemberForum(int memberID, int forumID) 246 throws DatabaseException { 247 248 Connection connection = null; 249 PreparedStatement statement = null; 250 ResultSet resultSet = null; 251 Collection retValue = new ArrayList (); 252 StringBuffer sql = new StringBuffer (512); 253 sql.append("SELECT Permission"); 254 sql.append(" FROM " + TABLE_NAME); 255 sql.append(" WHERE MemberID = ? AND ForumID = ?"); 256 try { 257 connection = DBUtils.getConnection(); 258 statement = connection.prepareStatement(sql.toString()); 259 statement.setInt(1, memberID); 260 statement.setInt(2, forumID); 261 262 resultSet = statement.executeQuery(); 263 while (resultSet.next()) { 264 MemberForumBean bean = new MemberForumBean(); 265 bean.setMemberID(memberID); 266 bean.setForumID(forumID); 267 bean.setPermission(resultSet.getInt("Permission")); 268 retValue.add(bean); 269 } 270 return retValue; 271 } catch (SQLException sqle) { 272 log.error("Sql Execution Error!", sqle); 273 throw new DatabaseException("Error executing SQL in MemberForumDAOImplJDBC.getBeans_inMemberForum."); 274 } finally { 275 DBUtils.closeResultSet(resultSet); 276 DBUtils.closeStatement(statement); 277 DBUtils.closeConnection(connection); 278 } 279 } 280 281 285 public Collection getBeans_inMember(int memberID) 286 throws DatabaseException { 287 288 Connection connection = null; 289 PreparedStatement statement = null; 290 ResultSet resultSet = null; 291 Collection retValue = new ArrayList (); 292 293 StringBuffer sql = new StringBuffer (512); 294 sql.append("SELECT MemberID, ForumID, Permission"); 295 sql.append(" FROM " + TABLE_NAME); 296 sql.append(" WHERE MemberID = ?"); 297 try { 298 connection = DBUtils.getConnection(); 299 statement = connection.prepareStatement(sql.toString()); 300 statement.setInt(1, memberID); 301 resultSet = statement.executeQuery(); 302 while (resultSet.next()) { 303 MemberForumBean bean = new MemberForumBean(); 304 bean.setMemberID(resultSet.getInt("MemberID")); 305 bean.setForumID(resultSet.getInt("ForumID")); 306 bean.setPermission(resultSet.getInt("Permission")); 307 retValue.add(bean); 308 } 309 return retValue; 310 } catch (SQLException sqle) { 311 log.error("Sql Execution Error!", sqle); 312 throw new DatabaseException("Error executing SQL in MemberForumDAOImplJDBC.getBeans_inMember."); 313 } finally { 314 DBUtils.closeResultSet(resultSet); 315 DBUtils.closeStatement(statement); 316 DBUtils.closeConnection(connection); 317 } 318 } 319 320 324 public Collection getBeans_inForum(int forumID) 325 throws DatabaseException { 326 327 Connection connection = null; 328 PreparedStatement statement = null; 329 ResultSet resultSet = null; 330 Collection retValue = new ArrayList (); 331 332 StringBuffer sql = new StringBuffer (512); 333 sql.append("SELECT MemberID, ForumID, Permission"); 334 sql.append(" FROM " + TABLE_NAME); 335 sql.append(" WHERE ForumID = ?"); 336 sql.append(" ORDER BY MemberID "); 337 338 try { 339 connection = DBUtils.getConnection(); 340 statement = connection.prepareStatement(sql.toString()); 341 statement.setInt(1, forumID); 342 resultSet = statement.executeQuery(); 343 while (resultSet.next()) { 344 MemberForumBean bean = new MemberForumBean(); 345 bean.setMemberID(resultSet.getInt("MemberID")); 346 bean.setForumID(resultSet.getInt("ForumID")); 347 bean.setPermission(resultSet.getInt("Permission")); 348 retValue.add(bean); 349 } 350 return retValue; 351 } catch (SQLException sqle) { 352 log.error("Sql Execution Error!", sqle); 353 throw new DatabaseException("Error executing SQL in MemberForumDAOImplJDBC.getBeans_inForum."); 354 } finally { 355 DBUtils.closeResultSet(resultSet); 356 DBUtils.closeStatement(statement); 357 DBUtils.closeConnection(connection); 358 } 359 } 360 } | Popular Tags |