Calling a Stored Procedure from JDBC in Java

By: Watson  

JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A callableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedure SHOW_SUPPLIERS using the connection con. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();

The ResultSet rs will be similar to the following:

----------------		-----------------------
Acme, Inc.			Colombian
Acme, Inc.			Colombian_Decaf
Superior Coffee			French_Roast
Superior Coffee			French_Roast_Decaf
The High Ground			Espresso

Note that the method used to execute cs is executeQuery because cs calls a stored procedure that contains one query and thus produces one result set. If the procedure had contained one update or one DDL statement, the method executeUpdate would have been the one to use. It is sometimes the case, however, that a stored procedure contains more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the method execute should be used to execute the CallableStatement .

The class CallableStatement is a subclass of PreparedStatement, so a CallableStatement object can take input parameters just as a PreparedStatement object can. In addition, a CallableStatement object can take output parameters, or parameters that are for both input and output. INOUT parameters and the method execute are used rarely.

Archived Comments

1. if my pl/sql procedure is like this:

View Tutorial          By: zia rockstar at 2016-07-29 12:51:52

2. nice one
View Tutorial          By: sarvesh at 2015-03-12 05:16:10

3. Look this framework ( Easy handling of stored procedur
View Tutorial          By: Mariane at 2013-07-04 14:27:52

4. Very nicely explained, especially the step-by-step approach to the problem and the explanation in ea
View Tutorial          By: Kaushik at 2012-09-20 06:58:45

5. Very nicely explained, especially the step-by-step approach to the problem and the explanation in ea
View Tutorial          By: Kaushik at 2012-09-20 04:14:21

6. Nice explanation , good Info on rare to find topic
View Tutorial          By: Vinit at 2012-08-27 08:34:42

7. Good example
View Tutorial          By: Lahiru at 2012-04-09 05:24:21

8. Nice Explanation.Thank you
View Tutorial          By: Vengat at 2011-09-24 09:17:24

9. Great stuff - helped me setting up the company SQL DB
View Tutorial          By: JW at 2011-07-11 12:09:19

10. Nice explanation with appropriate example
View Tutorial          By: VL at 2009-04-09 06:17:37

Most Viewed Articles (in JDBC )

Latest Articles (in JDBC)

Comment on this tutorial