|
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 |
Java API By Example, From Geeks To Geeks.