Over a million developers have joined DZone.

The ABCs of JDBC, Part 4 - Stored Procedures

· Java Zone

Discover how powerful static code analysis and ergonomic design make development not only productive but also an enjoyable experience, brought to you in partnership with JetBrains

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

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


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.

Learn more about Kotlin, a new programming language designed to solve problems that software developers face every day brought to you in partnership with JetBrains.


The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}