GORM: Challenges migrating away from deprecated Criteria API
Description
Activity
Christian Beikov August 17, 2022 at 3:12 PM
So let me point you to APIs through which you can implement the following:
Use
jakarta.persistence.criteria.CriteriaBuilder#betweenandjakarta.persistence.criteria.CriteriaBuilder#coalesceUse
jakarta.persistence.criteria.CriteriaBuilder#lessThanOrEqualToandjakarta.persistence.criteria.CriteriaBuilder#sumwith unsafe casts (please create an issue for improving this by adding new methods toHibernateCriteriaBuilder)This is just a regular subquery. Unless you don’t have entities mapped for the tables, you can just use the regular
CriteriaQuery#subqueryandjakarta.persistence.criteria.CriteriaBuilder#inas well asjakarta.persistence.criteria.CriteriaBuilder#notAPIs
For the other examples, follow the same advice as in 3. and just use the subquery APIs. I know the JPA Criteria API is not as nice as simply writing a String, but overall, the end result will be easily portable, not only across JPA providers, but also across databases.
Eduardo Simioni August 17, 2022 at 2:53 PM
Some examples:
1)
criteria.add(Restrictions.sqlRestriction(" ? between starts_on and IFNULL(ends_on, current_date)", getTodayNoTime(), CalendarType.INSTANCE));2)
criteria.add(Restrictions.sqlRestriction("CAST(TIMESTAMPADD(SQL_TSI_DAY, -({alias}.days_before_send), {alias}.next_on) as DATE) <= ?", getTodayNoTime(), CalendarDateType.INSTANCE));3)
criteria.add(Restrictions.sqlRestriction("this_.id not in(select cgc.customer_id from customer_group cg inner join customer_group_customer cgc on(cg.id = gc.customer_group_id) where cg.account_id = this_.account_id)"));4)
Criteria criteria = createEntityCriteriaBypassAccount();
criteria.add(Restrictions.sqlRestriction("this_.id NOT IN (SELECT daa.digital_account_id FROM digital_account_attribute daa WHERE daa.name " +
"= ?) AND this_.id IN (SELECT DISTINCT p.payee_id FROM payment p WHERE p.release_date > ? AND p.type IN('CREDIT_CARD', " +
"'INSTALLMENT_CREDIT_CARD'))", new Object[]{DigitalAccountAttributeName.HAS_CREDIT_CARD_PAYMENT.name(), getCalendar()},
new Type[]{StringType.INSTANCE, CalendarType.INSTANCE}));
return criteria.list();5)
criteria.add(Restrictions.sqlRestriction("this_.id in(select max(pns.id) from payment_notification_status pns where pns.payment_id = ?)", paymentId, LongType.INSTANCE));6)
Calendar last24Hours = CalendarUtils.addDays(getCalendar(), -1);
Criteria criteria = createEntityCriteriaBypassAccount();
criteria.add(Restrictions.gt("createdOn", last24Hours));
Type[] types = {IntegerType.INSTANCE, IntegerType.INSTANCE, CalendarType.INSTANCE};
Object[] values = {ResponseStatus.OK, PaymentNotificationStatus.MAX_NOTIFICATION_ATTEMPTS, last24Hours};
String subQuery1 = "this_.payment_id NOT IN (SELECT pns.payment_id FROM payment_notification_status pns "
+ "WHERE (pns.status_code = ? OR pns.attempt_number=?) AND pns.created_on > ?)";
criteria.add(Restrictions.sqlRestriction(subQuery1, values, types));
String subQuery2 = "this_.id IN (SELECT max(pns2.id) FROM payment_notification_status pns2 "
+ "WHERE pns2.status_code <> ? AND pns2.attempt_number < ? AND pns2.created_on > ? GROUP BY pns2.payment_id)";
criteria.add(Restrictions.sqlRestriction(subQuery2, values, types));7)
Type[] types = {CalendarType.INSTANCE, CalendarType.INSTANCE, StringType.INSTANCE, CalendarType.INSTANCE, CalendarType.INSTANCE};
Calendar start = sanitizeStartDate(filter);
Calendar end = sanitizeEndDate(filter);
Object[] values = {start, end, PaymentStatus.CONFIRMED.name(), start, end};
String subQuery = "select max(payment_.id) from payment payment_ inner join charge charge1_ on payment_.charge_id=charge1_.id where payment_.type in ('"
+ PaymentType.CREDIT_CARD + "', '" + PaymentType.INSTALLMENT_CREDIT_CARD
+ "') and ((payment_.date >= ? and payment_.date < ? and payment_.status = ? and payment_.partial_refund = 0"
+ ") or (payment_.updated_on >= ? and payment_.updated_on < ? and payment_.status in('" + PaymentStatus.CUSTOMER_PAID_BACK + "', '"
+ PaymentStatus.BANK_PAID_BACK + "'))) group by charge1_.charge_group_id";
criteria.add(sqlRestriction("this_.id in (" + subQuery + ")", values, types));Christian Beikov August 17, 2022 at 6:49 AM
Please share with us what kind of sql restrictions you think are impossible to model in HQL/Criteria. We added support for so many SQL constructs that I am really curious what you can’t model yet.
Eduardo Simioni August 16, 2022 at 5:50 PMEdited
What about the criteria.add(Restrictions.sqlRestriction()) option?
I’ve started a branch to migrate my company’s code to Hibernate 6 (Hibernate Criteria to JPA basically), but I can’t complete this migration without an sql restriction alternative. We are using only criteria, no HQL.
I’ve got very far and thought of possible alternative solutions, but in the end, although we have just 31 usages in a very large project, I could only migrate a few of them to other solutions, for all the rest, a replacement to SQL Restriction is absolutely needed.
Christian Beikov February 3, 2022 at 10:12 AMEdited
There is a new function in HQL named sql that accepts varargs which can be used to insert SQL fragments. I think you could use that as well to add support for RLike.
Query by example can be implemented manually as well. You just need to extract values from the entity and generate equality predicates.
Details
Details
Assignee
Reporter
Labels
Priority
Major
The old Criteria API has been deprecated and Hibernate prints warnings about this fact. This represents a problem for GORM which is used by thousands of Grails developers world wide and I imagine contributes a reasonable portion to the Hibernate user base.
The reason this represents a problem is there are functional gaps between what is possible with Hibernate Criteria API and what is possible with JPA criteria that we (the GORM team) don't know how to solve to move completely away from Hibernate criteria.
These include:
An equivalent of Restrictions.sqlRestriction method for custom SQL restrictions
How define custom criterion (for example for our support for RLIke) https://github.com/grails/gorm-hibernate5/blob/master/grails-datastore-gorm-hibernate5/src/main/groovy/grails/orm/RlikeExpression.java
No API to do query by example
Apart from that GORM uses the API extensively and it represents a massive task that is maybe impossible to achieve without breaking user applications. The following classes use the API extensively:
https://github.com/grails/gorm-hibernate5/blob/master/grails-datastore-gorm-hibernate5/src/main/groovy/org/grails/orm/hibernate/query/AbstractHibernateCriteriaBuilder.java
https://github.com/grails/gorm-hibernate5/blob/master/grails-datastore-gorm-hibernate5/src/main/groovy/org/grails/orm/hibernate/query/AbstractHibernateQuery.java
This issue requests that either the deprecation warnings be removed or that the functional gaps needed for us to completely move away from these APIs are closed so that we can update GORM without breaking existing Grails/GORM applications out there (and there are many).