Compare two dates with JPA

Question

Hello everybody,

I need to compare two dates in a JPQL query but it doesn't work.

Here is my query:

Query query = em.createQuery("SELECT h FROM PositionHistoric h, SeoDate d WHERE h.primaryKey.siteDb = :site AND h.primaryKey.engineDb = :engine AND h.primaryKey.keywordDb = :keyword AND h.date = d AND d.date <= :date ORDER BY h.date DESC");

My parameter date is a java.util.Date

My query return a objects list but the dates are upper and lower to my parameter.

Someone kown how to do this ?

Thanks.

Answer 1

I suggest to use java.sql.Date instead of java.util.Date

Because you pass it to a JPQL(kind of SQL).

I usually use TIMESTAMP instead of Date, but if you didn't have the choice..

And is it : h.date = d.date instead h.date = d ?

Answer 2

For nettogrof:

I try to passe my date in java.sql.Date and I obtain this message:

 @Temporal should only be set on a java.util.Date or java.util.Calendar

For ChssPly76, my classes:

SeoDate

    @Id
@Column(name = "date_db", nullable = false)
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer dateDb;
@Column(name = "date_date", nullable = false)
@Temporal(TemporalType.TIMESTAMP)
private Date date;
@Column(name = "date_year", nullable = false)
private Integer year;
@Column(name = "date_quarter", nullable = false)
private Integer quarter;
@Column(name = "date_month", nullable = false)
private Integer month;
@Column(name = "date_week", nullable = false)
private Integer week;
@Column(name = "date_day", nullable = false)
private Integer day;
@Column(name = "date_hour", nullable = false)
private Integer hour;

And PositionHistoric:

@EmbeddedId
private PrimaryPositionHistoric primaryKey;

@Column(name = "position", nullable = false)
private Integer position;

@Column(name = "position_double", nullable = false)
private Boolean positionDouble;

@ManyToOne(fetch=FetchType.LAZY, targetEntity=KeywordSite.class)
@JoinColumns({@JoinColumn(nullable=false, name="keyword_db", insertable=false, updatable=false),
@JoinColumn(name="site_db", nullable=false, insertable=false, updatable=false)})
private KeywordSite keyword;

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="site_db", nullable=false, insertable=false, updatable=false)
private Site site;

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name = "page_db", nullable = true)
private Page page;

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name = "engine_db", nullable = false, insertable=false, updatable=false)
private fr.seostrategies.dao.Engine engine;

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name = "date_db", nullable = false)
private SeoDate date;

Answer 3

The query you should use is:

 FROM PositionHistoric h
   INNER JOIN FETCH h.date
WHERE h.primaryKey.siteDb = :site
  AND h.primaryKey.engineDb = :engine
  AND h.primaryKey.keywordDb = :keyword
  AND h.date.date <= :date

Note that ordering by h.date is rather pointless (as it points to SeoDate entity and thus in effect you're ordering by PK) and you can't order by h.date.date since SeoDate is mapped as many-to-one.

Also note that INNER JOIN FETCH h.date line is not necessary per se but it will save you some overhead on additional lazy queries IF you will actually need to use SeoDate attributes in the returned list.