Calling a Stored Procedure from JDBC in Java

By: Watson Emailed: 1598 times Printed: 2044 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

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.


JDBC Home | All JDBC Tutorials | Latest JDBC Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(7)


1. View Comment

Nice explanation with appropriate example

View Tutorial          By: VL at 2009-04-09 06:17:37
2. View Comment

Great stuff - helped me setting up the company SQL DB

View Tutorial          By: JW at 2011-07-11 12:09:19
3. View Comment

Good example

View Tutorial          By: Lahiru at 2012-04-09 05:24:21
4. View Comment

Very nicely explained, especially the step-by-step approach to the problem and the explanation in easy language is the main attraction. Thanks :)

View Tutorial          By: Kaushik at 2012-09-20 04:14:21
5. View Comment

Very nicely explained, especially the step-by-step approach to the problem and the explanation in easy language is the main attraction. Thanks :)

View Tutorial          By: Kaushik at 2012-09-20 06:58:45
6. View Comment

Look this framework (https://code.google.com/p/lindbergframework/). Easy handling of stored procedures and functions, including treatment of cursors automatically.

https://code.google.com/p/lindbergframework/


View Tutorial          By: Mariane at 2013-07-04 14:27:52
7. View Comment

nice one

View Tutorial          By: sarvesh at 2015-03-12 05:16:10

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by Watson
Inheritance Example in Java
Calling a Stored Procedure from JDBC in Java

More Tutorials in JDBC
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
Import TEXT to TABLE in MSSQL
What is Referential Integrity in databases?
Handling CSV in Stored Procedures
setSavepoint and releaseSavepoint Example in Java
Calling a Stored Procedure from JDBC in Java
java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError
Creating Database Connection Pool in Tomcat 5.0 and Tomcat 5.5 for MySQL and Java
JDBC Basics and JDBC Components
SELECT Statements
WHERE Clauses in SQL
Joins example in SQL
Common SQL Commands
Result Sets, Cursors and Transactions in SQL
Stored Procedures example in SQL

More Latest News
Most Viewed Articles (in JDBC )
A simple JDBC application sample code
How connection pooling works in Java and JDBC
Using JDBC to extract data from a database and output to an XML document
Stored Procedures example in SQL
Using JDBC to connect to MySQL from Java Program
What is JDBC?
The Structure of JDBC
Using Transactions in JDBC
What is Referential Integrity in databases?
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
JDBC Components
Result Sets, Cursors and Transactions in SQL
Getting Started with JDBC
Using the DriverManager Class vs Using a DataSource Object for a connection
Calling a Stored Procedure from JDBC in Java
Most Emailed Articles (in JDBC)
What is JDBC?
What is the ACID principal?
Creating Database Tables Using ANT
Using JDBC to connect to MySQL from Java Program
JDBC Components
JDBC Architecture
The Structure of JDBC
A simple JDBC application sample code
How connection pooling works in Java and JDBC
Using JDBC to extract data from a database and output to an XML document
Using Transactions in JDBC
Data Access Technologies in Java
JDBC and Tomcat context settings
Creating Database Connection Pool in Tomcat 5.0 and Tomcat 5.5 for MySQL and Java
JDBC Basics and JDBC Components