java - JSP搜索数据库

Question

场景 1:用户想要找到管理员，他的名字是 jan。他输入“admin jan”或“jan admin”。那么结果应该是:如果数据库中有一个具有“jan”和“admin”的用户，它将显示该用户。即使还有其他 10 个人在他们的电子邮件、街道名称或其他内容中包含“jan”和“admin”，它也应该显示他们。场景 2:用户想要找到具有“HQL PHP bebe SQL JAVA”技能的名为“bebe”的人。它应该向每个用户显示数据库中的这些单词。因此它应该只显示具有这些技能和名称的用户，因此如果其他人没有 HQL 技能，但具有 PHP SQL JAVA 技能，则不要显示此技能。

我只搜索最后一个词。因此，如果我搜索“THIS THAT THOSE PHP SQL ADMIN”，它只会找到具有 ADMIN 的每个人，并忽略字符串的其余部分。

if (action.equals("searchUser")) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    String searchQuery = request.getParameter("searchQuery");
    String[] query = searchQuery.split(" ");

    for (int i = 0; i < query.length; i++) {
        String hql = "FROM User WHERE username LIKE '%" + query[i] + "%' OR firstname LIKE '%" + query[i] + "%' OR lastname LIKE '%" + query[i] + "%' OR emailAddress LIKE '%" + query[i] + "%'";

        List<User> result = session.createQuery(hql).list();
        //set our results on the request and redirect back
        request.setAttribute("users", result);
        request.setAttribute("usersSize", result.size());
        request.setAttribute("usersSizeResults", result.size());

        System.out.println("size:" + result.size());
    }
    redirect(request, response, "/search.jsp");
    session.close();

}

Answer 1

尝试这样做以避免重复值(未经测试):

public void searchUser() {
if (action.equals("searchUser")) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    String searchQuery = request.getParameter("searchQuery");
    String[] params = searchQuery.split(" ");

    // Found users
    List<User> usersFound = new ArrayList<User>();

    // Exact match
    String hqlMatch = this.getSearchHqlQuery(params, "AND");
    List<User> exactResult = session.createQuery(hqlMatch).list();
    if (exactResult!=null && !exactResult.isEmpty()) {
        usersFound.addAll(exactResult);
    }
    // Multiple search
    else {
        String hqlLike = this.getSearchHqlQuery(params, "OR");
        List<User> likeResult = session.createQuery(hqlLike).list();
        if (likeResult!=null && !likeResult.isEmpty()) {
            usersFound.addAll(likeResult);
        }
    }

    System.out.println("size:" + usersFound.size());
        // set our results on the request and redirect back
        request.setAttribute("users", usersFound);
        request.setAttribute("usersSize", usersFound.size());
        request.setAttribute("usersSizeResults", usersFound.size());

        redirect(request, response, "/search.jsp");
        session.close();
    }
}

private String getSearchHqlQuery(String[] params, String andOrfilter) {
    StringBuilder hql = new StringBuilder();
    hql.append("from User ");
    if (params.length > 0) {
        hql.append("where ");
        for (int i = 0; i < params.length; i++) {
            if (i > 0) { hql.append(andOrfilter); }
            hql.append(" (username like '%").append(params[i]);
            hql.append("%' OR firstname like '%").append(params[i]);
            hql.append("%' OR lastname like '%").append(params[i]);
            hql.append("%' OR emailAddress like '%").append(params[i]);
            hql.append("%') ");
        }
    }
    return hql.toString();
}