cloudscapecatalogdao.java

sun公司的petstore,一个J2ee的示例应用程序.

/*
 * Copyright 2002 Sun Microsystems, Inc. All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 * - Redistributions of source code must retain the above copyright
 *   notice, this list of conditions and the following disclaimer.
 *
 * - Redistribution in binary form must reproduce the above copyright
 *   notice, this list of conditions and the following disclaimer in
 *   the documentation and/or other materials provided with the
 *   distribution.
 *
 * Neither the name of Sun Microsystems, Inc. or the names of
 * contributors may be used to endorse or promote products derived
 * from this software without specific prior written permission.
 *
 * This software is provided "AS IS," without a warranty of any
 * kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND
 * WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY
 * EXCLUDED. SUN AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES
 * SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR
 * DISTRIBUTING THE SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN
 * OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR
 * FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR
 * PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
 * LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE,
 * EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
 *
 * You acknowledge that Software is not designed, licensed or intended
 * for use in the design, construction, operation or maintenance of
 * any nuclear facility.
 */

package com.sun.j2ee.blueprints.catalog.dao;

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

// catalog imports

import com.sun.j2ee.blueprints.catalog.util.JNDINames;
import com.sun.j2ee.blueprints.catalog.model.Page;
import com.sun.j2ee.blueprints.catalog.model.Category;
import com.sun.j2ee.blueprints.catalog.model.Product;
import com.sun.j2ee.blueprints.catalog.model.Item;
import com.sun.j2ee.blueprints.catalog.util.DatabaseNames;
import com.sun.j2ee.blueprints.catalog.exceptions.CatalogDAOSysException;

// service locator imports
import com.sun.j2ee.blueprints.servicelocator.ejb.ServiceLocator;
import com.sun.j2ee.blueprints.servicelocator.ServiceLocatorException;

/**
 * This class implements CatalogDAO for cloudscape DB.
 * This class encapsulates all the SQL calls made by Catalog EJB.
 * This layer maps the relational data stored in the database to
 * the objects needed by Catalog EJB.
*/
public class CloudscapeCatalogDAO implements CatalogDAO {
  public static String GET_CATEGORY_STATEMENT
  = "select name, descn "
  + " from (category a join category_details b on a.catid=b.catid) "
  + " where locale = ? and a.catid = ?";
  public static String GET_CATEGORIES_STATEMENT
  = "select a.catid, name, descn "
  + " from (category a join category_details b on a.catid=b.catid) "
  + " where locale = ? order by name";
  public static String GET_PRODUCT_STATEMENT
  = "select name, descn "
  + " from (product a join product_details b on a.productid=b.productid) "
  + " where locale = ? and a.productid = ? ";
  public static String GET_PRODUCTS_STATEMENT
  = "select a.productid, name, descn "
  + " from (product a join product_details b on a.productid=b.productid) "
  + " where locale = ? and a.catid = ? order by name";
  public static String GET_ITEM_STATEMENT
  = "select catid, a.productid, name, b.image, b.descn, attr1, "
  + "  attr2, attr3, attr4, attr5, listprice, unitcost "
  + " from (((item a join item_details b on a.itemid=b.itemid)"
  + "   join product_details c on a.productid=c.productid)"
  + "   join product d on d.productid = c.productid and b.locale = c.locale) "
  + " where b.locale = ? and a.itemid = ?";
  public static String GET_ITEMS_STATEMENT
  = "select catid, name, a.itemid, b.image, b.descn, attr1, "
  + "  attr2, attr3, attr4, attr5, listprice, unitcost "
  + " from (((item a join item_details b on a.itemid=b.itemid)"
  + "  join product_details c on a.productid=c.productid)"
  + "  join product d on d.productid=c.productid and b.locale = c.locale)"
  + " where b.locale = ? and a.productid = ?";
  public static String[] SEARCH_ITEMS_STATEMENT_FRAGMENTS
  = { "select catid, a.productid, name, a.itemid, b.image, b.descn, attr1,"
      + "  attr2, attr3, attr4, attr5, listprice, unitcost"
      + " from (((item a join item_details b on a.itemid=b.itemid)"
      + "  join product_details c on a.productid=c.productid)"
      + "  join product d on d.productid=c.productid and b.locale = c.locale)"
      + " where b.locale = ? ",
      "    and ((lower(name) like ? ",
      "          or lower(name) like ? ",
      "    ) or (lower(catid) like ? ",
      "          or lower(catid) like ? ",
      "    ) or (lower(b.descn) like ? ",
      "          or lower(b.descn) like ? ",
      ") )"
  };

  // Helper methods

  protected static DataSource getDataSource() throws CatalogDAOSysException {
      try {
          ServiceLocator sl = new ServiceLocator();
          return (DataSource) sl.getDataSource(JNDINames.CATALOG_DATASOURCE);
      } catch (ServiceLocatorException slx) {
                throw new CatalogDAOSysException("NamingException while looking up DB context : " +
                                       slx.getMessage());
      }
  }

  // Business methods

  public Category getCategory(String categoryID, Locale l)
    throws CatalogDAOSysException {
      Connection c = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      Category ret = null;

      try {
        c = getDataSource().getConnection();
        ps = c.prepareStatement(GET_CATEGORY_STATEMENT,
                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                ResultSet.CONCUR_READ_ONLY);
        ps.setString(1, l.toString());
        ps.setString(2, categoryID);
        rs = ps.executeQuery();
        if (rs.first()) {
          ret = new Category(categoryID, rs.getString(1), rs.getString(2));
        }

        rs.close();
        ps.close();
        c.close();
        return ret;
      } catch (SQLException se) {
        throw new CatalogDAOSysException("SQLException: " + se.getMessage());
      }
  }

  public Page getCategories(int start, int count, Locale l)
    throws CatalogDAOSysException {
      Connection c = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      Page ret = null;

      try {
        c = getDataSource().getConnection();
        ps = c.prepareStatement(GET_CATEGORIES_STATEMENT,
                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                ResultSet.CONCUR_READ_ONLY);
        ps.setString(1, l.toString());
        rs = ps.executeQuery();
        if (start >= 0 && rs.absolute(start+1)) {
          boolean hasNext = false;
          List items = new ArrayList();
          do {
            items.add(new Category(rs.getString(1).trim(),
                                   rs.getString(2),
                                   rs.getString(3)));
          } while ((hasNext = rs.next()) && (--count > 0));
          ret = new Page(items, start, hasNext);
        } else {
          ret = Page.EMPTY_PAGE;
        }

        rs.close();
        ps.close();
        c.close();
        return ret;
      } catch (SQLException se) {
        se.printStackTrace(System.err);
        throw new CatalogDAOSysException("SQLException: " + se.getMessage());
      }
  }

  public Product getProduct(String productID, Locale l)
    throws CatalogDAOSysException {
      Connection c = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      Product ret = null;

      try {
        c = getDataSource().getConnection();
        ps = c.prepareStatement(GET_PRODUCT_STATEMENT,
                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                ResultSet.CONCUR_READ_ONLY);
        ps.setString(1, l.toString());
        ps.setString(2, productID);
        rs = ps.executeQuery();
        if (rs.first()) {
          ret = new Product(productID, rs.getString(1), rs.getString(2));
        }

        rs.close();
        ps.close();
        c.close();
        return ret;
      } catch (SQLException se) {
        throw new CatalogDAOSysException("SQLException: " + se.getMessage());
      }
  }

  public Page getProducts(String categoryID, int start, int count, Locale l)