KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2  * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/PmAttachmentDAOImplJDBC.java,v 1.17 2006/04/14 17:05:26 minhnn Exp $
3  * $Author: minhnn $
4  * $Revision: 1.17 $
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
52 import org.apache.commons.logging.Log;
53 import org.apache.commons.logging.LogFactory;
54
55 public class PmAttachmentDAOImplJDBC implements PmAttachmentDAO {
56
57     private static Log log = LogFactory.getLog(PmAttachmentDAOImplJDBC.class);
58
59     // this variable will support caching if cache for this class is needed
60
private static boolean m_dirty = true;
61
62     public PmAttachmentDAOImplJDBC() {
63     }
64
65     protected static boolean isDirty() {
66         return m_dirty;
67     }
68
69     protected static void setDirty(boolean dirty) {
70         m_dirty = dirty;
71     }
72
73     /**
74      * This is a customized method
75      */

76     protected static int findPmAttachID(int memberID, Timestamp pmAttachCreationDate)
77         throws ObjectNotFoundException, DatabaseException {
78
79         Connection connection = null;
80         PreparedStatement statement = null;
81         ResultSet resultSet = null;
82         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
83         sql.append("SELECT PmAttachID");
84         sql.append(" FROM " + TABLE_NAME);
85         sql.append(" WHERE MemberID = ? AND PmAttachCreationDate = ? ");
86         try {
87             connection = DBUtils.getConnection();
88             statement = connection.prepareStatement(sql.toString());
89             statement.setInt(1, memberID);
90             statement.setTimestamp(2, pmAttachCreationDate);
91             resultSet = statement.executeQuery();
92             if(!resultSet.next()) {
93                 throw new ObjectNotFoundException("Cannot find the PmAttachID in table PmAttachment.");
94             }
95
96             return resultSet.getInt("PmAttachID");
97         } catch(SQLException sqle) {
98             log.error("Sql Execution Error!", sqle);
99             throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.findPmAttachID.");
100         } finally {
101             DBUtils.closeResultSet(resultSet);
102             DBUtils.closeStatement(statement);
103             DBUtils.closeConnection(connection);
104         }
105     }
106
107     public void findByPrimaryKey(int pmAttachID)
108         throws ObjectNotFoundException, DatabaseException {
109
110         Connection connection = null;
111         PreparedStatement statement = null;
112         ResultSet resultSet = null;
113         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
114         sql.append("SELECT PmAttachID");
115         sql.append(" FROM " + TABLE_NAME);
116         sql.append(" WHERE PmAttachID = ?");
117         try {
118             connection = DBUtils.getConnection();
119             statement = connection.prepareStatement(sql.toString());
120             statement.setInt(1, pmAttachID);
121             resultSet = statement.executeQuery();
122             if (!resultSet.next()) {
123                 throw new ObjectNotFoundException("Cannot find the primary key (" + pmAttachID + ") in table 'PmAttachment'.");
124             }
125         } catch(SQLException sqle) {
126             log.error("Sql Execution Error!", sqle);
127             throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.findByPrimaryKey.");
128         } finally {
129             DBUtils.closeResultSet(resultSet);
130             DBUtils.closeStatement(statement);
131             DBUtils.closeConnection(connection);
132         }
133     }
134
135     /*
136      * Included columns: MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc,
137      * PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption,
138      * PmAttachStatus
139      * Excluded columns: PmAttachID
140      */

141     public int create(int memberID, String JavaDoc pmAttachFilename, int pmAttachFileSize,
142                         String JavaDoc pmAttachMimeType, String JavaDoc pmAttachDesc, String JavaDoc pmAttachCreationIP,
143                         Timestamp pmAttachCreationDate, Timestamp pmAttachModifiedDate, int pmAttachDownloadCount,
144                         int pmAttachOption, int pmAttachStatus)
145         throws CreateException, DatabaseException, ForeignKeyNotFoundException, ObjectNotFoundException {
146
147         try {
148             // @todo: modify the parameter list as needed
149
// You may have to regenerate this method if the needed columns dont have attribute 'include'
150
DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
151         } catch(ObjectNotFoundException e) {
152             throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new PmAttachment.");
153         }
154
155         Connection connection = null;
156         PreparedStatement statement = null;
157         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
158         sql.append("INSERT INTO " + TABLE_NAME + " (MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus)");
159         sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
160         try {
161             connection = DBUtils.getConnection();
162             statement = connection.prepareStatement(sql.toString());
163
164             statement.setInt(1, memberID);
165             statement.setString(2, pmAttachFilename);
166             statement.setInt(3, pmAttachFileSize);
167             statement.setString(4, pmAttachMimeType);
168             if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
169                 statement.setCharacterStream(5, new StringReader JavaDoc(pmAttachDesc), pmAttachDesc.length());
170             } else {
171                 statement.setString(5, pmAttachDesc);
172             }
173             statement.setString(6, pmAttachCreationIP);
174             statement.setTimestamp(7, pmAttachCreationDate);
175             statement.setTimestamp(8, pmAttachModifiedDate);
176             statement.setInt(9, pmAttachDownloadCount);
177             statement.setInt(10, pmAttachOption);
178             statement.setInt(11, pmAttachStatus);
179
180             if (statement.executeUpdate() != 1) {
181                 throw new CreateException("Error adding a row into table 'PmAttachment'.");
182             }
183             m_dirty = true;
184         } catch(SQLException sqle) {
185             log.error("Sql Execution Error!", sqle);
186             throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.create.");
187         } finally {
188             DBUtils.closeStatement(statement);
189             DBUtils.closeConnection(connection);
190         }
191
192         int pmAttachID = 0;
193         try {
194             pmAttachID = findPmAttachID(memberID, pmAttachCreationDate);
195         } catch (ObjectNotFoundException ex) {
196             // Hack the Oracle 9i problem
197
Timestamp roundTimestamp = new Timestamp((pmAttachCreationDate.getTime()/1000)*1000);
198             pmAttachID = findPmAttachID(memberID, roundTimestamp);
199         }
200         return pmAttachID;
201     }
202
203     public void delete(int pmAttachID)
204         throws DatabaseException, ObjectNotFoundException {
205
206         Connection connection = null;
207         PreparedStatement statement = null;
208         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
209         sql.append("DELETE FROM " + TABLE_NAME);
210         sql.append(" WHERE PmAttachID = ?");
211
212         try {
213             connection = DBUtils.getConnection();
214             statement = connection.prepareStatement(sql.toString());
215             statement.setInt(1, pmAttachID);
216             if (statement.executeUpdate() != 1) {
217                 throw new ObjectNotFoundException("Cannot delete a row in table PmAttachment where primary key = (" + pmAttachID + ").");
218             }
219             m_dirty = true;
220         } catch(SQLException sqle) {
221             log.error("Sql Execution Error!", sqle);
222             throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.delete.");
223         } finally {
224             DBUtils.closeStatement(statement);
225             DBUtils.closeConnection(connection);
226         }
227     }
228
229     /*
230      * Included columns: PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType,
231      * PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount,
232      * PmAttachOption, PmAttachStatus
233      * Excluded columns:
234      */

235     public PmAttachmentBean getPmAttachment(int pmAttachID)
236         throws ObjectNotFoundException, DatabaseException {
237
238         Connection connection = null;
239         PreparedStatement statement = null;
240         ResultSet resultSet = null;
241         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
242         sql.append("SELECT PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus");
243         sql.append(" FROM " + TABLE_NAME);
244         sql.append(" WHERE PmAttachID = ?");
245         try {
246             connection = DBUtils.getConnection();
247             statement = connection.prepareStatement(sql.toString());
248             statement.setInt(1, pmAttachID);
249             resultSet = statement.executeQuery();
250             if(!resultSet.next()) {
251                 throw new ObjectNotFoundException("Cannot find the row in table PmAttachment where primary key = (" + pmAttachID + ").");
252             }
253
254             PmAttachmentBean bean = new PmAttachmentBean();
255             // @todo: uncomment the following line(s) as needed
256
//bean.setPmAttachID(pmAttachID);
257
bean.setPmAttachID(resultSet.getInt("PmAttachID"));
258             bean.setMemberID(resultSet.getInt("MemberID"));
259             bean.setPmAttachFilename(resultSet.getString("PmAttachFilename"));
260             bean.setPmAttachFileSize(resultSet.getInt("PmAttachFileSize"));
261             bean.setPmAttachMimeType(resultSet.getString("PmAttachMimeType"));
262             bean.setPmAttachDesc(resultSet.getString("PmAttachDesc"));
263             bean.setPmAttachCreationIP(resultSet.getString("PmAttachCreationIP"));
264             bean.setPmAttachCreationDate(resultSet.getTimestamp("PmAttachCreationDate"));
265             bean.setPmAttachModifiedDate(resultSet.getTimestamp("PmAttachModifiedDate"));
266             bean.setPmAttachDownloadCount(resultSet.getInt("PmAttachDownloadCount"));
267             bean.setPmAttachOption(resultSet.getInt("PmAttachOption"));
268             bean.setPmAttachStatus(resultSet.getInt("PmAttachStatus"));
269             return bean;
270         } catch(SQLException sqle) {
271             log.error("Sql Execution Error!", sqle);
272             throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.getPmAttachment(pk).");
273         } finally {
274             DBUtils.closeResultSet(resultSet);
275             DBUtils.closeStatement(statement);
276             DBUtils.closeConnection(connection);
277         }
278     }
279
280     /**
281      * This method should be call only when we can make sure that postID is in database
282      */

283     public void increaseDownloadCount(int pmAttachID)
284         throws DatabaseException, ObjectNotFoundException {
285
286         Connection connection = null;
287         PreparedStatement statement = null;
288         String JavaDoc sql = "UPDATE " + TABLE_NAME + " SET PmAttachDownloadCount = PmAttachDownloadCount + 1 WHERE PmAttachID = ?";
289         try {
290             connection = DBUtils.getConnection();
291             statement = connection.prepareStatement(sql);
292             statement.setInt(1, pmAttachID);
293             if (statement.executeUpdate() != 1) {
294                 throw new ObjectNotFoundException("Cannot update the PmAttachDownloadCount in table PmAttachment. Please contact Web site Administrator.");
295             }
296             // @todo: review these lines below
297
// ATTENTION !!!
298
setDirty(true);
299         } catch (SQLException sqle) {
300             log.error("Sql Execution Error!", sqle);
301             throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.increaseDownloadCount(pk).");
302         } finally {
303             DBUtils.closeStatement(statement);
304             DBUtils.closeConnection(connection);
305         }
306     }
307
308     /*
309      * Included columns: PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType,
310      * PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount,
311      * PmAttachOption, PmAttachStatus
312      * Excluded columns:
313      */

314     public Collection JavaDoc getPmAttachments_inMessage(int messageID)
315         throws DatabaseException {
316
317         Connection connection = null;
318         PreparedStatement statement = null;
319         ResultSet resultSet = null;
320         Collection JavaDoc retValue = new ArrayList JavaDoc();
321         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
322         sql.append("SELECT pmattachment.PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus");
323         sql.append(" FROM " + TABLE_NAME + " pmattachment, " + PmAttachMessageDAO.TABLE_NAME + " pmattachmessage");
324         sql.append(" WHERE pmattachment.PmAttachID = pmattachmessage.PmAttachID AND pmattachmessage.MessageID = ?");
325         sql.append(" ORDER BY pmattachment.PmAttachID ASC ");
326         try {
327             connection = DBUtils.getConnection();
328             statement = connection.prepareStatement(sql.toString());
329             statement.setInt(1, messageID);
330             resultSet = statement.executeQuery();
331             while (resultSet.next()) {
332                 PmAttachmentBean bean = new PmAttachmentBean();
333                 bean.setPmAttachID(resultSet.getInt("PmAttachID"));
334                 bean.setMemberID(resultSet.getInt("MemberID"));
335                 bean.setPmAttachFilename(resultSet.getString("PmAttachFilename"));
336                 bean.setPmAttachFileSize(resultSet.getInt("PmAttachFileSize"));
337                 bean.setPmAttachMimeType(resultSet.getString("PmAttachMimeType"));
338                 bean.setPmAttachDesc(resultSet.getString("PmAttachDesc"));
339                 bean.setPmAttachCreationIP(resultSet.getString("PmAttachCreationIP"));
340                 bean.setPmAttachCreationDate(resultSet.getTimestamp("PmAttachCreationDate"));
341                 bean.setPmAttachModifiedDate(resultSet.getTimestamp("PmAttachModifiedDate"));
342                 bean.setPmAttachDownloadCount(resultSet.getInt("PmAttachDownloadCount"));
343                 bean.setPmAttachOption(resultSet.getInt("PmAttachOption"));
344                 bean.setPmAttachStatus(resultSet.getInt("PmAttachStatus"));
345                 retValue.add(bean);
346             }
347             return retValue;
348         } catch(SQLException sqle) {
349             log.error("Sql Execution Error!", sqle);
350             throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.getPmAttachments_inMessage.");
351         } finally {
352             DBUtils.closeResultSet(resultSet);
353             DBUtils.closeStatement(statement);
354             DBUtils.closeConnection(connection);
355         }
356     }
357
358     /*
359      * Included columns: PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType,
360      * PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount,
361      * PmAttachOption, PmAttachStatus
362      * Excluded columns:
363      */

364     // Note: this method using LEFT JOIN
365
public Collection JavaDoc getOrphanPmAttachments()
366         throws DatabaseException {
367
368         Connection connection = null;
369         PreparedStatement statement = null;
370         ResultSet resultSet = null;
371         Collection JavaDoc retValue = new ArrayList JavaDoc();
372         StringBuffer JavaDoc sql = new StringBuffer JavaDoc(512);
373         if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
374             // Oracle query
375
sql.append("SELECT pmattachment.PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus");
376             sql.append(" FROM " + TABLE_NAME + " pmattachment , " + PmAttachMessageDAO.TABLE_NAME + " pmattachmessage");
377             sql.append(" WHERE pmattachment.PmAttachID = pmattachmessage.PmAttachID (+) ");
378             sql.append(" AND pmattachmessage.PmAttachID IS NULL ");
379         } else {
380             // standard query
381
sql.append("SELECT pmattachment.PmAttachID, MemberID, PmAttachFilename, PmAttachFileSize, PmAttachMimeType, PmAttachDesc, PmAttachCreationIP, PmAttachCreationDate, PmAttachModifiedDate, PmAttachDownloadCount, PmAttachOption, PmAttachStatus");
382             sql.append(" FROM " + TABLE_NAME + " pmattachment LEFT JOIN " + PmAttachMessageDAO.TABLE_NAME + " pmattachmessage");
383             sql.append(" ON pmattachment.PmAttachID = pmattachmessage.PmAttachID ");
384             sql.append(" WHERE pmattachmessage.PmAttachID IS NULL ");
385         }
386         try {
387             connection = DBUtils.getConnection();
388             statement = connection.prepareStatement(sql.toString());
389             resultSet = statement.executeQuery();
390             while (resultSet.next()) {
391                 PmAttachmentBean bean = new PmAttachmentBean();
392                 bean.setPmAttachID(resultSet.getInt("PmAttachID"));
393                 bean.setMemberID(resultSet.getInt("MemberID"));
394                 bean.setPmAttachFilename(resultSet.getString("PmAttachFilename"));
395                 bean.setPmAttachFileSize(resultSet.getInt("PmAttachFileSize"));
396                 bean.setPmAttachMimeType(resultSet.getString("PmAttachMimeType"));
397                 bean.setPmAttachDesc(resultSet.getString("PmAttachDesc"));
398                 bean.setPmAttachCreationIP(resultSet.getString("PmAttachCreationIP"));
399                 bean.setPmAttachCreationDate(resultSet.getTimestamp("PmAttachCreationDate"));
400                 bean.setPmAttachModifiedDate(resultSet.getTimestamp("PmAttachModifiedDate"));
401                 bean.setPmAttachDownloadCount(resultSet.getInt("PmAttachDownloadCount"));
402                 bean.setPmAttachOption(resultSet.getInt("PmAttachOption"));
403                 bean.setPmAttachStatus(resultSet.getInt("PmAttachStatus"));
404                 retValue.add(bean);
405             }
406             return retValue;
407         } catch(SQLException sqle) {
408             log.error("Sql Execution Error!", sqle);
409             throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.getOrphanPmAttachments.");
410         } finally {
411             DBUtils.closeResultSet(resultSet);
412             DBUtils.closeStatement(statement);
413             DBUtils.closeConnection(connection);
414         }
415     }
416     
417     public void updatePmAttachOption(int pmAttachID, int pmAttachOption)
418         throws DatabaseException, ObjectNotFoundException {
419
420         Connection connection = null;
421         PreparedStatement statement = null;
422         String JavaDoc sql = "UPDATE " + TABLE_NAME + " SET PmAttachOption = ? WHERE PmAttachID = ?";
423         try {
424             connection = DBUtils.getConnection();
425             statement = connection.prepareStatement(sql);
426     
427             statement.setInt(1, pmAttachOption);
428             statement.setInt(2, pmAttachID);
429             if (statement.executeUpdate() != 1) {
430                 throw new ObjectNotFoundException("Cannot update the Option in table PmAttachment. Please contact Web site Administrator.");
431             }
432             setDirty(true);
433         } catch (SQLException sqle) {
434             log.error("Sql Execution Error!", sqle);
435             throw new DatabaseException("Error executing SQL in PmAttachmentDAOImplJDBC.updatePmAttachOption.");
436         } finally {
437             DBUtils.closeStatement(statement);
438             DBUtils.closeConnection(connection);
439         }
440     }
441
442 }// end of class PmAttachmentDAOImplJDBC
443
Popular Tags