java - JSP在数据库中搜索更多表

Question

使用此 search.jsp，它会查找用户搜索的所有单词的匹配项，然后删除重复的找到的用户并显示找到的匹配项的列表。

我只能搜索用户的电子邮件、名字、姓氏、用户名，但我还想搜索技能，并显示与找到的技能匹配的用户。

For example I search for username; Admin, it finds the admin and shows this person in the result. This works now, but I also want this;;; If I search for Java, Then I want everybody that has the skill Java to show up in the result.

我知道使用 SQL 查询更容易，但这是不同的。我在下面提供了模型和数据库信息；

数据库:

**Table name: User**
userId
emailAddress
firstname
lastname
username

**Table name: user_skill**
User_userId
skills_skillId

**Table name: skill**
skillId
name

这一切发生的地方: : : search.jsp:

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    System.out.println("Hij komt er in");

    //get the action
    String uri = request.getRequestURI();
    String action = uri.substring(uri.lastIndexOf("/") + 1);

    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();
}

模型.user.java:

@Entity
public class User implements Serializable{

    @Id
    @GeneratedValue
    private int userId;
    private String username, firstname, lastname, emailAddress, position, password;
    private String fullName;
    private boolean isAdmin;

    @ManyToMany
    private List<Skill> skills;

    public User(){

    }

模型.skill.java:

@Entity
public class Skill implements Serializable {

    @Id
    @GeneratedValue

    private long skillId;
    @Column(columnDefinition = "varchar(25)")
    private String name;
    @Column(columnDefinition = "varchar(25)")
    private String level;
    @Column(columnDefinition = "varchar(250)")
    private String description;

    public Skill() {
    }

Answer 1

向技能添加联接，并向查询添加 or 子句:

select distinct u from User u
left join u.skills skill
where ... (existing or clauses)
or skill.name like :param

此外，您的代码容易受到 SQL 注入(inject)攻击，如果参数包含单引号，则会失败。使用如上所示的命名参数。