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:
SUP_NAME COF_NAME ---------------- ----------------------- 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:
CREATE OR REPLACE PROCEDURE CLONING
as
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 (https://code.google.com/p/lindbergframework/). 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
Comment on this tutorial
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Related Tutorials
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
What is Referential Integrity in databases?
Handling CSV in Stored Procedures
java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError
Calling a Stored Procedure from JDBC in Java
setSavepoint and releaseSavepoint Example in Java
PreparedStatement Example in Java
Creating Database Tables Using ANT
Using the DriverManager Class vs Using a DataSource Object for a connection
Stored Procedures example in SQL