Platinum Partner
java,sql,jdbc,stored procedures,server-side

The ABCs of JDBC, Part 4 - Stored Procedures

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.

{{ tag }}, {{tag}},

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks
Tweet

{{parent.nComments}}