KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > mvnforum > db > jdbc > AttachmentDAOImplJDBC


1 /*
2  * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/AttachmentDAOImplJDBC.java,v 1.18 2006/04/14 17:05:26 minhnn Exp $
3  * $Author: minhnn $
4  * $Revision: 1.18 $
5  * $Date: 2006/04/14 17:05:26 $
6  *
7  * ====================================================================
8  *
9  * Copyright (C) 2002-2006 by MyVietnam.net
10  *
11  * All copyright notices regarding mvnForum MUST remain
12  * intact in the scripts and in the outputted HTML.
13  * The "powered by" text/logo with a link back to
14  * http://www.mvnForum.com and http://www.MyVietnam.net in
15  * the footer of the pages MUST remain visible when the pages
16  * are viewed on the internet or intranet.
17  *
18  * This program is free software; you can redistribute it and/or modify
19  * it under the terms of the GNU General Public License as published by
20  * the Free Software Foundation; either version 2 of the License, or
21  * any later version.
22  *
23  * This program is distributed in the hope that it will be useful,
24  * but WITHOUT ANY WARRANTY; without even the implied warranty of
25  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
26  * GNU General Public License for more details.
27  *
28  * You should have received a copy of the GNU General Public License
29  * along with this program; if not, write to the Free Software
30  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
31  *
32  * Support can be obtained from support forums at:
33  * http://www.mvnForum.com/mvnforum/index
34  *
35  * Correspondence and Marketing Questions can be sent to:
36  * info at MyVietnam net
37  *
38  * @author: Minh Nguyen
39  * @author: Mai Nguyen
40  */

41 package com.mvnforum.db.jdbc;
42
43 import java.io.StringReader JavaDoc;
44 import java.sql.*;
45 import java.util.ArrayList JavaDoc;
46 import java.util.Collection JavaDoc;
47
48 import com.mvnforum.db.*;
49 import net.myvietnam.mvncore.db.DBUtils;
50 import net.myvietnam.mvncore.exception.*;
51 import org.apache.commons.logging.Log;
52 import org.apache.commons.logging.LogFactory;
53
54 public class AttachmentDAOImplJDBC implements AttachmentDAO {
55
56     private static Log log = LogFactory.getLog(AttachmentDAOImplJDBC.class);
57
58     // this variable will support caching if cache for this class is needed
59
private static boolean m_dirty = true;
60
61     public AttachmentDAOImplJDBC() {
62     }
63
64     protected static boolean isDirty() {
65         return m_dirty;
66     }
67
68     protected static void setDirty(boolean dirty) {
69         m_dirty = dirty;
70     }
71
72     /*
73      * Included columns: PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType,
74      * AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount,
75      * AttachOption, AttachStatus
76      * Excluded columns: AttachID
77      */

78     public void create(int postID, int memberID, String JavaDoc attachFilename,
79                         int attachFileSize, String JavaDoc attachMimeType, String JavaDoc attachDesc,
80                         String JavaDoc attachCreationIP, Timestamp attachCreationDate, Timestamp attachModifiedDate,
81                         int attachDownloadCount, int attachOption, int attachStatus)
82                         throws CreateException, DatabaseException, ForeignKeyNotFoundException {
83
84         try {
85             // @todo: modify the parameter list as needed
86
// You may have to regenerate this method if the needed columns dont have attribute 'include'
87
DAOFactory.getPostDAO().findByPrimaryKey(postID);
88         } catch(ObjectNotFoundException e) {
89             throw new ForeignKeyNotFoundException("Foreign key refers to table 'Post' does not exist. Cannot create new Attachment.");
90         }
91
92         //if admin allowed guest to send attachments, we must allow that too
93
if (memberID!=0) {
94             try {
95                 // @todo: modify the parameter list as needed
96
// You may have to regenerate this method if the needed columns dont have attribute 'include'
97
DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
98             } catch(ObjectNotFoundException e) {
99                 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Attachment.");
100             }
101         }
102
103         Connection connection = null;
104         PreparedStatement statement = null;
105         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
106         sql.append("INSERT INTO " + TABLE_NAME + " (PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus)");
107         sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
108         try {
109             connection = DBUtils.getConnection();
110             statement = connection.prepareStatement(sql.toString());
111
112             statement.setInt(1, postID);
113             statement.setInt(2, memberID);
114             statement.setString(3, attachFilename);
115             statement.setInt(4, attachFileSize);
116             statement.setString(5, attachMimeType);
117             if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
118                 statement.setCharacterStream(6, new StringReader JavaDoc(attachDesc), attachDesc.length());
119             } else {
120                 statement.setString(6, attachDesc);
121             }
122             statement.setString(7, attachCreationIP);
123             statement.setTimestamp(8, attachCreationDate);
124             statement.setTimestamp(9, attachModifiedDate);
125             statement.setInt(10, attachDownloadCount);
126             statement.setInt(11, attachOption);
127             statement.setInt(12, attachStatus);
128
129             if (statement.executeUpdate() != 1) {
130                 throw new CreateException("Error adding a row into table 'Attachment'.");
131             }
132             m_dirty = true;
133         } catch(SQLException sqle) {
134             log.error("Sql Execution Error!", sqle);
135             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.create.");
136         } finally {
137             DBUtils.closeStatement(statement);
138             DBUtils.closeConnection(connection);
139         }
140     }
141
142     public int createAttachment(int postID, int memberID, String JavaDoc attachFilename,
143                         int attachFileSize, String JavaDoc attachMimeType, String JavaDoc attachDesc,
144                         String JavaDoc attachCreationIP, Timestamp attachCreationDate, Timestamp attachModifiedDate,
145                         int attachDownloadCount, int attachOption, int attachStatus)
146         throws CreateException, DatabaseException, ForeignKeyNotFoundException, ObjectNotFoundException {
147
148         create(postID, memberID, attachFilename, attachFileSize, attachMimeType, attachDesc, attachCreationIP, attachCreationDate, attachModifiedDate, attachDownloadCount, attachOption, attachStatus);
149
150         int attachID = 0;
151         try {
152             attachID = findAttachID(postID, memberID, attachCreationDate);
153         } catch (ObjectNotFoundException ex) {
154             // Hack the Oracle 9i problem
155
Timestamp roundTimestamp = new Timestamp((attachCreationDate.getTime()/1000)*1000);
156             attachID = findAttachID(postID, memberID, roundTimestamp);
157         }
158         return attachID;
159     }
160
161     public void delete(int attachID)
162         throws DatabaseException, ObjectNotFoundException {
163
164         Connection connection = null;
165         PreparedStatement statement = null;
166         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
167         sql.append("DELETE FROM " + TABLE_NAME);
168         sql.append(" WHERE AttachID = ?");
169
170         try {
171             connection = DBUtils.getConnection();
172             statement = connection.prepareStatement(sql.toString());
173             statement.setInt(1, attachID);
174             if (statement.executeUpdate() != 1) {
175                 throw new ObjectNotFoundException("Cannot delete a row in table Attachment where primary key = (" + attachID + ").");
176             }
177             m_dirty = true;
178         } catch(SQLException sqle) {
179             log.error("Sql Execution Error!", sqle);
180             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.delete.");
181         } finally {
182             DBUtils.closeStatement(statement);
183             DBUtils.closeConnection(connection);
184         }
185     }
186
187     /*
188      * Included columns: PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType,
189      * AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount,
190      * AttachOption, AttachStatus
191      * Excluded columns: AttachID
192      */

193     public AttachmentBean getAttachment(int attachID)
194         throws ObjectNotFoundException, DatabaseException {
195
196         Connection connection = null;
197         PreparedStatement statement = null;
198         ResultSet resultSet = null;
199         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
200         sql.append("SELECT PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
201         sql.append(" FROM " + TABLE_NAME);
202         sql.append(" WHERE AttachID = ?");
203         try {
204             connection = DBUtils.getConnection();
205             statement = connection.prepareStatement(sql.toString());
206             statement.setInt(1, attachID);
207             resultSet = statement.executeQuery();
208             if(!resultSet.next()) {
209                 throw new ObjectNotFoundException("Cannot find the row in table Attachment where primary key = (" + attachID + ").");
210             }
211
212             AttachmentBean bean = new AttachmentBean();
213             bean.setAttachID(attachID);
214             bean.setPostID(resultSet.getInt("PostID"));
215             bean.setMemberID(resultSet.getInt("MemberID"));
216             bean.setAttachFilename(resultSet.getString("AttachFilename"));
217             bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
218             bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
219             bean.setAttachDesc(resultSet.getString("AttachDesc"));
220             bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
221             bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
222             bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
223             bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
224             bean.setAttachOption(resultSet.getInt("AttachOption"));
225             bean.setAttachStatus(resultSet.getInt("AttachStatus"));
226             return bean;
227         } catch(SQLException sqle) {
228             log.error("Sql Execution Error!", sqle);
229             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachment(pk).");
230         } finally {
231             DBUtils.closeResultSet(resultSet);
232             DBUtils.closeStatement(statement);
233             DBUtils.closeConnection(connection);
234         }
235     }
236
237     /*
238      * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
239      * AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
240      * AttachDownloadCount, AttachOption, AttachStatus
241      * Excluded columns:
242      */

243     public Collection JavaDoc getAttachments()
244         throws DatabaseException {
245
246         Connection connection = null;
247         PreparedStatement statement = null;
248         ResultSet resultSet = null;
249         Collection JavaDoc retValue = new ArrayList JavaDoc();
250         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
251         sql.append("SELECT AttachID, PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
252         sql.append(" FROM " + TABLE_NAME);
253         //sql.append(" WHERE "); // @todo: uncomment as needed
254
//sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
255
try {
256             connection = DBUtils.getConnection();
257             statement = connection.prepareStatement(sql.toString());
258             resultSet = statement.executeQuery();
259             while (resultSet.next()) {
260                 AttachmentBean bean = new AttachmentBean();
261                 bean.setAttachID(resultSet.getInt("AttachID"));
262                 bean.setPostID(resultSet.getInt("PostID"));
263                 bean.setMemberID(resultSet.getInt("MemberID"));
264                 bean.setAttachFilename(resultSet.getString("AttachFilename"));
265                 bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
266                 bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
267                 bean.setAttachDesc(resultSet.getString("AttachDesc"));
268                 bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
269                 bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
270                 bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
271                 bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
272                 bean.setAttachOption(resultSet.getInt("AttachOption"));
273                 bean.setAttachStatus(resultSet.getInt("AttachStatus"));
274                 retValue.add(bean);
275             }
276             return retValue;
277         } catch(SQLException sqle) {
278             log.error("Sql Execution Error!", sqle);
279             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachments.");
280         } finally {
281             DBUtils.closeResultSet(resultSet);
282             DBUtils.closeStatement(statement);
283             DBUtils.closeConnection(connection);
284         }
285     }
286
287     public int getNumberOfAttachments()
288         throws AssertionException, DatabaseException {
289
290         Connection connection = null;
291         PreparedStatement statement = null;
292         ResultSet resultSet = null;
293         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
294         sql.append("SELECT Count(*)");
295         sql.append(" FROM " + TABLE_NAME);
296         //sql.append(" WHERE "); // @todo: uncomment as needed
297
try {
298             connection = DBUtils.getConnection();
299             statement = connection.prepareStatement(sql.toString());
300             resultSet = statement.executeQuery();
301             if (!resultSet.next()) {
302                 throw new AssertionException("Assertion in AttachmentDAOImplJDBC.getNumberOfAttachments.");
303             }
304             return resultSet.getInt(1);
305         } catch(SQLException sqle) {
306             log.error("Sql Execution Error!", sqle);
307             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments.");
308         } finally {
309             DBUtils.closeResultSet(resultSet);
310             DBUtils.closeStatement(statement);
311             DBUtils.closeConnection(connection);
312         }
313     }
314
315     public int getNumberOfAttachments_inPost(int postID)
316         throws AssertionException, DatabaseException {
317
318         Connection connection = null;
319         PreparedStatement statement = null;
320         ResultSet resultSet = null;
321         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
322         sql.append("SELECT Count(*)");
323         sql.append(" FROM " + TABLE_NAME);
324         sql.append(" WHERE PostID = ?");
325         try {
326             connection = DBUtils.getConnection();
327             statement = connection.prepareStatement(sql.toString());
328             statement.setInt(1, postID);
329             resultSet = statement.executeQuery();
330             if (!resultSet.next()) {
331                 throw new AssertionException("Assertion in AttachmentDAOImplJDBC.getNumberOfAttachments_inPost.");
332             }
333             return resultSet.getInt(1);
334         } catch(SQLException sqle) {
335             log.error("Sql Execution Error!", sqle);
336             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments_inPost.");
337         } finally {
338             DBUtils.closeResultSet(resultSet);
339             DBUtils.closeStatement(statement);
340             DBUtils.closeConnection(connection);
341         }
342     }
343
344    public int getNumberOfAttachments_inThread(int threadID)
345         throws AssertionException, DatabaseException {
346
347         Connection connection = null;
348         PreparedStatement statement = null;
349         ResultSet resultSet = null;
350         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
351         sql.append("SELECT Count(*)");
352         sql.append(" FROM " + TABLE_NAME + " attachment , " + PostDAO.TABLE_NAME + " post");
353         sql.append(" WHERE attachment.PostID = post.PostID ");
354         sql.append(" AND post.ThreadID = ?");
355         try {
356             connection = DBUtils.getConnection();
357             statement = connection.prepareStatement(sql.toString());
358             statement.setInt(1, threadID);
359             resultSet = statement.executeQuery();
360             if (!resultSet.next()) {
361                 throw new AssertionException("Assertion in AttachmentDAOImplJDBC.getNumberOfAttachments_inThread.");
362             }
363             return resultSet.getInt(1);
364         } catch(SQLException sqle) {
365             log.error("Sql Execution Error!", sqle);
366             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments_inThread.");
367         } finally {
368             DBUtils.closeResultSet(resultSet);
369             DBUtils.closeStatement(statement);
370             DBUtils.closeConnection(connection);
371         }
372     }
373     /************************************************
374      * Customized methods come below
375      ************************************************/

376
377     /**
378      * This is a customized method
379      */

380     protected static int findAttachID(int postID, int memberID, Timestamp attachCreationDate)
381         throws ObjectNotFoundException, DatabaseException {
382
383         Connection connection = null;
384         PreparedStatement statement = null;
385         ResultSet resultSet = null;
386         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
387         sql.append("SELECT AttachID");
388         sql.append(" FROM " + TABLE_NAME);
389         sql.append(" WHERE PostID = ? AND MemberID = ? AND AttachCreationDate = ? ");
390         try {
391             connection = DBUtils.getConnection();
392             statement = connection.prepareStatement(sql.toString());
393             statement.setInt(1, postID);
394             statement.setInt(2, memberID);
395             statement.setTimestamp(3, attachCreationDate);
396             resultSet = statement.executeQuery();
397             if(!resultSet.next()) {
398                 throw new ObjectNotFoundException("Cannot find the AttachID in table Attachment.");
399             }
400
401             return resultSet.getInt("AttachID");
402         } catch(SQLException sqle) {
403             log.error("Sql Execution Error!", sqle);
404             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.findAttachID.");
405         } finally {
406             DBUtils.closeResultSet(resultSet);
407             DBUtils.closeStatement(statement);
408             DBUtils.closeConnection(connection);
409         }
410     }
411
412     public void delete_inPost(int postID)
413         throws DatabaseException {
414
415         Connection connection = null;
416         PreparedStatement statement = null;
417         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
418         sql.append("DELETE FROM " + TABLE_NAME);
419         sql.append(" WHERE PostID = ?");
420
421         try {
422             connection = DBUtils.getConnection();
423             statement = connection.prepareStatement(sql.toString());
424             statement.setInt(1, postID);
425             statement.executeUpdate();
426             m_dirty = true;
427         } catch(SQLException sqle) {
428             log.error("Sql Execution Error!", sqle);
429             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.delete_inPost.");
430         } finally {
431             DBUtils.closeStatement(statement);
432             DBUtils.closeConnection(connection);
433         }
434     }
435
436     /*
437      * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
438      * AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
439      * AttachDownloadCount, AttachOption, AttachStatus
440      * Excluded columns:
441      */

442     public Collection JavaDoc getAttachments_inPost(int postID)
443         throws DatabaseException {
444
445         Connection connection = null;
446         PreparedStatement statement = null;
447         ResultSet resultSet = null;
448         Collection JavaDoc retValue = new ArrayList JavaDoc();
449         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
450         sql.append("SELECT AttachID, PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
451         sql.append(" FROM " + TABLE_NAME);
452         sql.append(" WHERE PostID = ?");
453         sql.append(" ORDER BY AttachID ASC ");
454         try {
455             connection = DBUtils.getConnection();
456             statement = connection.prepareStatement(sql.toString());
457             statement.setInt(1, postID);
458             resultSet = statement.executeQuery();
459             while (resultSet.next()) {
460                 AttachmentBean bean = new AttachmentBean();
461                 bean.setAttachID(resultSet.getInt("AttachID"));
462                 bean.setPostID(resultSet.getInt("PostID"));
463                 bean.setMemberID(resultSet.getInt("MemberID"));
464                 bean.setAttachFilename(resultSet.getString("AttachFilename"));
465                 bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
466                 bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
467                 bean.setAttachDesc(resultSet.getString("AttachDesc"));
468                 bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
469                 bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
470                 bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
471                 bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
472                 bean.setAttachOption(resultSet.getInt("AttachOption"));
473                 bean.setAttachStatus(resultSet.getInt("AttachStatus"));
474                 retValue.add(bean);
475             }
476             return retValue;
477         } catch(SQLException sqle) {
478             log.error("Sql Execution Error!", sqle);
479             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachments_inPost.");
480         } finally {
481             DBUtils.closeResultSet(resultSet);
482             DBUtils.closeStatement(statement);
483             DBUtils.closeConnection(connection);
484         }
485     }
486
487     /*
488      * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
489      * AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
490      * AttachDownloadCount, AttachOption, AttachStatus
491      * Excluded columns:
492      */

493     public Collection JavaDoc getAttachments_inThread(int threadID)
494         throws DatabaseException {
495
496         Connection connection = null;
497         PreparedStatement statement = null;
498         ResultSet resultSet = null;
499         Collection JavaDoc retValue = new ArrayList JavaDoc();
500         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
501         sql.append("SELECT AttachID, attachment.PostID, attachment.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
502         sql.append(" FROM " + TABLE_NAME + " attachment, " + PostDAO.TABLE_NAME + " post ");
503         sql.append(" WHERE attachment.PostID = post.PostID AND post.ThreadID = ? ");
504         sql.append(" ORDER BY AttachID ASC ");
505         try {
506             connection = DBUtils.getConnection();
507             statement = connection.prepareStatement(sql.toString());
508             statement.setInt(1, threadID);
509             resultSet = statement.executeQuery();
510             while (resultSet.next()) {
511                 AttachmentBean bean = new AttachmentBean();
512                 bean.setAttachID(resultSet.getInt("AttachID"));
513                 bean.setPostID(resultSet.getInt("PostID"));
514                 bean.setMemberID(resultSet.getInt("MemberID"));
515                 bean.setAttachFilename(resultSet.getString("AttachFilename"));
516                 bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
517                 bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
518                 bean.setAttachDesc(resultSet.getString("AttachDesc"));
519                 bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
520                 bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
521                 bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
522                 bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
523                 bean.setAttachOption(resultSet.getInt("AttachOption"));
524                 bean.setAttachStatus(resultSet.getInt("AttachStatus"));
525                 retValue.add(bean);
526             }
527             return retValue;
528         } catch(SQLException sqle) {
529             log.error("Sql Execution Error!", sqle);
530             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachments_inThread.");
531         } finally {
532             DBUtils.closeResultSet(resultSet);
533             DBUtils.closeStatement(statement);
534             DBUtils.closeConnection(connection);
535         }
536     }
537
538     /*
539      * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
540      * AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
541      * AttachDownloadCount, AttachOption, AttachStatus
542      * Excluded columns:
543      */

544     public Collection JavaDoc getAttachments_inForum(int forumID)
545         throws DatabaseException {
546
547         Connection connection = null;
548         PreparedStatement statement = null;
549         ResultSet resultSet = null;
550         Collection JavaDoc retValue = new ArrayList JavaDoc();
551         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
552         sql.append("SELECT AttachID, attachment.PostID, attachment.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
553         sql.append(" FROM " + TABLE_NAME + " attachment, " + PostDAO.TABLE_NAME + " post ");
554         sql.append(" WHERE attachment.PostID = post.PostID AND post.ForumID = ? ");
555         sql.append(" ORDER BY AttachID ASC ");
556         try {
557             connection = DBUtils.getConnection();
558             statement = connection.prepareStatement(sql.toString());
559             statement.setInt(1, forumID);
560             resultSet = statement.executeQuery();
561             while (resultSet.next()) {
562                 AttachmentBean bean = new AttachmentBean();
563                 bean.setAttachID(resultSet.getInt("AttachID"));
564                 bean.setPostID(resultSet.getInt("PostID"));
565                 bean.setMemberID(resultSet.getInt("MemberID"));
566                 bean.setAttachFilename(resultSet.getString("AttachFilename"));
567                 bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
568                 bean.setAttachMimeType(resultSet.getString("AttachMimeType"));
569                 bean.setAttachDesc(resultSet.getString("AttachDesc"));
570                 bean.setAttachCreationIP(resultSet.getString("AttachCreationIP"));
571                 bean.setAttachCreationDate(resultSet.getTimestamp("AttachCreationDate"));
572                 bean.setAttachModifiedDate(resultSet.getTimestamp("AttachModifiedDate"));
573                 bean.setAttachDownloadCount(resultSet.getInt("AttachDownloadCount"));
574                 bean.setAttachOption(resultSet.getInt("AttachOption"));
575                 bean.setAttachStatus(resultSet.getInt("AttachStatus"));
576                 retValue.add(bean);
577             }
578             return retValue;
579         } catch(SQLException sqle) {
580             log.error("Sql Execution Error!", sqle);
581             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.getAttachments_inForum.");
582         } finally {
583             DBUtils.closeResultSet(resultSet);
584             DBUtils.closeStatement(statement);
585             DBUtils.closeConnection(connection);
586         }
587     }
588
589     /**
590      * This method should be call only when we can make sure that postID is in database
591      */

592     public void increaseDownloadCount(int attachID)
593         throws DatabaseException, ObjectNotFoundException {
594
595         Connection connection = null;
596         PreparedStatement statement = null;
597         String JavaDoc sql = "UPDATE " + TABLE_NAME + " SET AttachDownloadCount = AttachDownloadCount + 1 WHERE AttachID = ?";
598         try {
599             connection = DBUtils.getConnection();
600             statement = connection.prepareStatement(sql);
601             statement.setInt(1, attachID);
602             if (statement.executeUpdate() != 1) {
603                 throw new ObjectNotFoundException("Cannot update the AttachDownloadCount in table Attachment. Please contact Web site Administrator.");
604             }
605             //@todo: coi lai cho nay
606
// ATTENTION !!!
607
setDirty(true);
608         } catch (SQLException sqle) {
609             log.error("Sql Execution Error!", sqle);
610             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.increaseDownloadCount.");
611         } finally {
612             DBUtils.closeStatement(statement);
613             DBUtils.closeConnection(connection);
614         }
615     }
616
617     public void updateAttachDesc(int attachID, String JavaDoc attachDesc)
618         throws DatabaseException, ObjectNotFoundException {
619
620         Connection connection = null;
621         PreparedStatement statement = null;
622         String JavaDoc sql = "UPDATE " + TABLE_NAME + " SET AttachDesc = ? WHERE AttachID = ?";
623         try {
624             connection = DBUtils.getConnection();
625             statement = connection.prepareStatement(sql);
626
627             if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
628                 statement.setCharacterStream(1, new StringReader JavaDoc(attachDesc), attachDesc.length());
629             } else {
630                 statement.setString(1, attachDesc);
631             }
632             statement.setInt(2, attachID);
633             if (statement.executeUpdate() != 1) {
634                 throw new ObjectNotFoundException("Cannot update the Desc in table Attachment. Please contact Web site Administrator.");
635             }
636             //@todo: coi lai cho nay
637
// ATTENTION !!!
638
setDirty(true);
639         } catch (SQLException sqle) {
640             log.error("Sql Execution Error!", sqle);
641             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.updateAttachDesc.");
642         } finally {
643             DBUtils.closeStatement(statement);
644             DBUtils.closeConnection(connection);
645         }
646     }
647
648     public void updateAttachOption(int attachID, int attachOption)
649         throws DatabaseException, ObjectNotFoundException {
650
651         Connection connection = null;
652         PreparedStatement statement = null;
653         String JavaDoc sql = "UPDATE " + TABLE_NAME + " SET AttachOption = ? WHERE AttachID = ?";
654         try {
655             connection = DBUtils.getConnection();
656             statement = connection.prepareStatement(sql);
657
658             statement.setInt(1, attachOption);
659             statement.setInt(2, attachID);
660             if (statement.executeUpdate() != 1) {
661                 throw new ObjectNotFoundException("Cannot update the Option in table Attachment. Please contact Web site Administrator.");
662             }
663             // @todo: coi lai cho nay
664
// ATTENTION !!!
665
setDirty(true);
666         } catch (SQLException sqle) {
667             log.error("Sql Execution Error!", sqle);
668             throw new DatabaseException("Error executing SQL in AttachmentDAOImplJDBC.updateAttachOption.");
669         } finally {
670             DBUtils.closeStatement(statement);
671             DBUtils.closeConnection(connection);
672         }
673     }
674
675 }// end of class AttachmentDAOImplJDBC
676
Popular Tags