Spring Data JPA调用Oracle Function [英] Spring Data JPA calling Oracle Function

问题描述

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

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

下面是一个示例Oracle函数，我希望在Service实现类中返回该值.

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

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;

在没有框架的情况下执行此操作很简单，但是该项目内置于Spring Boot JPA中，因此最好使用它.

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

我需要参考指南链接或简单的基本结构. 我在SO和Spring Data JPA参考中搜索了所有内容，发现的所有示例都是关于CRUD和存储过程的，而关于Function则没有.

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.

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

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

推荐答案

您可以通过本机查询调用函数，并从对偶中获取结果.

You can call your function via native query and get result from 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);

}

请注意，如果您的函数正在使用DML语句，则它将无法正常工作.在这种情况下，您需要对查询使用批注，但是由于@Modyfing返回类型的限制，该函数本身必须返回数字.

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

您还可以实现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));
    }

}

这篇关于Spring Data JPA调用Oracle Function的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！