How to call a Database function using spring data jpa?

Question

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

Hi and welcome, please read how to ask a question here (stackoverflow.com/help/how-to-ask) , then edit your question accordingly. — Leviand
– Leviand, Commented Sep 5, 2018 at 9:53
Thank you @benjaminc . Can u provide me any examples for the same? — Indrajith K V
– Indrajith K V, Commented Sep 5, 2018 at 10:01
@benjaminc I want to call the above function using spring data jpa? Since i don't have any entity related to that function how can i create an interface extending jpaRepository — Indrajith K V
– Indrajith K V, Commented Sep 5, 2018 at 10:06

Indrajith K V · Accepted Answer · 2018-09-06 04:40:05Z

15

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

answered Sep 6, 2018 at 4:40

Indrajith K V

2571 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ashish Khurana Over a year ago

for me this worked @Query(nativeQuery = true, value = "SELECT CAST(gen_random_uuid as varchar) from gen_random_uuid") I had to cast the output otherwise it was throwing an error.

Cray · Accepted Answer · 2020-01-29 13:50:27Z

1

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

edited Jan 29, 2020 at 13:50

Cray

2,8468 gold badges25 silver badges34 bronze badges

answered Jan 29, 2020 at 12:45

Lalit

194 bronze badges

Comments

Braian Coronel · Accepted Answer · 2021-01-05 00:44:16Z

1

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

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

GL

answered Jan 5, 2021 at 0:44

Braian Coronel

23k4 gold badges64 silver badges69 bronze badges

Comments

Rajeev Ranjan · Accepted Answer · 2020-01-29 12:52:15Z

0

you call DB function from a DAO as

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

eg

{? = call mypackage.myfunction(?)}

answered Jan 29, 2020 at 12:52

Rajeev Ranjan

2793 gold badges11 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to call a Database function using spring data jpa?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related