Spring stored procedure | Java Techie

How to call stored procedure using Spring data jpa
#JavaTechie #SpringBoot #StoredProcesure
GitHub: github.com/Jav...
Facebook : / 919464521471923
Blog : javagyanmantra...
Like & subscribe

[@poojashetty479]

amazing...very nicely explained...this is what i was looking for thank you

[@kaushaladlak4222]

Thank you sir it is very helpful for everyone...superb

[@johnbritto7850]

thank u so much...u saved my life:):)

[@luisantoniojachozambrano7605]

For some reason i tried this and had an error with the parameters i had a error: parameter has been not registered. So i tried solething like this in the dao classand it worked ! for anyone who needs it !. thanks 4 the video. StoredProcedureQuery query = em.createStoredProcedureQuery("calculate"); query.registerStoredProcedureParameter("x", Double.class, ParameterMode.IN);

[@Javatechie]

Cool Luis

[@rajuranjan910]

Thank you very much for uploading and your time

[@bigstars1827]

Good explanation.. Liked it.

[@sagarchpatro]

Bhai.. Tame poora jhakkaas explanation daucha

[@mukeshmishra6967]

Basant Bhai, you told we don't need to modify the code in case of in future any other table fields are required to access, only we can change in the stored procedure itself. But, Like for this example, you have added procedure parameter in the code for "category", similarly in future, if we require 4 other fields along with category, then apart from changing in stored procedure, we have to add other fields as Procedure Parameters also in our code, right?? Then how is this approach becoming feasible? Please help.

[@mayanksrkumar16]

Thans a lot. This worked for me.

[@SamPaulIsaac]

Thanks, it helps!

[@VikasSharma-hm6xt]

well Explained

[@vikassharma-e4x4i]

sir plesae make a video on how to call a procedure with mutiple joins (it is returing a list of result set that is a combination multiple tables )

[@tranxuano1092]

thank sir, can u add English sub for your video? it's very helpful

[@Javatechie]

This is an old video so I wasn't enabled but i will do that .

[@abdallaabuu6608]

In the dao class, function to insert,update and delete and in controller in this example u tought us only select using list function Thanks

[@aryashrestha6601]

simple and easy thanks mate.

[@soni21rajeev]

Thanks ..looking for it.

[@sunilbehera2176]

nice bro.

[@rohitkrishnamurthy6322]

Hi, Thanks for this detailed video. I have created the standalone service as thought in your video as per my requirements, it worked perfectly fine. When I integrated with other project, the same service, this is throwing error as no @NamedStoredProcedureQuery was found with that name= Please note there is no in and out parameter in my case. Thank you in advance!

[@Selscript]

Hi, if I need key value pair then how can I get these values?

[@thecolonyengineer]

thanks a lot!

[@Зебратв-п7э]

can i do the same with oracle procedure pl/sql

[@Javatechie]

Yes you can do that

[@michaeledwards460]

This is showing a simple example where I am linking one entity to a stored procedure that selects data from a table containing records of data for that entity. In reality we are going to use complex stored procedures joining multiple tables. how do I call a stored procedure that is joining multiple tables and is performing complex operations? Do I just link all the entities that are used in that stored procedure or what? Just need some clarification on that. Every where else I look I am seeing simple examples like what you just showed.

[@emmaaloysius4241]

I am also looking for this solution. How to invoke a Stored Procedure which cannot be associated with a single Entity class

[@Javatechie]

Hi we can map multiple entity join query in stored procedure Are you getting any issue on that

[@emmaaloysius4241]

@@Javatechie Thanks for the response. So, if a procedure is using multiple tables, to which Entity do we link this procedure.

[@emmaaloysius4241]

@@Javatechie Sorry I didn't follow what is meant by 'map multiple Entity join query in Stored procedure'

[@ayusitika]

@@emmaaloysius4241 did you find any solution for that....... as far as i can understand it means we can define the output schema in our entity whihch will be returned by the stored procedure....instead of definining multiple entities in our spring application ...that part is to be done by store procedure...we just need to create one or more entity depending on what will be returned by store procedure..... let me know if you find any solution or am i wrong in this approach.

[@dileepalla6769]

super

[@abdallaabuu6608]

Thank u for nice tutorial but how to do insert,update and delete ??

[@Javatechie]

Approach is same only you need to change query

[@abdallaabuu6608]

@@Javatechie I ment in the dao class, function to insert,update and delete and in controller in this example u tought us only select using list function Thanks

[@thiemhoang6586]

tks bro. I have some question. Now a my result is Array of Array. i want to convert it to Entity. Help me please

[@Javatechie]

Didn't get you can you please elaborate it

[@thiemhoang6586]

@@Javatechie your result is Array. I want convert it to Json

[@thiemhoang6586]

sr bro. My english is not good

[@Javatechie]

@@thiemhoang6586 just use ObjectMapper for example ObjectMapper mapper=new ObjectMapper (); mapper.writeValueAsString("pass your array here ");

[@limnes.]

@@Javatechie thanks for your videos, my result is array too, where should put that code (objectMapper) in your example, thanks

[@vaishnavideekonda372]

Hello. I have copied and pasted the code as it is from gitub. I have created table and two procedures as you created. But I am not able to fetch any data in the postman using get. I am getting status 500 . and in the console i got nullpointerexception. What could be the reason?

[@Javatechie]

Debug it once you will get to know who causing this issue

[@bhavanisankar71]

Sir how to save our entity variables in preserve order because in my entity i used Id,name, salary, address but db storing id, address, salary,name but i need id,name, salary, address how sir

[@Javatechie]

You need to add one key in application.properties like physicalNamingStrategy

[@bhavanisankar71]

Please tell me full answer

[@Javatechie]

Did you search in Google what i suggested? If not please do it then ask

[@bhavanisankar71]

@@Javatechie ok I will search if not possible i will tell you

[@bhavanisankar71]

@@Javatechie i added PhysicalNaming strategy it is not working please do me one video short video also ok please sir 🙏

[@manutyagi8019]

Good afternoon sir, how can we call stored procedure in jsp using spring boot?

[@nilolima5401]

How i can create Stored Functions in Spring Boot?

[@Javatechie]

That is what I explained in this video

[@ranikanojiya7394]

When one single store procedure have multiple query how to retrieve that data?

[@RobertoSilvaZuniga]

Thank you, you know who I can create a Pagination with stored procedure?

[@thiru53]

Hello sir, Can you please help me to pass Java collection to Oracle Stored procedure List to Oracle Stored procedure as IN parameter

[@Javatechie]

Let me check

[@jasper5016]

Can you try to pass this query to ThymeLeaf and check? It would not work because you get List and you are converting them to List type.

[@rohithsuvvari5634]

Hi sir, I am getting this error : java.lang.IllegalArgumentException: Named parameter [tcategory] is not registered with this procedure call. I wrote the same procedure call like you and same steps. What am I missing

[@Javatechie]

Please compare with my code you can find your mistake.else do let me know

[@rohithsuvvari5634]

@@Javatechie This is the only missing line spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect This is giving error for me, hence I removed it. I am using MySQL 8. Could this be the root cause ? Rest all I followed exactly same.

[@rohithsuvvari5634]

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. This also I dropped, Nothing else.

[@Javatechie]

@@rohithsuvvari5634 this is just warning you can ignore it . Check in procedure annotation

[@rohithsuvvari5634]

@@Javatechie It is actually a run time error only for this procedure where there input parameters. 2019-08-15 17:28:25.705 ERROR 1564 --- [nio-8080-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Named parameter [t] is not registered with this procedure call; nested exception is java.lang.IllegalArgumentException: Named parameter [t] is not registered with this procedure call] with root cause java.lang.IllegalArgumentException: Named parameter [t] is not registered with this procedure call at org.hibernate.query.procedure.internal.ProcedureParameterMetadata.getQueryParameter(ProcedureParameterMetadata.java:125) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.procedure.internal.ProcedureCallImpl.setParameter(ProcedureCallImpl.java:816) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.procedure.internal.ProcedureCallImpl.setParameter(ProcedureCallImpl.java:69) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at com.example.practice015.storedprocedure.dao.ticketdao.getTicketinfo2(ticketdao.java:31) ~[classes/:na] at com.example.practice015.storedprocedure.dao.ticketdao$$FastClassBySpringCGLIB$$93ffe426.invoke() ~[classes/:na] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE] at com.example.practice015.storedprocedure.dao.ticketdao$$EnhancerBySpringCGLIB$$9ea46931.getTicketinfo2() ~[classes/:na] at com.example.practice015.storedprocedure.controller.ticketcontroller.findTicketsByCatgeory(ticketcontroller.java:31) ~[classes/:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_121] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_121] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_121] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121] at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) ~[spring-webmvc-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892) ~[spring-webmvc-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797) ~[spring-webmvc-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039) ~[spring-webmvc-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) ~[spring-webmvc-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005) ~[spring-webmvc-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:897) ~[spring-webmvc-5.1.9.RELEASE.jar:5.1.9.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882) ~[spring-webmvc-5.1.9.RELEASE.jar:5.1.9.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.22.jar:9.0.22] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) ~[spring-web-5.1.9.RELEASE.jar:5.1.9.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:853) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587) [tomcat-embed-core-9.0.22.jar:9.0.22] at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.22.jar:9.0.22] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_121] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_121] at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.22.jar:9.0.22] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_121]

[@kaushiklad6551]

In the response its giving array ....but my need id i want key value pair array ...and u are getting simple array how can i distinguished with json key value

[@Javatechie]

kaushik , its very simple i missed it to mention in video ,in @NamedStoredProcedureQuery annotation just use one more attribute i.e resultClasses = Ticket.class example : @NamedStoredProcedureQueries({ @NamedStoredProcedureQuery(name = "firstProcedure", procedureName = "getTickets",resultClasses = Ticket.class)})

[@kaushiklad6551]

Thank u i try tomorrow

[@kaushiklad6551]

Yes i try today and it work...but Resulset class has no @id or primary key So i make some string column name as id .... So it result is duplicate of firsf row ... I try @generatedvalue(gnerationtype.auto) Still not work ... How can get data when i have no primary key in table or result

[@mohammadkassir3545]

How to convert the response to json ?

[@Javatechie]

Its json only

[@yogeshhivarkar9838]

Hi sir , i want to insert data into oracle database in 7 different table with 7 diff store procedure so how ca do this using jpa .in one hit. please suggest the solution.......thanks in advanced

[@Javatechie]

Need to check this

[@yogeshhivarkar9838]

@@Javatechie ok sir thanks

[@SurajYadav-yd2cu]

i am getting entity manager as null can anyone help ?

[@garikinarajesh1375]

How to create procedure in h2 database

[@Javatechie]

Not sure in h2 will check and update you

[@garikinarajesh1375]

@@Javatechie Could you please make a video on Ack,Nack and nackandreque in RAbbitMq Plz?

[@nimeshaghera5372]

How TO Retrieve Data in JSON Format with paramere name?

[@Javatechie]

Return retrieve object from controller directly else use objectmapper class to convert it to object

[@wangjamdevendra8338]

how to display the output in html table ?? please show

[@Javatechie]

Hi wangjam, Please check my yahoo stock api video description there i shared one link where I displayed data in html in tabular format

[@ajithdas2127]

What is lamboc bro

[@Javatechie]

Lombok is a tool through which we can generate setter,getter,constructor,toString methods automatically using annotation , we no need write those common logic manually Please refer my blogs : javagyanmantra.wixsite.com/website/single-post/2018/02/11/How-to-configure-Lombok-in-eclipse

[@sridharans2230]

Work on your spellings

[@soboko123]

Spellings are irrelevant to what he is teaching. You are watching this to learn spring , not to learn English.

[@fragaeverton]

And you work on your gratitude. This guy is trying to help us and you´re complainnig.

[@subhrajeetmitra3681]

java.sql.SQLSyntaxErrorException: PROCEDURE ticket.firstProcedure does not exist - I am getting this. Can you explain why

[@KarlGernanz]

@Autowired private EntityManager em; --> Could Not Autowire.No beans of 'EntityManager' type foun d😪

[@Javatechie]

Did you define entity manager as a bean in config class

[@KarlGernanz]

@@JavatechieCould you explain how? please.

[@Javatechie]

I have already explained in video buddy

[@KarlGernanz]

@@Javatechie Perfect, It Works .. Thanks a lot!!! Blessings 💯