CODEWITHSUNDEEP
javaspringspring-data-jpa
Indrajith K V
Indrajith K V

Reputation: 257

How to call a Database function using spring data jpa?

I want to call a database function from spring boot application which will return a decrypted value.

Is it possible to call database functions using spring data jpa? If possible how?

If not possible using spring data jpa, Is there any alternatives to call functions from spring boot?

Here is my function

IF OBJECT_ID('fn_MASK_CARD') IS NOT NULL
    DROP FUNCTION fn_MASK_CARD
GO

CREATE FUNCTION fn_MASK_CARD (
    @CARD_NUMBER  VARCHAR(19)
    )
   RETURNS VARCHAR(19)
AS
BEGIN
    RETURN SUBSTRING(@CARD_NUMBER,1,6)+REPLICATE('#',LEN(@CARD_NUMBER)-10)+
                                                        SUBSTRING(@CARD_NUMBER,LEN(@CARD_NUMBER)-3,LEN(@CARD_NUMBER));
END
GO

Upvotes: 7

Views: 24203

Answers (4)

Braian Coronel
Braian Coronel

Reputation: 22867

nativeQuery = true it is a precondition in Spring JPA to call the database functions.

@Query(value = "SELECT your_function()", nativeQuery = true)

GL

Upvotes: 1

Lalit
Lalit

Reputation: 19

It worked for me. Try adding the below code into your repository.

@Query(nativeQuery = true, value = "SELECT fn_MASK_CARD(:card_num) FROM DUAL")
String maskCard(@Param("card_num") String card_num);

Upvotes: 1

Rajeev Ranjan
Rajeev Ranjan

Reputation: 279

you call DB function from a DAO as

{? = call <package_name>.<>Function_Name(<parameter >)}

eg

{? = call mypackage.myfunction(?)}

Upvotes: 0

Indrajith K V
Indrajith K V

Reputation: 257

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

Upvotes: 15

Related Questions