Complete Support for JPA Criteria API

[varvay]

Feature description

Micronaut implementation of JPA criteria API is lacking of essential features that makes it almost impracticable. For example, it currently not supporting a very simple feature like upper, concat and fetch operation, which I believe are required in most projects. Here are list of features that I considered as essential to be implemented,

• fetch
• neg
• abs
• sum
• prod
• diff
• quot
• mod
• sqrt
• toLong
• toInteger
• toFloat
• toDouble
• toBigDecimal
• toBigInteger
• toString
• isEmpty
• isNotEmpty
• size
• isMember
• isNotMember
• concat
• subString
• trim
• lower
• upper
• length
• locate
• currentDate
• currentTimestamp
• currentTime
• coalesce
• nullif
• selectCase
• function
• treat
• localDate
• localDateTime
• localTime
• sign
• ceiling
• floor
• exp
• ln
• power
• round

[dstepanov]

I don't know if all the methods make sense without JPA. It would be nice to have an SQL example for each.

[voronaam]

My personal opinion is that most of those would be covered by the function() support in the query builder. Note that Micronaut documentation on Criteria API states that static query is a preferred way to go in Micronaut, and with @Query annotation there is no problem with any of those functions. We are embracing the precompiled queries approach.

The only use case for Criteria API is highly dynamic Search queries, where the user has a lot of optional filters to enable and API has a lot of nullable fields. We are actually supporting those via the static queries as well, they are just overly long.

For example, optional filtering by subfields in a JSONB field user looks something like this in a @Query:

AND (:originalUser IS NULL OR user ->> 'original' = :originalUser)
AND (:modifiedUser IS NULL OR user ->> 'modified' = :modifiedUser)

I'd be fine with Criteria API being a bit more limited - it is fine to push the developers towards the better-performing pre-compiled queries in most cases.

[dstepanov]

Yeah, I want to support JSON accessors too in criteria. I think that is going to be possible with the methods too

[stevenlmcgraw]

I came across this trying to see if some currently unsupported criteria features are on the docket for support, so if I may chime in with a more "bare-minimum" of what would get the Micronaut Criteria API fully viable at least in the use case I am facing:

I believe the io.micronaut.data.model.jpa.criteria.IExpression various overridden ::in methods and the ::as method would do the trick in my case.

I don't have an example of how we are using this in a manner that fails readily available but I will try to provide one in the next few days or so.

[dstepanov]

The latest version supports some of the methods mentioned:

• fetch
• neg
• abs
  - sum
• prod
• diff
• quot
• mod
• sqrt
• toLong
• toInteger
• toFloat
• toDouble
• toBigDecimal
• toBigInteger
• toString
  - isEmpty
  - isNotEmpty
• size
• isMember
• isNotMember
  - concat
• subString
• trim
  - lower
  - upper
• length
• locate
• currentDate
• currentTimestamp
• currentTime
• coalesce
• nullif
• selectCase
  - function
• treat
• localDate
• localDateTime
• localTime
• sign
• ceiling
• floor
• exp
  - ln
• power
• round

[voronaam]

Thank you! Is function supported now as well? Something like

criteriaBuilder.like(
  criteriaBuilder.function("jsonb_extract_path_text", String.class,  root.get("json"), criteriaBuilder.literal("author")), 
  "%john%"
),

[voronaam]

Looks like it does! The new code (https://github.com/micronaut-projects/micronaut-data/blob/4.10.x/data-model/src/main/java/io/micronaut/data/model/jpa/criteria/impl/AbstractCriteriaBuilder.java#L1364) does not have throw CriteriaUtils.notSupportedOperation() anymore!

Thank you a huge ton for doing it!