Im using Wildfly 20 including Hibernate Core 5.3.17.Final.
Using JPA Criteria API to create a query.
Root<MbiAestt> mbiAestt = cq.from( MbiAestt.class );
Expression<Integer> monthFromDate = cb.function( "month", Integer.class, mbiAestt.get( MbiAestt_.zpktDatum ) );
Expression<Integer> dayFromDate = cb.function( "day", Integer.class, mbiAestt.get( MbiAestt_.zpktDatum ) );
cq.select( cb.construct( Triplet.class, monthFromDate, dayFromDate, cb.sum( mbiAestt.get( MbiAestt_.nettoLw ) ) ) );
...
cq.groupBy( monthFromDate, dayFromDate );
I created my own InformixDialect to overwrite day, month and year functions.
public class Informix12Dialect extends Informix10Dialect
{
public Informix12Dialect( )
{
super();
this.registerFunction( "day", new SQLFunctionTemplate( StandardBasicTypes.INTEGER, "day(?1)" ) );
this.registerFunction( "month", new SQLFunctionTemplate( StandardBasicTypes.INTEGER, "month(?1)" ) );
this.registerFunction( "year", new SQLFunctionTemplate( StandardBasicTypes.INTEGER, "year(?1)" ) );
}
}
Hibernate loggin output:
17.06. 00:33:01,804 INFO [stdout#write] Hibernate:
17.06. 00:33:01,808 INFO [stdout#write] /* select
17.06. 00:33:01,810 INFO [stdout#write] new biz.mbisoftware.fn.ejb.session.sales.Statistics$Triplet(function('month',
17.06. 00:33:01,811 INFO [stdout#write] generatedAlias0.zpktDatum),
17.06. 00:33:01,813 INFO [stdout#write] function('day',
17.06. 00:33:01,815 INFO [stdout#write] generatedAlias0.zpktDatum),
17.06. 00:33:01,817 INFO [stdout#write] sum(generatedAlias0.nettoLw))
17.06. 00:33:01,819 INFO [stdout#write] from
17.06. 00:33:01,821 INFO [stdout#write] MbiAestt as generatedAlias0
17.06. 00:33:01,823 INFO [stdout#write] where
17.06. 00:33:01,825 INFO [stdout#write] (
17.06. 00:33:01,827 INFO [stdout#write] generatedAlias0.zpktDatum between :param0 and :param1
17.06. 00:33:01,829 INFO [stdout#write] )
17.06. 00:33:01,833 INFO [stdout#write] and (
17.06. 00:33:01,835 INFO [stdout#write] generatedAlias0.aktuell=:param2
17.06. 00:33:01,837 INFO [stdout#write] )
17.06. 00:33:01,838 INFO [stdout#write] group by
17.06. 00:33:01,840 INFO [stdout#write] function('month',
17.06. 00:33:01,842 INFO [stdout#write] generatedAlias0.zpktDatum),
17.06. 00:33:01,845 INFO [stdout#write] function('day',
17.06. 00:33:01,847 INFO [stdout#write] generatedAlias0.zpktDatum) */ select
17.06. 00:33:01,849 INFO [stdout#write] month(mbiaestt0_.zpkt_datum) as col_0_0_,
17.06. 00:33:01,851 INFO [stdout#write] day(mbiaestt0_.zpkt_datum) as col_1_0_,
17.06. 00:33:01,853 INFO [stdout#write] sum(mbiaestt0_.netto_lw) as col_2_0_
17.06. 00:33:01,858 INFO [stdout#write] from
17.06. 00:33:01,864 INFO [stdout#write] mbi_aestt mbiaestt0_
17.06. 00:33:01,870 INFO [stdout#write] where
17.06. 00:33:01,880 INFO [stdout#write] (
17.06. 00:33:01,891 INFO [stdout#write] mbiaestt0_.zpkt_datum between ? and ?
17.06. 00:33:01,900 INFO [stdout#write] )
17.06. 00:33:01,909 INFO [stdout#write] and mbiaestt0_.aktuell=?
17.06. 00:33:01,911 INFO [stdout#write] group by
17.06. 00:33:01,913 INFO [stdout#write] month(mbiaestt0_.zpkt_datum) ,
17.06. 00:33:01,915 INFO [stdout#write] day(mbiaestt0_.zpkt_datum)
The group by statement is not valid for Informix.
Instead of using the functions again Informix requires to use the column aliases here.
Instead of “group by month(mbiaestt0_.zpkt_datum), day(mbiaestt0_.zpkt_datum)” this should be
“group by col_0_0_, col_1_0_”.
Is this achievable either by changing the javax.persistence.criteria.CriteriaQuery.groupBy(Expression<?>…) usage or enhancement of my InformixDialect ?