fail with registerCustomSqlFunction

[phdbutbachelor]

I'm trying to add the MySQL customer function into jinq. I defined the function in database as:

CREATE FUNCTION `date_formatter`(a date, b varchar(16)) RETURNS varchar(16) CHARSET utf8mb4
BEGIN
    RETURN(date_format(a, b));
END

then I registerd it into jinq as:

this.jinqJPAStreamProvider.registerCustomSqlFunction(MySqlFuntions.class.getDeclaredMethod("format", Date.class, String.class), "date_formatter");

and used it like:

List<Pair<String, Long>> _counts = stream(VisitForm.class)
                .group(i -> MySqlFuntions.format(i.getCreateTime(), "%Y%m%d"), (key, value) -> value.count())
                .toList();

then the program threw exception below:

2022-11-27 22:45:27.014 [ERROR] @http-nio-80-exec-9: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 +-[METHOD_CALL] MethodNode: 'function (date_formatter)'
 |  +-[METHOD_NAME] IdentNode: 'date_formatter' {originalText=date_formatter}
 |  \-[EXPR_LIST] SqlNode: 'exprList'
 |     +-[DOT] DotNode: 'visitform0_.createTime' {propertyName=createTime,dereferenceType=PRIMITIVE,getPropertyPath=createTime,path=A.createTime,tableAlias=visitform0_,className=com.chinamobile.healthcode.model.migrant.VisitForm,classAlias=A}
 |     |  +-[ALIAS_REF] IdentNode: 'visitform0_.id' {alias=A, className=com.chinamobile.healthcode.model.migrant.VisitForm, tableAlias=visitform0_}
 |     |  \-[IDENT] IdentNode: 'createTime' {originalText=createTime}
 |     \-[QUOTED_STRING] LiteralNode: ''%Y%m%d''
 [SELECT function('date_formatter', A.createTime, '%Y%m%d'), COUNT(A) FROM com.chinamobile.healthcode.model.migrant.VisitForm A GROUP BY function('date_formatter', A.createTime, '%Y%m%d')]] with root cause
org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 +-[METHOD_CALL] MethodNode: 'function (date_formatter)'
 |  +-[METHOD_NAME] IdentNode: 'date_formatter' {originalText=date_formatter}
 |  \-[EXPR_LIST] SqlNode: 'exprList'
 |     +-[DOT] DotNode: 'visitform0_.createTime' {propertyName=createTime,dereferenceType=PRIMITIVE,getPropertyPath=createTime,path=A.createTime,tableAlias=visitform0_,className=com.chinamobile.healthcode.model.migrant.VisitForm,classAlias=A}
 |     |  +-[ALIAS_REF] IdentNode: 'visitform0_.id' {alias=A, className=com.chinamobile.healthcode.model.migrant.VisitForm, tableAlias=visitform0_}
 |     |  \-[IDENT] IdentNode: 'createTime' {originalText=createTime}
 |     \-[QUOTED_STRING] LiteralNode: ''%Y%m%d''

I don't know what's wrong with my code, it seems that generated sql "SELECT function('date_formatter', A.createTime, '%Y%m%d')" is illegal for "select date_formatter(A.createTime, '%Y%m%d')" is the correct one?

[my2iu]

Did you register the function in Hibernate as well?

[my2iu]

To be honest, I didn’t even remember that Jinq had this functionality, so I could be mistaking things, but after dumpster diving through the code, I did find this code snippet that seems to register a SQL function in Hibernate:

  {
     Query q = session.createNativeQuery("CREATE FUNCTION isRegexMatch( str VARCHAR(200), regex VARCHAR(200) ) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL EXTERNAL NAME 'org.jinq.hibernate.CreateHibernateDb.isRegexMatch_DbFunction' ");
     q.executeUpdate();
  }

[my2iu]

Or maybe not? To be honest, I no longer remember exactly how any of this works. If you can’t figure it out, let me know and I can look more deeply into the code.

[my2iu]

Actually, maybe you don’t need to register anything. Yeah, I don’t know. I don’t even remember implementing this functionality, let alone whether I tested it with a database other than Apache Derby.

This page here

https://thorben-janssen.com/database-functions/

Seems to say that if Hibernate can’t figure out how to type a function, you may need to register the types with Hibernate using some messy process.

[phdbutbachelor]

Sorry, I misunderstood the usage of registerCustomSqlFunction, I already solved this problem.