How to Call a Database Function Using Spring Data Jpa

How to call a Database function using spring data jpa?

Finally found the answer, just added the below code in my repository and it worked!

@Query(nativeQuery = true, value = "SELECT DBO.fn_MASK_CARD(:text)")
String callMaskCard(@Param("text") String text);

Calling database function JPA repository

Finally I found the answer:

String call = "{ ? = call FCRLIVE.AP_CH_GET_ACCT_BALANCES(?, ?, ?, ?, ?) }";
CallableStatement cstmt = conn.prepareCall(call);
cstmt.setQueryTimeout(1800);
cstmt.setString(1, inputCode);
cstmt.registerOutParameter(2, Types.NUMBER);
cstmt.executeUpdate();

Is not the exactly code, but getting the connection from the entityManager, in pure JPA is the solution.

Spring Data JPA calling Oracle Function

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

}

Execute database function with JPA

• Select Query will not work when there are any DML statements in the function.
  
  If there are no DML statements then SELECT QUERY is the best and efficient way to proceed.
• If there are DML statements, then you have to go with interface CallableStatement.
  
  It is pretty forward when we are using JDBC in our project, but for Spring Project i.e. using JPA we will need to obtain the session from EntityManager and ReturningWork.

  Session session = entityManager.unwrap(Session.class);
      CallableStatement callableStatement =  session.doReturningWork(new ReturningWork<CallableStatement>() {
            @Override
            public CallableStatement execute(Connection connection) throws SQLException {
                CallableStatement function = connection.prepareCall(
                        "{ ? = call package_name.function_name(?,?) }");
                function.registerOutParameter(1, Types.INTEGER);
                function.setLong(2, 56);
                function.setString(3,"some text");

                function.execute();
                return function;
            }
        });

        try {
            return callableStatement.getBigDecimal(1);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

How to call MySQL function from Spring Data repositories

Suppose you have a function named echo which takes a string as its input and return the same string as part of the output:

CREATE FUNCTION echo(to_be_echoed TEXT) RETURNS TEXT;

Then if you want to call this function from your repository, you can use a Native Query with the following SQL:

SELECT function_name(arguments)

Hence, your repository method would be like:

@Query(value = "SELECT echo(?1)", nativeQuery = true)
String echo(String toBeEchoed);

How do I call a user defined sql function from springboot?

I think that it should be like that (i do not have chance to check):

@Query(nativeQuery = true, value = "SELECT package.function(:value) FROM dual")
float getFuction(@Param("value") String value);

It is based on this answer

---

Related Topics