alvinalexander.com | career | drupal | java | mac | mysql | perl | scala | uml | unix  

What this is

This file is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.

Other links

The source code

/*
 * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/CategoryDAOImplJDBC.java,v 1.7 2005/01/18 11:52:19 minhnn Exp $
 * $Author: minhnn $
 * $Revision: 1.7 $
 * $Date: 2005/01/18 11:52:19 $
 *
 * ====================================================================
 *
 * Copyright (C) 2002-2005 by MyVietnam.net
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or any later version.
 *
 * All copyright notices regarding mvnForum MUST remain intact
 * in the scripts and in the outputted HTML.
 * The "powered by" text/logo with a link back to
 * http://www.mvnForum.com and http://www.MyVietnam.net in the
 * footer of the pages MUST remain visible when the pages
 * are viewed on the internet or intranet.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 * Support can be obtained from support forums at:
 * http://www.mvnForum.com/mvnforum/index
 *
 * Correspondence and Marketing Questions can be sent to:
 * info@MyVietnam.net
 *
 * @author: Minh Nguyen  minhnn@MyVietnam.net
 * @author: Mai  Nguyen  mai.nh@MyVietnam.net
 */
package com.mvnforum.db.jdbc;

import java.io.StringReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;

import com.mvnforum.db.CategoryBean;
import com.mvnforum.db.CategoryDAO;
import net.myvietnam.mvncore.db.DBUtils;
import net.myvietnam.mvncore.exception.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class CategoryDAOImplJDBC implements CategoryDAO {

    private static Log log = LogFactory.getLog(CategoryDAOImplJDBC.class);

    // this variable will support caching if cache for this class is needed
    private static boolean m_dirty = true;

    public CategoryDAOImplJDBC() {
    }

    public static boolean isDirty() {
        return m_dirty;
    }

    public static void setDirty(boolean dirty) {
        m_dirty = dirty;
    }

    public void findByPrimaryKey(int categoryID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CategoryID");
        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 primary key (" + categoryID + ") in table 'Category'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.findByPrimaryKey.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void findByAlternateKey_CategoryName(String categoryName)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CategoryName");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CategoryName = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, categoryName);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the alternate key [CategoryName] (" + categoryName + ") in table 'Category'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.findByAlternateKey_CategoryName.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate,
     *                   CategoryOrder, CategoryOption, CategoryStatus
     * Excluded columns: CategoryID
     */
    public void create(int parentCategoryID, String categoryName, String categoryDesc,
                       Timestamp categoryCreationDate, Timestamp categoryModifiedDate, int categoryOrder,
                       int categoryOption, int categoryStatus)
        throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {

        // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
        // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
        try {
            //Check if alternate key already exists
            findByAlternateKey_CategoryName(categoryName);
            //If so, then we have to throw an exception
            throw new DuplicateKeyException("Alternate key already exists. Cannot create new Category with the same [CategoryName] (" + categoryName + ").");
        } catch(ObjectNotFoundException e) {
            //Otherwise we can go ahead
        }

        try {
            // @todo: modify the parameter list as needed
            // You may have to regenerate this method if the needed columns dont have attribute 'include'
            if (parentCategoryID != 0) {
                findByPrimaryKey(parentCategoryID);
            }
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Category' does not exist. Cannot create new Category.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("INSERT INTO " + TABLE_NAME + " (ParentCategoryID, CategoryName, CategoryDesc, CategoryCreationDate, CategoryModifiedDate, CategoryOrder, CategoryOption, CategoryStatus)");
        sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, parentCategoryID);
            statement.setString(2, categoryName);
            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(3, new StringReader(categoryDesc), categoryDesc.length());
            } else {
                statement.setString(3, categoryDesc);
            }
            statement.setTimestamp(4, categoryCreationDate);
            statement.setTimestamp(5, categoryModifiedDate);
            statement.setInt(6, categoryOrder);
            statement.setInt(7, categoryOption);
            statement.setInt(8, categoryStatus);

            if (statement.executeUpdate() != 1) {
                throw new CreateException("Error adding a row into table 'Category'.");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CategoryDAOImplJDBC.create.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void delete(int categoryID)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("DELETE FROM " + TABLE_NAME);
        sql.append(" WHERE CategoryID = ?");

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, categoryID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot delete a row in 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.delete.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * 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
... this post is sponsored by my books ...

#1 New Release!

FP Best Seller

 

new blog posts

 

Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.

A percentage of advertising revenue from
pages under the /java/jwarehouse URI on this website is
paid back to open source projects.