• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to call Stored Procedure using jdbcTemplate and store result in object DTO/String/List/Result se

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, I'm bit confused with the calling pattern of Stored Procedure using jdbcTemplate.
Do i need to add a new class everytime a new SP is to be called?

I WANT to call a SP with some IN and OUT parameters and want to store the result in some DTO object and want to do this using spring in such a manner
that many functions can be listed in a sinlge class some XXXDAO.java

So that it can be called from a bllImp class with some input arguments and gets a class object DTO/String/List/Result set in return.

Please give a running example or provide a .war with the example code or any link where i can clear my doubts.

Thanks in Advance
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Basically, you have to create a separate class for each Stored Procedure, but you can re-use an instance over and over for many calls to the stored procedure with different values for the IN parameters.

This class extends Spring's Stored Procedure class.

In its constructor you are telling Spring what are the IN and OUT parameters and what types they are.

Then when you use this class, you call the execute method on it passing in values for the IN parameters and it returns a Map with all the values returned as OUT parameters.

Hope that clears it up for you

Mark
 
sushant verma
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your reply but I found how to do it i a single class with different Stored Procedure names:












I tested it and it worked all fine...
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The only problem is that it is not Threadsafe.

If two threads are running at the same time, one calls one method, the other calls the other method, then the procReaduserDto SimpleJdbcCall instance might have the wrong procedure name for the wrong thread.

Mark
 
sushant verma
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Mark for your reply, can you please guide me how to make it ThreadSafe.... will it be right to make it threadLocal...
I have modified the JdbcUserDtoDao class and separated into two parent and child and instantiated JdbcCallDataSource using Spring IOC







and please explain how to check any function to be threadsafe.
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Making a property static doesn't change Thread safety.

Personally, I would go back to my first recommendation of creating a StoredProcedure class for each and every stored procedure you have. Don't try to be fancy or tricky to see if you can use just one, getting that to work is too much work, much more than creating one per stored procedure.

You could create a Map where you have many SimpleJdbcCall objects in the map. One for each stored procedure, then the key is the stored procedure map. The problem with this is that it is actually less OO best practices than what I said before.


I recommend doing this.

public class MyFirstStoredProcedure extends StoredProcedure{ ….}

public class MySecondStoredProcedure extends StoredProcedure{…}

That is the best OO design that you can use to re-use the Stored Procedure class in whatever DAOs you want.

Mark

To test Thread safety, create two different threads and make one do one thing and the other do the other thing at the same time.

Mark
 
sushant verma
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Mark for your valuable input.

Just to check the thread safety of my method i created this :




Its result is below for 3 iterations in the format: "TIME | THREAD NAME | METHOD NAME | LINE NO | logs "






The same method is being called by two threads simultaneously at the same time (loop 0) and as expected for
thread 0 :
|__isUserValid=true
|__getUserExists=1

thread 1:
|__isUserValid=false
|__getUserExists=0





Is that sufficient to check for thread safety of a method?
I mean to say the same method is being called by two threads simultaneously and with different parameters and gets separate result of their own.

Please Check and correct me if i missed something or if I'm wrong on thread safety check.

Thanks & regards
Sushant Verma
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Basically, you have the concept correct, but it appears all Threads in your example are always calling the exact same Stored Procedure. So the issue won't show up. You have to have one thread call one stored procedure and another thread call the other stored procedure at the exact same time, and you have to have this happen multiple times, because trying to catch the thread issue in a simple test is all about timing. So I would hope you could have many threads all running simultaneously all calling different Stored Procedures at the exact same time over and over again, and you will eventually catch the threading issue with your code.

I would just forget about your approach and use the simpler approach. Create a single class StoredProcedure for each and every Stored Procedure you have in your database, then you won't have these issues. To me you are over complicating things going your route and will cause possible problems with Threading.

Simple is always the better solution.

Thanks

Mark
reply
    Bookmark Topic Watch Topic
  • New Topic