NamedQuery optional parameter, inconsistent data types

Question

I am executing the named query below:

@NamedQuery(name = Employment.EMPLOYEES,
                query = "select e from Employmente left join fetch e.person p where" +
                        " e.endDate is not null " +
                        " and (:paramFromDate is null or e.endDate >= :paramFromDate)" +
                        " and (:paramToDate is null or e.endDate <= :paramToDate)")

where paramFromDate and paramToDate is optional.

But the resulting query throws an error;

Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected DATE got BINARY

Any suggestions?

Answer 1

named queries are not dynamic, but you can try this solution :

@NamedQuery(name = Employment.EMPLOYEES, query = Employment.NAMED_QUERY)
@Entity
public class Employment{
    private static String NAMED_QUERY= "default query";
    //+your persistent fields/properties...
}

public String buildQuery(Date paramFromDate,Date paramToDate){

    if(paramFromDate != null ){
        NAMED_QUERY="your query using paramFromDate param";
    }
    if(paramToDate != null ){
        NAMED_QUERY="your query using paramToDate param";
    }
}

//and later in your code
Employment.buildQuery(paramFromDate,paramToDate);
TypedQuery<MyEntity> query = entityManager.createQuery(Employment.NAMED_QUERY , Employment.class);