cloudscapecatalogdao.java

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

    throws CatalogDAOSysException {
      Connection c = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      Page ret = null;

      try {
        c = getDataSource().getConnection();
        ps = c.prepareStatement(GET_PRODUCTS_STATEMENT,
                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                ResultSet.CONCUR_READ_ONLY);
        ps.setString(1, l.toString());
        ps.setString(2, categoryID);
        rs = ps.executeQuery();
        if (start >= 0 && rs.absolute(start+1)) {
          boolean hasNext = false;
          List items = new ArrayList();
          do {
            items.add(new Product(rs.getString(1).trim(),
                                  rs.getString(2).trim(),
                                  rs.getString(3).trim()));
          } 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) {
        throw new CatalogDAOSysException("SQLException: " + se.getMessage());
      }
  }

  public Item getItem(String itemID, Locale l)
    throws CatalogDAOSysException {
      Connection c = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      Item ret = null;

      try {
        c = getDataSource().getConnection();
        ps = c.prepareStatement(GET_ITEM_STATEMENT,
                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                ResultSet.CONCUR_READ_ONLY);
        ps.setString(1, l.toString());
        ps.setString(2, itemID);
        rs = ps.executeQuery();
        if (rs.first()) {
          int i = 1;
          ret = new Item(rs.getString(i++).trim(),
                         rs.getString(i++).trim(),
                         rs.getString(i++),
                         itemID,
                         rs.getString(i++).trim(),
                         rs.getString(i++),
                         rs.getString(i++),
                         rs.getString(i++),
                         rs.getString(i++),
                         rs.getString(i++),
                         rs.getString(i++),
                         rs.getDouble(i++),
                         rs.getDouble(i++));
        }

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

  public Page getItems(String productID, 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_ITEMS_STATEMENT,
                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                ResultSet.CONCUR_READ_ONLY);
        ps.setString(1, l.toString());
        ps.setString(2, productID);
        rs = ps.executeQuery();
        if (start >= 0 && rs.absolute(start+1)) {
          boolean hasNext = false;
          List items = new ArrayList();
          do {
            int i = 1;
            items.add(new Item(productID,
                               rs.getString(i++).trim(),
                               rs.getString(i++),
                               rs.getString(i++).trim(),
                               rs.getString(i++).trim(),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getDouble(i++),
                               rs.getDouble(i++)));
          } 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) {
        throw new CatalogDAOSysException("SQLException: " + se.getMessage());
      }
  }

  public Page searchItems(String searchQuery, int start, int count, Locale l)
    throws CatalogDAOSysException {
      Collection keywords = new HashSet();
      StringTokenizer st = new StringTokenizer(searchQuery);
      while (st.hasMoreTokens()) {
        keywords.add(st.nextToken());
      }
      if (keywords.isEmpty()) {
        return Page.EMPTY_PAGE;
      }

      Connection c = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      Page ret = null;

      try {
        c = getDataSource().getConnection();
        Iterator it;
        int i;
        StringBuffer sb = new StringBuffer();
        sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[0]);
        int keywordsSize = keywords.size();
        if (keywordsSize > 0) {
          sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[1]);
          for (i = 1; i != keywordsSize; i++) {
            sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[2]);
          }
          sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[3]);
          for (i = 1; i != keywordsSize; i++) {
            sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[4]);
          }
          sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[5]);
          for (i = 1; i != keywordsSize; i++) {
            sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[6]);
          }
          sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[7]);
        }
        //System.err.println(sb.toString());
        ps = c.prepareStatement(sb.toString(),
                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                ResultSet.CONCUR_READ_ONLY);
        ps.setString(1, l.toString());
        // The three loops are necessary because of the way the
        // query was constructed.
        i = 2;
        for (it = keywords.iterator(); it.hasNext(); i++) {
          String keyword = ((String) it.next()).toLowerCase();
          ps.setString(i, "%" + keyword + "%");
        }
        for (it = keywords.iterator(); it.hasNext(); i++) {
          String keyword = ((String) it.next()).toLowerCase();
          ps.setString(i, "%" + keyword + "%");
        }
        for (it = keywords.iterator(); it.hasNext(); i++) {
          String keyword = ((String) it.next()).toLowerCase();
          ps.setString(i, "%" + keyword + "%");
        }
        rs = ps.executeQuery();
        if (start >= 0 && rs.absolute(start+1)) {
          boolean hasNext = false;
          List items = new ArrayList();
          do {
            i = 1;
            items.add(new Item(rs.getString(i++).trim(),
                               rs.getString(i++).trim(),
                               rs.getString(i++),
                               rs.getString(i++).trim(),
                               rs.getString(i++).trim(),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getString(i++),
                               rs.getDouble(i++),
                               rs.getDouble(i++)));
          } 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) {
        throw new CatalogDAOSysException("SQLException: " + se.getMessage());
      }
  }
}