File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Spring and the fly likes How to call Stored Procedure using jdbcTemplate and store result in object DTO/String/List/Result se Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Spring
Bookmark "How to call Stored Procedure using jdbcTemplate and store result in object DTO/String/List/Result se" Watch "How to call Stored Procedure using jdbcTemplate and store result in object DTO/String/List/Result se" New topic
Author

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

sushant verma
Greenhorn

Joined: Jun 08, 2011
Posts: 8
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
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
sushant verma
Greenhorn

Joined: Jun 08, 2011
Posts: 8
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
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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

Joined: Jun 08, 2011
Posts: 8
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
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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

Joined: Jun 08, 2011
Posts: 8
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
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to call Stored Procedure using jdbcTemplate and store result in object DTO/String/List/Result se