Spring Data JPA 调用 Oracle 函数

Spring Data JPA calling Oracle Function

提问by Bruno Cal?a

I am running an simple application that uses Spring Boot + Spring Data JPA for persistence.

我正在运行一个简单的应用程序，它使用 Spring Boot + Spring Data JPA 进行持久化。

Below is a sample Oracle function I would like to have the value returned at the Service implementation class.

下面是一个示例 Oracle 函数，我希望在服务实现类中返回值。

CREATE OR REPLACE PACKAGE PKG_TEST AS 
  FUNCTION HELLO_WORLD(TEXT VARCHAR2) RETURN VARCHAR2;
END PKG_TEST;

CREATE OR REPLACE PACKAGE BODY PKG_TEST AS 
  FUNCTION HELLO_WORLD(TEXT VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN 'HELLO WORLD ' || TEXT;
  END;
END PKG_TEST;

Doing this with no framework would be simple, but the project is built into Spring Boot JPA, so it's better to use it.

在没有框架的情况下这样做会很简单，但是该项目内置在 Spring Boot JPA 中，因此最好使用它。

I need a reference guide link or simple base structure to follow. I searched all over SO and Spring Data JPA reference and all examples I found are for CRUD and Stored Procedures, nothing for Functions.

我需要一个参考指南链接或简单的基础结构来遵循。我搜索了所有 SO 和 Spring Data JPA 参考，我找到的所有示例都是针对 CRUD 和存储过程的，没有针对函数。

I tried to use the Stored procedure example modified for function but didn't work.

我尝试使用为功能修改的存储过程示例，但没有奏效。

采纳答案by Micha? Stochmal

You can call your function via native query and get result from dual.

您可以通过本机查询调用您的函数并从 Dual 中获取结果。

public interface HelloWorldRepository extends JpaRepository<HelloWorld, Long> {

    @Query(nativeQuery = true, value = "SELECT PKG_TEST.HELLO_WORLD(:text) FROM dual")
    String callHelloWorld(@Param("text") String text);

}

Note that it won't work if your function is using DML statements. In this case you'll need to use @Modyfingannotation over query, but then the function itself must return number due to @Modyfingreturn type restrictions.

请注意，如果您的函数使用 DML 语句，它将不起作用。在这种情况下，您需要@Modyfing在查询上使用注释，但由于@Modyfing返回类型限制，函数本身必须返回数字。

You can also implement your CustomRepositoryand use SimpleJdbcCall:

您还可以实现您的CustomRepository和使用SimpleJdbcCall：

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

@Repository
public class HelloWorldRepositoryImpl implements HelloWorldRepositoryCustom {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public String callHelloWorld() {
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withCatalogName("PKG_TEST") //package name
                .withFunctionName("HELLO_WORLD");
        SqlParameterSource paramMap = new MapSqlParameterSource()
                .addValue("param", "value"));
        //First parameter is function output parameter type.
        return jdbcCall.executeFunction(String.class, paramMap));
    }

}

回答by Sudabe-Neirizi

public interface inteface-name extends CrudRepository<Object,Long> {

    @Procedure(procedureName = "procedure-name", outputParameterName = "param-out-name")
    BigDecimal method-name(dataType input-param);
}

回答by Sarvan_C

Incase if you need to return a set of columns with n number of rows returned as a oracle type from a function, Use Table(function_name)with the select statement to parse the type as table and get as list of object array List.

如果您需要从函数返回一组包含 n 行的列作为 oracle 类型，请使用Table(function_name)和 select 语句将类型解析为 table 并获取为对象数组 List 的列表。

@Query(nativeQuery = true, value = "SELECT * FROM TABLE(ListOfStates(:country))")
List<Object[]> findStatesByCountry(@Param("country") String country);

回答by D.S.

using entityManager.createNativeQuery works.

使用 entityManager.createNativeQuery 有效。

See: How to call a custom Oracle function returning a value from JPA

请参阅：如何调用从 JPA 返回值的自定义 Oracle 函数

and the referenced page: https://vladmihalcea.com/how-to-call-oracle-stored-procedures-and-functions-from-hibernate/

和参考页面：https: //vladmihalcea.com/how-to-call-oracle-stored-procedures-and-functions-from-hibernate/

回答by Dmitry

If you are using Hibernate as JPA Provider you may create custom dialect and register needed function.

如果您使用 Hibernate 作为 JPA 提供程序，您可以创建自定义方言并注册所需的功能。

public class CustomDialect extends Oracle10gDialect {
    public CustomDialect() {
        super();
        // CustomFunction implements SqlFunction
        registerFunction("custom_function", new CustomFunction());
        // or use StandardSQLFunction; useful for coalesce
        registerFunction("coalesce", new StandardSQLFunction("coalesce"));
    }
}