GeeCON Prague 2014*
The moose likes JDBC and the fly likes Running a procedure in JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Running a procedure in JDBC" Watch "Running a procedure in JDBC" New topic
Author

Running a procedure in JDBC

jason adam
Chicken Farmer ()
Ranch Hand

Joined: May 08, 2001
Posts: 1932
Basically, I was given a page with a bunch of SQL stuff on it, and asked to duplicate it in JDBC. The SQL commands are basically:

First time I've really done anything with stored procedures, so new territory. Looking at it, it seems like I could create a Statement to do the CREATE OR REPLACE part, the UPDATES, and then have 3 ResultSets containing info from the SELECT statements. I would then write 3 if/else blocks for the if/else stuff. However, after doing some reading through the forums here about CallableStatement, and other searches on the web, is that going about it the wrong way?
If someone could give advice on how to proceed, or point me at some good tutorials/references for a problem like this, would be greatly appreciated.
Thanks!
Jason
[ March 06, 2002: Message edited by: jason adam ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Jason: This is a good example of when a Stored Procedure should be used. You have a bunch of related queries and updates that need to be grouped together. If you move this procedure to java code, you will have to make numerous calls to the database (three times for each query and once for each update). So you can reduce the number of calls to the database by using your stored procedure and calling it from java using a CallableStatement. If you must convert it to pure java code then you can look at samples of using Statements, PreparedStatements
Jamie
jason adam
Chicken Farmer ()
Ranch Hand

Joined: May 08, 2001
Posts: 1932
I figured as much, my problem is I a) don't understand PL/SQL procedures that much, though I get the general jist, and b) not sure how to put all of that stuff into a CallableStatement.
Would I first create a Statement that holds the "CREATE OR REPLACE PROCEDURE... v_tertiary_type VARCHAR2(1);", stuff, and then have a CallableStatement with the "BEGIN UPDATE mon_employees... END UpdateOncallList"? That way reducing the number of queries and updates to just two statements (a regular Statement and a CallableStatement).
I think what they were looking for was a way to write that entire procedure in JDBC, and do away with the whole procedure. But if it makes more sense to build the procedure in JDBC and execute it that way, I'll see if I can work it.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

if you already have the stored procedure compiled at the database, you don't need to create it, only call it (recommended). Then the only code you would need would be

That should be it (exception catching not included) if the stored procedure is already compiled on the database side.
another way, if the stored procedure could not reside on the database is to do the following:

hope that clarifies it a little.
Of course, you could translate the procedure fairly easily into a java method as well. let me know if you need doing this, I know a little about PL/SQL and might be able to help in the translation.
Jamie
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

a quick translation of your stored procedure:

had a few minutes,
Jamie
jason adam
Chicken Farmer ()
Ranch Hand

Joined: May 08, 2001
Posts: 1932
Thanks Jamie! I'll probably go with the method, since I'm not sure about the structure of the database and whether or not the procedure is already compiled in there, and I think that is the route they want me to go anyways.
So basically, the method is replacing the need for the procedure?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by jason adam:
So basically, the method is replacing the need for the procedure?

Yes... the method is replacing the procedure entirely (not just the need!).
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

one last note:
This method can only be called by java programs. So if you still need to call this stored procedure from the database, you'll have to create a Java Stored Procedure. Otherwise all is fine
Jamie
jason adam
Chicken Farmer ()
Ranch Hand

Joined: May 08, 2001
Posts: 1932
Yeah, this is basically going to be intergrated into a web app, will be called by some custom tag. What it's for is when someone goes to a webpage, they will see a list of oncall people for that group. They have the ability to change the order of who is oncall (primary oncall person, etc.). When they submit, this method will be called to update the appropriate tables so that the monitoring software knows who to page for certain events.
Thanks again for the help
Jason
 
jQuery in Action, 2nd edition
 
subject: Running a procedure in JDBC