genericcatalogdao.java

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

          int i = 1;
          items.add(new Item(productID,
                             resultSet.getString(i++).trim(),
                             resultSet.getString(i++),
                             resultSet.getString(i++).trim(),
                             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++)));
        } while ((hasNext = resultSet.next()) && (--count > 0));
        return new Page(items, start, hasNext);
      }
      return Page.EMPTY_PAGE;
    } catch (SQLException exception) {
      throw new CatalogDAOSysException("SQLException: " + exception.getMessage());
    } finally {
      closeAll(connection, statement, resultSet);
    }
  }

  public Page searchItems(String searchQuery, int start, int count, Locale locale)
    throws CatalogDAOSysException {
      Collection keywordSet = new HashSet();
      StringTokenizer tokenizer = new StringTokenizer(searchQuery);
      while (tokenizer.hasMoreTokens()) {
        keywordSet.add(tokenizer.nextToken());
      }
      if (keywordSet.isEmpty()) {
        return Page.EMPTY_PAGE;
      }
      String[] keywords = (String[]) keywordSet.toArray(new String[0]);
      Connection connection = null;
      PreparedStatement statement = null;
      ResultSet resultSet = null;
      try {
        connection = getDataSource().getConnection();
        String[] parameterValues = new String[1 + (keywords.length * 3)];
        parameterValues[0] = locale.toString();
        for (int i = 0; i < keywords.length; i++) {
          parameterValues[(i * 3) + 1] = "%" + keywords[i] + "%";
          parameterValues[(i * 3) + 2] = "%" + keywords[i] + "%";
          parameterValues[(i * 3) + 3] = "%" + keywords[i] + "%";
        }
        if (TRACE) {
          printSQLStatement(sqlStatements, XML_SEARCH_ITEMS, parameterValues);
        }
        statement = buildSQLStatement(connection, sqlStatements, XML_SEARCH_ITEMS, parameterValues);
        resultSet = statement.executeQuery();
        if (start >= 0 && resultSet.absolute(start + 1)) {
          boolean hasNext = false;
          List items = new ArrayList();
          do {
            int i = 1;
            items.add(new Item(resultSet.getString(i++).trim(),
                               resultSet.getString(i++).trim(),
                               resultSet.getString(i++),
                               resultSet.getString(i++).trim(),
                               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++)));
          } while ((hasNext = resultSet.next()) && (--count > 0));
          return new Page(items, start, hasNext);
        }
        return Page.EMPTY_PAGE;
      } catch (SQLException exception) {
        throw new CatalogDAOSysException("SQLException: " + exception.getMessage());
      } finally {
        closeAll(connection, statement, resultSet);
      }
  }

  private PreparedStatement buildSQLStatement(Connection connection, Map sqlStatements, String sqlStatementKey,
                                              String[] parameterValues)
    throws SQLException {
      Statement statement = (Statement) sqlStatements.get(sqlStatementKey);
      if (statement != null) {
        return buildSQLStatement(connection, statement, parameterValues);
      }
      return null;
  }

  private PreparedStatement buildSQLStatement(Connection connection, Statement sqlStatement, String[] parameterValues)
    throws SQLException {
      StringBuffer buffer = new StringBuffer();
      int totalParameterValueNb = parameterValues != null ? parameterValues.length : 0;
      for (int i = 0; i < sqlStatement.fragments.length; i++) {
        if (sqlStatement.fragments[i].variableOccurrence) {
          while (totalParameterValueNb > 0 && totalParameterValueNb >= sqlStatement.fragments[i].parameterNumber) {
            buffer.append(sqlStatement.fragments[i].text);
            totalParameterValueNb -= sqlStatement.fragments[i].parameterNumber;
          }
        } else {
          buffer.append(sqlStatement.fragments[i].text);
          totalParameterValueNb -= sqlStatement.fragments[i].parameterNumber;
        }
      }
      if (totalParameterValueNb > 0) {
        System.err.println("Number of values doesn't match number of parameters: " +
                           totalParameterValueNb + "/" + parameterValues.length);
      }
      PreparedStatement statement = connection.prepareStatement(buffer.toString(),
                                                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                                ResultSet.CONCUR_READ_ONLY);
      if (parameterValues != null) {
        for (int i = 0; i < parameterValues.length; i++) {
          statement.setString(i + 1, parameterValues[i]);
        }
      }
      return statement;
  }

  private void printSQLStatement(Map sqlStatements, String sqlStatementKey, String[] parameterValues) {
    Statement statement = (Statement) sqlStatements.get(sqlStatementKey);
    if (statement != null) {
      printSQLStatement(statement, parameterValues);
    } else {
      System.err.println("No statement found for: " + sqlStatementKey);
    }
    return;
  }

  private void printSQLStatement(Statement sqlStatement, String[] parameterValues) {
    StringBuffer buffer = new StringBuffer();
    int totalParameterValueNb = parameterValues != null ? parameterValues.length : 0;
    for (int i = 0; i < sqlStatement.fragments.length; i++) {
      if (sqlStatement.fragments[i].variableOccurrence) {
        while (totalParameterValueNb > 0 && totalParameterValueNb >= sqlStatement.fragments[i].parameterNumber) {
          buffer.append(' ').append(sqlStatement.fragments[i].text);
          totalParameterValueNb -= sqlStatement.fragments[i].parameterNumber;
        }
      } else {
        buffer.append(' ').append(sqlStatement.fragments[i].text);
        totalParameterValueNb -= sqlStatement.fragments[i].parameterNumber;
      }
    }
    if (totalParameterValueNb > 0) {
      System.err.println("Number of values doesn't match number of parameters: " +
                         totalParameterValueNb + "/" + parameterValues.length);
    }
    StringTokenizer tokenizer = new StringTokenizer(buffer.toString(), "?", true);
    for (int i = 0; tokenizer.hasMoreTokens();) {
      String token = tokenizer.nextToken();
      if (token.equals("?")) {
        System.out.print("\'" + parameterValues[i++] + "\'");
      } else {
        System.out.print(token);
      }
    }
    System.out.println(";");
    return;
  }

  private static class ParsingDoneException extends SAXException {

    ParsingDoneException() {
      super("");
    }
  }

  private static class Statement {
    Fragment[] fragments;

    static class Fragment {
      boolean variableOccurrence = false;
      int parameterNumber = 0;
      String text;

      public String toString() {
        return new StringBuffer(text).append('/')
          .append(parameterNumber).append('/')
          .append(variableOccurrence).toString();
      }
    }

    public String toString() {
      StringBuffer buffer = new StringBuffer();
      for (int i = 0; i < fragments.length; i++) {
        buffer.append(fragments[i].toString()).append("\n\t");
      }
      return buffer.toString();
    }
  }

  private void loadSQLStatements(SAXParser parser, final String database, InputSource source)
    throws SAXException, IOException {
      try {
        parser.parse(source, new DefaultHandler() {
          private boolean foundEntry = false;
          private String operation = null;
          List fragments = new ArrayList();
          Statement.Fragment fragment;
          private StringBuffer buffer = new StringBuffer();

          public void startElement(String namespace, String name, String qName, Attributes attrs)
            throws SAXException {
            if (!foundEntry) {
              if (name.equals(XML_DAO_STATEMENTS) && attrs.getValue(XML_DATABASE).equals(database)) {
                foundEntry = true;
              }
            } else if (operation != null) {
              if (name.equals(XML_SQL_FRAGMENT)) {
                fragment = new Statement.Fragment();
                String value = attrs.getValue(XML_PARAMETER_NB);
                if (value != null) {
                  try {
                    fragment.parameterNumber = Integer.parseInt(value);
                  } catch (NumberFormatException exception) {
                    //throw new SAXException(exception);
                  }
                }
                value = attrs.getValue(XML_OCCURRENCE);
                fragment.variableOccurrence = (value != null && value.equals(XML_VARIABLE));
                buffer.setLength(0);
              }
            } else {
              if (name.equals(XML_SQL_STATEMENT)) {
                operation = attrs.getValue(XML_METHOD);
                fragments.clear();
              }
            }
            return;
          }

          public void characters(char[] chars, int start, int length) throws SAXException {
            if (foundEntry && operation != null) {
              buffer.append(chars, start, length);
            }
            return;
          }

          public void endElement(String namespace, String name, String qName) throws SAXException {
            if (foundEntry) {
              if (name.equals(XML_DAO_STATEMENTS)) {
                foundEntry = false;
                throw new ParsingDoneException(); // Interrupt the parsing since everything has been collected
              } else if (name.equals(XML_SQL_STATEMENT)) {
                Statement statement = new Statement();
                statement.fragments = (Statement.Fragment[]) fragments.toArray(new Statement.Fragment[0]);
                sqlStatements.put(operation, statement);
                operation = null;
              } else if (name.equals(XML_SQL_FRAGMENT)) {
                fragment.text = buffer.toString().trim();
                fragments.add(fragment);
                fragment = null;
              }
            }
            return;
          }

          public void warning(SAXParseException exception) {
            System.err.println("[Warning]: " + exception.getMessage());
            return;
          }

          public void error(SAXParseException exception) {
            System.err.println("[Error]: " + exception.getMessage());
            return;
          }

          public void fatalError(SAXParseException exception) throws SAXException {
            System.err.println("[Fatal Error]: " + exception.getMessage());
            throw exception;
          }
        });
      } catch (ParsingDoneException exception) {} // Ignored
      return;
  }

  public static void main(String[] args) {
        if (args.length <= 2) {
      try {
        GenericCatalogDAO catalogDAO = new GenericCatalogDAO(args[0], args[1]);
        String[] parameterValues = new String[] { "FR_fr", "Chien" };
        catalogDAO.printSQLStatement(catalogDAO.sqlStatements, XML_GET_CATEGORY, parameterValues);
        parameterValues = new String[] { "FR_fr" };
        catalogDAO.printSQLStatement(catalogDAO.sqlStatements, XML_GET_CATEGORIES, parameterValues);
        parameterValues = new String[] { "FR_fr", "Caniche" };
        catalogDAO.printSQLStatement(catalogDAO.sqlStatements, XML_GET_PRODUCT, parameterValues);
        parameterValues = new String[] { "FR_fr", "Chien" };
        catalogDAO.printSQLStatement(catalogDAO.sqlStatements, XML_GET_PRODUCTS, parameterValues);
        parameterValues = new String[] { "FR_fr", "Medor" };
        catalogDAO.printSQLStatement(catalogDAO.sqlStatements, XML_GET_ITEM, parameterValues);
        parameterValues = new String[] { "FR_fr", "Caniche" };
        catalogDAO.printSQLStatement(catalogDAO.sqlStatements, XML_GET_ITEMS, parameterValues);
        String[] keywords = { "Chien", "Chat", "Poisson" };
        parameterValues = new String[1 + (keywords.length * 2)];
        parameterValues[0] = "FR_fr";
        for (int i = 0; i < keywords.length; i++) {
          parameterValues[(i * 2) + 1] = keywords[i];
          parameterValues[(i * 2) + 2] = keywords[i];
        }
        catalogDAO.printSQLStatement(catalogDAO.sqlStatements, XML_SEARCH_ITEMS, parameterValues);
                System.exit(0);
      } catch (Exception exception) {
                exception.printStackTrace(System.err);
                System.err.println(exception);
                System.exit(2);
      }
        }
        System.err.println("Usage: " + GenericCatalogDAO.class.getName() + " [file-name] [databas-type]");
        System.exit(1);
  }
}