How do I execute stored procedure?

Category: java.sql, viewed: 9562 time(s).

Stored procedure are user-generated functions or procedures that, once created in the database, can be called by the client applications, such as Java application. In this example we'll demonstrate how to use the JDBC java.sql.CallableStatement to call a stored procedure.

The store procedure in this example is just for inserting a record into table. Just like the PreparedStatement interface, in the CallableStatement we can pass the parameter to the procedure by calling the appropriate setXXX(index, value) method.

package org.kodejava.example.sql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class CallableStatementDemo {
    private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
    private static String username = "kodejava";
    private static String password = "welcome";

    public static void main(String[] args) throws Exception {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(url, username, password);

            //
            // Create a CallableStatement to execute the CREATE_USERS procedure
            // 
            CallableStatement stmt = conn.prepareCall("{call CREATE_USERS (?, ?, ?, ?, ?, ?)}");

            //
            // Defines all the required parameter values.
            // 
            stmt.setString(1, "kodejava");
            stmt.setString(2, "welcome");
            stmt.setString(3, "Kode");
            stmt.setString(4, "Java");
            stmt.setString(5, "Denpasar - Bali");
            stmt.setString(6, "webmaster[at]kodejava[.]org");
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }
    }
}

Below is the stored procedure that was executed in the code above.

CREATE OR REPLACE PROCEDURE CREATE_USERS (username IN VARCHAR2, password IN VARCHAR2, firstName IN VARCHAR2, lastName IN VARCHAR2, address IN VARCHAR2, email IN VARCHAR2) AS
BEGIN
    INSERT INTO users (username, password, first_name, last_name, address, email) VALUES (username, password, firstName, lastName, address, email);
END CREATE_USERS;
Click here to lend your support to: Kode Java Org and make a donation at www.pledgie.com !

 

Uncensored Newsgroups
Download Hundreds of Complimentary Industry Resources

Get hundreds of popular Industry magazines, white papers, webinars, podcasts, and more; all available at no cost to you. With more than 600 complimentary offers, you'll find plenty of titles to suit your professional interests and needs. Click Here and Sign up today!

Related Examples


Java Training

Sponsored Links

Example Categories

100 Top & Latest

Our Friends

Statistics

Locations of visitors to this page
visitor stats