Calling database function JPA repository-CodePudding

i have a question.

I want to call a database function from my JPARepository in spring boot... My function is the next:

CREATE function sf_getval(seqname varchar2) return NUMBER IS ret_val number :=0; 
begin
 INSERT INTO schema.table(IDENT ,NAME) VALUES (12321,'Name');
  return ret_val;
  END sf_getval;

That is not doing anything, i just want a function that insert something in database and return a number, i need this, cant change, is the definition.

Then from JPA i need to consume like this:

@Repository
public interface myRepository extends JpaRepository<Some, SomeId> {

@Query(nativeQuery = true, value = "CALL \"pkgName\".\"sf_getval\"(:name) ")
int sf_getval(@Param("name") String name);

If i do a select pkgname.sf_getval() var from dual; did not work because that violates isolation in the database, is not an option to me. Necesary must be a call command.

I use de repository directly because in my project i've already configure spring.cloud.config and i dont need entityManager or something like that. Is not a solution do a jdbc call.

Thanks, sorry for my english. Regards

CodePudding user response：

I confused stored procedures with user defined functions - you also use the CALL syntax in your native query, but functions are used with SELECT. Only precedures are invoked with CALL.

You will have to use a native query like this:

@Query(value = "SELECT DOCUMENTOS.sf_getval(:name) FROM DUAL", native = true)
int sfGetVal(@Param("name") String name);

I'm not a 100% sure about the FROM DUAL - you do this when selecting a constant or function that doesn't depend on any table, so it's a dummy value.

You can read more in Vlad Mihalcea's blog post "How to call Oracle stored procedures and functions with JPA and Hibernate".

CodePudding user response：

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.