Using a CallableStatement Statement to Call a Stored Procedure - Java JDBC

Java examples for JDBC:CallableStatement

  1. HOME
  2. Java
  3. JDBC
  4. CallableStatement

Description

Using a CallableStatement Statement to Call a Stored Procedure

Demo Code

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class Main {
  public static void main(String[] args) {
    Connection conn = null;/* www .j  a  v a 2 s .c o  m*/
    try {
      conn = JDBCUtil.getConnection();

      giveRaise(conn, 102, 5.0);

      giveRaise(conn, -100, 5.0);
      JDBCUtil.commit(conn);
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      JDBCUtil.rollback(conn);
    } finally {
      JDBCUtil.closeConnection(conn);
    }
  }

  public static void giveRaise(Connection conn, int personId, double raise)
      throws SQLException {
    String SQL = "{call app.give_raise (?, ?, ?, ?)}";
    CallableStatement cstmt = null;
    try {
      cstmt = conn.prepareCall(SQL);

      cstmt.setInt(1, personId);
      cstmt.setDouble(2, raise);

      cstmt.registerOutParameter(3, Types.DOUBLE);
      cstmt.registerOutParameter(4, Types.DOUBLE);

      int updatedCount = cstmt.executeUpdate();

      double oldIncome = cstmt.getDouble(3);
      boolean oldIncomeisNull = cstmt.wasNull();

      double newIncome = cstmt.getDouble(4);
      boolean newIncomeisNull = cstmt.wasNull();

      System.out.println("Updated Record: " + updatedCount);

      System.out.println("Old Income: " + oldIncome + ", New Income: "
          + newIncome);

      System.out.println("Old Income was null: " + oldIncomeisNull
          + ", New Income is null: " + newIncomeisNull);
    } finally {
      JDBCUtil.closeStatement(cstmt);
    }
  }
}

class JDBCUtil {
  public static Connection getConnection() throws SQLException {
    // Register the Java DB embedded JDBC driver
    Driver derbyEmbeddedDriver = null;// new
                                      // org.apache.derby.jdbc.EmbeddedDriver();
    DriverManager.registerDriver(derbyEmbeddedDriver);

    String dbURL = "jdbc:derby:beginningJavaDB;create=true;";
    String userId = "root";
    String password = "password";

    Connection conn = DriverManager.getConnection(dbURL, userId, password);

    conn.setAutoCommit(false);

    return conn;
  }

  public static void closeConnection(Connection conn) {
    try {
      if (conn != null) {
        conn.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeStatement(Statement stmt) {
    try {
      if (stmt != null) {
        stmt.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeResultSet(ResultSet rs) {
    try {
      if (rs != null) {
        rs.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void commit(Connection conn) {
    try {
      if (conn != null) {
        conn.commit();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void rollback(Connection conn) {
    try {
      if (conn != null) {
        conn.rollback();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      System.out.println("Connetced to the database.");
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      closeConnection(conn);
    }
  }
}

Related Tutorials