|
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 MessageFolderDAOImplJDBC implements MessageFolderDAO {
54
55 private static Log log = LogFactory.getLog(MessageFolderDAOImplJDBC.class);
56
57 private static boolean m_dirty = true;
59
60 public MessageFolderDAOImplJDBC() {
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(String folderName, 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 FolderName, MemberID");
79 sql.append(" FROM " + TABLE_NAME);
80 sql.append(" WHERE FolderName = ? AND MemberID = ?");
81 try {
82 connection = DBUtils.getConnection();
83 statement = connection.prepareStatement(sql.toString());
84 statement.setString(1, folderName);
85 statement.setInt(2, memberID);
86 resultSet = statement.executeQuery();
87 if (!resultSet.next()) {
88 throw new ObjectNotFoundException("Cannot find the primary key (" + folderName + ", " + memberID + ") in table 'MessageFolder'.");
89 }
90 } catch(SQLException sqle) {
91 log.error("Sql Execution Error!", sqle);
92 throw new DatabaseException("Error executing SQL in MessageFolderDAOImplJDBC.findByPrimaryKey.");
93 } finally {
94 DBUtils.closeResultSet(resultSet);
95 DBUtils.closeStatement(statement);
96 DBUtils.closeConnection(connection);
97 }
98 }
99
100
105 public void create(String folderName, int memberID, int folderOrder,
106 int folderStatus, int folderOption, int folderType,
107 Timestamp folderCreationDate, Timestamp folderModifiedDate)
108 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {
109
110 try {
114 findByPrimaryKey(folderName, memberID);
116 throw new DuplicateKeyException("Primary key already exists. Cannot create new MessageFolder with the same [FolderName, MemberID] (" + folderName + ", " + memberID + ").");
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 'Member' does not exist. Cannot create new MessageFolder.");
128 }
129
130 Connection connection = null;
131 PreparedStatement statement = null;
132 StringBuffer sql = new StringBuffer (512);
133 sql.append("INSERT INTO " + TABLE_NAME + " (FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate)");
134 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
135 try {
136 connection = DBUtils.getConnection();
137 statement = connection.prepareStatement(sql.toString());
138
139 statement.setString(1, folderName);
140 statement.setInt(2, memberID);
141 statement.setInt(3, folderOrder);
142 statement.setInt(4, folderStatus);
143 statement.setInt(5, folderOption);
144 statement.setInt(6, folderType);
145 statement.setTimestamp(7, folderCreationDate);
146 statement.setTimestamp(8, folderModifiedDate);
147
148 if (statement.executeUpdate() != 1) {
149 throw new CreateException("Error adding a row into table 'MessageFolder'.");
150 }
151 m_dirty = true;
152 } catch(SQLException sqle) {
153 log.error("Sql Execution Error!", sqle);
154 throw new DatabaseException("Error executing SQL in MessageFolderDAOImplJDBC.create.");
155 } finally {
156 DBUtils.closeStatement(statement);
157 DBUtils.closeConnection(connection);
158 }
159 }
160
161 public void delete(String folderName, int memberID)
162 throws DatabaseException, ObjectNotFoundException {
163
164 Connection connection = null;
165 PreparedStatement statement = null;
166 StringBuffer sql = new StringBuffer (512);
167 sql.append("DELETE FROM " + TABLE_NAME);
168 sql.append(" WHERE FolderName = ? AND MemberID = ?");
169
170 try {
171 connection = DBUtils.getConnection();
172 statement = connection.prepareStatement(sql.toString());
173 statement.setString(1, folderName);
174 statement.setInt(2, memberID);
175 if (statement.executeUpdate() != 1) {
176 throw new ObjectNotFoundException("Cannot delete a row in table MessageFolder where primary key = (" + folderName + ", " + memberID + ").");
177 }
178 m_dirty = true;
179 } catch(SQLException sqle) {
180 log.error("Sql Execution Error!", sqle);
181 throw new DatabaseException("Error executing SQL in MessageFolderDAOImplJDBC.delete.");
182 } finally {
183 DBUtils.closeStatement(statement);
184 DBUtils.closeConnection(connection);
185 }
186 }
187
188
193 public MessageFolderBean getMessageFolder(String folderName, int memberID)
194 throws ObjectNotFoundException, DatabaseException {
195
196 Connection connection = null;
197 PreparedStatement statement = null;
198 ResultSet resultSet = null;
199 StringBuffer sql = new StringBuffer (512);
200 sql.append("SELECT FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate");
201 sql.append(" FROM " + TABLE_NAME);
202 sql.append(" WHERE FolderName = ? AND MemberID = ?");
203 try {
204 connection = DBUtils.getConnection();
205 statement = connection.prepareStatement(sql.toString());
206 statement.setString(1, folderName);
207 statement.setInt(2, memberID);
208 resultSet = statement.executeQuery();
209 if(!resultSet.next()) {
210 throw new ObjectNotFoundException("Cannot find the row in table MessageFolder where primary key = (" + folderName + ", " + memberID + ").");
211 }
212
213 MessageFolderBean bean = new MessageFolderBean();
214 bean.setFolderName(resultSet.getString("FolderName"));
218 bean.setMemberID(resultSet.getInt("MemberID"));
219 bean.setFolderOrder(resultSet.getInt("FolderOrder"));
220 bean.setFolderStatus(resultSet.getInt("FolderStatus"));
221 bean.setFolderOption(resultSet.getInt("FolderOption"));
222 bean.setFolderType(resultSet.getInt("FolderType"));
223 bean.setFolderCreationDate(resultSet.getTimestamp("FolderCreationDate"));
224 bean.setFolderModifiedDate(resultSet.getTimestamp("FolderModifiedDate"));
225 return bean;
226 } catch(SQLException sqle) {
227 log.error("Sql Execution Error!", sqle);
228 throw new DatabaseException("Error executing SQL in MessageFolderDAOImplJDBC.getMessageFolder(pk).");
229 } finally {
230 DBUtils.closeResultSet(resultSet);
231 DBUtils.closeStatement(statement);
232 DBUtils.closeConnection(connection);
233 }
234 }
235
236
242 public Collection getMessageFolders_inMember(int memberID)
243 throws DatabaseException {
244
245 Connection connection = null;
246 PreparedStatement statement = null;
247 ResultSet resultSet = null;
248 Collection retValue = new ArrayList ();
249 StringBuffer sql = new StringBuffer (512);
250 sql.append("SELECT FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate");
251 sql.append(" FROM " + TABLE_NAME);
252 sql.append(" WHERE MemberID = ?");
253 sql.append(" ORDER BY FolderOrder ASC ");
254 try {
255 connection = DBUtils.getConnection();
256 statement = connection.prepareStatement(sql.toString());
257 statement.setInt(1, memberID);
258 resultSet = statement.executeQuery();
259 while (resultSet.next()) {
260 MessageFolderBean bean = new MessageFolderBean();
261 bean.setFolderName(resultSet.getString("FolderName"));
262 bean.setMemberID(resultSet.getInt("MemberID"));
263 bean.setFolderOrder(resultSet.getInt("FolderOrder"));
264 bean.setFolderStatus(resultSet.getInt("FolderStatus"));
265 bean.setFolderOption(resultSet.getInt("FolderOption"));
266 bean.setFolderType(resultSet.getInt("FolderType"));
267 bean.setFolderCreationDate(resultSet.getTimestamp("FolderCreationDate"));
268 bean.setFolderModifiedDate(resultSet.getTimestamp("FolderModifiedDate"));
269 retValue.add(bean);
270 }
271 return retValue;
272 } catch(SQLException sqle) {
273 log.error("Sql Execution Error!", sqle);
274 throw new DatabaseException("Error executing SQL in MessageFolderDAOImplJDBC.getMessageFolders_inMember.");
275 } finally {
276 DBUtils.closeResultSet(resultSet);
277 DBUtils.closeStatement(statement);
278 DBUtils.closeConnection(connection);
279 }
280 }
281
282 public int getMaxFolderOrder(int memberID)
283 throws DatabaseException, AssertionException {
284
285 Connection connection = null;
286 PreparedStatement statement = null;
287 ResultSet resultSet = null;
288 StringBuffer sql = new StringBuffer (512);
289
290 sql.append("SELECT MAX(FolderOrder)");
291 sql.append(" FROM " + TABLE_NAME);
292 sql.append(" WHERE MemberID = ?");
293
294 try {
295 connection = DBUtils.getConnection();
296 statement = connection.prepareStatement(sql.toString());
297 statement.setInt(1, memberID);
298 resultSet = statement.executeQuery();
299 if (!resultSet.next()) {
300 throw new AssertionException("Assertion in MessageFolderDAOImplJDBC.getMaxFolderOrder.");
301 }
302 return resultSet.getInt(1);
303 } catch(SQLException sqle) {
304 log.error("Sql Execution Error!", sqle);
305 throw new DatabaseException("Error executing SQL in MessageFolderDAOImplJDBC.getMaxFolderOrder(memberID).");
306 } finally {
307 DBUtils.closeResultSet(resultSet);
308 DBUtils.closeStatement(statement);
309 DBUtils.closeConnection(connection);
310 }
311 }
312
313
318 public void increaseFolderOrder(String folderName, int memberID, Timestamp folderModifiedDate)
320 throws ObjectNotFoundException, DatabaseException {
321
322 Connection connection = null;
323 PreparedStatement statement = null;
324 StringBuffer sql = new StringBuffer (512);
325 sql.append("UPDATE " + TABLE_NAME + " SET FolderOrder = FolderOrder + 1, FolderModifiedDate = ?");
326 sql.append(" WHERE FolderName = ? AND MemberID = ?");
327 try {
328 connection = DBUtils.getConnection();
329 statement = connection.prepareStatement(sql.toString());
330
331 statement.setTimestamp(1, folderModifiedDate);
333
334 statement.setString(2, folderName);
336 statement.setInt(3, memberID);
337
338 if (statement.executeUpdate() != 1) {
339 throw new ObjectNotFoundException("Cannot update table MessageFolder where primary key = (" + folderName + ", " + memberID + ").");
340 }
341 m_dirty = true;
342 } catch(SQLException sqle) {
343 log.error("Sql Execution Error!", sqle);
344 throw new DatabaseException("Error executing SQL in MessageFolderDAOImplJDBC.increaseFolderOrder.");
345 } finally {
346 DBUtils.closeStatement(statement);
347 DBUtils.closeConnection(connection);
348 }
349 }
350
351
356 public void decreaseFolderOrder(String folderName, int memberID, Timestamp folderModifiedDate)
358 throws ObjectNotFoundException, DatabaseException {
359
360 Connection connection = null;
361 PreparedStatement statement = null;
362 StringBuffer sql = new StringBuffer (512);
363 sql.append("UPDATE " + TABLE_NAME + " SET FolderOrder = FolderOrder - 1, FolderModifiedDate = ?");
364 sql.append(" WHERE FolderName = ? AND MemberID = ?");
365 try {
366 connection = DBUtils.getConnection();
367 statement = connection.prepareStatement(sql.toString());
368
369 statement.setTimestamp(1, folderModifiedDate);
371
372 statement.setString(2, folderName);
374 statement.setInt(3, memberID);
375
376 if (statement.executeUpdate() != 1) {
377 throw new ObjectNotFoundException("Cannot update table MessageFolder where primary key = (" + folderName + ", " + memberID + ").");
378 }
379 m_dirty = true;
380 } catch(SQLException sqle) {
381 log.error("Sql Execution Error!", sqle);
382 throw new DatabaseException("Error executing SQL in MessageFolderDAOImplJDBC.decreaseFolderOrder.");
383 } finally {
384 DBUtils.closeStatement(statement);
385 DBUtils.closeConnection(connection);
386 }
387 }
388 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.