|
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 FavoriteThreadDAOImplJDBC implements FavoriteThreadDAO {
54
55 private static Log log = LogFactory.getLog(FavoriteThreadDAOImplJDBC.class);
56
57 private static boolean m_dirty = true;
59
60 public FavoriteThreadDAOImplJDBC() {
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 threadID)
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, ThreadID");
79 sql.append(" FROM " + TABLE_NAME);
80 sql.append(" WHERE MemberID = ? AND ThreadID = ?");
81 try {
82 connection = DBUtils.getConnection();
83 statement = connection.prepareStatement(sql.toString());
84 statement.setInt(1, memberID);
85 statement.setInt(2, threadID);
86 resultSet = statement.executeQuery();
87 if (!resultSet.next()) {
88 throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + ", " + threadID + ") in table 'FavoriteThread'.");
89 }
90 } catch(SQLException sqle) {
91 log.error("Sql Execution Error!", sqle);
92 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.findByPrimaryKey.");
93 } finally {
94 DBUtils.closeResultSet(resultSet);
95 DBUtils.closeStatement(statement);
96 DBUtils.closeConnection(connection);
97 }
98 }
99
100
105 public void create(int memberID, int threadID, int forumID,
106 Timestamp favoriteCreationDate, int favoriteType, int favoriteOption,
107 int favoriteStatus)
108 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {
109
110 try {
114 findByPrimaryKey(memberID, threadID);
116 throw new DuplicateKeyException("Primary key already exists. Cannot create new FavoriteThread with the same [MemberID, ThreadID] (" + memberID + ", " + threadID + ").");
118 } catch(ObjectNotFoundException e) {
119 }
121
122 try {
123 DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
126 } catch(ObjectNotFoundException e) {
127 throw new ForeignKeyNotFoundException("Foreign key refers to table 'mvnforumMember' does not exist. Cannot create new FavoriteThread.");
128 }
129
130 try {
131 DAOFactory.getThreadDAO().findByPrimaryKey(threadID);
134 } catch(ObjectNotFoundException e) {
135 throw new ForeignKeyNotFoundException("Foreign key refers to table 'mvnforumThread' does not exist. Cannot create new FavoriteThread.");
136 }
137
138 try {
139 DAOFactory.getForumDAO().findByPrimaryKey(forumID);
142 } catch(ObjectNotFoundException e) {
143 throw new ForeignKeyNotFoundException("Foreign key refers to table 'mvnforumForum' does not exist. Cannot create new FavoriteThread.");
144 }
145
146 Connection connection = null;
147 PreparedStatement statement = null;
148 StringBuffer sql = new StringBuffer (512);
149 sql.append("INSERT INTO " + TABLE_NAME + " (MemberID, ThreadID, ForumID, FavoriteCreationDate, FavoriteType, FavoriteOption, FavoriteStatus)");
150 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?)");
151 try {
152 connection = DBUtils.getConnection();
153 statement = connection.prepareStatement(sql.toString());
154
155 statement.setInt(1, memberID);
156 statement.setInt(2, threadID);
157 statement.setInt(3, forumID);
158 statement.setTimestamp(4, favoriteCreationDate);
159 statement.setInt(5, favoriteType);
160 statement.setInt(6, favoriteOption);
161 statement.setInt(7, favoriteStatus);
162
163 if (statement.executeUpdate() != 1) {
164 throw new CreateException("Error adding a row into table 'FavoriteThread'.");
165 }
166 m_dirty = true;
167 } catch(SQLException sqle) {
168 log.error("Sql Execution Error!", sqle);
169 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.create.");
170 } finally {
171 DBUtils.closeStatement(statement);
172 DBUtils.closeConnection(connection);
173 }
174 }
175
176 public void delete(int memberID, int threadID)
177 throws DatabaseException, ObjectNotFoundException {
178
179 Connection connection = null;
180 PreparedStatement statement = null;
181 StringBuffer sql = new StringBuffer (512);
182 sql.append("DELETE FROM " + TABLE_NAME);
183 sql.append(" WHERE MemberID = ? AND ThreadID = ?");
184
185 try {
186 connection = DBUtils.getConnection();
187 statement = connection.prepareStatement(sql.toString());
188 statement.setInt(1, memberID);
189 statement.setInt(2, threadID);
190 if (statement.executeUpdate() != 1) {
191 throw new ObjectNotFoundException("Cannot delete a row in table FavoriteThread where primary key = (" + memberID + ", " + threadID + ").");
192 }
193 m_dirty = true;
194 } catch(SQLException sqle) {
195 log.error("Sql Execution Error!", sqle);
196 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.delete.");
197 } finally {
198 DBUtils.closeStatement(statement);
199 DBUtils.closeConnection(connection);
200 }
201 }
202
203 public void delete_inThread(int threadID)
204 throws DatabaseException {
205
206 Connection connection = null;
207 PreparedStatement statement = null;
208 StringBuffer sql = new StringBuffer (512);
209 sql.append("DELETE FROM " + TABLE_NAME);
210 sql.append(" WHERE ThreadID = ?");
211
212 try {
213 connection = DBUtils.getConnection();
214 statement = connection.prepareStatement(sql.toString());
215 statement.setInt(1, threadID);
216
217 statement.executeUpdate();
218 m_dirty = true;
219 } catch(SQLException sqle) {
220 log.error("Sql Execution Error!", sqle);
221 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.delete_inThread.");
222 } finally {
223 DBUtils.closeStatement(statement);
224 DBUtils.closeConnection(connection);
225 }
226 }
227
228 public void delete_inForum(int forumID)
229 throws DatabaseException {
230
231 Connection connection = null;
232 PreparedStatement statement = null;
233 StringBuffer sql = new StringBuffer (512);
234 sql.append("DELETE FROM " + TABLE_NAME);
235 sql.append(" WHERE ForumID = ?");
236
237 try {
238 connection = DBUtils.getConnection();
239 statement = connection.prepareStatement(sql.toString());
240 statement.setInt(1, forumID);
241
242 statement.executeUpdate();
243 m_dirty = true;
244 } catch(SQLException sqle) {
245 log.error("Sql Execution Error!", sqle);
246 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.delete_inForum.");
247 } finally {
248 DBUtils.closeStatement(statement);
249 DBUtils.closeConnection(connection);
250 }
251 }
252
253 public void delete_inMember(int memberID)
254 throws DatabaseException {
255
256 Connection connection = null;
257 PreparedStatement statement = null;
258 StringBuffer sql = new StringBuffer (512);
259 sql.append("DELETE FROM " + TABLE_NAME);
260 sql.append(" WHERE MemberID = ?");
261
262 try {
263 connection = DBUtils.getConnection();
264 statement = connection.prepareStatement(sql.toString());
265 statement.setInt(1, memberID);
266 statement.executeUpdate();
267 m_dirty = true;
268 } catch(SQLException sqle) {
269 log.error("Sql Execution Error!", sqle);
270 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.delete_inMember.");
271 } finally {
272 DBUtils.closeStatement(statement);
273 DBUtils.closeConnection(connection);
274 }
275 }
276
277
280 public void update_ForumID_inThread(int threadID, int forumID)
281 throws DatabaseException, ForeignKeyNotFoundException {
282
283 try {
284 DAOFactory.getForumDAO().findByPrimaryKey(forumID);
287 } catch(ObjectNotFoundException e) {
288 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot update table 'FavoriteThread'.");
289 }
290
291 Connection connection = null;
292 PreparedStatement statement = null;
293 StringBuffer sql = new StringBuffer (512);
294 sql.append("UPDATE " + TABLE_NAME + " SET ForumID = ?");
295 sql.append(" WHERE ThreadID = ?");
296 try {
297 connection = DBUtils.getConnection();
298 statement = connection.prepareStatement(sql.toString());
299
300 statement.setInt(1, forumID);
302
303 statement.setInt(2, threadID);
305
306 statement.executeUpdate();
307 m_dirty = true;
308 } catch(SQLException sqle) {
309 log.error("Sql Execution Error!", sqle);
310 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.update_ForumID_inThread.");
311 } finally {
312 DBUtils.closeStatement(statement);
313 DBUtils.closeConnection(connection);
314 }
315 }
316
317
322 public Collection getFavoriteThreads_inMember(int memberID)
323 throws DatabaseException {
324
325 Connection connection = null;
326 PreparedStatement statement = null;
327 ResultSet resultSet = null;
328 Collection retValue = new ArrayList ();
329 StringBuffer sql = new StringBuffer (512);
330 sql.append("SELECT MemberID, ThreadID, FavoriteCreationDate, FavoriteType, FavoriteOption, FavoriteStatus");
331 sql.append(" FROM " + TABLE_NAME);
332 sql.append(" WHERE MemberID = ?");
333 sql.append(" ORDER BY ThreadID ASC ");
334 try {
335 connection = DBUtils.getConnection();
336 statement = connection.prepareStatement(sql.toString());
337 statement.setInt(1, memberID);
338 resultSet = statement.executeQuery();
339 while (resultSet.next()) {
340 FavoriteThreadBean bean = new FavoriteThreadBean();
341 bean.setMemberID(resultSet.getInt("MemberID"));
342 bean.setThreadID(resultSet.getInt("ThreadID"));
343 bean.setFavoriteCreationDate(resultSet.getTimestamp("FavoriteCreationDate"));
344 bean.setFavoriteType(resultSet.getInt("FavoriteType"));
345 bean.setFavoriteOption(resultSet.getInt("FavoriteOption"));
346 bean.setFavoriteStatus(resultSet.getInt("FavoriteStatus"));
347 retValue.add(bean);
348 }
349 return retValue;
350 } catch(SQLException sqle) {
351 log.error("Sql Execution Error!", sqle);
352 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.getFavoriteThreads_inMember.");
353 } finally {
354 DBUtils.closeResultSet(resultSet);
355 DBUtils.closeStatement(statement);
356 DBUtils.closeConnection(connection);
357 }
358 }
359
387
388 public int getNumberOfFavoriteThreads_inMember(int memberID)
389 throws AssertionException, DatabaseException {
390
391 Connection connection = null;
392 PreparedStatement statement = null;
393 ResultSet resultSet = null;
394 StringBuffer sql = new StringBuffer (512);
395 sql.append("SELECT Count(*)");
396 sql.append(" FROM " + TABLE_NAME);
397 sql.append(" WHERE MemberID = ? ");
398 try {
399 connection = DBUtils.getConnection();
400 statement = connection.prepareStatement(sql.toString());
401 statement.setInt(1, memberID);
402 resultSet = statement.executeQuery();
403 if (!resultSet.next()) {
404 throw new AssertionException("Assertion in FavoriteThreadDAOImplJDBC.getNumberOfFavoriteThreads_inMember.");
405 }
406 return resultSet.getInt(1);
407 } catch(SQLException sqle) {
408 log.error("Sql Execution Error!", sqle);
409 throw new DatabaseException("Error executing SQL in FavoriteThreadDAOImplJDBC.getNumberOfFavoriteThreads_inMember.");
410 } finally {
411 DBUtils.closeResultSet(resultSet);
412 DBUtils.closeStatement(statement);
413 DBUtils.closeConnection(connection);
414 }
415 }
416
417 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.