英文:
How to call postgresql function with more than one input and output parameter from Java?
问题
有一个在PostgreSQL（V_11）中编写的函数，具有多个输入参数和多个输出参数，返回为表格。
CREATE OR REPLACE FUNCTION schema_name.function_name(param1 varchar, param2 int, param3 int)
RETURNS TABLE (column1 int, column2 numeric, column3 int, column3 int)
AS $$
return query
SELECT column1, column2, column3, column3
FROM schema_name.table
WHERE {condition}
BEGIN
如何在Java Spring Boot中使用JPA调用此函数并将其直接分配给List<CLASS>？
如果我们可以创建一个与函数返回的列名相同的类。
如何在Java Spring Boot中使用JPA调用此函数并将其直接分配给List<CLASS>？
如果我们可以创建一个与函数返回的列名相同的类。
英文:
There a function written in postgresql(V_11) with more than one input parameters and more than one output parameters returned as table.
CREATE OR REPLACE FUNCTION schema_name.function_name(param1 varchar, param2 int, param3 int)
RETURNS TABLE (column1 int, column2 numeric, column3 int, column3 int)
AS $$
return query
SELECT column1, column2, column3, column3
FROM schema_name.table
WHERE {condition}
BEGIN
How to call this function from Java Spring boot using JPA and directly assign it to a List<CLASS> ?
If we can make a class with same name of column name returned from function.
答案1
得分: 1
我尝试了多种方法和排列后找到了答案。
你可以通过nativeQuery或者使用StoredProcedureQuery来调用db函数（StoredProcedureQuery在内部使用与nativequery相同的方式，就像CallableStatement一样）。
你可以自动装配你的entityManager类：
@Autowired
EntityManager entityManager;
你可以像这样使用StoredProcedureQuery来调用存储过程或者函数：
StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("schema_name.function_name")
.registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
.registerStoredProcedureParameter(2, Integer.class, ParameterMode.IN)
.registerStoredProcedureParameter(3, Integer.class, ParameterMode.IN)
.setParameter(1, param1)
.setParameter(2, param2)
.setParameter(3, param3);
query.execute();
List<Object[]> dataList = query.getResultList();
你会以List<Object[]>的形式得到结果列表。Object[]中的值会与你在schema中定义的函数的返回类型进行映射，你需要进行迭代来转换成你的对象。
（你可以在调试模式下获取查询对象中的字段名（ColumnIndexNames）：请告诉我是否能够直接以List
迭代dataList并转换为MyClass对象列表。
List<MyClass> myClassObjectList = new ArrayList<>();
dataList.forEach((data) -> {
MyClass myClassObject = new MyClass();
myClassObject.setField1(Integer.parseInt(data[0].toString()));
// 转换逻辑的代码继续写在这里
});
英文:
I got the answer after trying with multiple methods and permutations.
You can call the db function by nativeQuery or using StoredProcedureQuery(which internally uses the same nativequery, same like CallableStatement)
You can autowired your entityManager class :
@Autowired
EntityManager entityManager;
You can call a storedProcedure or function by StoredProcedureQuery like this :
StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("schema_name.function_name")
.registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
.setParameter(1, param1)
.setParameter(2, param2)
.setParameter(3, param3);
query.execute();
List<Object[]> dataList= query.getResultList();
You will get the resultList in List<Object[]> form. Object[] will have values mapped to your return type defined by function in schema and you need to iterate to convert in your object.
(You can get the field names (ColumnIndexNames) in query object in debug mode : Please let me know if one can able to get results directly in List<MyClass> form)
iterate the dataList and convert into the MyClass object list.
List<MyClass> myClassObjectList = new ArrayList<>();
dataList.forEach( (data) -> {
MyClass myClassObject= new MyClass();
myClassObject.setField1(Integer.parseInt(slsTOData[0].toString()));
// code goes on for conversion logic
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论