CODEWITHSUNDEEP
javajpajpql
user3419507
user3419507

Reputation: 117

Extract Year from date field using jpql and jpa

I want to extract the year part from a row in the database in order to compare it with a value.

Here's my function

public List<Dossier> getAllDossierParAn() {
    Date date = new Date();
    Calendar calendar = new GregorianCalendar();
    calendar.setTime(date);
    int strI = calendar.get(Calendar.YEAR);
    TypedQuery<Dossier> query;
    query = em.createQuery("SELECT d FROM DOSSIER d WHERE EXTRACT(YEAR ,d.dateCreation)=2015", Dossier.class);
    System.out.println(strI);
    return query.getResultList();
}

I get always

An exception occurred while creating a query in EntityManager: Exception Description: Problem compiling [SELECT d FROM DOSSIER d WHERE EXTRACT(YEAR FROM d.dateCreation)=2015]. [14, 21] The abstract schema type 'DOSSIER' is unknown. [48, 62] The state field path 'd.dateCreation' cannot be resolved to a valid type.

I test it directly in the database and it works

select * from dossier where extract(year from date_creation)=2015

I'm using jpa and ejb and jdeveloper as IDE.

Upvotes: 6

Views: 28111

Answers (4)

Bwire
Bwire

Reputation: 1201

On eclipselink, the way that works for me was something like:

SELECT  a.id, EXTRACT(WEEK CURRENT_DATE ) FROM Account a

This works on postgres and sql server at least but should work with other supported databases too.

From javadocs the syntax supported is :

extract_expression ::= EXTRACT(date_part_literal [FROM] scalar_expression)

The FROM seems to cause funny exceptions on sql server.

Upvotes: 0

user3419507
user3419507

Reputation: 117

thank you guys i solved the problem: first i got the current year than format it to int than to String in order to do the comparaison and substract it here's my code it work fine:

    public List<Dossier> getAllDossierParAn() {
    Date date = new Date();
    Calendar calendar = new GregorianCalendar();
    calendar.setTime(date);
    int strI = calendar.get(Calendar.YEAR);
   String strInt  =Integer.toString(strI);
    String nvlstri= strInt.substring(2, 4);
    TypedQuery<Dossier> query;
    query = em.createQuery("SELECT d FROM Dossier d WHERE SUBSTRING(d.dateCreation, 7, 2) = :vr", Dossier.class);
  query.setParameter("vr",nvlstri);
   System.out.println("l anne est " +strI);
   System.out.println("la date formaté " +nvlstri);
    return query.getResultList();
}

Upvotes: 0

renke
renke

Reputation: 1230

First, the main problem with your query is what the error message say:

The abstract schema type 'DOSSIER' is unknown

Since JPA is mapping your POJOs as entities, their names are case sensitive. Your query should be: SELECT d FROM Dossier d WHERE ...

Also, regarding the problem you mentioned, the EXTRACT function is only supported by EclipseLink, as far as I know. By the error message, I think this is your JPA implementation, but if it's not, there are two options:

  1. If you're using Hibernate, it has built in functions for retrieving date parts, such as YEAR(date), MONTH(date), DAY(date), HOUR(date), MINUTE(date) and SECOND(date).
  2. For any other JPA implementation, or if you want to keep it JPQL compliant, you can workaround with SUBSTRING: SUBSTRING(d.dateCreation, 1, 4). Note the first position of a string is denoted by 1;

Hope it helps

Upvotes: 6

Neil Stockton
Neil Stockton

Reputation: 11531

"Directly in the database" is called SQL.

createQuery takes in JPQL not SQL, and YEAR / EXTRACT are invalid keywords (though YEAR, but not EXTRACT, is supported by some JPA providers). Any decent JPA docs would spell that out.

Upvotes: 1

Related Questions