Quarkus REST API与Hibernate/Panache-Endpoints查询Postgres存储过程

Question

有没有特别好的方法可以做到这一点？我使用Panache/Hibernate ORM扩展PanacheEntity来为新表的模式创建映射。使用Repository方法，所有工作都按预期进行，并且我有反映GET、PUTs等的正确endpoint。我当前的问题是，我试图使用一个完全不同的endpoint，它只在Postgresql函数/存储过程上执行GET，并在访问该endpoint时返回数据。这是终点--

import javax.inject.Inject;
import javax.persistence.EntityManager;
import javax.ws.rs.Consumes;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;



@Path("/holidays")
@Produces("application/json")
@Consumes("application/json")
public class HolidayResource {

    @Inject
    EntityManager entityManager;

    @GET
    public Holiday[] get() {
        return entityManager.createNamedQuery("Holidays.findAll", Holiday.class)
                .getResultList().toArray(new Holiday[0]);
    }
    
}

import java.sql.Date;
import java.sql.Timestamp;

import javax.persistence.Entity;
import javax.persistence.Id;

import org.hibernate.annotations.NamedNativeQuery;


@Entity
@NamedNativeQuery(name = "Holidays.findAll", query = "SELECT * FROM holidays.usa('NY', 2020, 2020)")
public class Holiday {


    public static enum Authority {
        federal,
        national,
        bank,
        provincial,
        state,
        informal,
        observance,
        shortened_work_day,
        optional,
        de_facto,
        religious,
        extra_work_day,
        municipal
    }

    @Id public long id;
    public Date datestamp;
    public String description;
    public Authority authority;
    public Boolean day_off;
    public Boolean observation_shifted;
    public Timestamp start_time;
    public Timestamp end_time;

}

java.lang.RuntimeException: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.IllegalArgumentException: SRCFG00013: No Converter registered for interface java.nio.file.Path
        at io.quarkus.vertx.http.runtime.VertxHttpRecorder.startServerAfterFailedStart(VertxHttpRecorder.java:223)
        at io.quarkus.vertx.http.runtime.devmode.VertxHttpHotReplacementSetup.handleFailedInitialStart(VertxHttpHotReplacementSetup.java:37)
        at io.quarkus.deployment.dev.RuntimeUpdatesProcessor.startupFailed(RuntimeUpdatesProcessor.java:662)
        at io.quarkus.deployment.dev.IsolatedDevModeMain.firstStart(IsolatedDevModeMain.java:137)
        at io.quarkus.deployment.dev.IsolatedDevModeMain.accept(IsolatedDevModeMain.java:378)
        at io.quarkus.deployment.dev.IsolatedDevModeMain.accept(IsolatedDevModeMain.java:56)
        at io.quarkus.bootstrap.app.CuratedApplication.runInCl(CuratedApplication.java:127)
        at io.quarkus.bootstrap.app.CuratedApplication.runInAugmentClassLoader(CuratedApplication.java:84)
        at io.quarkus.deployment.dev.DevModeMain.start(DevModeMain.java:144)
        at io.quarkus.deployment.dev.DevModeMain.main(DevModeMain.java:63)
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.IllegalArgumentException: SRCFG00013: No Converter registered for interface java.nio.file.Path
        at io.quarkus.runtime.configuration.ConfigInstantiator.handleObject(ConfigInstantiator.java:106)
        at io.quarkus.runtime.configuration.ConfigInstantiator.handleObject(ConfigInstantiator.java:57)
        at io.quarkus.vertx.http.runtime.VertxHttpRecorder.startServerAfterFailedStart(VertxHttpRecorder.java:195)
        ... 9 more
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.IllegalArgumentException: SRCFG00013: No Converter registered for interface java.nio.file.Path
        at io.quarkus.runtime.configuration.ConfigInstantiator.handleObject(ConfigInstantiator.java:106)
        at io.quarkus.runtime.configuration.ConfigInstantiator.handleObject(ConfigInstantiator.java:79)
        ... 11 more
Caused by: java.lang.RuntimeException: java.lang.IllegalArgumentException: SRCFG00013: No Converter registered for interface java.nio.file.Path
        at io.quarkus.runtime.configuration.ConfigInstantiator.handleObject(ConfigInstantiator.java:106)
        at io.quarkus.runtime.configuration.ConfigInstantiator.handleObject(ConfigInstantiator.java:79)
        ... 12 more
Caused by: java.lang.IllegalArgumentException: SRCFG00013: No Converter registered for interface java.nio.file.Path
        at io.smallrye.config.SmallRyeConfig.lambda$getConverter$2(SmallRyeConfig.java:292)
        at java.base/java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1705)
        at io.smallrye.config.SmallRyeConfig.getConverter(SmallRyeConfig.java:289)
        at io.quarkus.runtime.configuration.ConfigInstantiator.getConverterFor(ConfigInstantiator.java:121)
        at io.quarkus.runtime.configuration.ConfigInstantiator.getConverterFor(ConfigInstantiator.java:117)
        at io.quarkus.runtime.configuration.ConfigInstantiator.handleObject(ConfigInstantiator.java:91)
        ... 13 more

SELECT * from holidays.usa('NY', 2020, 2020);

datestamp       description                authority      day_off      observation_shifted   start_time   end_time        
[DATE]          [TEXT]                     [ENUM]         [BOOLEAN]    [BOOLEAN]             [TIME]       [TIME]
------------    -----------------------    -----------    ---------    -------------------   ----------   ----------
"2020-01-01"    "New Year's Day"           "federal"       true        false                 "00:00:00"   "24:00:00"
"2020-02-17"    "Family Day"               "provincial"    true        false                 "00:00:00"   "24:00:00

有没有更好的办法做到这一点？我尝试过的一些事情似乎把我引入了错误的圈子。

Answer 1

您可以定义@namedstoredprocedure:

@NamedStoredProcedureQuery(
    name = "Holidays.findAll", 
    procedureName = "holidays.usa", 
    resultClasses = Holiday.class, 
    parameters = {
            @StoredProcedureParameter(
                name = "city",
                type = String.class,
                mode = ParameterMode.IN
            ),
            @StoredProcedureParameter(
                name = "year",
                type = Integer.class,
                mode = ParameterMode.IN
            ),
            // Additional parameters
            ...
        }
)

@Entity
public class Holiday {
}

List<Holiday> holidays = entityManager
.createNamedStoredProcedureQuery( "Holidays.findAll" )
.setParameter( "city", "NY" )
.setParameter( "year", 2020 )
 ...
.getResultList();

也可以通过EntityManager调用存储过程：

StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "holidays.usa", Holiday.class);
query.registerStoredProcedureParameter( "city", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter( "year", Integer.class, ParameterMode.IN);
... // register other parameters

query.setParameter("city", "NY");
query.setParameter("year", 2020);
... // set other parameters
List<Holiday> holidays = query.getResultList();