Java + JPA：在数据库中搜索用户通过名字和姓氏返回太多结果。有没有更好的办法？

Right now my implementation is like this, so if the user type is Peter Smith, then I would a split on the query string like this:

现在我的实现是这样的,所以如果用户类型是Peter Smith,那么我会对查询字符串进行拆分,如下所示:

String[] name = searchQuery.split(" "); //searchQuery is type String

then I would loop through name array, and for each name, I match it against first name and last name fields in my db as below

然后我将遍历名称数组,并为每个名称,我将其与我的数据库中的名字和姓氏字段匹配,如下所示

@NamedQuery(name="User.findUserByName",
    query="select c from User c where c.fname LIKE :param OR c.lname LIKE :param"),

public List<User> findUser(String searchParam){
    Query query = em.createNamedQuery("User.findUserByName");
    query.setParameter("param", "%" + searchParam + "%");
    return query.getResultList();
}

however, if I do this, it return too many results (Every thing related to Peter will be display and everything related to Smith will be as well). Is there a better way to do this?

但是,如果我这样做,它会返回太多结果(与Peter相关的每件事都会显示出来,而与史密斯有关的一切也会如此)。有一个更好的方法吗?

I am using EclipseLink btw

我正在使用EclipseLink btw

1 个解决方案

#1

If I were you I'd use the Criteria API instead. Here's a sample implementation:

如果我是你,我会使用Criteria API。这是一个示例实现:

public List<User> findUsersByName(final String firstName,
    final String lastName){

    final boolean hasFirst = firstName != null && firstName.length() > 0;
    // if you use Apache Commons / Lang, do it like this:
    // final boolean hasFirst = StringUtils.isNotBlank(firstName);

    final boolean hasLast = lastName != null && lastName.length() > 0;
    if(!hasFirst && !hasLast){
        // or throw IllegalArgumentException
        return Collections.emptyList();
    }
    final CriteriaBuilder cb = em.getCriteriaBuilder();
    final CriteriaQuery<User> query = cb.createQuery(User.class);
    final Root<User> root = query.from(User.class);
    if(hasFirst && hasLast){
        query.where(cb.and(
            likeExpression(cb, root,"lname", lastName),
            likeExpression(cb, root, "fname", firstName)
            ));
    } else if(hasFirst){
        query.where(likeExpression(cb, root,"fname", firstName));
    } else{
        query.where(likeExpression(cb, root,"lname", lastName));
    }
    return em.createQuery(query).getResultList();
}

private static Predicate likeExpression(final CriteriaBuilder cb,
    final Root<User> root,
    final String path, String parameter){
    return cb.like(root.<String> get(path), "*"+parameter.trim()+"*");
}

Now you can easily create overloaded versions of this method:

现在您可以轻松创建此方法的重载版本:

public List<User> findUsersByFullName(final String fullName){
    final String[] parts = fullName.split("\\s+");
    if(parts.length != 2){
        // probably you should assemble first name from all parts
        // except the last, but I'm lazy
        throw new IllegalArgumentException("Bad name: " + fullName);
    }
    return findUsersByName(parts[0], parts[1]);
}

public List<User> findUsersByLastName(final String lastName){
    return findUsersByName(null, lastName);
}

public List<User> findUsersByFirstName(final String firstName){
    return findUsersByName(firstName, null);
}

Reference:

• Java EE 6 Tutorial: Using the Criteria API to Create Queries

Java EE 6教程:使用Criteria API创建查询