Case insensitive JPQL

There is no case insensitive comparison in SQL.
Thus, to write a case insensitive SQL, we have to first change the both parameter and value to same case.

The same concept is apply to JPQL as well.

below is the example of case insensitive JPQL.

String caseInsensitiveJPQL = " SELECT a FROM ENTITY a "

caseInsensitiveJPQL += " WHERE UPPER(a.PARAM1) = '" + value1.toUpperCase() "' ";

in the above example, I convert both PARAM1 and value1 to upper case.


Done!!

How to programmatically append "WHERE" keyword

When querying database from program (Java, .Net, PHP, etc) with criteria (WHERE and/or AND clause), normally we will need to check the existence of WHERE or whether this is the first criteria and programmatically append the WHERE or AND keyword before search criteria..
eg.

String myQuery = "SELECT * FROM TABLE_A";
if(!myQuery.contains("WHERE")) {
    myQuery.append(" WHERE ");
} else {
    myQuery.append(" AND ");
}
// append criteria

JPQL IN clause

There are two ways to write IN clause in JPQL

1. conventional way

    Pass in a comma separated String into the query 

 

    StringBuffer jpql = new StringBuffer();
    jpql.append("SELECT a FROM TABLE_A a");
    jpql.append("WHERE a.id IN (:idList)");

    Query query = entityManager.createQuery(jpql.toString());
    query.setParameter("idList", idList);

        in this case, idList is a comma separated string, eg. "1, 2, 3, 4"