catalogdaojdbc.java

用JBuilder+Jboos+mysql实现的EJB项目

package com.jdon.estore.catalog.dao;

import com.jdon.estore.model.Category;
import com.jdon.controller.model.PageIterator;
import com.jdon.estore.model.Product;
import com.jdon.estore.model.Item;

import java.util.*;
import javax.sql.*;
import java.sql.*;

import com.jdon.servicelocator.ejb.ServiceLocator;
import com.jdon.servicelocator.ServiceLocatorException;

import com.jdon.controller.model.PageIterator;
import com.jdon.util.DbUtil;
import com.jdon.estore.JNDINames;

import org.apache.log4j.Logger;

public class CatalogDAOJDBC implements CatalogDAO {
  private final static Logger logger = Logger.getLogger(CatalogDAOJDBC.class);
  private DataSource dataSource = null;

  private final static String GET_CATEGORY =
      "select a.name, a.description from category_details a where catId = ?";

  private final static String GET_CATEGORIES =
      "select category.catId from category";

  private final static String GET_CATEGORIES_ALLCOUNT =
      "select count(1) from category";


  protected DataSource getDataSource() throws Exception {
    try {
      if (dataSource == null) {
        ServiceLocator sl = new ServiceLocator();
        dataSource = (DataSource) sl.getDataSource(JNDINames.CATALOG_DATASOURCE);
      }
      return dataSource;
    } catch (ServiceLocatorException slx) {
      logger.error(slx);
      throw new Exception(slx);
    }
  }

  public Category getCategory(String catId) throws Exception {
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Category ret = null;
    try {
      c = getDataSource().getConnection();
      ps = c.prepareStatement(GET_CATEGORY,
                              ResultSet.TYPE_SCROLL_INSENSITIVE,
                              ResultSet.CONCUR_READ_ONLY);
      ps.setString(1, catId);
      rs = ps.executeQuery();
      if (rs.first()) {
        ret = new Category(catId, rs.getString(1), rs.getString(2));
      }
    } catch (SQLException se) {
      throw new Exception("SQLException: " + se.getMessage());
    } finally {
      if (rs != null)
        rs.close();
      if (ps != null)
        ps.close();
      if (c != null)
        c.close();
    }
    return ret;
  }

  public int getCategoryAllCount() throws Exception {
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    int ret = 0;
    try {
      c = getDataSource().getConnection();
      ps = c.prepareStatement(GET_CATEGORIES_ALLCOUNT,
                              ResultSet.TYPE_SCROLL_INSENSITIVE,
                              ResultSet.CONCUR_READ_ONLY);
      rs = ps.executeQuery();
      if (rs.first()) {
        ret = rs.getInt(1);
      }
    } catch (SQLException se) {
      throw new Exception("SQLException: " + se.getMessage());
    } finally {
      if (rs != null)
        rs.close();
      if (ps != null)
        ps.close();
      if (c != null)
        c.close();
    }
    return ret;

  }

  public PageIterator getCategories(int start, int count) throws Exception {
    logger.debug("--> getCategores from start=" + start + " size=" + count);
    Connection c = null;
    Statement stmt = null;
    ResultSet rs = null;
    PageIterator pageIterator = null;
    boolean hasNext = false;
    try {
      c = getDataSource().getConnection();
      stmt = c.createStatement();

      DbUtil.testConnection(c);
      if (DbUtil.supportsMaxRows)
        stmt.setMaxRows(count + start + 1);
      rs = stmt.executeQuery(GET_CATEGORIES);
      if (DbUtil.supportsFetchSize)
        rs.setFetchSize(count);
      if (start >= 0 && rs.absolute(start + 1)) {
        List items = new ArrayList(count);
        do {
          items.add(rs.getString(1));
        }
        while ( (hasNext = rs.next()) && (--count > 0));
        String[] itemsA = (String[]) items.toArray(new String[0]);
        pageIterator = new PageIterator(itemsA, start, hasNext);
      } else
        pageIterator = new PageIterator(PageIterator.EMPTY, start, hasNext);
      logger.debug("--> getCategores succefully ..");
    } catch (SQLException se) {
      logger.error(se);
      throw new Exception("SQLException: " + se.getMessage());
    } finally {
      if (rs != null)
        rs.close();
      if (stmt != null)
        stmt.close();
      if (c != null)
        c.close();
    }
    return pageIterator;
  }

  private final static String GET_PRODUCT =
      "select a.name, a.description from product a where productId = ?";


  private final static String GET_PRODUCTS =
      "select a.productId from product a join category_product b " +
      "on a.productId = b.productId where b.catId = ?";

  private final static String GET_ITEMS =
        "select a.itemId, a.name, a.unitcost, a.listprice from "+
        "item a join product_item b on a.itemId = b.itemId " +
        "where b.productId = ?  ";


  private final static String GET_PRODUCTS_ALLCOUNT =
      "select count(1) from product a join category_product b " +
      "on a.productId = b.productId where b.catId = ?";


  public Product getProduct(String productId) throws Exception {
    logger.debug(" by JDBC get product id=" + productId);
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Product ret = null;
    try {
      c = getDataSource().getConnection();
      ps = c.prepareStatement(GET_PRODUCT,
                              ResultSet.TYPE_SCROLL_INSENSITIVE,
                              ResultSet.CONCUR_READ_ONLY);
      ps.setString(1, productId);
      rs = ps.executeQuery();
      if (rs.first()) {
        ret = new Product(productId, rs.getString(1), rs.getString(2));
      }

      rs.close();
      ps.close();

      logger.debug(" get the items of product " );
      ps = c.prepareStatement(GET_ITEMS,
                              ResultSet.TYPE_SCROLL_INSENSITIVE,
                              ResultSet.CONCUR_READ_ONLY);
      ps.setString(1, productId);
      rs = ps.executeQuery();
      while(rs.next()){
         Item item = new Item(rs.getString(1),
                              rs.getString(2), rs.getFloat(3), rs.getFloat(4));
         ret.getItems().add(item);
      }
    } catch (SQLException se) {
      throw new Exception("SQLException: " + se.getMessage());
    } finally {
      if (rs != null)        rs.close();
      if (ps != null)        ps.close();
      if (c != null)         c.close();
    }
    return ret;
  }

  public int getProductAllCount(String catId) throws Exception {
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    int ret = 0;
    try {
      c = getDataSource().getConnection();
      ps = c.prepareStatement(GET_PRODUCTS_ALLCOUNT,
                              ResultSet.TYPE_SCROLL_INSENSITIVE,
                              ResultSet.CONCUR_READ_ONLY);
      ps.setString(1, catId);
      rs = ps.executeQuery();
      if (rs.first()) {
        ret = rs.getInt(1);
      }
    } catch (SQLException se) {
      throw new Exception("SQLException: " + se.getMessage());
    } finally {
      if (rs != null)    rs.close();
      if (ps != null)    ps.close();
      if (c != null)     c.close();
    }
    return ret;

  }


  public PageIterator getProducts(String catId, int start, int count) throws
      Exception {
    logger.debug("--> getProducts from start=" + start + " size=" + count);
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    PageIterator pageIterator = null;
    boolean hasNext = false;
    try {
      c = getDataSource().getConnection();

      DbUtil.testConnection(c);
      ps = c.prepareStatement(GET_PRODUCTS,
                               ResultSet.TYPE_SCROLL_INSENSITIVE,
                                 ResultSet.CONCUR_READ_ONLY);

      ps.setString(1, catId);
      rs = ps.executeQuery();
      if (DbUtil.supportsFetchSize)
        rs.setFetchSize(count);
      if (start >= 0 && rs.absolute(start + 1)) {
        List items = new ArrayList(count);
        do {
          items.add(rs.getString(1));
        }
        while ( (hasNext = rs.next()) && (--count > 0));
        String[] itemsA = (String[]) items.toArray(new String[0]);
        pageIterator = new PageIterator(itemsA, start, hasNext);
      } else
        pageIterator = new PageIterator(PageIterator.EMPTY, start, hasNext);
      logger.debug("--> getProducts succefully ..");
    } catch (SQLException se) {
      logger.error(se);
      throw new Exception("SQLException: " + se.getMessage());
    } finally {
      if (rs != null)        rs.close();
      if (ps != null)      ps.close();
      if (c != null)         c.close();
    }
    return pageIterator;
  }

  private final static String GET_IMAGE =
        "select a.data from images a where imagePath = ?";

  public byte[] getImage(String Id) throws Exception{
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    byte[] ret = null;
    try {
      c = getDataSource().getConnection();
      ps = c.prepareStatement(GET_IMAGE,
                              ResultSet.TYPE_SCROLL_INSENSITIVE,
                              ResultSet.CONCUR_READ_ONLY);
      ps.setString(1, Id);
      rs = ps.executeQuery();
      if (rs.first()) {
        ret = rs.getBytes(1);
      }
    } catch (SQLException se) {
      throw new Exception("SQLException: " + se.getMessage());
    } finally {
      if (rs != null)
        rs.close();
      if (ps != null)
        ps.close();
      if (c != null)
        c.close();
    }
    return ret;
  }

  private final static String GET_ITEM =
      "select a.name, a.unitcost, a.listprice from item a where itemId = ?";

  public Item getItem(String itemId) throws Exception {
    logger.debug(" by JDBC get item id=" + itemId);
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Item ret = null;
    try {
      c = getDataSource().getConnection();
      ps = c.prepareStatement(GET_ITEM,
                              ResultSet.TYPE_SCROLL_INSENSITIVE,
                              ResultSet.CONCUR_READ_ONLY);
      ps.setString(1, itemId);
      rs = ps.executeQuery();
      if (rs.first()) {
        ret = new Item(itemId,
                       rs.getString(1), rs.getFloat(2), rs.getFloat(3));
      }

    } catch (SQLException se) {
      throw new Exception("SQLException: " + se.getMessage());
    } finally {
      if (rs != null)
        rs.close();
      if (ps != null)
        ps.close();
      if (c != null)
        c.close();
    }
    return ret;
  }

  public PageIterator getItems(String productId, int start, int size) throws
      Exception {
    /**@todo Implement this com.jdon.estore.catalog.dao.CatalogDAO method*/
    throw new java.lang.UnsupportedOperationException(
        "Method getItems() not yet implemented.");
  }

  public PageIterator searchItems(String query, int start, int size) throws
      Exception {
    /**@todo Implement this com.jdon.estore.catalog.dao.CatalogDAO method*/
    throw new java.lang.UnsupportedOperationException(
        "Method searchItems() not yet implemented.");
  }

}