请教petstore1.3.1_02中searchItems的一个问题:

03-07-21 jd2bs

因为有了中文UI版,所以下了一个.奇怪的是,en-Us版搜索时搜索不到任何结果,但中文版可以搜索到结果.

例如:

成年雄性吉娃娃狗

善于叫唤的狗 ¥1,080.00 加入购物筐

成年雄性吉娃娃狗

能成为您的朋友 ¥1,300.00 加入购物筐

我输入"娃娃狗"可以找到.

英文版中,我输入Bulldog,Manx等,根本找不到记录.

我看了下代码也只有locale l参数的缘故了,按理说英文搜索应该更加容易啊?还是encoding时出了问题?:

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 ? ",
      ") )"
  };
试验证明:以上catid部分不起作用,我认为应该去掉
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());
      }
  }
<p>