java可选参数JPA2.1

Question

我有这门课

@Entity
public class Message {

  @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
  private long id;

  @Column( nullable = false )
  private String mobile;

  @Column( nullable = false )
  private String message;

  @Column( nullable = false )
  private Lang lang;

  @Column( nullable = false )
  private int status;

  @Column( nullable = false )
  private Calendar creationDate;
  ...
}

我希望能够使用表单中的可选参数查询表。我正在使用JPA2.1和Hibernate。有没有办法在不使用多个查询语句或不将我的应用程序与hibernate或我正在使用的特定数据库紧密耦合的情况下做到这一点

以下是我正在使用的表单：

<form action="${pageContext.request.contextPath}/search">
<label>From</label>
<input type="datetime" placeholder="dd/mm/yy hh/mm" name="from" required> 
<label>To</label>
<input type="datetime" placeholder="dd/mm/yy hh/mm" name="to" required> 
<select name="lang">
    <option value="english">English</option>
    <option value="arabic">Arabic</option>
</select>
<input type="tel" placeholder="Recipient" name="recipient">
<select name="status">
    <option value="new">New</option>
    <option value="picked">Picked</option>
    <option value="sent">Sent</option>
    <option value="rejected">Rejected</option>
</select>
<input type="submit" value="Search">

更新::

我试过这个：

    public List<Message> getMessages(Calendar fromDate, Calendar toDate, String lang, String recipient, String status) {
    /*TypedQuery<Message> query = em.createQuery(
            "SELECT e FROM Message e WHERE e.creationDate BETWEEN :fromDate AND :toDate", Message.class);
    query.setParameter("fromDate", fromDate);
    query.setParameter("toDate", toDate);
    List<Message> list = query.getResultList();
    return list;*/

    List<Predicate> predicates = new ArrayList<>();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Message> query = cb.createQuery(Message.class);
    Root<Message> root = query.from(Message.class);

    predicates.add(cb.between(root.get("creationDate"), fromDate, toDate));

    if (lang != null && !lang.equalsIgnoreCase("any")) {
           predicates.add(cb.equal(root.get("lang"), lang));
        }

    if (recipient != null && !recipient.equals("")) {
           predicates.add(cb.equal(root.get("mobile"), recipient));
        }

    if (status != null && !status.equalsIgnoreCase("any")) {
           predicates.add(cb.equal(root.get("mobile"), recipient));
        }

    query.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));
    List<Message> list = em.createQuery(query).getResultList();

    return list;
}

但我得到了以下错误：

java.lang.IllegalArgumentException: Parameter value [0] did not match expected type [entity.Lang (n/a)]
at org.hibernate.jpa.spi.BaseQueryImpl.validateBinding(BaseQueryImpl.java:897)
at org.hibernate.jpa.internal.QueryImpl.access$000(QueryImpl.java:61)
at org.hibernate.jpa.internal.QueryImpl$ParameterRegistrationImpl.bindValue(QueryImpl.java:235)
at org.hibernate.jpa.spi.BaseQueryImpl.setParameter(BaseQueryImpl.java:638)
at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:163)
at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:32)
at org.hibernate.jpa.criteria.compile.CriteriaCompiler$1$1.bind(CriteriaCompiler.java:109)
at org.hibernate.jpa.criteria.CriteriaQueryImpl$1.buildCompiledQuery(CriteriaQueryImpl.java:369)
at org.hibernate.jpa.criteria.compile.CriteriaCompiler.compile(CriteriaCompiler.java:130)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:699)
at dao.MessageDAO.getMessages(MessageDAO.java:63)

Answer 1

简短回答：是的。您不需要使用特定于hibernate的类来构建满足您需求的查询

但是，您可能希望在生成查询之前过滤掉可选参数。 如果tel是null/empty，您可能不希望在查询中包含它。所以不要把它添加到查询中。 查看the Java EE persistence tutorial和^{}以按照需要的方式构建TypedQuery

伪代码（显然未经测试…但我希望您能理解）：

List<Predicate> predicates = ...
CriteriaBuilder cb = ...
Root<?> table = ...
if (isNotBlank(tel)) {
   predicates.add(cb.equal(table.get("tel"), tel));
}
if (isNotBlank(...)) {
   predicates.add(cb.like(table.get("..."), ...));
}
...
query.where(cb.and(predicates.toArray(Predicate[]::new)));

也许以下内容也有帮助：Really dynamic JPA CriteriaBuilder

Answer 2

这里有一种更好的方法，可以根据可选参数进行更新

public int updateMyEntity(String value1, String value2){

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaUpdate<MyEntity> cu = cb.createCriteriaUpdate(MyEntity.class);
    Root<MyEntity> root = cu.from(MyEntity.class);

    if (value1 != null) {
        cu.where(cb.equal(root.get(MyEntity.value1), value1));
    }

    if (value2 != null) {
        cu.where(cb.equal(root.get(MyEntity.value2), value2));
    }

    // set 
    cu.set(MyEntity_.isAvailable,  true);

    return em.createQuery(cu).executeUpdate();
}