Java Stored Procedures Application Example

This example illustrate how to use Java stored procedures application.The example is based on a simple business activity: Creating Employee data, Editing Employee data, Deleting Employee data.

import java.sql.*;

public class JavaStoredProcedureExample {

  public static void addEmployee (int empNo, 
                                  String empName,
                                  String street,
                                  String city,
                                  String state,
                                  String zipCode,
                                  String phoneNo) 
    throws SQLException 
  {
    String sql = "INSERT INTO EMPLOYEE_DETAILS VALUES (?,?,?,?,?,?,?)";
    try
    {
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, empNo);
      pstmt.setString(2, empName);
      pstmt.setString(3, street);
      pstmt.setString(4, city);
      pstmt.setString(5, state);
      pstmt.setString(6, zipCode);
      pstmt.setString(7, phoneNo);
      pstmt.executeUpdate(); 
      pstmt.close();
    }
    catch (SQLException e)
    {
      System.err.println(e.getMessage());}
    }

  public static void editEmployee (int empNo, 
                                   String empName,
                                   String street,
                                   String city,
                                   String state,
                                   String zipCode,
                                   String phoneNo)
  throws SQLException
{
  String sql = "UPDATE EMPLOYEE_DETAILS SET EMP_NAME = ?, STREET = ?, CITY = ?, STATE = ?, ZIP = ?, PHONE = ? WHERE EMP_NO = ?";
  try
  {
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, empName);
    pstmt.setString(2, street);
    pstmt.setString(3, city);
    pstmt.setString(4, state);
    pstmt.setString(5, zipCode);
    pstmt.setString(6, phoneNo);
    pstmt.setInt(7, empNo);
    pstmt.executeUpdate(); 
    pstmt.close();
  }
  catch (SQLException e)
  {
    System.err.println(e.getMessage());
  }
}

  public static void deleteEmployee (int empNo) 
    throws SQLException
  {
    String sql = "DELETE FROM EMPLOYEE_DETAILS WHERE EMP_NO = ?";
    try
    {
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, empNo);
      pstmt.executeUpdate(); 
      pstmt.close();
    }
    catch (SQLException e)
    {
      System.err.println(e.getMessage());
    }
  }
}

SQL Query for This Java Example

CREATE TABLE EMPLOYEE_DETAILS (
  emp_no   NUMBER(8) NOT NULL,
  emp_name VARCHAR2(30) NOT NULL,
  street   VARCHAR2(20) NOT NULL,
  city     VARCHAR2(20) NOT NULL,
  state    CHAR(2) NOT NULL,
  zip      VARCHAR2(10) NOT NULL,
  phone    VARCHAR2(12),
  PRIMARY KEY (emp_no)
);




CREATE OR REPLACE PACKAGE employee_mgr AS

  PROCEDURE add_employee (emp_no NUMBER, emp_name VARCHAR2, street VARCHAR2, city VARCHAR2, state CHAR, zip_code VARCHAR2, phone_no VARCHAR2);

  PROCEDURE edit_employee (emp_name VARCHAR2, street VARCHAR2, city VARCHAR2, state CHAR, zip_code VARCHAR2, phone_no VARCHAR2, emp_no NUMBER);

  PROCEDURE delete_employee (emp_no NUMBER);

END employee_mgr;




CREATE OR REPLACE PACKAGE BODY employee_mgr AS

  PROCEDURE add_employee (cust_no NUMBER, cust_name VARCHAR2, street VARCHAR2, city VARCHAR2, state CHAR, zip_code VARCHAR2, phone_no VARCHAR2) AS LANGUAGE JAVA
       NAME 'JavaStoredProcedureExample.addEmployee(int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)';

  PROCEDURE edit_employee (cust_name VARCHAR2, street VARCHAR2, city VARCHAR2, state CHAR, zip_code VARCHAR2, phone_no VARCHAR2, cust_no NUMBER) AS LANGUAGE JAVA
       NAME 'JavaStoredProcedureExample.editEmployee(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, int)';

  PROCEDURE delete_employee (emp_no NUMBER) AS LANGUAGE JAVA
       NAME 'JavaStoredProcedureExample.deleteEmployee(int)';

END employee_mgr;