Issue
I'm trying to utilize a registered function from my custom hibernate dialect inside of a formula field. The problem is that the formula field does not utilize my registered function. I'm trying to figure out what I'm doing wrong.
For background, I have an application that I'm working to make functional for both Oracle and Postgresql. Not simultaneously, but for whichever database its being deployed to. I have several formula fields in my models that are used to aggregate the names of OneToMany mapped entities into a single comma-delimited list for easy searching and display. This was done utilizing LISTAGG when it was purely Oracle. That won't work in Postgresql, but given that it needs to work with both environments, I can't just change the syntax of my Formula to STRING_AGG. So, I'm attempting to register a function for both that will utilize the appropriate format for whichever database is being used.
I'm using Custom Dialect extensions and registering my functions, but it doesn't utilize my registered function. I'm not sure what I'm doing wrong.
If this isn't actually possible and I'm approaching this from the wrong direction, is there a good approach to defining the formula fields dynamically? Not during runtime, but during compile time when the dialect is set?
public class CustomPostgresqlDialect extends PostgreSQL95Dialect {
public CustomPostgresqlDialect() {
super();
registerFunction("MY_LISTAGG", new SQLFunctionTemplate( StandardBasicTypes.STRING, " STRING_AGG(?1 , ', ' ORDER BY ?1) "));
}
}
...
public class CustomOracleDialect extends Oracle12cDialect {
public CustomOracleDialect() {
super();
registerKeyword("WITHIN");
registerFunction("MY_LISTAGG", new SQLFunctionTemplate( StandardBasicTypes.STRING,"LISTAGG(?1,', ') WITHIN GROUP(ORDER BY ?1)"));
}
}
And here is my model with the formula:
public class Contractor extends Object implements Serializable {
...
@OneToMany(mappedBy = "contractor", fetch = FetchType.LAZY)
private Set<ProjectManager> projectManagers;
...
@Formula("(" +
"SELECT\r\n" +
" MY_LISTAGG(PM.NAME)\r\n" +
"FROM\r\n" +
" PROJECTMANAGERS PM\r\n" +
" INNER JOIN CONTRACTORS C ON C.ID = PM.FK_CONTRACTOR\r\n" +
"WHERE\r\n" +
" C.ID = id" +
")"
)
@NotAudited
private String pmNames;
...
}
Solution
You can not use @Formula
for this purpose. As it's stated in the documentation:
You should be aware that the
@Formula
annotation takes a native SQL clause which may affect database portability.
You can try to use JPQL/Criteria query for this.
Answered By - SternK