JPA动态条件-API查询

Question

@Entity
public class Person implements Serializable {
    @Id
    private Long id;
    private String name;
    private String givenName;

    @Temporal(TemporalType.DATE)
    private Date birthdate;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "INFORMATION_ID")
    private Information information;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "ADDRESS_ID")
    private Address address;
    ...
}

@Entity
public class Information implements Serializable {
    @Id
    private Long id;
    private String detail;
    ...
}

@Entity
public class Address implements Serializable {
    @Id
    private Long id;
    private String street;
    private String city;
    ...
}

@Stateless
public class PersonService {
    @PersistenceContext(unitName = "ProblemGenericDatatableFilterPU")
    private EntityManager em;
    public List<Person> findAllPersons222(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters) {
        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<Person> criteriaQuery = builder.createQuery(Person.class);
        Root<Person> rootPerson = criteriaQuery.from(Person.class);
        Join<Person, Information> joinPersonInformation = rootPerson.join(Person_.information);
        Join<Person, Address> joinPersonAddress = rootPerson.join(Person_.address);

        // select
        criteriaQuery.select(rootPerson);

        // filter
        List<Predicate> allPredicates = new ArrayList<>();
        for(Entry<String, Object> currentEntry : filters.entrySet()) {
            Predicate currentPredicate;

            if(currentEntry.getKey().startsWith("information_")) {
                currentPredicate = builder.like(
                        builder.lower(joinPersonInformation.<String>get(currentEntry.getKey())),
                    builder.lower(builder.literal(String.valueOf(currentEntry.getValue())))
                );
            }
            else if(currentEntry.getKey().startsWith("address_")) {
                currentPredicate = builder.like(
                        builder.lower(joinPersonAddress.<String>get(currentEntry.getKey())),
                    builder.lower(builder.literal(String.valueOf(currentEntry.getValue())))
                );
            }
            else {
                currentPredicate = builder.like(
                        builder.lower(rootPerson.<String>get(currentEntry.getKey())),
                    builder.lower(builder.literal(String.valueOf(currentEntry.getValue())))
                );
            }
            allPredicates.add(currentPredicate);
        }
        criteriaQuery.where(builder.and(allPredicates.toArray(new Predicate[0])));

        // order
        if(sortField != null && !sortField.isEmpty()) {
            Order orderBy;
            if(sortField.startsWith("information_")) {
                orderBy = (sortOrder == SortOrder.DESCENDING
                        ? builder.desc(joinPersonInformation.get(sortField))
                        : builder.asc(joinPersonInformation.get(sortField)));
            }
            else if(sortField.startsWith("address_")) {
                orderBy = (sortOrder == SortOrder.DESCENDING
                        ? builder.desc(joinPersonAddress.get(sortField))
                        : builder.asc(joinPersonAddress.get(sortField)));
            }
            else {
                orderBy = (sortOrder == SortOrder.DESCENDING
                        ? builder.desc(rootPerson.get(sortField))
                        : builder.asc(rootPerson.get(sortField)));
            }
            criteriaQuery.orderBy(orderBy);
        }

        Query query = em.createQuery(criteriaQuery);
        // pagination
        query.setFirstResult(first);
        query.setMaxResults(pageSize);
        return query.getResultList();
    }
}

现在我扪心自问，是不是有什么“点符号”或者其他什么东西让这个案例变得可有可无。在E。例如：本机SQL我会做一些事情，比如创建一个子查询，并从内部投影中选择所有别名值(select*from(select Person.name as name，address.street as street,...)where name=...and street like...)。

如果有任何建议，我将不胜感激。

Answer 1

我终于有时间处理我的问题了。我找到了一个并不完美，但对我有效的解决方案。

当我搜索另一个问题时，我找到了Leonardo Shikida的这篇文章，发现了一个非常方便的Path<？>getPath(...)方法（我还深入研究了CriteriaAPI中出色的继承关系:Path、Root、Join、From等）。有了这一点，我想起了我以前的问题，并想到了一种更古老的方法。下面是我对它的看法：

首先，我创建我需要的所有联接（即root<？>和join<？？>)，并将它们放入一个map ，其中string是一个元素，在该元素上以点表示法查询属性（命名约定和完整解决方案的缺点），而from是相应的源。

有了这个映射，我可以用一种或多或少通用的方式进行过滤和排序。

要使其工作，前端需要使用完全相同的命名约定并相应地传递filters-Map（即JSF在p：column中使用primefacesfield属性）。

public List<Person> newFindAllPersons(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters)
{
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Person> criteriaQuery = builder.createQuery(Person.class);

    // setting up the required joins
    Root<Person> rootPerson = criteriaQuery.from(Person.class);
    Join<Person, Information> joinPersonInformation = rootPerson.join(Person_.information);
    Join<Person, Address> joinPersonAddress = rootPerson.join(Person_.address);
    Join<Address, Information> joinAddressInformation = joinPersonAddress.join(Address_.information);

    // putting all joins into a map with a dot`ted name
    Map<String, From<?, ?>> mapFieldToFrom = new HashMap<>();
    mapFieldToFrom.put("person", rootPerson);
    mapFieldToFrom.put("person.address", joinPersonAddress);
    mapFieldToFrom.put("person.information", joinPersonInformation);
    mapFieldToFrom.put("person.address.information", joinAddressInformation);

    // select
    criteriaQuery.select(rootPerson);

    // filter
    List<Predicate> allPredicates = new ArrayList<>();
    for(Entry<String, Object> currentEntry : filters.entrySet())
    {
        Predicate currentPredicate = builder.like(
                builder.lower(getStringPath(currentEntry.getKey(), mapFieldToFrom)),
                builder.lower(builder.literal("%" + String.valueOf(currentEntry.getValue()) + "%"))
        );
        allPredicates.add(currentPredicate);
    }
    criteriaQuery.where(builder.and(allPredicates.toArray(new Predicate[0])));

    // order
    if(sortField != null && !sortField.isEmpty())
    {
        Path<?> actualPath = getStringPath(sortField, mapFieldToFrom);
        Order orderBy = (sortOrder == SortOrder.DESCENDING
                ? builder.desc(actualPath)
                : builder.asc(actualPath));

        criteriaQuery.orderBy(orderBy);
    }

    Query query = em.createQuery(criteriaQuery);
    // pagination
    query.setFirstResult(first);
    query.setMaxResults(pageSize);
    return query.getResultList();
}

/**
 * divides the given field at the last dot and takes <br>
 * -   the first part as the key in the map to retrieve the From<?, ?> <br>
 * -   the last part as the name of the column in the entity
 */
private Path<String> getStringPath(String field, Map<String, From<?, ?>> mapFieldToFrom)
{
    if(!field.matches(".+\\..+"))
    {
        throw new IllegalArgumentException("field '" + field + "' needs to be a dotted path (i. e. customer.address.city.zipcode)");
    }
    String fromPart = field.substring(0, field.lastIndexOf('.'));
    String fieldPart = field.substring(field.lastIndexOf('.') + 1);

    From<?, ?> actualFrom = mapFieldToFrom.get(fromPart);
    if(actualFrom == null)
    {
        throw new IllegalStateException("the given map does not contain a from or for the value '" + fromPart + "' or is null");
    }
    return actualFrom.get(fieldPart);
}

前端示例

<p:dataTable>
    <!-- mapFieldToFrom.put("person", rootPerson); -->
    <p:column field="person.name"> 
    </p:column>

    <!-- mapFieldToFrom.put("person.address", joinPersonAddress); -->
    <p:column field="person.address.street">
    </p:column>
</p:dataTable>