JPA: Predicate and expression both in QueryCriteria where clause

Question

I have a situation where in my where clause I have single predicate and expression. And both needs to be ANDed in where clause:

Expression<String> col1 = tableEntity.get("col1");
Expression<String> regExpr = criteriaBuilder.literal("\\.\\d+$");
Expression<Boolean> regExprLike = criteriaBuilder.function("regexp_like", Boolean.class, col, regExpr);

Expression<TableEntity> col2= tableEntity.get("col2");
Predicate predicateNull = criteriaBuilder.isNull(col2);

createQuery.where(cb.and(predicateNull));
createQuery.where(regExprLike);

In this case I am not able to do something like: createQuery.where(predicateNull, regExprLike);

I tried using CriteriaBuilder's isTrue() method:

Predicate predicateNull = criteriaBuilder.isNull(col2);
Predicate predicateTrue = criteriaBuilder.isTrue(regExprLike);
createQuery.where(predicateNull, predicateTrue);

But it didnt help.

CriteriaQuery either allows predicates or expressions, but not both, in where clause. any idea how can I use both, predicates and expression in QueryCriteria's where clause?

Update 10 Oct 2014: As suggested by Chris, I tried to use:

createQuery.where(predicateNull, regExprLike);

But my query fails with exception:

Caused by: org.jboss.arquillian.test.spi.ArquillianProxyException: org.hibernate.hql.internal.ast.QuerySyntaxException : unexpected AST node: ( near line 1, column 311 [select coalesce(substring(generatedAlias0.col1,0,(locate(regexp_substr(generatedAlias0.col1, :param0),
generatedAlias0.col1)-1)), generatedAlias0.col1), generatedAlias0.col1 
from com.temp.TableEntity as generatedAlias0 
where (generatedAlias0.col2 is null ) and ( regexp_like(generatedAlias0.col1, :param1))] [Proxied because : Original exception not deserilizable, ClassNotFoundException]

My code looks like:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> createQuery = criteriaBuilder.createQuery(Object[].class);

Root<TableEntity> tableEntity = createQuery.from(TableEntity.class);

Expression<String> path = tableEntity.get("col1");

Expression<String> regExpr = criteriaBuilder.literal("\\.\\d+$");
Expression<String> regExprSubStr = criteriaBuilder.function("regexp_substr", String.class, path, regExpr);

Expression<Boolean> regExprLike = criteriaBuilder.function("regexp_like", Boolean.class, path, regExpr);


Expression<Integer> l3 = criteriaBuilder.locate(path, regExprSubStr);
Expression<Integer> minusOne = criteriaBuilder.literal(1);
Expression<Integer> l3Sub1 = criteriaBuilder.diff(l3, minusOne);
Expression<Integer> zeroIndex = criteriaBuilder.literal(0);
Expression<String> s3 = criteriaBuilder.substring(path, zeroIndex, l3Sub1);

Expression<TableEntity> col1 = tableEntity.get("col1");
Expression<TableEntity> col2 = tableEntity.get("col2");

Expression<String> coalesceExpr = criteriaBuilder.coalesce(s3, path);
createQuery.multiselect(coalesceExpr, col1);

Predicate predicateNull = criteriaBuilder.isNull(col2);

createQuery.where(criteriaBuilder.and(predicateNull, regExprLike));
String query = entityManager.createQuery(createQuery).unwrap(org.hibernate.Query.class).getQueryString();

Deach

Have you tried simply adding your predicate and expression together? createQuery.where(criteriaBuilder.and(predicateNull, regExprLike)); – Footstall

Yes I tried. I do not get any compile time error, but my query generation fails. I have updated my question with latest updates. Please let me know, if you have more information. Thanks. – Deach

@Crystal, I could not figure it that time. So I used native query implementation. I wonder if someone else has figured out. – Deach

Answer 1

I think your problem is oracle does not classify 'regexp_like' as a function. To make it work, you have to extend Oracle dialect with new registered function:

 public class Oracle12cExtendedDialect extends Oracle12cDialect {

public Oracle12cExtendedDialect() {
    super();
    registerFunction(
            "regexp_like", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN,
                    "(case when (regexp_like(?1, ?2)) then 1 else 0 end)")
    );
}
}

and then you can change your where clause:

        createQuery.where(criteriaBuilder.and(predicateNull, criteriaBuilder.equal(regExprLike, 1)));

Of course, your have to register your new dialect in persistence.xml

            <property name="hibernate.dialect" value="path.to.your.dialect.class.Oracle12cExtendedDialect" />

Knownothing

Somehow I thought your approach gave me same errors cause I tried "createQuery.where(criteriaBuilder.and(predicateNull, regExprLike))". But I realized my mistake and I tried exactly as suggested. And bammm.. It worked like a charm :) Many thanks for your time and efforts :) – Deach