Hibernate 6.x - How to register a PostgreSQL function returning a table for use in JPQL? - Stack Overflow

admin管理员组

文章数量:1406453

i'm trying to register a PostgreSQL custom function in Hibernate 6.x so that I can use it directly in JPQL, without relying on native queries. Scenario

I have a PostgreSQL SQL function that returns a table with multiple columns:

CREATE FUNCTION get_ordine_dettagli(p_id BIGINT, p_data DATE) RETURNS TABLE(id BIGINT, prodotto TEXT, quantita INT, totale NUMERIC) AS $$ BEGIN RETURN QUERY SELECT o.id, p.nome, o.quantita, (o.quantita * p.prezzo) FROM ordine o JOIN prodotto p ON o.prodotto_id = p.id WHERE o.id = p_id AND o.data_ordine = p_data; END; $$ LANGUAGE plpgsql;

I would like to register this function in the Hibernate Dialect so that it can be used directly in JPQL, just like a normal HQL query:

@Query("SELECT o FROM get_ordine_dettagli(:id, :data) o") List<OrdineDettagli> getDettagliOrdine(@Param("id") Long id, @Param("data") LocalDate data);

Is there a way to register a SQL function that returns a table in Hibernate and have it automatically mapped to a custom DTO in JPQL?

If this is not possible, what is the recommended approach to handle this type of scenario in Hibernate 6.x?

Thanks in advance for your support!

i'm trying to register a PostgreSQL custom function in Hibernate 6.x so that I can use it directly in JPQL, without relying on native queries. Scenario

I have a PostgreSQL SQL function that returns a table with multiple columns:

CREATE FUNCTION get_ordine_dettagli(p_id BIGINT, p_data DATE) RETURNS TABLE(id BIGINT, prodotto TEXT, quantita INT, totale NUMERIC) AS $$ BEGIN RETURN QUERY SELECT o.id, p.nome, o.quantita, (o.quantita * p.prezzo) FROM ordine o JOIN prodotto p ON o.prodotto_id = p.id WHERE o.id = p_id AND o.data_ordine = p_data; END; $$ LANGUAGE plpgsql;

I would like to register this function in the Hibernate Dialect so that it can be used directly in JPQL, just like a normal HQL query:

@Query("SELECT o FROM get_ordine_dettagli(:id, :data) o") List<OrdineDettagli> getDettagliOrdine(@Param("id") Long id, @Param("data") LocalDate data);

Is there a way to register a SQL function that returns a table in Hibernate and have it automatically mapped to a custom DTO in JPQL?

If this is not possible, what is the recommended approach to handle this type of scenario in Hibernate 6.x?

Thanks in advance for your support!

• postgresql
• hibernate
• hql

Share Follow asked Mar 5 at 18:46 Oscar FanchinOscar Fanchin 911 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You can create a class that implements .hibernate.boot.model.FunctionContributor Then you register this class by creating a file named:

.hibernate.boot.model.FunctionContributor

Place this file in :

src/main/resources/META-INF/services

In this file you place the fully qualified class name of the FunctionContributor implementation you created. The Hibernate ServiceLoading mechanism will register your function and then you can use it in your jpql queries. For instance, I created this FunctionContributor for Oracle:

public class IsValidFunctionContributor implements FunctionContributor {

@Override
public void contributeFunctions(final FunctionContributions functionContributions) {
    functionContributions.getFunctionRegistry().registerPattern(
            "isvalid", "SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(?1, ?2)",
            functionContributions.getTypeConfiguration()
                                 .getBasicTypeRegistry().resolve(StandardBasicTypes.STRING));
}

}

Now you can use the functionname in your @Query annotations.

本文标签： Hibernate 6xHow to register a PostgreSQL function returning a table for use in JPQLStack Overflow