使用日期字段查询JPA标准的例外

Question

我正在尝试使用JPA标准构建动态查询，以使用高级搜索表格中的参数从GAE-DATASTORE检索数据。该表格具有五个字段，类型，最小金额，最大金额，日期和日期。类型，最小和最高金额，运行良好，但是对于日期字段，我有这个例外

严重：javax.persistence.persistenceException：预期'）'在字符68中。.date＆lt; =星期五1月31日00:00:00 CET 2014）

这是我dao中的方法，它引发了例外：

    public List<Outlay> findByParameters(String author, String page, String pageSize,  
    String type, String minAmount, String maxAmount, String fromDate, String toDate)
    throws PersistenceException, ParseException {
    EntityManager em = EMF.get().createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Outlay> cq = cb.createQuery(Outlay.class);
    Root<Outlay> outlay = cq.from(Outlay.class);
    List<Predicate> predicates = new ArrayList<Predicate>();
    if(author != null && !author.isEmpty()) {
        predicates.add(cb.equal(outlay.<String>get("author"), author));
    }
    if(type != null && !type.isEmpty()) {
        predicates.add(cb.equal(outlay.<String>get("type"), type));
    }
    if(minAmount != null && !minAmount.isEmpty()) {                             
        Double min = Double.parseDouble(minAmount);
        predicates.add(cb.ge(outlay.<Double>get("amount"), min));
    }
    if(maxAmount != null && !maxAmount.isEmpty()) {
        Double max = Double.parseDouble(maxAmount);
        predicates.add(cb.le(outlay.<Double>get("amount"), max));
    }
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
    if(fromDate != null && !fromDate.isEmpty()) {
        Date from = formatter.parse(fromDate);
        log.info("From: " + formatter.format(from));
        predicates.add(cb.greaterThanOrEqualTo(outlay.<Date>get("date"), from));
    }
    if(toDate != null && !toDate.isEmpty()) {           
        Date to = formatter.parse(toDate);          
        log.info("To: " + formatter.format(to));    
        predicates.add(cb.lessThanOrEqualTo(outlay.<Date>get("date"), to));
    }       
    cq.select(outlay).where(predicates.toArray(new Predicate[]{}));
    log.info("Query: " + cq.toString());
    TypedQuery<Outlay> query = em.createQuery(cq);
    Integer index = 0;   
    if(page != null && !page.isEmpty()) {
        index = Integer.parseInt(page);
    }
    Integer step = 10;      
    if(pageSize != null && !pageSize.isEmpty()) {
        step = Integer.parseInt(pageSize);
    }       
    query.setFirstResult(index * step);
    query.setMaxResults(step);
    List<Outlay> outlayList = new ArrayList<Outlay>();
    try {
        outlayList = query.getResultList();         
        log.info("Retrieved " + outlayList.size() + " Outlay");                                                 
    } finally {         
        em.close();
    }
    return outlayList;
}

这是结果查询

从com.mycompany.model.outlay dn_this中选择dn_this dn_this（dn_this.author ='18580476422013912411'）and（dn_this.date> = = = = = = = = = 1月1日1月01日01 00:00:00 cet 2014）和（dn_this＆dn_tate＆dn_tate＆ltt星期五1月31日00:00:00 CET 2014）

这是我的实体

import java.io.Serializable;
import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
@Entity
public class Outlay implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) 
    private Long id;
    private String author;
    private String type;
    private Double amount;
    private Date date;
    private String description; 
    Outlay() { }
    public Outlay(String author, String type, Double amount, Date date,
            String description) {
        super();
        this.author = author;
        this.type = type;
        this.amount = amount;
        this.date = date;
        this.description = description;
    }
    public long getId() {
        return id;
    }
    public String getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
    public String getType() {
        return type;
    }
    public void setType(String type) {
        this.type = type;
    }
    public Double getAmount() {
        return amount;
    }
    public void setAmount(Double amount) {
        this.amount = amount;
    }
    @Temporal(TemporalType.DATE)
    public Date getDate() {
        return date;
    }
    public void setDate(Date date) {
        this.date = date;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    @Override
    public String toString() {
        return "Outlay [id=" + id + ", author=" + author + ", type=" + type
                + ", amount=" + amount + ", date=" + date + "]";
    }   
}

奇怪的是，我在dao中有类似的方法

    public Long countByParameters(String author, String type, String minAmount,  
        String maxAmount, String fromDate, String toDate)
        throws PersistenceException, ParseException {
    EntityManager em = EMF.get().createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Long> cq = cb.createQuery(Long.class);
    Root<Outlay> outlay = cq.from(Outlay.class);
    List<Predicate> predicates = new ArrayList<Predicate>();
    if(author != null && !author.isEmpty()) {
        predicates.add(cb.equal(outlay.<String>get("author"), author));
    }
    if(type != null && !type.isEmpty()) {
        predicates.add(cb.equal(outlay.<String>get("type"), type));
    }
    if(minAmount != null && !minAmount.isEmpty()) {                             
        Double min = Double.parseDouble(minAmount);
        predicates.add(cb.ge(outlay.<Double>get("amount"), min));
    }
    if(maxAmount != null && !maxAmount.isEmpty()) {
        Double max = Double.parseDouble(maxAmount);
        predicates.add(cb.le(outlay.<Double>get("amount"), max));
    }
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
    if(fromDate != null && !fromDate.isEmpty()) {
        Date from = formatter.parse(fromDate);
        log.info("From: " + formatter.format(from));
        predicates.add(cb.greaterThanOrEqualTo(outlay.<Date>get("date"), from));
    }
    if(toDate != null && !toDate.isEmpty()) {           
        Date to = formatter.parse(toDate);          
        log.info("To: " + formatter.format(to));            
        predicates.add(cb.lessThanOrEqualTo(outlay.<Date>get("date"), to));
    }       
    cq.select(cb.count(outlay)).where(predicates.toArray(new Predicate[]{}));
    log.info("Query: " + cq.toString());
    Long count = 0l;
    try {           
        count = em.createQuery(cq).getSingleResult();
        log.info("OutlayList " + count + " size");                                                  
    } finally {         
        em.close();
    }
    return count; 
}

这很好。

我的设置是GAE SDK 1.8.9，JPA 2.0，datanucleus核3.1.3，datanucleus-jpa-api 3.1.3和datanucleus-appengine-2.1.2。

任何帮助将不胜感激！预先感谢。

Answer 1

这是我的解决方案：

dao类

    public List<Outlay> findByParameters(String author, String page, String pageSize,  
    String type, String minAmount, String maxAmount, String fromDate, String toDate)
    throws PersistenceException, ParseException {
    EntityManager em = EMF.get().createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();       
    CriteriaQuery<Outlay> cq = cb.createQuery(Outlay.class);
    Root<Outlay> outlay = cq.from(Outlay.class);
    List<Predicate> predicates = new ArrayList<Predicate>();
    if(author != null && !author.isEmpty()) {
        predicates.add(cb.equal(outlay.get(Outlay_.author), 
            cb.parameter(String.class, "author")));
    }
    if(type != null && !type.isEmpty()) {
        predicates.add(cb.equal(outlay.get(Outlay_.type), 
            cb.parameter(String.class, "type")));
    }
    if(minAmount != null && !minAmount.isEmpty()) {
        predicates.add(cb.ge(outlay.get(Outlay_.amount), 
            cb.parameter(Double.class, "minAmount")));
    }
    if(maxAmount != null && !maxAmount.isEmpty()) {
        predicates.add(cb.le(outlay.get(Outlay_.amount), 
            cb.parameter(Double.class, "maxAmount")));
    }
    if(fromDate != null && !fromDate.isEmpty()) {
        predicates.add(cb.greaterThanOrEqualTo(outlay.get(Outlay_.date), 
            cb.parameter(Date.class, "fromDate")));
    }
    if(toDate != null && !toDate.isEmpty()) {
        predicates.add(cb.lessThanOrEqualTo(outlay.get(Outlay_.date), 
                cb.parameter(Date.class, "toDate")));           
    }
    cq.select(outlay).where(predicates.toArray(new Predicate[]{}));
    TypedQuery<Outlay> query = em.createQuery(cq);
    if(author != null && !author.isEmpty()) {
        query.setParameter("author", author);
    }
    if(type != null && !type.isEmpty()) {
        query.setParameter("type", type);
    }
    if(minAmount != null && !minAmount.isEmpty()) {
        query.setParameter("minAmount", Double.parseDouble(minAmount));
    }
    if(maxAmount != null && !maxAmount.isEmpty()) {
        query.setParameter("maxAmount", Double.parseDouble(maxAmount));
    }
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
    if(fromDate != null && !fromDate.isEmpty()) {
        query.setParameter("fromDate", formatter.parse(fromDate));
    }
    if(toDate != null && !toDate.isEmpty()) {
        query.setParameter("toDate", formatter.parse(toDate));
    }
    Integer index = 0;   
    if(page != null && !page.isEmpty()) {
        index = Integer.parseInt(page);
    }
    Integer step = 10;      
    if(pageSize != null && !pageSize.isEmpty()) {
        step = Integer.parseInt(pageSize);
    }       
    query.setFirstResult(index * step);
    query.setMaxResults(step);
    List<Outlay> outlayList = new ArrayList<Outlay>();
    try {
        outlayList = query.getResultList();
        log.info("Retrieved " + outlayList.size() + " Outlay");                                                 
    } finally {         
        em.close();
    }
    return outlayList;
}

然后添加了metamodel类：

package com.mycompany.model;
import java.util.Date;
import javax.persistence.metamodel.SingularAttribute;
@javax.persistence.metamodel.StaticMetamodel(com.mycompany.model.Outlay.class)
public class Outlay_ {
    public static volatile SingularAttribute<Outlay, String> author;
    public static volatile SingularAttribute<Outlay, String> type;
    public static volatile SingularAttribute<Outlay, Double> amount;
    public static volatile SingularAttribute<Outlay, Date> date;    
}

Answer 2

我总是会指定参数而不是文字，从那时起，您就会获得查询/语句重复使用的好处，尤其是使用日期文字，因为它们并没有得到datastores的普遍支持，而参数为