genericcatalogdao.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 java.net.*;
import java.io.*;
import javax.naming.*;
import javax.sql.*;
import javax.xml.parsers.*;
import org.xml.sax.*;
import org.xml.sax.helpers.*;

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.exceptions.CatalogDAOSysException;

import com.sun.j2ee.blueprints.util.tracer.Debug;


/**
 * This class implements a generic CatalogDAO which loads the SQL statement
 * descriptions fron an XML configuration file.
 * 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 GenericCatalogDAO implements CatalogDAO {
  private static final boolean TRACE = false;
  private static final String XML_DAO_CONFIGURATION = "DAOConfiguration";
  private static final String XML_DAO_STATEMENTS = "DAOStatements";
  private static final String XML_DATABASE = "database";
  private static final String XML_SQL_STATEMENT = "SQLStatement";
  private static final String XML_METHOD = "method";
  private static final String XML_SQL_FRAGMENT = "SQLFragment";
  private static final String XML_PARAMETER_NB = "parameterNb";
  private static final String XML_OCCURRENCE = "occurrence";
  private static final String XML_ONCE = "ONCE";
  private static final String XML_VARIABLE = "VARIABLE";
  private static final String XML_GET_CATEGORY = "GET_CATEGORY";
  private static final String XML_GET_CATEGORIES = "GET_CATEGORIES";
  private static final String XML_GET_PRODUCT = "GET_PRODUCT";
  private static final String XML_GET_PRODUCTS = "GET_PRODUCTS";
  private static final String XML_GET_ITEM = "GET_ITEM";
  private static final String XML_GET_ITEMS = "GET_ITEMS";
  private static final String XML_SEARCH_ITEMS = "SEARCH_ITEMS";
  private Map sqlStatements = new HashMap();


  public GenericCatalogDAO() throws CatalogDAOSysException {
    try {
      InitialContext context = new InitialContext();
      URL daoSQLURL = (URL) context.lookup(JNDINames.CATALOG_DAO_SQL_URL);
      String database = (String) context.lookup(JNDINames.CATALOG_DAO_DATABASE);
      SAXParserFactory parserFactory = SAXParserFactory.newInstance();
      parserFactory.setValidating(true);
      parserFactory.setNamespaceAware(true);
      XMLReader reader = parserFactory.newSAXParser().getXMLReader();
      loadSQLStatements(parserFactory.newSAXParser(), database,
                        new InputSource(daoSQLURL.openStream()));
      if (TRACE) {
        System.err.println("DAO SQL statements used: " + sqlStatements);
      }
    } catch (Exception exception) {
      System.err.println(exception);
      throw new CatalogDAOSysException(exception.getMessage());
    }
    return;
  }

  private GenericCatalogDAO(String daoSQLFileName, String database) throws CatalogDAOSysException {
    try {
      SAXParserFactory parserFactory = SAXParserFactory.newInstance();
      parserFactory.setValidating(true);
      parserFactory.setNamespaceAware(true);
      XMLReader reader = parserFactory.newSAXParser().getXMLReader();
      loadSQLStatements(parserFactory.newSAXParser(), database, new InputSource(daoSQLFileName));
      if (TRACE) {
        System.err.println("DAO SQL statements used: " + sqlStatements);
      }
    } catch (Exception exception) {
      exception.printStackTrace(System.err);
      System.err.println(exception);
      throw new CatalogDAOSysException(exception.getMessage());
    }
    return;
  }

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

  protected static void closeAll(Connection connection, PreparedStatement statement, ResultSet resultSet) {
    if (resultSet != null) {
      try {
        resultSet.close();
      } catch (Exception exception) {}
    }
    if (statement != null) {
      try {
        statement.close();
      } catch (Exception exception) {}
    }
    if (connection != null) {
      try {
        connection.close();
      } catch (Exception exception) {}
    }
    return;
  }

  // Business methods

  public Category getCategory(String categoryID, Locale locale) throws CatalogDAOSysException {
    Connection connection = null;
    ResultSet resultSet = null;
    PreparedStatement statement = null;
    try {
      connection = getDataSource().getConnection();
      String[] parameterValues = new String[] { locale.toString(), categoryID };
      if (TRACE) {
        printSQLStatement(sqlStatements, XML_GET_CATEGORY, parameterValues);
      }
      statement = buildSQLStatement(connection, sqlStatements, XML_GET_CATEGORY, parameterValues);
      resultSet = statement.executeQuery();
      if (resultSet.first()) {
        return new Category(categoryID, resultSet.getString(1), resultSet.getString(2));
      }
      return null;
    } catch (SQLException exception) {
      throw new CatalogDAOSysException("SQLException: " + exception.getMessage());
    } finally {
      closeAll(connection, statement, resultSet);
    }
  }

  public Page getCategories(int start, int count, Locale locale) throws CatalogDAOSysException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {
      connection = getDataSource().getConnection();
      String[] parameterValues = new String[] { locale.toString() };
      if (TRACE) {
        printSQLStatement(sqlStatements, XML_GET_CATEGORIES, parameterValues);
      }
      statement = buildSQLStatement(connection, sqlStatements, XML_GET_CATEGORIES, parameterValues);
      resultSet = statement.executeQuery();
      if (start >= 0 && resultSet.absolute(start + 1)) {
        boolean hasNext = false;
        List categories = new ArrayList();
        do {
          categories.add(new Category(resultSet.getString(1).trim(),
                                      resultSet.getString(2),
                                      resultSet.getString(3)));
        } while ((hasNext = resultSet.next()) && (--count > 0));
        return new Page(categories, start, hasNext);
      }
      return Page.EMPTY_PAGE;
    } catch (SQLException exception) {
      throw new CatalogDAOSysException("SQLException: " + exception.getMessage());
    } finally {
      closeAll(connection, statement, resultSet);
    }
  }

  public Product getProduct(String productID, Locale locale) throws CatalogDAOSysException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {
      connection = getDataSource().getConnection();
      String[] parameterValues = new String[] { locale.toString(), productID };
      if (TRACE) {
        printSQLStatement(sqlStatements, XML_GET_PRODUCT, parameterValues);
      }
      statement = buildSQLStatement(connection, sqlStatements, XML_GET_PRODUCT, parameterValues);
      resultSet = statement.executeQuery();
      if (resultSet.first()) {
        return new Product(productID, resultSet.getString(1), resultSet.getString(2));
      }
      return null;
    }
    catch (SQLException exception) {
      throw new CatalogDAOSysException("SQLException: " + exception.getMessage());
    } finally {
      closeAll(connection, statement, resultSet);
    }
  }

  public Page getProducts(String categoryID, int start, int count, Locale locale) throws CatalogDAOSysException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {
      connection = getDataSource().getConnection();
      String[] parameterValues = new String[] { locale.toString(), categoryID };
      if (TRACE) {
        printSQLStatement(sqlStatements, XML_GET_PRODUCTS, parameterValues);
      }
      statement = buildSQLStatement(connection, sqlStatements, XML_GET_PRODUCTS, parameterValues);
      resultSet = statement.executeQuery();
      if (start >= 0 && resultSet.absolute(start + 1)) {
        boolean hasNext = false;
        List products = new ArrayList();
        do {
          products.add(new Product(resultSet.getString(1).trim(),
                                   resultSet.getString(2),
                                   resultSet.getString(3)));
        } while ((hasNext = resultSet.next()) && (--count > 0));
        return new Page(products, start, hasNext);
      }
      return Page.EMPTY_PAGE;
    } catch (SQLException exception) {
      throw new CatalogDAOSysException("SQLException: " + exception.getMessage());
    } finally {
      closeAll(connection, statement, resultSet);
    }
  }

  public Item getItem(String itemID, Locale locale) throws CatalogDAOSysException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {
      connection = getDataSource().getConnection();
      String[] parameterValues = new String[] { locale.toString(), itemID };
      if (TRACE) {
        printSQLStatement(sqlStatements, XML_GET_ITEM, parameterValues);
      }
      statement = buildSQLStatement(connection, sqlStatements, XML_GET_ITEM, parameterValues);
      resultSet = statement.executeQuery();
      if (resultSet.first()) {
        int i = 1;
        return new Item(resultSet.getString(i++).trim(),
                        resultSet.getString(i++).trim(),
                        resultSet.getString(i++),
                        itemID,
                        resultSet.getString(i++).trim(),
                        resultSet.getString(i++),
                        resultSet.getString(i++),
                        resultSet.getString(i++),
                        resultSet.getString(i++),
                        resultSet.getString(i++),
                        resultSet.getString(i++),
                        resultSet.getDouble(i++),
                        resultSet.getDouble(i++));
      }
      return null;
    } catch (SQLException exception) {
      throw new CatalogDAOSysException("SQLException: " + exception.getMessage());
    } finally {
      closeAll(connection, statement, resultSet);
    }
  }

  public Page getItems(String productID, int start, int count, Locale locale) throws CatalogDAOSysException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {
      connection = getDataSource().getConnection();
      String[] parameterValues = new String[] { locale.toString(), productID };
      if (TRACE) {
        printSQLStatement(sqlStatements, XML_GET_ITEMS, parameterValues);
      }
      statement = buildSQLStatement(connection, sqlStatements, XML_GET_ITEMS, parameterValues);
      resultSet = statement.executeQuery();
      if (start >= 0 && resultSet.absolute(start + 1)) {
        boolean hasNext = false;
        List items = new ArrayList();
        do {