SQL Zone is brought to you in partnership with:

Daniel has posted 3 posts at DZone. View Full User Profile

The ABCs of JDBC, Part 4 - Stored Procedures

06.04.2010
| 9322 views |
  • submit to reddit

This week's installment in our JDBC FAQ series provides a quick overview of stored procedures, how to execute them, and how to receive ResultSets from them. Be sure to check out the other parts in our series (future installments will cover topics such as JDBC Data Types, Transactions, Troubleshooting, and more): 

 

What are stored procedures?

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server.

In the majority of cases, stored procedures are used to simplify the execution of complex logic. Instead of constantly performing a series of queries or insertions using the JDBC API, the same logic can be defined in a single stored procedure, which can then be invoked to obtain the same results.

 

How do I execute stored procedures?

Stored procedures are executed using the CallableStatement object and its executeQuery method, as illustrated in the following code listing

<pre>
<code>
Connection conn = ds.getConnection();
CallableStatement cs = con.prepareCall("{call JDBC_TIPS_SP()}");
ResultSet rs = cs.executeQuery();
</code>
</pre>

 

As you can observe, after obtaining a JDBC connection, a call is made to the prepareCall() method which receives the name of stored procedure -- using a standard SQL escape syntax "{call STORED_PROCEDURE_NAME}". Next, a call is made to the executeQuery() method which belongs to the CallableStatement object, with the stored procedure's results assigned to a standard JDBC ResultSet object.

 

How do I receive a ResultSet from a stored procedure?

Since a stored procedure also returns tabular data (i.e. rows &amp; columns) and is assigned to a ResultSet object -- just like a database table -- data extraction of a ResultSet is performed in the same fashion as a standard JDBC query.

Published at DZone with permission of its author, Daniel Rubio.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Sourabhh Sharma replied on Sun, 2010/06/06 - 12:20am

Having call to IN, OUT and INOUT parameter would add more value to this article?

Lukas Eder replied on Wed, 2011/01/12 - 4:24pm

Hi there

I can see, interfacing stored procedures with Java is a topic worth of discussion. Please, also consider my latest article on this topic on dzone:

http://java.dzone.com/articles/2011-great-year-stored

Cheers
Lukas

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.