Spring Boot Restful + PostgreSQL + JPA Hibernate Stored Procedure Functions @Procedure - Part 9

In this video I have explained how to call Stored Procedure / DB Functions and what are the ways to handle the results.
Repository Link:
gitlab.com/lsi...
Topics covered in this video:
1. Entity Manager
2. @Procedure
3. @NamedStoredProcedureQuery
4. @SqlResultSetMapping
Runtime Exceptions covered in this video:
1. org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
2. org.hibernate.MappingException: Cannot mix named parameters and REF_CURSOR parameter on PostgreSQL
3. org.postgresql.util.PSQLException: cursor unnamed portal 1 does not exists

[@semugutkha1258]

Thank you man, this is exactly what I needed. I will try this out and get back to this video.

[@TheLoveking1982]

Hi Bro,This is realy useful for me and thanks for sharing this vedio. but ur voice is very lower to hear u

[@sagarsomwanshi4533]

Thanks for giving a Nice Explanation for JPA with the procedure ..But the whole video is for the calling function not the Store procedure in PostgreSQL ..Could you please give that example?

[@ElAdamska]

Hi! In PostgreSQL we call functions not stored procedures

[@pulkitkaushik4539]

Great stuff man.. i will definitely try it and get back if i face anything

[@MessierNoventayNueve]

Thanks. lot of thanks, was an great helpfull video.

[@HariKrishna-ir2mx]

Thanks for sharing this bro

[@AhmadHamid]

@Procedure didn't work if the function is not defined in the public schema on postgresql

[@HariKrishna-ir2mx]

According to you, which one is better to call DB object in Spring boot.. please reply

[@LetsSortItOut]

If you are familiar with SQL, then Native query seems the easiest way to do this. But remember we use ORM for some really good reasons. For example: 1. it is database independent. 2. Every time you perform database operations through your JPA implementation it will also take care of the validation part before submitting it to the database (If you defined the validation), so you can limit the unnecessary traffic to your database server. 3. It encapsulates the actual database definition. 4. If you are working in a team it also removes that personal touch of the developer and the code looks more universal and easy to understand. So I recommend to use JPA whenever possible. By saying this I am not undermining the importance of Native queries, because there are some instances during development where you really need to use Native Queries as well.

[@rajchavan860]

I am getting an exception that says.
java.lang.NullPointerException: Cannot invoke "java.util.iterator" because "this.parameters" is null.

[@thiru53]

Hi, i am getting following error in oracle DB with same
ORA-06553: PLS-306: wrong number or types of arguments in call to 'procedure'

[@nilolima5401]

How i can create Stored Functions in Spring Boot????

[@karthickselvam3938]

Hi Man,
Please help...How to map multiple result set from one single SP.

[@minmai1357]

How about procedure using multiple tables ? Please help me

[@ThabiraGamingShorts]

Hi Sir,
I am getting "No Dialect mapping for JDBC type: 1111" this error for void method
is there any fix for this

[@LetsSortItOut]

The same error is handled in this video, watch from 10:51onwards. If you are still getting error, then I need more details. Possibly the stack trace of the error. Have you tried the source code that I uploaded on Gitlab? This error generally occurs when Hibernate is unable to map the return type. So basically first you need to check the procedure/function and write it according to this tutorial. Second, return type of the caller function in Repository. The return type of the DB function should be record if you are using PostgreSQL.

[@EjazAhmad-xs6cc]

Hello sir,
I am trying with these code but with SQL Oracle developer,I am not getting,can you help me

[@venkatesh4v]

Hello Mate, The video is very good and your explanation too. But in the real world project we get the result set from procedure from different tables.
Ex: select A.a,B.b,C.c
from table A, table B,table C
where A.id=B.id and B.id=C.id
column a from table A, column B from table B etc...how to handle this scenario or map in @Entity.

[@LetsSortItOut]

OK I will come up with a separate video on this.

[@yaswanthsatyavarapu3800]

Nice video,
If there are placeholders in sql stored procedures, then how to handle it in spring boot.
Please explain this

[@juerguenherrera5927]

Thank You

[@vageeshanvageesh5583]

Great 😊

[@ciliyaciliya2294]

Is it possible to pass entire insert query to a function or procudre via spring jpa?

[@ElAdamska]

When using PostgreSQL functions, you can either pass arrays, parameters or even custom Postgresql types as input elements.

[@tanvia29]

What are the benefits of using @Procedure over @Query?

[@ElAdamska]

More than benefits I saw limitations when using @Procedure

[@EjazAhmad-xs6cc]

How can I contact you