使用JOOQ从表值的Postgresql函数获取结果 [英] Getting results from table-valued Postgresql function with JOOQ
问题描述
我有一个Postgresql函数,返回表
I have a Postgresql function that returns Table
CREATE OR REPLACE FUNCTION test_func(IN param1 text, IN param2 integer)
RETURNS TABLE(result1 text, result2 integer) AS
$BODY$
BEGIN
result1 := 'aa';
result2 :=1;
return next;
result1 := 'bb';
result2 :=2;
return next;
END
$BODY$
LANGUAGE plpgsql
在pg中查询-admin返回正确的结果
Query in pg-admin returns correct result
select * from test_func('aaa', 23);
result1 | result2
"aa" | 1
"bb" | 2
JOOQ像往常一样生成函数
JOOQ generates function as always
...
public class TestFunc extends org.jooq.impl.AbstractRoutine<java.lang.Void> {
...
public TestFunc() {
super("test_func", ee.tavex.tavexwise.db.public_.Public.PUBLIC);
addInParameter(PARAM1);
addInParameter(PARAM2);
addOutParameter(RESULT1);
addOutParameter(RESULT2);
}
...
以及在常规课程中
...
public static ee.tavex.tavexwise.db.public_.routines.TestFunc testFunc(org.jooq.Configuration configuration, java.lang.String param1, java.lang.Integer param2) {
ee.tavex.tavexwise.db.public_.routines.TestFunc p = new ee.tavex.tavexwise.db.public_.routines.TestFunc();
p.setParam1(param1);
p.setParam2(param2);
p.execute(configuration);
return p;
}
我这样称呼
TestFunc records = Routines.testFunc(dslConfiguration, "xx", 10);
records.getResults() //returns empty List
records.getResult1() //returns "aa"
records.getResult2() //returns 1
因此,它正确地返回了第一行的值,但是如何获得整个表呢?
So, it correctly returns the first row's values, but how can I get the whole table?
(jooq 3.5.0)
(jooq 3.5.0)
推荐答案
调用表的正确方法jOOQ中的值函数是在 FROM
子句如您所链接的手册页中所述。
The correct way to call table-valued functions from jOOQ is by using them in FROM
clauses as documented in the manual page that you've linked.
在您的情况为:
Result<TestFuncRecord> result =
DSL.using(configuration)
.selectFrom(Routines.testFunc("xx", 10))
.fetch();
或者也从jOOQ 3.6开始
Or starting with jOOQ 3.6 also
Result<TestFuncRecord> result =
DSL.using(configuration)
.selectFrom(Tables.TEST_FUNC("xx", 10))
.fetch();
jOOQ代码生成器将表值函数视为普通表,而不是例程。这就是为什么 Routines
中不应该采用带有 Configuration
参数的方法的原因。
The jOOQ code generator treats table-valued functions like ordinary tables, not like routines. This is why there should be no method in Routines
that takes a Configuration
argument.
这篇关于使用JOOQ从表值的Postgresql函数获取结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!