How to use LIKE clause in JPQL?

Question

I have a table named orders and My purpose is getting all orders which ends with 2012. My date is VARCHAR and I'm using MY-SQL database. In a class, I run the following JPQL query and I got

Syntax error parsing the query [select object(o) from Orders as o WHERE o.orderDate LIKE %2012], line 1, column 58: unexpected char [%].

I tried with this,

public List<Orders> getOrdersInCurrentYear(String def) {
    EntityManager em = getEntityManager();
    try {
        Query q = em.createQuery("select object(o) from Orders as o WHERE o.orderDate LIKE :def");
        q.setParameter("def", "%" + def);
        return q.getResultList();
    } finally {
        em.close();
    }
}

How can I get the all orders that ends with 2012 using this method?

Thanks in Advance.

Answer 1

You cannot use LIKE operator for DATE AND DATETIME data type.

There are some function for date, month, year, etc. Most of the Persistence Provider supported.

FUNC('MONTH', date)
FUNC('YEAR', date)

For eclipselink

select object(o) from Orders as o WHERE FUNC('YEAR', o.orderDate) = :def

Parameter

q.setParameter("def", def);