Build condition with date for jpa2 typesafe query

I have following query:

SELECT DISTINCT * 
FROM Projekt p
WHERE p.bewilligungsdatum = to_date('01-07-2000', 'dd-mm-yyyy')

but i have problems to build the conditions. Here my code:

condition = criteriaBuilder.equal((Expression<String>) projekt.get(criterion), "to_date('" + projektSearchField + "', 'dd-mm-yyyy')");

this generate following:

SELECT  DISTINCT * 
FROM Projekt p 
WHERE p.bewilligungsdatum = 'to_date('01-07-2000', 'dd-mm-yyyy')'

and u开发者_开发问答fcorse doesn't work. Which method should i use for date comparision (or how to remove the outer ' chars in the pattern part)?

why don't you try to work with parameters like that. Then you can do the String->Date conversion in java and pass a real java.util.Date to the database.

EntityManager em; // initialized somewhere
Date datum; // initialized somewhere

...

String queryString = "SELECT p " 
             + "FROM Projekt p"
             + "WHERE p.bewilligungsdatum = :datum";

Query query = em.createQuery(queryString)

query.setParameter("datum", datum);

List<Projekt> projekte = query.getResultList();

This is the way to stay DB independent because your are not using the specific to_date function

viele Grüße aus Bremen ;o)

This should work too, by passing a date as parameter of a restriction

Date datum; // initialized somewhere

CriteriaQuery query = ...

query.add(Restrictions.eq( "bewilligungsdatum ", datum );

...

Sorry. I had the hibernate CriteriaQuery in mind.

Then try via the CriteriaBuilder somthing like that

Date datum; // initialized somewhere

...

final CriteriaQuery<Projekt> query = criteriaBuilder.createQuery(Projekt.class);
final Root<Projekt> projekt = query.from(Projekt.class);

Predicate condition = criteriaBuilder.equals(projekt.get("bewilligungsdatum"),datum);
query.where(condition)

I did not use this before, so have a try on your own

you can use https://openhms.sourceforge.io/sqlbuilder/ ,then use the Condition like

Object value1 = hire_date
Object value2 = new CustomObj("to_date('2018-12-01 00:00:00','yyyy-MM-dd HH:mm:ss')")

//CustomObj 
public class CustomObj extends Expression {
    private Object _value;

    public CustomObj(Object value) {
        _value = value;
    }

    @Override
    public boolean hasParens() {
        return false;
    }

    @Override
    protected void collectSchemaObjects(ValidationContext vContext) {
    }

    @Override
    public void appendTo(AppendableExt app) throws IOException {
        app.append(_value);
    }
}

BinaryCondition.greaterThan(value1, value2, inclusive);

the sql like hire_date >= to_date('2011-02-28 00:00:00','yyyy-MM-dd HH:mm:ss'))