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.CategoryBean; 49 import com.mvnforum.db.CategoryDAO; 50 import net.myvietnam.mvncore.db.DBUtils; 51 import net.myvietnam.mvncore.exception.*; 52 import org.apache.commons.logging.Log; 53 import org.apache.commons.logging.LogFactory; 54 55 public class CategoryDAOImplJDBC implements CategoryDAO { 56 57 private static Log log = LogFactory.getLog(CategoryDAOImplJDBC.class); 58 59 private static boolean m_dirty = true; 61 62 public CategoryDAOImplJDBC() { 63 } 64 65 public static boolean isDirty() { 66 return m_dirty; 67 } 68 69 public static void setDirty(boolean dirty) { 70 m_dirty = dirty; 71 } 72 73 public void findByPrimaryKey(int categoryID) 74 throws ObjectNotFoundException, DatabaseException { 75 76 Connection connection = null; 77 PreparedStatement statement = null; 78 ResultSet resultSet = null; 79 StringBuffer sql = new StringBuffer (512); 80 sql.append("SELECT CategoryID"); 81 sql.append(" FROM " + TABLE_NAME); 82 sql.append(" WHERE CategoryID = ?"); 83 try { 84 connection = DBUtils.getConnection(); 85 statement = connection.prepareStatement(sql.toString()); 86 statement.setInt(1, categoryID); 87 resultSet = statement.executeQuery(); 88 if (!resultSet.next()) { 89 throw new ObjectNotFoundException("Cannot find the primary key (" + categoryID + ") in table 'Category'."); 90 } 91 } catch(SQLException sqle) { 92 log.error("Sql Execution Error!", sqle); 93 throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.findByPrimaryKey."); 94 } finally { 95 DBUtils.closeResultSet(resultSet); 96 DBUtils.closeStatement(statement); 97 DBUtils.closeConnection(connection); 98 } 99 } 100 101 public void findByAlternateKey_CategoryName(String categoryName) 102 throws ObjectNotFoundException, DatabaseException { 103 104 Connection connection = null; 105 PreparedStatement statement = null; 106 ResultSet resultSet = null; 107 StringBuffer sql = new StringBuffer (512); 108 sql.append("SELECT CategoryName"); 109 sql.append(" FROM " + TABLE_NAME); 110 sql.append(" WHERE CategoryName = ?"); 111 try { 112 connection = DBUtils.getConnection(); 113 statement = connection.prepareStatement(sql.toString()); 114 statement.setString(1, categoryName); 115 resultSet = statement.executeQuery(); 116 if (!resultSet.next()) { 117 throw new ObjectNotFoundException("Cannot find the alternate key [CategoryName] (" + categoryName + ") in table 'Category'."); 118 } 119 } catch(SQLException sqle) { 120 log.error("Sql Execution Error!", sqle); 121 throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.findByAlternateKey_CategoryName."); 122 } finally { 123 DBUtils.closeResultSet(resultSet); 124 DBUtils.closeStatement(statement); 125 DBUtils.closeConnection(connection); 126 } 127 } 128 129 134 public void create(int parentCategoryID, String categoryName, String categoryDesc, 135 Timestamp categoryCreationDate, Timestamp categoryModifiedDate, int categoryOrder, 136 int categoryOption, int categoryStatus) 137 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException { 138 139 try { 142 findByAlternateKey_CategoryName(categoryName); 144 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Category with the same [CategoryName] (" + categoryName + ")."); 146 } catch(ObjectNotFoundException e) { 147 } 149 150 try { 151 if (parentCategoryID != 0) { 154 findByPrimaryKey(parentCategoryID); 155 } 156 } catch(ObjectNotFoundException e) { 157 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Category' does not exist. Cannot create new Category."); 158 } 159 160 Connection connection = null; 161 PreparedStatement statement = null; 162 StringBuffer sql = new StringBuffer (512); 163 sql.append("INSERT INTO " + TABLE_NAME + " (ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus)"); 164 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); 165 try { 166 connection = DBUtils.getConnection(); 167 statement = connection.prepareStatement(sql.toString()); 168 169 statement.setInt(1, parentCategoryID); 170 statement.setString(2, categoryName); 171 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) { 172 statement.setCharacterStream(3, new StringReader (categoryDesc), categoryDesc.length()); 173 } else { 174 statement.setString(3, categoryDesc); 175 } 176 statement.setTimestamp(4, categoryCreationDate); 177 statement.setTimestamp(5, categoryModifiedDate); 178 statement.setInt(6, categoryOrder); 179 statement.setInt(7, categoryOption); 180 statement.setInt(8, categoryStatus); 181 182 if (statement.executeUpdate() != 1) { 183 throw new CreateException("Error adding a row into table 'Category'."); 184 } 185 m_dirty = true; 186 } catch(SQLException sqle) { 187 log.error("Sql Execution Error!", sqle); 188 throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.create."); 189 } finally { 190 DBUtils.closeStatement(statement); 191 DBUtils.closeConnection(connection); 192 } 193 } 194 195 public void delete(int categoryID) 196 throws DatabaseException, ObjectNotFoundException { 197 198 Connection connection = null; 199 PreparedStatement statement = null; 200 StringBuffer sql = new StringBuffer (512); 201 sql.append("DELETE FROM " + TABLE_NAME); 202 sql.append(" WHERE CategoryID = ?"); 203 204 try { 205 connection = DBUtils.getConnection(); 206 statement = connection.prepareStatement(sql.toString()); 207 statement.setInt(1, categoryID); 208 if (statement.executeUpdate() != 1) { 209 throw new ObjectNotFoundException("Cannot delete a row in table Category where primary key = (" + categoryID + ")."); 210 } 211 m_dirty = true; 212 } catch(SQLException sqle) { 213 log.error("Sql Execution Error!", sqle); 214 throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.delete."); 215 } finally { 216 DBUtils.closeStatement(statement); 217 DBUtils.closeConnection(connection); 218 } 219 } 220 221 226 public void update(int categoryID, String categoryName, String categoryDesc, Timestamp categoryModifiedDate, 228 int categoryOrder, int categoryOption, int categoryStatus) 229 throws ObjectNotFoundException, DatabaseException, DuplicateKeyException { 230 231 CategoryBean bean = getCategory(categoryID); 233 if ( !categoryName.equals(bean.getCategoryName()) ) { 234 try { 236 findByAlternateKey_CategoryName(categoryName); 237 throw new DuplicateKeyException("Alternate key [CategoryName] (" + categoryName + ")already exists. Cannot update Category."); 238 } catch(ObjectNotFoundException e) { 239 } 241 } 242 243 Connection connection = null; 244 PreparedStatement statement = null; 245 StringBuffer sql = new StringBuffer (512); 246 sql.append("UPDATE " + TABLE_NAME + " SET CategoryName = ?, CategoryDesc = ?, CategoryModifiedDate = ?, CategoryOrder = ?, CategoryOption = ?, CategoryStatus = ?"); 247 sql.append(" WHERE CategoryID = ?"); 248 try { 249 connection = DBUtils.getConnection(); 250 statement = connection.prepareStatement(sql.toString()); 251 252 statement.setString(1, categoryName); 254 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) { 255 statement.setCharacterStream(2, new StringReader (categoryDesc), categoryDesc.length()); 256 } else { 257 statement.setString(2, categoryDesc); 258 } 259 statement.setTimestamp(3, categoryModifiedDate); 260 statement.setInt(4, categoryOrder); 261 statement.setInt(5, categoryOption); 262 statement.setInt(6, categoryStatus); 263 264 statement.setInt(7, categoryID); 266 267 if (statement.executeUpdate() != 1) { 268 throw new ObjectNotFoundException("Cannot update table Category where primary key = (" + categoryID + ")."); 269 } 270 m_dirty = true; 271 } catch(SQLException sqle) { 272 log.error("Sql Execution Error!", sqle); 273 throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.update."); 274 } finally { 275 DBUtils.closeStatement(statement); 276 DBUtils.closeConnection(connection); 277 } 278 } 279 280 285 public CategoryBean getCategory(int categoryID) 286 throws ObjectNotFoundException, DatabaseException { 287 288 Connection connection = null; 289 PreparedStatement statement = null; 290 ResultSet resultSet = null; 291 StringBuffer sql = new StringBuffer (512); 292 sql.append("SELECT ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus"); 293 sql.append(" FROM " + TABLE_NAME); 294 sql.append(" WHERE CategoryID = ?"); 295 try { 296 connection = DBUtils.getConnection(); 297 statement = connection.prepareStatement(sql.toString()); 298 statement.setInt(1, categoryID); 299 resultSet = statement.executeQuery(); 300 if(!resultSet.next()) { 301 throw new ObjectNotFoundException("Cannot find the row in table Category where primary key = (" + categoryID + ")."); 302 } 303 304 CategoryBean bean = new CategoryBean(); 305 bean.setCategoryID(categoryID); 307 bean.setParentCategoryID(resultSet.getInt("ParentCategoryID")); 308 bean.setCategoryName(resultSet.getString("CategoryName")); 309 bean.setCategoryDesc(resultSet.getString("CategoryDesc")); 310 bean.setCategoryCreationDate(resultSet.getTimestamp("CategoryCreationDate")); 311 bean.setCategoryModifiedDate(resultSet.getTimestamp("CategoryModifiedDate")); 312 bean.setCategoryOrder(resultSet.getInt("CategoryOrder")); 313 bean.setCategoryOption(resultSet.getInt("CategoryOption")); 314 bean.setCategoryStatus(resultSet.getInt("CategoryStatus")); 315 return bean; 316 } catch(SQLException sqle) { 317 log.error("Sql Execution Error!", sqle); 318 throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.getCategory(pk)."); 319 } finally { 320 DBUtils.closeResultSet(resultSet); 321 DBUtils.closeStatement(statement); 322 DBUtils.closeConnection(connection); 323 } 324 } 325 326 331 public Collection getCategories() 332 throws DatabaseException { 333 334 Connection connection = null; 335 PreparedStatement statement = null; 336 ResultSet resultSet = null; 337 Collection retValue = new ArrayList (); 338 StringBuffer sql = new StringBuffer (512); 339 sql.append("SELECT CategoryID, ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus"); 340 sql.append(" FROM " + TABLE_NAME); 341 sql.append(" ORDER BY CategoryOrder ASC "); try { 344 connection = DBUtils.getConnection(); 345 statement = connection.prepareStatement(sql.toString()); 346 resultSet = statement.executeQuery(); 347 while (resultSet.next()) { 348 CategoryBean bean = new CategoryBean(); 349 bean.setCategoryID(resultSet.getInt("CategoryID")); 350 bean.setParentCategoryID(resultSet.getInt("ParentCategoryID")); 351 bean.setCategoryName(resultSet.getString("CategoryName")); 352 bean.setCategoryDesc(resultSet.getString("CategoryDesc")); 353 bean.setCategoryCreationDate(resultSet.getTimestamp("CategoryCreationDate")); 354 bean.setCategoryModifiedDate(resultSet.getTimestamp("CategoryModifiedDate")); 355 bean.setCategoryOrder(resultSet.getInt("CategoryOrder")); 356 bean.setCategoryOption(resultSet.getInt("CategoryOption")); 357 bean.setCategoryStatus(resultSet.getInt("CategoryStatus")); 358 retValue.add(bean); 359 } 360 return retValue; 361 } catch(SQLException sqle) { 362 log.error("Sql Execution Error!", sqle); 363 throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.getCategories."); 364 } finally { 365 DBUtils.closeResultSet(resultSet); 366 DBUtils.closeStatement(statement); 367 DBUtils.closeConnection(connection); 368 } 369 } 370 371 374 375 378 public void decreaseCategoryOrder(int categoryID, Timestamp categoryModifiedDate) 379 throws DatabaseException, ObjectNotFoundException { 380 381 Connection connection = null; 382 PreparedStatement statement = null; 383 String sql = "UPDATE " + TABLE_NAME + " SET CategoryOrder = CategoryOrder - 1, CategoryModifiedDate = ? WHERE CategoryID = ?"; 384 try { 385 connection = DBUtils.getConnection(); 386 statement = connection.prepareStatement(sql); 387 statement.setTimestamp(1, categoryModifiedDate); 388 statement.setInt(2, categoryID); 389 if (statement.executeUpdate() != 1) { 390 throw new ObjectNotFoundException("Cannot update the CategoryOrder in table Category. Please contact Web site Administrator."); 391 } 392 setDirty(true); 395 } catch (SQLException sqle) { 396 log.error("Sql Execution Error!", sqle); 397 throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.decreaseCategoryOrder."); 398 } finally { 399 DBUtils.closeStatement(statement); 400 DBUtils.closeConnection(connection); 401 } 402 } 403 404 407 public void increaseCategoryOrder(int categoryID, Timestamp categoryModifiedDate) 408 throws DatabaseException, ObjectNotFoundException { 409 410 Connection connection = null; 411 PreparedStatement statement = null; 412 String sql = "UPDATE " + TABLE_NAME + " SET CategoryOrder = CategoryOrder + 1, CategoryModifiedDate = ? WHERE CategoryID = ?"; 413 try { 414 connection = DBUtils.getConnection(); 415 statement = connection.prepareStatement(sql); 416 statement.setTimestamp(1, categoryModifiedDate); 417 statement.setInt(2, categoryID); 418 if (statement.executeUpdate() != 1) { 419 throw new ObjectNotFoundException("Cannot update the CategoryOrder in table Category. Please contact Web site Administrator."); 420 } 421 setDirty(true); 424 } catch (SQLException sqle) { 425 log.error("Sql Execution Error!", sqle); 426 throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.increaseCategoryOrder."); 427 } finally { 428 DBUtils.closeStatement(statement); 429 DBUtils.closeConnection(connection); 430 } 431 } 432 433 } | Popular Tags |