Use Hibernate Registered Function in Formula Field

user_1956139 0
Viewed 463

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;

    ...
}
jpa hibernate java
2 Answers

Like SternK wrote, it's not possible to use JPQL/HQL functions in @Formula and I would also advise against using subqueries in formulas in general as that will incur the penalty of always executing these subqueries even if you don't need the data.

I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Contractor.class)
public interface ContractorDto {
    @IdMapping
    Long getId();
    @Mapping(value = "projectManagers.name", fetch = MULTISET)
    Set<String> getPmNames();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

ContractorDto a = entityViewManager.find(entityManager, ContractorDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<ContractorDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary! So in this case, a SQL query like the following would be created:

select 
  c.id, 
  (
    select json_agg(json_object(c1, pm.name)) 
    from project_manager pm 
    where pm.fk_contractor = c.id
  )
 from contractor c

If you really want an aggregated string, you could also use the GROUP_CONCAT function as provided by Blaze-Persistence within the mapping:

@EntityView(Contractor.class)
public interface ContractorDto {
    @IdMapping
    Long getId();
    @Mapping("GROUP_CONCAT(projectManagers.name, 'SEPARATOR', ', ', 'ORDER BY', projectManagers.name,, 'ASC')")
    String getPmNames();
}
user_4124460

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.

user_62771040
Related