Spring Data JPA calling Oracle Function

Question

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

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;

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

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.

Answer 1

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);

}

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

You can also implement your CustomRepository and use 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));
    }

}