Kotlin - Spring Data JPA calling Oracle Function

There are two way to call function in Spring Data JPA  with kotlin, I shares examples.

Create function in oracle.

CREATE OR replace FUNCTION Myfunction(value IN VARCHAR2) 
RETURN NUMBER 
IS 
  a NUMBER; 
  b NUMBER; 
BEGIN 
    RETURN a + b; 
END; 

Example 1, it won't work if your function is using DML statementes, but you can add annotation @Modifying annotation. 

import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.CrudRepository
import org.springframework.data.repository.query.Param

interface MyRepository: CrudRepository<YOUR_MODEL, YOUR_ID_MODEL>{
    @Query(nativeQuery = true, value = "SELECT myfunction(:value) FROM dual")
    fun myfunction(@Param("value") value: String?): Int?
}

Example 2: Create customer repository.

2.1 create custom repository

interface MyRepositoryCustom {

    fun myFunction(param1: String?): Int?
}

2.2 create a class, i use  SimpleJdbcCall.

import org.springframework.beans.factory.annotation.Autowired
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


class MyRespositoryImpl : MyRepositoryCustom {

    @Autowired
    lateinit var jdbcTemplate: JdbcTemplate

    override fun myFunction(param1: String?): Int? {
        val jdbcCall = SimpleJdbcCall(jdbcTemplate).withFunctionName("myfunction")

        val paramMap: SqlParameterSource = MapSqlParameterSource()
                .addValue("value", param1)

        return jdbcCall.executeFunction(Int::class.java, paramMap)
    }
}

2.3 I extend customer repository on principal repository.

interface MyRepository: CrudRepository<YOUR_MODEL, YOUR_ID_MODEL>, MyRepositoryCustom {

That it is.

References:

https://stackoverflow.com/questions/45867348/spring-data-jpa-calling-oracle-function