categorydaoimpljdbc.java

解觖java技术中后台无法上传数给的情况

        }
    }

    /*
     * Included columns: CategoryName, CategoryDesc, CategoryModifiedDate, CategoryOrder, CategoryOption,
     *                   CategoryStatus
     * Excluded columns: CategoryID, ParentCategoryID, CategoryCreationDate
     */
    public void update(int categoryID, // primary key
                       String categoryName, String categoryDesc, Timestamp categoryModifiedDate,
                       int categoryOrder, int categoryOption, int categoryStatus)
        throws ObjectNotFoundException, DatabaseException, DuplicateKeyException {

        CategoryBean bean = getCategory(categoryID); // @todo: comment or delete this line if no alternate key are included

        if ( !categoryName.equals(bean.getCategoryName()) ) {
            // Category tries to change its alternate key <CategoryName>, so we must check if it already exist
            try {
                findByAlternateKey_CategoryName(categoryName);
                throw new DuplicateKeyException("Alternate key [CategoryName] (" + categoryName + ")already exists. Cannot update Category.");
            } catch(ObjectNotFoundException e) {
                //Otherwise we can go ahead
            }
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("UPDATE " + TABLE_NAME + " SET CategoryName = ?, CategoryDesc = ?, CategoryModifiedDate = ?, CategoryOrder = ?, CategoryOption = ?, CategoryStatus = ?");
        sql.append(" WHERE CategoryID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setString(1, categoryName);
            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(2, new StringReader(categoryDesc), categoryDesc.length());
            } else {
                statement.setString(2, categoryDesc);
            }
            statement.setTimestamp(3, categoryModifiedDate);
            statement.setInt(4, categoryOrder);
            statement.setInt(5, categoryOption);
            statement.setInt(6, categoryStatus);

            // primary key column(s)
            statement.setInt(7, categoryID);

            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update table Category where primary key = (" + categoryID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.update.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate,
     *                   CategoryOrder, CategoryOption, CategoryStatus
     * Excluded columns: CategoryID
     */
    public CategoryBean getCategory(int categoryID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CategoryID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, categoryID);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Category where primary key = (" + categoryID + ").");
            }

            CategoryBean bean = new CategoryBean();
            // @todo: uncomment the following line(s) as needed
            bean.setCategoryID(categoryID);
            bean.setParentCategoryID(resultSet.getInt("ParentCategoryID"));
            bean.setCategoryName(resultSet.getString("CategoryName"));
            bean.setCategoryDesc(resultSet.getString("CategoryDesc"));
            bean.setCategoryCreationDate(resultSet.getTimestamp("CategoryCreationDate"));
            bean.setCategoryModifiedDate(resultSet.getTimestamp("CategoryModifiedDate"));
            bean.setCategoryOrder(resultSet.getInt("CategoryOrder"));
            bean.setCategoryOption(resultSet.getInt("CategoryOption"));
            bean.setCategoryStatus(resultSet.getInt("CategoryStatus"));
            return bean;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.getCategory(pk).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: CategoryID, ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate,
     *                   CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus
     * Excluded columns:
     */
    public Collection getCategories()
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CategoryID, ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus");
        sql.append(" FROM " + TABLE_NAME);
        //sql.append(" WHERE "); // @todo: uncomment as needed
        sql.append(" ORDER BY CategoryOrder ASC "); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                CategoryBean bean = new CategoryBean();
                bean.setCategoryID(resultSet.getInt("CategoryID"));
                bean.setParentCategoryID(resultSet.getInt("ParentCategoryID"));
                bean.setCategoryName(resultSet.getString("CategoryName"));
                bean.setCategoryDesc(resultSet.getString("CategoryDesc"));
                bean.setCategoryCreationDate(resultSet.getTimestamp("CategoryCreationDate"));
                bean.setCategoryModifiedDate(resultSet.getTimestamp("CategoryModifiedDate"));
                bean.setCategoryOrder(resultSet.getInt("CategoryOrder"));
                bean.setCategoryOption(resultSet.getInt("CategoryOption"));
                bean.setCategoryStatus(resultSet.getInt("CategoryStatus"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.getCategories.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /************************************************
     * Customized methods come below
     ************************************************/

    /**
     * This method should be call only when we can make sure that memberID is in database
     */
    public void decreaseCategoryOrder(int categoryID, Timestamp categoryModifiedDate)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET CategoryOrder = CategoryOrder - 1, CategoryModifiedDate = ? WHERE CategoryID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setTimestamp(1, categoryModifiedDate);
            statement.setInt(2, categoryID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the CategoryOrder in table Category. Please contact Web site Administrator.");
            }
            //@todo: coi lai cho nay
            // ATTENTION !!!
            setDirty(true);
        } catch (SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.decreaseCategoryOrder.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /**
     * This method should be call only when we can make sure that memberID is in database
     */
    public void increaseCategoryOrder(int categoryID, Timestamp categoryModifiedDate)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "UPDATE " + TABLE_NAME + " SET CategoryOrder = CategoryOrder + 1, CategoryModifiedDate = ? WHERE CategoryID = ?";
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setTimestamp(1, categoryModifiedDate);
            statement.setInt(2, categoryID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update the CategoryOrder in table Category. Please contact Web site Administrator.");
            }
            //@todo: coi lai cho nay
            // ATTENTION !!!
            setDirty(true);
        } catch (SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.increaseCategoryOrder.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

}// end of class CategoryDAOImplJDBC