java - JPA 返回空结果列表，而 DB 返回行集

Question

我正在尝试运行查询以从我的数据库中获取一些统计数据。我正在使用 JPA。但是我遇到了这样一个麻烦:当我运行JPQL 查询时，返回的是空结果集。但是当我运行 SQL 时，使用 JPA 为那个 JPQL 查询生成，我得到了一行数据。

这是我得到的:

Ticket 实体

@Entity
@Table(name="tickets")
public class Ticket extends AbstractEntity {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;

    @Embedded
    private Owner owner;

    @ManyToOne
    @JoinColumn(name="flightId")
    private Flight flight;

    private String status;

    public Ticket() {
        this.status = "AVAILABLE";
    }

飞行实体

@Entity
@Table(name="flights")
public class Flight extends AbstractEntity {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;

    private String departure;
    private String destination;
    private Date date;
    private float ticketCost;

    @OneToMany(mappedBy="flight", fetch=FetchType.LAZY, cascade=CascadeType.ALL)
    private List<Ticket> tickets = new ArrayList<Ticket>();

结果行类

public class SoldReportRow {
    private String departure;
    private String destination;
    private DateTime date;
    private int ticketsSold;
    private float totalCost;

    public SoldReportRow(Date date, String departure, String destination, Long ticketsSold, Double totalCost) {
        this.departure = departure;
        this.destination = destination;
        this.ticketsSold = ticketsSold.intValue();
        this.totalCost = totalCost.floatValue();
        this.date = new DateTime(date);
    }

JPQL

SELECT NEW entities.SoldReportRow(f.date, f.departure, f.destination, 
    COUNT(t.id), SUM(f.ticketCost)) 
FROM  Ticket t JOIN t.flight f  
WHERE t.status = 'SOLD' AND t.owner IS NOT NULL AND f.date BETWEEN ? and ? 
GROUP BY f.id

生成的SQL

SELECT t0.DATE, t0.DEPARTURE, t0.DESTINATION, COUNT(t1.ID), SUM(t0.TICKETCOST) 
FROM flights t0, tickets t1 
WHERE ((((t1.STATUS = ?) AND NOT ((((((t1.ADDRESS IS NULL) 
    AND (t1.EMAIL IS NULL)) AND (t1.NAME IS NULL)) AND (t1.OWNERFROM IS NULL)) 
    AND (t1.PHONE IS NULL)))) AND (t0.DATE BETWEEN ? AND ?)) 
    AND (t0.ID = t1.flightId)) GROUP BY t0.ID

所以这是我在运行 JPQL 时得到的结果:

下面是我运行生成的 SQL 时得到的结果:

UPD:TicketDAO 方法

// ...

protected static EntityManagerFactory factory;
protected static EntityManager em;

static {
    factory = Persistence.createEntityManagerFactory(UNIT_NAME);
}

// ...

public static List<SoldReportRow> soldReportByDate(String from, String to) {
    DateTimeFormatter dfTxt = DateTimeFormat.forPattern("dd/MM/yyyy");
    DateTimeFormatter dfSql = DateTimeFormat.forPattern("yyyy-MM-dd");

    String startDate = dfSql.print(dfTxt.parseDateTime(from));
    String endDate = dfSql.print(dfTxt.parseDateTime(to));

    String query = String.format(
            "SELECT NEW entities.SoldReportRow(f.date, f.departure, f.destination, COUNT(t.id), SUM(f.ticketCost)) FROM " +
            "Ticket t JOIN t.flight f " +
            "WHERE t.status = 'SOLD' AND t.owner IS NOT NULL AND f.date BETWEEN '%s' and '%s' " +
            "GROUP BY f.id",
            startDate, endDate
    );

    return TicketDAO.query(SoldReportRow.class, query);
}

public static <T> List<T> query(Class<T> entityClass, String query) {
    EntityManager entityManager = getEntityManager();
    TypedQuery<T> q = entityManager.createQuery(query, entityClass);
    List<T> entities = null;

    try {
        entities = q.getResultList();
    } finally {
        entityManager.close();
    }

    return entities;
}

public static EntityManager getEntityManager() {
    return factory.createEntityManager();
}

问题是，为什么会发生这种情况以及如何解决？

谢谢!

Answer 1

经过研究，我发现问题出在数据库的数据上。默认情况下，SQLite 没有 DATE 列类型。它使用字符串来描述时间戳。因此，对于日期比较(就像 SELECT ... WHERE date BETWEEN a AND b)，最好使用 UTC 日期格式，而不是字符串一(1397036688 比2014-03-09)。