stored procedures for java Callblestatement

Table

.............

CREATE TABLE DBUSER(
   USER_ID NUMBER(5) NOT NULL, 
   USERNAME VARCHAR(20) NOT NULL, 
   CREATED_BY VARCHAR(20) NOT NULL, 
   CREATED_DATE DATE NOT NULL, 
   PRIMARY KEY (USER_ID) 
) 

Sample -1

.......... 

CREATE OR REPLACE PROCEDURE insertDBUSER(
    p_userid IN DBUSER.USER_ID%TYPE,
    p_username IN DBUSER.USERNAME%TYPE,
    p_createdby IN DBUSER.CREATED_BY%TYPE,
    p_date IN DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
 
  INSERT INTO DBUSER ("USER_ID", "USERNAME", "CREATED_BY", "CREATED_DATE") 
  VALUES (p_userid, p_username,p_createdby, p_date);
 
  COMMIT;
 
END;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class JDBCCallableStatementINParameterExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:SUDHEER";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   callOracleStoredProcINParameter();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void callOracleStoredProcINParameter() throws SQLException {
 
  Connection dbConnection = null;
  CallableStatement callableStatement = null;
 
  String insertStoreProc = "{call insertDBUSER(?,?,?,?)}";
 
  try {
   dbConnection = getDBConnection();
   callableStatement = dbConnection.prepareCall(insertStoreProc);
 
   callableStatement.setInt(1, 1000);
   callableStatement.setString(2, "mkyong");
   callableStatement.setString(3, "system");
   callableStatement.setDate(4, getCurrentDate());
 
   // execute insertDBUSER store procedure
   callableStatement.executeUpdate();
 
   System.out.println("Record is inserted into DBUSER table!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (callableStatement != null) {
    callableStatement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
    DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
 private static java.sql.Date getCurrentDate() {
  java.util.Date today = new java.util.Date();
  return new java.sql.Date(today.getTime());
 }
 
} 

Sample-2

...............

CREATE OR REPLACE PROCEDURE getDBUSERByUserId(
    p_userid IN DBUSER.USER_ID%TYPE,
    o_username OUT DBUSER.USERNAME%TYPE,
    o_createdby OUT  DBUSER.CREATED_BY%TYPE,
    o_date OUT DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
 
  SELECT USERNAME , CREATED_BY, CREATED_DATE
  INTO o_username, o_createdby,  o_date 
  FROM  DBUSER WHERE USER_ID = p_userid;
 
END;

import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class JDBCCallableStatementOUTParameterExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:SUDHEER";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   callOracleStoredProcOUTParameter();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void callOracleStoredProcOUTParameter() throws SQLException {
 
  Connection dbConnection = null;
  CallableStatement callableStatement = null;
 
  String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
 
  try {
   dbConnection = getDBConnection();
   callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
 
   callableStatement.setInt(1, 10);
   callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
   callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
   callableStatement.registerOutParameter(4, java.sql.Types.DATE);
 
   // execute getDBUSERByUserId store procedure
   callableStatement.executeUpdate();
 
   String userName = callableStatement.getString(2);
   String createdBy = callableStatement.getString(3);
   Date createdDate = callableStatement.getDate(4);
 
   System.out.println("UserName : " + userName);
   System.out.println("CreatedBy : " + createdBy);
   System.out.println("CreatedDate : " + createdDate);
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (callableStatement != null) {
    callableStatement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
    DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
}

Sample-3

............

CREATE OR REPLACE PROCEDURE getDBUSERCursor(
    p_username IN DBUSER.USERNAME%TYPE,
    c_dbuser OUT SYS_REFCURSOR)
IS
BEGIN
 
  OPEN c_dbuser FOR
  SELECT * FROM DBUSER WHERE USERNAME LIKE p_username || '%';
 
END;

import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import oracle.jdbc.OracleTypes;
 
public class JDBCCallableStatementCURSORExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:SUDHEER";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   callOracleStoredProcCURSORParameter();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void callOracleStoredProcCURSORParameter()
   throws SQLException {
 
  Connection dbConnection = null;
  CallableStatement callableStatement = null;
  ResultSet rs = null;
 
  String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";
 
  try {
   dbConnection = getDBConnection();
   callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
 
   callableStatement.setString(1, "mkyong");
   callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
 
   // execute getDBUSERCursor store procedure
   callableStatement.executeUpdate();
 
   // get cursor and cast it to ResultSet
   rs = (ResultSet) callableStatement.getObject(2);
 
   while (rs.next()) {
    String userid = rs.getString("USER_ID");
    String userName = rs.getString("USERNAME");
    String createdBy = rs.getString("CREATED_BY");
    String createdDate = rs.getString("CREATED_DATE");
 
    System.out.println("UserName : " + userid);
    System.out.println("UserName : " + userName);
    System.out.println("CreatedBy : " + createdBy);
    System.out.println("CreatedDate : " + createdDate);
   }
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (rs != null) {
    rs.close();
   }
 
   if (callableStatement != null) {
    callableStatement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
    DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
}