Source Code Cross Referenced for AttachmentDAOImplJDBC.java in  » Forum » mvnforum-1.1 » com » mvnforum » db » jdbc » Java Source Code / Java DocumentationJava Source Code and Java Documentation

Java Source Code / Java Documentation
1. 6.0 JDK Core
2. 6.0 JDK Modules
3. 6.0 JDK Modules com.sun
4. 6.0 JDK Modules com.sun.java
5. 6.0 JDK Modules sun
6. 6.0 JDK Platform
7. Ajax
8. Apache Harmony Java SE
9. Aspect oriented
10. Authentication Authorization
11. Blogger System
12. Build
13. Byte Code
14. Cache
15. Chart
16. Chat
17. Code Analyzer
18. Collaboration
19. Content Management System
20. Database Client
21. Database DBMS
22. Database JDBC Connection Pool
23. Database ORM
24. Development
25. EJB Server geronimo
26. EJB Server GlassFish
27. EJB Server JBoss 4.2.1
28. EJB Server resin 3.1.5
29. ERP CRM Financial
30. ESB
31. Forum
32. GIS
33. Graphic Library
34. Groupware
35. HTML Parser
36. IDE
37. IDE Eclipse
38. IDE Netbeans
39. Installer
40. Internationalization Localization
41. Inversion of Control
42. Issue Tracking
43. J2EE
44. JBoss
45. JMS
46. JMX
47. Library
48. Mail Clients
49. Net
50. Parser
51. PDF
52. Portal
53. Profiler
54. Project Management
55. Report
56. RSS RDF
57. Rule Engine
58. Science
59. Scripting
60. Search Engine
61. Security
62. Sevlet Container
63. Source Control
64. Swing Library
65. Template Engine
66. Test Coverage
67. Testing
68. UML
69. Web Crawler
70. Web Framework
71. Web Mail
72. Web Server
73. Web Services
74. Web Services apache cxf 2.0.1
75. Web Services AXIS2
76. Wiki Engine
77. Workflow Engines
78. XML
79. XML UI
Java
Java Tutorial
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Java Source Code / Java Documentation » Forum » mvnforum 1.1 » com.mvnforum.db.jdbc 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


0001:        /*
0002:         * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/AttachmentDAOImplJDBC.java,v 1.37 2007/12/17 09:09:39 minhnn Exp $
0003:         * $Author: minhnn $
0004:         * $Revision: 1.37 $
0005:         * $Date: 2007/12/17 09:09:39 $
0006:         *
0007:         * ====================================================================
0008:         *
0009:         * Copyright (C) 2002-2007 by MyVietnam.net
0010:         *
0011:         * All copyright notices regarding mvnForum MUST remain
0012:         * intact in the scripts and in the outputted HTML.
0013:         * The "powered by" text/logo with a link back to
0014:         * http://www.mvnForum.com and http://www.MyVietnam.net in
0015:         * the footer of the pages MUST remain visible when the pages
0016:         * are viewed on the internet or intranet.
0017:         *
0018:         * This program is free software; you can redistribute it and/or modify
0019:         * it under the terms of the GNU General Public License as published by
0020:         * the Free Software Foundation; either version 2 of the License, or
0021:         * any later version.
0022:         *
0023:         * This program is distributed in the hope that it will be useful,
0024:         * but WITHOUT ANY WARRANTY; without even the implied warranty of
0025:         * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
0026:         * GNU General Public License for more details.
0027:         *
0028:         * You should have received a copy of the GNU General Public License
0029:         * along with this program; if not, write to the Free Software
0030:         * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
0031:         *
0032:         * Support can be obtained from support forums at:
0033:         * http://www.mvnForum.com/mvnforum/index
0034:         *
0035:         * Correspondence and Marketing Questions can be sent to:
0036:         * info at MyVietnam net
0037:         *
0038:         * @author: Minh Nguyen
0039:         * @author: Mai  Nguyen
0040:         */
0041:        package com.mvnforum.db.jdbc;
0042:
0043:        import java.io.StringReader;
0044:        import java.sql.*;
0045:        import java.util.ArrayList;
0046:        import java.util.Collection;
0047:
0048:        import com.mvnforum.db.*;
0049:
0050:        import net.myvietnam.mvncore.db.DBUtils;
0051:        import net.myvietnam.mvncore.exception.*;
0052:        import net.myvietnam.mvncore.util.AssertionUtil;
0053:
0054:        import org.apache.commons.logging.Log;
0055:        import org.apache.commons.logging.LogFactory;
0056:
0057:        public class AttachmentDAOImplJDBC implements  AttachmentDAO {
0058:
0059:            private static Log log = LogFactory
0060:                    .getLog(AttachmentDAOImplJDBC.class);
0061:
0062:            // this variable will support caching if cache for this class is needed
0063:            private static boolean m_dirty = true;
0064:
0065:            public AttachmentDAOImplJDBC() {
0066:            }
0067:
0068:            protected static boolean isDirty() {
0069:                return m_dirty;
0070:            }
0071:
0072:            protected static void setDirty(boolean dirty) {
0073:                m_dirty = dirty;
0074:            }
0075:
0076:            /*
0077:             * Included columns: PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType,
0078:             *                   AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount,
0079:             *                   AttachOption, AttachStatus
0080:             * Excluded columns: AttachID
0081:             */
0082:            public void create(int postID, int memberID, String attachFilename,
0083:                    int attachFileSize, String attachMimeType,
0084:                    String attachDesc, String attachCreationIP,
0085:                    Timestamp attachCreationDate, Timestamp attachModifiedDate,
0086:                    int attachDownloadCount, int attachOption, int attachStatus)
0087:                    throws CreateException, DatabaseException,
0088:                    ForeignKeyNotFoundException {
0089:
0090:                try {
0091:                    // @todo: modify the parameter list as needed
0092:                    // You may have to regenerate this method if the needed columns dont have attribute 'include'
0093:                    DAOFactory.getPostDAO().findByPrimaryKey(postID);
0094:                } catch (ObjectNotFoundException e) {
0095:                    throw new ForeignKeyNotFoundException(
0096:                            "Foreign key refers to table 'Post' does not exist. Cannot create new Attachment.");
0097:                }
0098:
0099:                //if admin allowed guest to send attachments, we must allow that too
0100:                if (memberID != 0) {
0101:                    try {
0102:                        // @todo: modify the parameter list as needed
0103:                        // You may have to regenerate this method if the needed columns dont have attribute 'include'
0104:                        DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
0105:                    } catch (ObjectNotFoundException e) {
0106:                        throw new ForeignKeyNotFoundException(
0107:                                "Foreign key refers to table 'Member' does not exist. Cannot create new Attachment.");
0108:                    }
0109:                }
0110:
0111:                Connection connection = null;
0112:                PreparedStatement statement = null;
0113:                StringBuffer sql = new StringBuffer(512);
0114:                sql
0115:                        .append("INSERT INTO "
0116:                                + TABLE_NAME
0117:                                + " (PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus)");
0118:                sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
0119:                try {
0120:                    connection = DBUtils.getConnection();
0121:                    statement = connection.prepareStatement(sql.toString());
0122:
0123:                    statement.setInt(1, postID);
0124:                    statement.setInt(2, memberID);
0125:                    statement.setString(3, attachFilename);
0126:                    statement.setInt(4, attachFileSize);
0127:                    statement.setString(5, attachMimeType);
0128:                    if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
0129:                        statement.setCharacterStream(6, new StringReader(
0130:                                attachDesc), attachDesc.length());
0131:                    } else {
0132:                        statement.setString(6, attachDesc);
0133:                    }
0134:                    statement.setString(7, attachCreationIP);
0135:                    statement.setTimestamp(8, attachCreationDate);
0136:                    statement.setTimestamp(9, attachModifiedDate);
0137:                    statement.setInt(10, attachDownloadCount);
0138:                    statement.setInt(11, attachOption);
0139:                    statement.setInt(12, attachStatus);
0140:
0141:                    if (statement.executeUpdate() != 1) {
0142:                        throw new CreateException(
0143:                                "Error adding a row into table 'Attachment'.");
0144:                    }
0145:                    m_dirty = true;
0146:                } catch (SQLException sqle) {
0147:                    log.error("Sql Execution Error!", sqle);
0148:                    throw new DatabaseException(
0149:                            "Error executing SQL in AttachmentDAOImplJDBC.create.");
0150:                } finally {
0151:                    DBUtils.closeStatement(statement);
0152:                    DBUtils.closeConnection(connection);
0153:                }
0154:            }
0155:
0156:            public int createAttachment(int postID, int memberID,
0157:                    String attachFilename, int attachFileSize,
0158:                    String attachMimeType, String attachDesc,
0159:                    String attachCreationIP, Timestamp attachCreationDate,
0160:                    Timestamp attachModifiedDate, int attachDownloadCount,
0161:                    int attachOption, int attachStatus) throws CreateException,
0162:                    DatabaseException, ForeignKeyNotFoundException,
0163:                    ObjectNotFoundException {
0164:
0165:                create(postID, memberID, attachFilename, attachFileSize,
0166:                        attachMimeType, attachDesc, attachCreationIP,
0167:                        attachCreationDate, attachModifiedDate,
0168:                        attachDownloadCount, attachOption, attachStatus);
0169:
0170:                int attachID = 0;
0171:                try {
0172:                    attachID = findAttachID(postID, memberID,
0173:                            attachCreationDate);
0174:                } catch (ObjectNotFoundException ex) {
0175:                    // Hack the Oracle 9i problem
0176:                    Timestamp roundTimestamp = new Timestamp(
0177:                            (attachCreationDate.getTime() / 1000) * 1000);
0178:                    attachID = findAttachID(postID, memberID, roundTimestamp);
0179:                }
0180:                return attachID;
0181:            }
0182:
0183:            public void delete(int attachID) throws DatabaseException,
0184:                    ObjectNotFoundException {
0185:
0186:                Connection connection = null;
0187:                PreparedStatement statement = null;
0188:                StringBuffer sql = new StringBuffer(512);
0189:                sql.append("DELETE FROM " + TABLE_NAME);
0190:                sql.append(" WHERE AttachID = ?");
0191:
0192:                try {
0193:                    connection = DBUtils.getConnection();
0194:                    statement = connection.prepareStatement(sql.toString());
0195:                    statement.setInt(1, attachID);
0196:                    if (statement.executeUpdate() != 1) {
0197:                        throw new ObjectNotFoundException(
0198:                                "Cannot delete a row in table Attachment where primary key = ("
0199:                                        + attachID + ").");
0200:                    }
0201:                    m_dirty = true;
0202:                } catch (SQLException sqle) {
0203:                    log.error("Sql Execution Error!", sqle);
0204:                    throw new DatabaseException(
0205:                            "Error executing SQL in AttachmentDAOImplJDBC.delete.");
0206:                } finally {
0207:                    DBUtils.closeStatement(statement);
0208:                    DBUtils.closeConnection(connection);
0209:                }
0210:            }
0211:
0212:            /*
0213:             * Included columns: PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType,
0214:             *                   AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount,
0215:             *                   AttachOption, AttachStatus
0216:             * Excluded columns: AttachID
0217:             */
0218:            public AttachmentBean getAttachment(int attachID)
0219:                    throws ObjectNotFoundException, DatabaseException {
0220:
0221:                Connection connection = null;
0222:                PreparedStatement statement = null;
0223:                ResultSet resultSet = null;
0224:                StringBuffer sql = new StringBuffer(512);
0225:                sql
0226:                        .append("SELECT attach.PostID, attach.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus, post.ForumID");
0227:                sql.append(" FROM " + TABLE_NAME + " attach, "
0228:                        + PostDAO.TABLE_NAME + " post");
0229:                sql.append(" WHERE attach.PostID =  post.PostID");
0230:                sql.append(" AND AttachID = ?");
0231:                try {
0232:                    connection = DBUtils.getConnection();
0233:                    statement = connection.prepareStatement(sql.toString());
0234:                    statement.setInt(1, attachID);
0235:                    resultSet = statement.executeQuery();
0236:                    if (!resultSet.next()) {
0237:                        throw new ObjectNotFoundException(
0238:                                "Cannot find the row in table Attachment where primary key = ("
0239:                                        + attachID + ").");
0240:                    }
0241:
0242:                    AttachmentBean bean = new AttachmentBean();
0243:                    bean.setAttachID(attachID);
0244:                    bean.setPostID(resultSet.getInt("PostID"));
0245:                    bean.setMemberID(resultSet.getInt("MemberID"));
0246:                    bean.setAttachFilename(resultSet
0247:                            .getString("AttachFilename"));
0248:                    bean.setAttachFileSize(resultSet.getInt("AttachFileSize"));
0249:                    bean.setAttachMimeType(resultSet
0250:                            .getString("AttachMimeType"));
0251:                    bean.setAttachDesc(resultSet.getString("AttachDesc"));
0252:                    bean.setAttachCreationIP(resultSet
0253:                            .getString("AttachCreationIP"));
0254:                    bean.setAttachCreationDate(resultSet
0255:                            .getTimestamp("AttachCreationDate"));
0256:                    bean.setAttachModifiedDate(resultSet
0257:                            .getTimestamp("AttachModifiedDate"));
0258:                    bean.setAttachDownloadCount(resultSet
0259:                            .getInt("AttachDownloadCount"));
0260:                    bean.setAttachOption(resultSet.getInt("AttachOption"));
0261:                    bean.setAttachStatus(resultSet.getInt("AttachStatus"));
0262:                    bean.setForumID(resultSet.getInt("ForumID"));
0263:                    return bean;
0264:                } catch (SQLException sqle) {
0265:                    log.error("Sql Execution Error!", sqle);
0266:                    throw new DatabaseException(
0267:                            "Error executing SQL in AttachmentDAOImplJDBC.getAttachment(pk).");
0268:                } finally {
0269:                    DBUtils.closeResultSet(resultSet);
0270:                    DBUtils.closeStatement(statement);
0271:                    DBUtils.closeConnection(connection);
0272:                }
0273:            }
0274:
0275:            /*
0276:             * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
0277:             *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
0278:             *                   AttachDownloadCount, AttachOption, AttachStatus
0279:             * Excluded columns:
0280:             */
0281:            public Collection getAttachments() throws DatabaseException {
0282:
0283:                Connection connection = null;
0284:                PreparedStatement statement = null;
0285:                ResultSet resultSet = null;
0286:                Collection retValue = new ArrayList();
0287:                StringBuffer sql = new StringBuffer(512);
0288:                sql
0289:                        .append("SELECT AttachID, attach.PostID, attach.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus, post.ForumID");
0290:                sql.append(" FROM " + TABLE_NAME + " attach, "
0291:                        + PostDAO.TABLE_NAME + " post");
0292:                sql.append(" WHERE attach.PostID =  post.PostID");
0293:                //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
0294:                try {
0295:                    connection = DBUtils.getConnection();
0296:                    statement = connection.prepareStatement(sql.toString());
0297:                    resultSet = statement.executeQuery();
0298:                    while (resultSet.next()) {
0299:                        AttachmentBean bean = new AttachmentBean();
0300:                        bean.setAttachID(resultSet.getInt("AttachID"));
0301:                        bean.setPostID(resultSet.getInt("PostID"));
0302:                        bean.setMemberID(resultSet.getInt("MemberID"));
0303:                        bean.setAttachFilename(resultSet
0304:                                .getString("AttachFilename"));
0305:                        bean.setAttachFileSize(resultSet
0306:                                .getInt("AttachFileSize"));
0307:                        bean.setAttachMimeType(resultSet
0308:                                .getString("AttachMimeType"));
0309:                        bean.setAttachDesc(resultSet.getString("AttachDesc"));
0310:                        bean.setAttachCreationIP(resultSet
0311:                                .getString("AttachCreationIP"));
0312:                        bean.setAttachCreationDate(resultSet
0313:                                .getTimestamp("AttachCreationDate"));
0314:                        bean.setAttachModifiedDate(resultSet
0315:                                .getTimestamp("AttachModifiedDate"));
0316:                        bean.setAttachDownloadCount(resultSet
0317:                                .getInt("AttachDownloadCount"));
0318:                        bean.setAttachOption(resultSet.getInt("AttachOption"));
0319:                        bean.setAttachStatus(resultSet.getInt("AttachStatus"));
0320:                        bean.setForumID(resultSet.getInt("ForumID"));
0321:                        retValue.add(bean);
0322:                    }
0323:                    return retValue;
0324:                } catch (SQLException sqle) {
0325:                    log.error("Sql Execution Error!", sqle);
0326:                    throw new DatabaseException(
0327:                            "Error executing SQL in AttachmentDAOImplJDBC.getAttachments.");
0328:                } finally {
0329:                    DBUtils.closeResultSet(resultSet);
0330:                    DBUtils.closeStatement(statement);
0331:                    DBUtils.closeConnection(connection);
0332:                }
0333:            }
0334:
0335:            public int getNumberOfAttachments(int category, int forum)
0336:                    throws DatabaseException {
0337:
0338:                Connection connection = null;
0339:                PreparedStatement statement = null;
0340:                ResultSet resultSet = null;
0341:                StringBuffer sql = new StringBuffer(512);
0342:                sql.append("SELECT Count(*)");
0343:                sql.append(" FROM " + TABLE_NAME + " attach");
0344:
0345:                if ((forum > -1) || (category > -1)) {
0346:                    sql.append(" , " + PostDAO.TABLE_NAME + " post");
0347:                }
0348:                if (category > -1) {
0349:                    sql.append(" , " + ForumDAO.TABLE_NAME + " forum");
0350:                }
0351:
0352:                if ((category > -1) && (forum > -1)) {
0353:                    throw new AssertionError(
0354:                            "Assertion in AttachmentDAOImplJDBC.getNumberOfAttachments.");
0355:                }
0356:                if (category > -1) {
0357:                    sql.append(" WHERE post.PostID = attach.PostID ");
0358:                    sql
0359:                            .append(" AND (forum.ForumID = post.ForumID AND forum.CategoryID = ?) ");
0360:                } else if (forum > -1) {
0361:                    sql
0362:                            .append(" WHERE (post.PostID = attach.PostID AND post.ForumID = ?) ");
0363:                }
0364:                try {
0365:                    connection = DBUtils.getConnection();
0366:                    statement = connection.prepareStatement(sql.toString());
0367:
0368:                    if (category > -1) {
0369:                        statement.setInt(1, category);
0370:                    } else if (forum > -1) {
0371:                        statement.setInt(1, forum);
0372:                    }
0373:
0374:                    resultSet = statement.executeQuery();
0375:                    AssertionUtil
0376:                            .doAssert(resultSet.next(),
0377:                                    "Assertion in AttachmentDAOImplJDBC.getNumberOfAttachments.");
0378:                    return resultSet.getInt(1);
0379:                } catch (SQLException sqle) {
0380:                    log.error("Sql Execution Error!", sqle);
0381:                    throw new DatabaseException(
0382:                            "Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments.");
0383:                } finally {
0384:                    DBUtils.closeResultSet(resultSet);
0385:                    DBUtils.closeStatement(statement);
0386:                    DBUtils.closeConnection(connection);
0387:                }
0388:            }
0389:
0390:            public int getNumberOfAttachments_inPost(int postID)
0391:                    throws DatabaseException {
0392:
0393:                Connection connection = null;
0394:                PreparedStatement statement = null;
0395:                ResultSet resultSet = null;
0396:                StringBuffer sql = new StringBuffer(512);
0397:                sql.append("SELECT Count(*)");
0398:                sql.append(" FROM " + TABLE_NAME);
0399:                sql.append(" WHERE PostID = ?");
0400:                try {
0401:                    connection = DBUtils.getConnection();
0402:                    statement = connection.prepareStatement(sql.toString());
0403:                    statement.setInt(1, postID);
0404:                    resultSet = statement.executeQuery();
0405:                    AssertionUtil
0406:                            .doAssert(resultSet.next(),
0407:                                    "Assertion in AttachmentDAOImplJDBC.getNumberOfAttachments_inPost.");
0408:                    return resultSet.getInt(1);
0409:                } catch (SQLException sqle) {
0410:                    log.error("Sql Execution Error!", sqle);
0411:                    throw new DatabaseException(
0412:                            "Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments_inPost.");
0413:                } finally {
0414:                    DBUtils.closeResultSet(resultSet);
0415:                    DBUtils.closeStatement(statement);
0416:                    DBUtils.closeConnection(connection);
0417:                }
0418:            }
0419:
0420:            public int getNumberOfAttachments_inThread(int threadID)
0421:                    throws DatabaseException {
0422:
0423:                Connection connection = null;
0424:                PreparedStatement statement = null;
0425:                ResultSet resultSet = null;
0426:                StringBuffer sql = new StringBuffer(512);
0427:                sql.append("SELECT Count(*)");
0428:                sql.append(" FROM " + TABLE_NAME + " attachment, "
0429:                        + PostDAO.TABLE_NAME + " post");
0430:                sql.append(" WHERE attachment.PostID = post.PostID ");
0431:                sql.append(" AND post.ThreadID = ?");
0432:                try {
0433:                    connection = DBUtils.getConnection();
0434:                    statement = connection.prepareStatement(sql.toString());
0435:                    statement.setInt(1, threadID);
0436:                    resultSet = statement.executeQuery();
0437:                    AssertionUtil
0438:                            .doAssert(resultSet.next(),
0439:                                    "Assertion in AttachmentDAOImplJDBC.getNumberOfAttachments_inThread.");
0440:                    return resultSet.getInt(1);
0441:                } catch (SQLException sqle) {
0442:                    log.error("Sql Execution Error!", sqle);
0443:                    throw new DatabaseException(
0444:                            "Error executing SQL in AttachmentDAOImplJDBC.getNumberOfAttachments_inThread.");
0445:                } finally {
0446:                    DBUtils.closeResultSet(resultSet);
0447:                    DBUtils.closeStatement(statement);
0448:                    DBUtils.closeConnection(connection);
0449:                }
0450:            }
0451:
0452:            /************************************************
0453:             * Customized methods come below
0454:             ************************************************/
0455:
0456:            /**
0457:             * This is a customized method
0458:             */
0459:            protected static int findAttachID(int postID, int memberID,
0460:                    Timestamp attachCreationDate)
0461:                    throws ObjectNotFoundException, DatabaseException {
0462:
0463:                Connection connection = null;
0464:                PreparedStatement statement = null;
0465:                ResultSet resultSet = null;
0466:                StringBuffer sql = new StringBuffer(512);
0467:                sql.append("SELECT MAX(AttachID)");
0468:                sql.append(" FROM " + TABLE_NAME);
0469:                sql
0470:                        .append(" WHERE PostID = ? AND MemberID = ? AND AttachCreationDate = ?");
0471:                try {
0472:                    connection = DBUtils.getConnection();
0473:                    statement = connection.prepareStatement(sql.toString());
0474:                    statement.setInt(1, postID);
0475:                    statement.setInt(2, memberID);
0476:                    statement.setTimestamp(3, attachCreationDate);
0477:                    resultSet = statement.executeQuery();
0478:                    if (!resultSet.next()) {
0479:                        throw new ObjectNotFoundException(
0480:                                "Cannot find the AttachID in table Attachment.");
0481:                    }
0482:
0483:                    return resultSet.getInt(1);
0484:                } catch (SQLException sqle) {
0485:                    log.error("Sql Execution Error!", sqle);
0486:                    throw new DatabaseException(
0487:                            "Error executing SQL in AttachmentDAOImplJDBC.findAttachID.");
0488:                } finally {
0489:                    DBUtils.closeResultSet(resultSet);
0490:                    DBUtils.closeStatement(statement);
0491:                    DBUtils.closeConnection(connection);
0492:                }
0493:            }
0494:
0495:            public void delete_inPost(int postID) throws DatabaseException {
0496:
0497:                Connection connection = null;
0498:                PreparedStatement statement = null;
0499:                StringBuffer sql = new StringBuffer(512);
0500:                sql.append("DELETE FROM " + TABLE_NAME);
0501:                sql.append(" WHERE PostID = ?");
0502:
0503:                try {
0504:                    connection = DBUtils.getConnection();
0505:                    statement = connection.prepareStatement(sql.toString());
0506:                    statement.setInt(1, postID);
0507:                    statement.executeUpdate();
0508:                    m_dirty = true;
0509:                } catch (SQLException sqle) {
0510:                    log.error("Sql Execution Error!", sqle);
0511:                    throw new DatabaseException(
0512:                            "Error executing SQL in AttachmentDAOImplJDBC.delete_inPost.");
0513:                } finally {
0514:                    DBUtils.closeStatement(statement);
0515:                    DBUtils.closeConnection(connection);
0516:                }
0517:            }
0518:
0519:            /*
0520:             * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
0521:             *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
0522:             *                   AttachDownloadCount, AttachOption, AttachStatus
0523:             * Excluded columns:
0524:             */
0525:            public Collection getAttachments_inPost(int postID)
0526:                    throws DatabaseException {
0527:
0528:                Connection connection = null;
0529:                PreparedStatement statement = null;
0530:                ResultSet resultSet = null;
0531:                Collection retValue = new ArrayList();
0532:                StringBuffer sql = new StringBuffer(512);
0533:                sql
0534:                        .append("SELECT AttachID, PostID, MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
0535:                sql.append(" FROM " + TABLE_NAME);
0536:                sql.append(" WHERE PostID = ?");
0537:                sql.append(" ORDER BY AttachID ASC ");
0538:                try {
0539:                    connection = DBUtils.getConnection();
0540:                    statement = connection.prepareStatement(sql.toString());
0541:                    statement.setInt(1, postID);
0542:                    resultSet = statement.executeQuery();
0543:                    while (resultSet.next()) {
0544:                        AttachmentBean bean = new AttachmentBean();
0545:                        bean.setAttachID(resultSet.getInt("AttachID"));
0546:                        bean.setPostID(resultSet.getInt("PostID"));
0547:                        bean.setMemberID(resultSet.getInt("MemberID"));
0548:                        bean.setAttachFilename(resultSet
0549:                                .getString("AttachFilename"));
0550:                        bean.setAttachFileSize(resultSet
0551:                                .getInt("AttachFileSize"));
0552:                        bean.setAttachMimeType(resultSet
0553:                                .getString("AttachMimeType"));
0554:                        bean.setAttachDesc(resultSet.getString("AttachDesc"));
0555:                        bean.setAttachCreationIP(resultSet
0556:                                .getString("AttachCreationIP"));
0557:                        bean.setAttachCreationDate(resultSet
0558:                                .getTimestamp("AttachCreationDate"));
0559:                        bean.setAttachModifiedDate(resultSet
0560:                                .getTimestamp("AttachModifiedDate"));
0561:                        bean.setAttachDownloadCount(resultSet
0562:                                .getInt("AttachDownloadCount"));
0563:                        bean.setAttachOption(resultSet.getInt("AttachOption"));
0564:                        bean.setAttachStatus(resultSet.getInt("AttachStatus"));
0565:                        retValue.add(bean);
0566:                    }
0567:                    return retValue;
0568:                } catch (SQLException sqle) {
0569:                    log.error("Sql Execution Error!", sqle);
0570:                    throw new DatabaseException(
0571:                            "Error executing SQL in AttachmentDAOImplJDBC.getAttachments_inPost.");
0572:                } finally {
0573:                    DBUtils.closeResultSet(resultSet);
0574:                    DBUtils.closeStatement(statement);
0575:                    DBUtils.closeConnection(connection);
0576:                }
0577:            }
0578:
0579:            /*
0580:             * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
0581:             *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
0582:             *                   AttachDownloadCount, AttachOption, AttachStatus
0583:             * Excluded columns:
0584:             */
0585:            public Collection getAttachments_inThread(int threadID)
0586:                    throws DatabaseException {
0587:
0588:                Connection connection = null;
0589:                PreparedStatement statement = null;
0590:                ResultSet resultSet = null;
0591:                Collection retValue = new ArrayList();
0592:                StringBuffer sql = new StringBuffer(512);
0593:                sql
0594:                        .append("SELECT AttachID, attachment.PostID, attachment.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
0595:                sql.append(" FROM " + TABLE_NAME + " attachment, "
0596:                        + PostDAO.TABLE_NAME + " post ");
0597:                sql
0598:                        .append(" WHERE attachment.PostID = post.PostID AND post.ThreadID = ? ");
0599:                sql.append(" ORDER BY AttachID ASC ");
0600:                try {
0601:                    connection = DBUtils.getConnection();
0602:                    statement = connection.prepareStatement(sql.toString());
0603:                    statement.setInt(1, threadID);
0604:                    resultSet = statement.executeQuery();
0605:                    while (resultSet.next()) {
0606:                        AttachmentBean bean = new AttachmentBean();
0607:                        bean.setAttachID(resultSet.getInt("AttachID"));
0608:                        bean.setPostID(resultSet.getInt("PostID"));
0609:                        bean.setMemberID(resultSet.getInt("MemberID"));
0610:                        bean.setAttachFilename(resultSet
0611:                                .getString("AttachFilename"));
0612:                        bean.setAttachFileSize(resultSet
0613:                                .getInt("AttachFileSize"));
0614:                        bean.setAttachMimeType(resultSet
0615:                                .getString("AttachMimeType"));
0616:                        bean.setAttachDesc(resultSet.getString("AttachDesc"));
0617:                        bean.setAttachCreationIP(resultSet
0618:                                .getString("AttachCreationIP"));
0619:                        bean.setAttachCreationDate(resultSet
0620:                                .getTimestamp("AttachCreationDate"));
0621:                        bean.setAttachModifiedDate(resultSet
0622:                                .getTimestamp("AttachModifiedDate"));
0623:                        bean.setAttachDownloadCount(resultSet
0624:                                .getInt("AttachDownloadCount"));
0625:                        bean.setAttachOption(resultSet.getInt("AttachOption"));
0626:                        bean.setAttachStatus(resultSet.getInt("AttachStatus"));
0627:                        retValue.add(bean);
0628:                    }
0629:                    return retValue;
0630:                } catch (SQLException sqle) {
0631:                    log.error("Sql Execution Error!", sqle);
0632:                    throw new DatabaseException(
0633:                            "Error executing SQL in AttachmentDAOImplJDBC.getAttachments_inThread.");
0634:                } finally {
0635:                    DBUtils.closeResultSet(resultSet);
0636:                    DBUtils.closeStatement(statement);
0637:                    DBUtils.closeConnection(connection);
0638:                }
0639:            }
0640:
0641:            /*
0642:             * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
0643:             *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
0644:             *                   AttachDownloadCount, AttachOption, AttachStatus
0645:             * Excluded columns:
0646:             */
0647:            public Collection getAttachments_inForum(int forumID)
0648:                    throws DatabaseException {
0649:
0650:                Connection connection = null;
0651:                PreparedStatement statement = null;
0652:                ResultSet resultSet = null;
0653:                Collection retValue = new ArrayList();
0654:                StringBuffer sql = new StringBuffer(512);
0655:                sql
0656:                        .append("SELECT AttachID, attachment.PostID, attachment.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus");
0657:                sql.append(" FROM " + TABLE_NAME + " attachment, "
0658:                        + PostDAO.TABLE_NAME + " post ");
0659:                sql
0660:                        .append(" WHERE attachment.PostID = post.PostID AND post.ForumID = ? ");
0661:                sql.append(" ORDER BY AttachID ASC ");
0662:                try {
0663:                    connection = DBUtils.getConnection();
0664:                    statement = connection.prepareStatement(sql.toString());
0665:                    statement.setInt(1, forumID);
0666:                    resultSet = statement.executeQuery();
0667:                    while (resultSet.next()) {
0668:                        AttachmentBean bean = new AttachmentBean();
0669:                        bean.setAttachID(resultSet.getInt("AttachID"));
0670:                        bean.setPostID(resultSet.getInt("PostID"));
0671:                        bean.setMemberID(resultSet.getInt("MemberID"));
0672:                        bean.setAttachFilename(resultSet
0673:                                .getString("AttachFilename"));
0674:                        bean.setAttachFileSize(resultSet
0675:                                .getInt("AttachFileSize"));
0676:                        bean.setAttachMimeType(resultSet
0677:                                .getString("AttachMimeType"));
0678:                        bean.setAttachDesc(resultSet.getString("AttachDesc"));
0679:                        bean.setAttachCreationIP(resultSet
0680:                                .getString("AttachCreationIP"));
0681:                        bean.setAttachCreationDate(resultSet
0682:                                .getTimestamp("AttachCreationDate"));
0683:                        bean.setAttachModifiedDate(resultSet
0684:                                .getTimestamp("AttachModifiedDate"));
0685:                        bean.setAttachDownloadCount(resultSet
0686:                                .getInt("AttachDownloadCount"));
0687:                        bean.setAttachOption(resultSet.getInt("AttachOption"));
0688:                        bean.setAttachStatus(resultSet.getInt("AttachStatus"));
0689:                        retValue.add(bean);
0690:                    }
0691:                    return retValue;
0692:                } catch (SQLException sqle) {
0693:                    log.error("Sql Execution Error!", sqle);
0694:                    throw new DatabaseException(
0695:                            "Error executing SQL in AttachmentDAOImplJDBC.getAttachments_inForum.");
0696:                } finally {
0697:                    DBUtils.closeResultSet(resultSet);
0698:                    DBUtils.closeStatement(statement);
0699:                    DBUtils.closeConnection(connection);
0700:                }
0701:            }
0702:
0703:            /**
0704:             * This method should be call only when we can make sure that postID is in database
0705:             */
0706:            public void increaseDownloadCount(int attachID)
0707:                    throws DatabaseException, ObjectNotFoundException {
0708:
0709:                Connection connection = null;
0710:                PreparedStatement statement = null;
0711:                String sql = "UPDATE "
0712:                        + TABLE_NAME
0713:                        + " SET AttachDownloadCount = AttachDownloadCount + 1 WHERE AttachID = ?";
0714:                try {
0715:                    connection = DBUtils.getConnection();
0716:                    statement = connection.prepareStatement(sql);
0717:                    statement.setInt(1, attachID);
0718:                    if (statement.executeUpdate() != 1) {
0719:                        throw new ObjectNotFoundException(
0720:                                "Cannot update the AttachDownloadCount in table Attachment. Please contact Web site Administrator.");
0721:                    }
0722:                    //@todo: coi lai cho nay
0723:                    // ATTENTION !!!
0724:                    setDirty(true);
0725:                } catch (SQLException sqle) {
0726:                    log.error("Sql Execution Error!", sqle);
0727:                    throw new DatabaseException(
0728:                            "Error executing SQL in AttachmentDAOImplJDBC.increaseDownloadCount.");
0729:                } finally {
0730:                    DBUtils.closeStatement(statement);
0731:                    DBUtils.closeConnection(connection);
0732:                }
0733:            }
0734:
0735:            public void updateAttachDesc(int attachID, String attachDesc)
0736:                    throws DatabaseException, ObjectNotFoundException {
0737:
0738:                Connection connection = null;
0739:                PreparedStatement statement = null;
0740:                String sql = "UPDATE " + TABLE_NAME
0741:                        + " SET AttachDesc = ? WHERE AttachID = ?";
0742:                try {
0743:                    connection = DBUtils.getConnection();
0744:                    statement = connection.prepareStatement(sql);
0745:
0746:                    if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
0747:                        statement.setCharacterStream(1, new StringReader(
0748:                                attachDesc), attachDesc.length());
0749:                    } else {
0750:                        statement.setString(1, attachDesc);
0751:                    }
0752:                    statement.setInt(2, attachID);
0753:                    if (statement.executeUpdate() != 1) {
0754:                        throw new ObjectNotFoundException(
0755:                                "Cannot update the Desc in table Attachment. Please contact Web site Administrator.");
0756:                    }
0757:                    //@todo: coi lai cho nay
0758:                    // ATTENTION !!!
0759:                    setDirty(true);
0760:                } catch (SQLException sqle) {
0761:                    log.error("Sql Execution Error!", sqle);
0762:                    throw new DatabaseException(
0763:                            "Error executing SQL in AttachmentDAOImplJDBC.updateAttachDesc.");
0764:                } finally {
0765:                    DBUtils.closeStatement(statement);
0766:                    DBUtils.closeConnection(connection);
0767:                }
0768:            }
0769:
0770:            public void updateAttachOption(int attachID, int attachOption)
0771:                    throws DatabaseException, ObjectNotFoundException {
0772:
0773:                Connection connection = null;
0774:                PreparedStatement statement = null;
0775:                String sql = "UPDATE " + TABLE_NAME
0776:                        + " SET AttachOption = ? WHERE AttachID = ?";
0777:                try {
0778:                    connection = DBUtils.getConnection();
0779:                    statement = connection.prepareStatement(sql);
0780:
0781:                    statement.setInt(1, attachOption);
0782:                    statement.setInt(2, attachID);
0783:                    if (statement.executeUpdate() != 1) {
0784:                        throw new ObjectNotFoundException(
0785:                                "Cannot update the Option in table Attachment. Please contact Web site Administrator.");
0786:                    }
0787:                    // @todo: coi lai cho nay
0788:                    // ATTENTION !!!
0789:                    setDirty(true);
0790:                } catch (SQLException sqle) {
0791:                    log.error("Sql Execution Error!", sqle);
0792:                    throw new DatabaseException(
0793:                            "Error executing SQL in AttachmentDAOImplJDBC.updateAttachOption.");
0794:                } finally {
0795:                    DBUtils.closeStatement(statement);
0796:                    DBUtils.closeConnection(connection);
0797:                }
0798:            }
0799:
0800:            public Collection getAttachments_withSortSupport_limit(int offset,
0801:                    int rowsToReturn, int category, int forum)
0802:                    throws IllegalArgumentException, DatabaseException {
0803:
0804:                if (offset < 0)
0805:                    throw new IllegalArgumentException(
0806:                            "The offset < 0 is not allowed.");
0807:                if (rowsToReturn <= 0)
0808:                    throw new IllegalArgumentException(
0809:                            "The rowsToReturn <= 0 is not allowed.");
0810:
0811:                if ((category > -1) && (forum > -1)) {
0812:                    throw new IllegalArgumentException(
0813:                            "getAttachments_withSortSupport_limit() does not accept (category > -1) and (forum > -1)");
0814:                }
0815:
0816:                if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
0817:                    return getAttachments_withSortSupport_limit_mysql(offset,
0818:                            rowsToReturn, category, forum);
0819:                }
0820:
0821:                return getAttachments_withSortSupport_limit_general(offset,
0822:                        rowsToReturn, category, forum);
0823:            }
0824:
0825:            /*
0826:             * Included columns: AttachID, PostID, MemberID, AttachFilename, AttachFileSize,
0827:             *                   AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate,
0828:             *                   AttachDownloadCount, AttachOption, AttachStatus
0829:             * Excluded columns:
0830:             */
0831:            public Collection getAttachments_withSortSupport_limit_mysql(
0832:                    int offset, int rowsToReturn, int category, int forum)
0833:                    throws IllegalArgumentException, DatabaseException {
0834:
0835:                Connection connection = null;
0836:                PreparedStatement statement = null;
0837:                ResultSet resultSet = null;
0838:                Collection retValue = new ArrayList();
0839:                StringBuffer sql = new StringBuffer(512);
0840:                sql
0841:                        .append("SELECT AttachID, attach.PostID, attach.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus, post.ForumID");
0842:                sql.append(" FROM " + TABLE_NAME + " attach, "
0843:                        + PostDAO.TABLE_NAME + " post");
0844:                if (category > -1) {
0845:                    sql.append(" , " + ForumDAO.TABLE_NAME + " forum");
0846:                }
0847:                sql.append(" WHERE attach.PostID = post.PostID");
0848:                if ((category > -1) && (forum > -1)) {
0849:                    throw new AssertionError(
0850:                            "Assertion in AttachmentDAOImplJDBC.getAttachments_withSortSupport_limit_mysql()");
0851:                }
0852:                if (category > -1) {
0853:                    sql
0854:                            .append(" AND (forum.ForumID = post.ForumID AND forum.CategoryID = ?) ");
0855:                } else if (forum > -1) {
0856:                    sql.append(" AND post.ForumID = ? ");
0857:                } else {
0858:                    // do nothing, because we will get all attachment
0859:                }
0860:                //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
0861:                sql.append(" LIMIT ?, ?");
0862:                try {
0863:                    connection = DBUtils.getConnection();
0864:                    statement = connection.prepareStatement(sql.toString());
0865:                    int i = 0;
0866:                    if (category > -1) {
0867:                        statement.setInt(++i, category);
0868:                    } else if (forum > -1) {
0869:                        statement.setInt(++i, forum);
0870:                    } else {
0871:                        // do nothing, because we will get all attachment
0872:                    }
0873:                    statement.setInt(++i, offset);
0874:                    statement.setInt(++i, rowsToReturn);
0875:                    resultSet = statement.executeQuery();
0876:                    while (resultSet.next()) {
0877:                        AttachmentBean bean = new AttachmentBean();
0878:                        bean.setAttachID(resultSet.getInt("AttachID"));
0879:                        bean.setPostID(resultSet.getInt("PostID"));
0880:                        bean.setMemberID(resultSet.getInt("MemberID"));
0881:                        bean.setAttachFilename(resultSet
0882:                                .getString("AttachFilename"));
0883:                        bean.setAttachFileSize(resultSet
0884:                                .getInt("AttachFileSize"));
0885:                        bean.setAttachMimeType(resultSet
0886:                                .getString("AttachMimeType"));
0887:                        bean.setAttachDesc(resultSet.getString("AttachDesc"));
0888:                        bean.setAttachCreationIP(resultSet
0889:                                .getString("AttachCreationIP"));
0890:                        bean.setAttachCreationDate(resultSet
0891:                                .getTimestamp("AttachCreationDate"));
0892:                        bean.setAttachModifiedDate(resultSet
0893:                                .getTimestamp("AttachModifiedDate"));
0894:                        bean.setAttachDownloadCount(resultSet
0895:                                .getInt("AttachDownloadCount"));
0896:                        bean.setAttachOption(resultSet.getInt("AttachOption"));
0897:                        bean.setAttachStatus(resultSet.getInt("AttachStatus"));
0898:                        bean.setForumID(resultSet.getInt("ForumID"));
0899:                        retValue.add(bean);
0900:                    }
0901:                    return retValue;
0902:                } catch (SQLException sqle) {
0903:                    log.error("Sql Execution Error!", sqle);
0904:                    throw new DatabaseException(
0905:                            "Error executing SQL in AttachmentDAOImplJDBC.getAttachments_withSortSupport_limit_mysql.");
0906:                } finally {
0907:                    DBUtils.closeResultSet(resultSet);
0908:                    DBUtils.closeStatement(statement);
0909:                    DBUtils.closeConnection(connection);
0910:                }
0911:            }
0912:
0913:            public Collection getAttachments_withSortSupport_limit_general(
0914:                    int offset, int rowsToReturn, int category, int forum)
0915:                    throws IllegalArgumentException, DatabaseException {
0916:
0917:                Connection connection = null;
0918:                PreparedStatement statement = null;
0919:                ResultSet resultSet = null;
0920:                Collection retValue = new ArrayList();
0921:                StringBuffer sql = new StringBuffer(512);
0922:                sql
0923:                        .append("SELECT AttachID, attach.PostID, attach.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus, post.ForumID");
0924:                sql.append(" FROM " + TABLE_NAME + " attach, "
0925:                        + PostDAO.TABLE_NAME + " post");
0926:                if (category > -1) {
0927:                    sql.append(" , " + ForumDAO.TABLE_NAME + " forum");
0928:                }
0929:                sql.append(" WHERE attach.PostID = post.PostID");
0930:                if ((category > -1) && (forum > -1)) {
0931:                    throw new AssertionError(
0932:                            "Assertion in AttachmentDAOImplJDBC.getAttachments_withSortSupport_limit_mysql()");
0933:                }
0934:                if (category > -1) {
0935:                    sql
0936:                            .append(" AND (forum.ForumID = post.ForumID AND forum.CategoryID = ?) ");
0937:                } else if (forum > -1) {
0938:                    sql.append(" AND post.ForumID = ? ");
0939:                } else {
0940:                    // do nothing, because we will get all attachment
0941:                }
0942:                //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
0943:                try {
0944:                    connection = DBUtils.getConnection();
0945:                    statement = connection.prepareStatement(sql.toString(),
0946:                            ResultSet.TYPE_SCROLL_INSENSITIVE,
0947:                            ResultSet.CONCUR_READ_ONLY);
0948:                    int i = 0;
0949:                    if (category > -1) {
0950:                        statement.setInt(++i, category);
0951:                    } else if (forum > -1) {
0952:                        statement.setInt(++i, forum);
0953:                    } else {
0954:                        // do nothing, because we will get all attachment
0955:                    }
0956:                    statement.setMaxRows(offset + rowsToReturn);
0957:                    try {
0958:                        statement.setFetchSize(Math.min(rowsToReturn,
0959:                                DBUtils.MAX_FETCH_SIZE));
0960:                    } catch (SQLException sqle) {
0961:                        //do nothing, postgreSQL does not support this method
0962:                    }
0963:                    resultSet = statement.executeQuery();
0964:                    boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
0965:                    while (loop) {
0966:                        AttachmentBean bean = new AttachmentBean();
0967:                        bean.setAttachID(resultSet.getInt("AttachID"));
0968:                        bean.setPostID(resultSet.getInt("PostID"));
0969:                        bean.setMemberID(resultSet.getInt("MemberID"));
0970:                        bean.setAttachFilename(resultSet
0971:                                .getString("AttachFilename"));
0972:                        bean.setAttachFileSize(resultSet
0973:                                .getInt("AttachFileSize"));
0974:                        bean.setAttachMimeType(resultSet
0975:                                .getString("AttachMimeType"));
0976:                        bean.setAttachDesc(resultSet.getString("AttachDesc"));
0977:                        bean.setAttachCreationIP(resultSet
0978:                                .getString("AttachCreationIP"));
0979:                        bean.setAttachCreationDate(resultSet
0980:                                .getTimestamp("AttachCreationDate"));
0981:                        bean.setAttachModifiedDate(resultSet
0982:                                .getTimestamp("AttachModifiedDate"));
0983:                        bean.setAttachDownloadCount(resultSet
0984:                                .getInt("AttachDownloadCount"));
0985:                        bean.setAttachOption(resultSet.getInt("AttachOption"));
0986:                        bean.setAttachStatus(resultSet.getInt("AttachStatus"));
0987:                        bean.setForumID(resultSet.getInt("ForumID"));
0988:                        retValue.add(bean);
0989:                        if (retValue.size() == rowsToReturn)
0990:                            break;// Fix the Sybase bug
0991:                        loop = resultSet.next();
0992:                    }//while
0993:                    return retValue;
0994:                } catch (SQLException sqle) {
0995:                    log.error("Sql Execution Error!", sqle);
0996:                    throw new DatabaseException(
0997:                            "Error executing SQL in AttachmentDAOImplJDBC.getAttachments_withSortSupport_limit_mysql.");
0998:                } finally {
0999:                    DBUtils.closeResultSet(resultSet);
1000:                    DBUtils.resetStatement(statement);
1001:                    DBUtils.closeStatement(statement);
1002:                    DBUtils.closeConnection(connection);
1003:                }
1004:            }
1005:
1006:            public int getMaxAttachmentID() throws DatabaseException {
1007:
1008:                Connection connection = null;
1009:                PreparedStatement statement = null;
1010:                ResultSet resultSet = null;
1011:                StringBuffer sql = new StringBuffer(512);
1012:                sql.append("SELECT MAX(AttachID)");
1013:                sql.append(" FROM " + TABLE_NAME);
1014:                try {
1015:                    connection = DBUtils.getConnection();
1016:                    statement = connection.prepareStatement(sql.toString());
1017:                    resultSet = statement.executeQuery();
1018:                    AssertionUtil
1019:                            .doAssert(resultSet.next(),
1020:                                    "Assertion in AttachmentDAOImplJDBC.getMaxAttachmentID.");
1021:                    return resultSet.getInt(1);
1022:                } catch (SQLException sqle) {
1023:                    log.error("Sql Execution Error!", sqle);
1024:                    throw new DatabaseException(
1025:                            "Error executing SQL in AttachmentDAOImplJDBC.getMaxAttachmentID.");
1026:                } finally {
1027:                    DBUtils.closeResultSet(resultSet);
1028:                    DBUtils.closeStatement(statement);
1029:                    DBUtils.closeConnection(connection);
1030:                }
1031:            }
1032:
1033:            public Collection getAttachments_fromIDRange(int fromID, int toID)
1034:                    throws IllegalArgumentException, DatabaseException {
1035:
1036:                if (fromID < 0)
1037:                    throw new IllegalArgumentException(
1038:                            "The fromID < 0 is not allowed.");
1039:                if (toID < fromID)
1040:                    throw new IllegalArgumentException(
1041:                            "toID < fromID is not allowed.");
1042:
1043:                Connection connection = null;
1044:                PreparedStatement statement = null;
1045:                ResultSet resultSet = null;
1046:                Collection retValue = new ArrayList();
1047:                StringBuffer sql = new StringBuffer(512);
1048:                sql
1049:                        .append("SELECT AttachID, attach.PostID, attach.MemberID, AttachFilename, AttachFileSize, AttachMimeType, AttachDesc, AttachCreationIP, AttachCreationDate, AttachModifiedDate, AttachDownloadCount, AttachOption, AttachStatus, post.ForumID");
1050:                sql.append(" FROM " + TABLE_NAME + " attach, "
1051:                        + PostDAO.TABLE_NAME + " post");
1052:                sql.append(" WHERE attach.PostID =  post.PostID");
1053:                sql.append(" AND (AttachID >= ?) AND (AttachID <= ?)");
1054:                sql.append(" ORDER BY AttachID ASC ");
1055:                //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
1056:                try {
1057:                    connection = DBUtils.getConnection();
1058:                    statement = connection.prepareStatement(sql.toString());
1059:                    statement.setInt(1, fromID);
1060:                    statement.setInt(2, toID);
1061:                    resultSet = statement.executeQuery();
1062:                    while (resultSet.next()) {
1063:                        AttachmentBean bean = new AttachmentBean();
1064:                        bean.setAttachID(resultSet.getInt("AttachID"));
1065:                        bean.setPostID(resultSet.getInt("PostID"));
1066:                        bean.setMemberID(resultSet.getInt("MemberID"));
1067:                        bean.setAttachFilename(resultSet
1068:                                .getString("AttachFilename"));
1069:                        bean.setAttachFileSize(resultSet
1070:                                .getInt("AttachFileSize"));
1071:                        bean.setAttachMimeType(resultSet
1072:                                .getString("AttachMimeType"));
1073:                        bean.setAttachDesc(resultSet.getString("AttachDesc"));
1074:                        bean.setAttachCreationIP(resultSet
1075:                                .getString("AttachCreationIP"));
1076:                        bean.setAttachCreationDate(resultSet
1077:                                .getTimestamp("AttachCreationDate"));
1078:                        bean.setAttachModifiedDate(resultSet
1079:                                .getTimestamp("AttachModifiedDate"));
1080:                        bean.setAttachDownloadCount(resultSet
1081:                                .getInt("AttachDownloadCount"));
1082:                        bean.setAttachOption(resultSet.getInt("AttachOption"));
1083:                        bean.setAttachStatus(resultSet.getInt("AttachStatus"));
1084:                        bean.setForumID(resultSet.getInt("ForumID"));
1085:                        retValue.add(bean);
1086:                    }
1087:                    return retValue;
1088:                } catch (SQLException sqle) {
1089:                    log.error("Sql Execution Error!", sqle);
1090:                    throw new DatabaseException(
1091:                            "Error executing SQL in AttachmentDAOImplJDBC.getAttachments_fromIDRange.");
1092:                } finally {
1093:                    DBUtils.closeResultSet(resultSet);
1094:                    DBUtils.closeStatement(statement);
1095:                    DBUtils.closeConnection(connection);
1096:                }
1097:            }
1098:
1099:        }// end of class AttachmentDAOImplJDBC
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.