• JDBC CallableStatement Example

    JDBC CallableStatement Example

    The JDBC CallableStatement object allows us to call a database stored procedure to Java Application. A CallableStatement object contains a call to a stored procedure, it does not contain the stored procedure itself, as the stored procedure is stored in the database like Oracle.

    CallableStatement extends PreparedStatment, so a CallableStatement object can also take output parameters or parameters that are for both input and output.

    Here, I have created simple JDBC CallableStatement example that call the stored procedure from Oracle user and perform the database operations.

    JDBC CallableStatement Program Code

    1. In oracle login to appropriate account (scott/tiger) and create following table and procedure.
    CREATE TABLE EMPLOYEE(

    “ENO”   VARCHAR2(10 BYTE),

    “ENAME” VARCHAR2(10 BYTE),

    “EDESIGNATION” VARCHAR2(10 BYTE),

    “ECITY” VARCHAR2(10 BYTE)

    );

    CREATE OR REPLACE PROCEDURE insertEmployee

    (in_id IN EMPLOYEE.ENO%TYPE,

    in_name IN EMPLOYEE.ENAME%TYPE,

    in_desig IN EMPLOYEE.EDESIGNATION%TYPE,

    in_city IN EMPLOYEE.ECITY%TYPE,

    out_result OUT VARCHAR2) AS

    BEGIN

    INSERT INTO EMPLOYEE values(in_id,in_name,in_desig,in_city);

    commit;

    out_result := ‘TRUE’;

    EXCEPTION

    WHEN OTHERS THEN

    out_result := ‘FALSE’;

    ROLLBACK;

    END;

    1. Now start Netbeans and draw the form.
    JDBC CallableStatement Example

    JDBC CallableStatement Example

    1. We need to add OracleJDBC driver libraries to this project. For that, Click on NetBeans -> Tools -> Libraries -> New library (Give Descriptive Name : OracleJDBC).
    2. Click on Add JAR/Folder Button and find the path for “ojdbc14.jar” and all other jar files. (Path: C:\oracle\product\10.2.0\db_1\jdbc\lib) and select all files and add to library.
    3. Now in projects area find the “libraries” folder and right-click on it and select “Add Libraries”.
    4. Add the Library which have created in step:3 (OracleJDBC) .
    5. After completing the design, click on source tab and start to declare variable in general declaration under class area.
    Connection c;Statement s;

    ResultSet r;

    CallableStatementstmt = null;

    1. Now write down necessary code into class constructor which are need to initialize once like, calling Class.forName() method, initialize connection, creating ResultSet.
    public Practical03CallStateConnection() {initComponents();

    try{

    Class.forName(“oracle.jdbc.driver.OracleDriver”);

    c = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:orcl”,”scott”,”tiger”);

    s = c.createStatement();

    r = s.executeQuery(“Select * From Employee”);

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    1. Write down the operation code on ActionPerformed event of appropriate button Like, Insert Button, and Save Button.
    private void btnInsertActionPerformed(java.awt.event.ActionEventevt) {// TODO add your handling code here:

    tfENo.setText(“”);

    tfEName.setText(“”);

    tfEDesignation.setText(“”);

    tfECity.setText(“”);

    }

    private void btnSaveActionPerformed(java.awt.event.ActionEventevt) {

    // TODO add your handling code here:

    String ENo = tfENo.getText();

    String EName = tfEName.getText();

    String EDesig = tfEDesignation.getText();

    String ECity = tfECity.getText();

    try{

    stmt = c.prepareCall(“{call insertEmployee(?,?,?,?,?)}”);

    stmt.setString(1, ENo);

    stmt.setString(2, EName);

    stmt.setString(3, EDesig);

    stmt.setString(4, ECity);

    //register the OUT parameter before calling the stored procedure

    stmt.registerOutParameter(5, java.sql.Types.VARCHAR);

    stmt.executeUpdate();

    //read the OUT parameter now

    String result = stmt.getString(5);

    System.out.println(“Employee Record Save Success::”+result);

    }catch(Exception e){

    e.printStackTrace();

    }

    }

    1. Write down the navigation code on ActionPerformed event of appropriate button Like, Move First, Move Previous, Move Next, and Move Last.
    private void btnFirstActionPerformed(java.awt.event.ActionEventevt) {// TODO add your handling code here:

    try{

    r.first();

    tfENo.setText(r.getString(“ENo”));

    tfEName.setText(r.getString(“EName”));

    tfEDesignation.setText(r.getString(“EDesignation”));

    tfECity.setText(r.getString(“ECity”));

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    private void btnPreviousActionPerformed(java.awt.event.ActionEventevt) {

    // TODO add your handling code here:

    try{

    r.previous();

    if(r.isBeforeFirst()){

    r.first();

    }

    tfENo.setText(r.getString(“ENo”));

    tfEName.setText(r.getString(“EName”));

    tfEDesignation.setText(r.getString(“EDesignation”));

    tfECity.setText(r.getString(“ECity”));

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    private void btnNextActionPerformed(java.awt.event.ActionEventevt) {

    // TODO add your handling code here:

    try{

    r.next();

    if(r.isAfterLast()){

    r.last();

    }

    tfENo.setText(r.getString(“ENo”));

    tfEName.setText(r.getString(“EName”));

    tfEDesignation.setText(r.getString(“EDesignation”));

    tfECity.setText(r.getString(“ECity”));

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    private void btnLastActionPerformed(java.awt.event.ActionEventevt) {

    // TODO add your handling code here:

    try{

    r.last();

     

    tfENo.setText(r.getString(“ENo”));

    tfEName.setText(r.getString(“EName”));

    tfEDesignation.setText(r.getString(“EDesignation”));

    tfECity.setText(r.getString(“ECity”));

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    1. Now Save and Run Program.
    Appreciate my work :Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestShare on RedditShare on StumbleUponShare on TumblrDigg thisShare on YummlyShare on VKFlattr the authorBuffer this page

Leave a Reply

Your email address will not be published. Required fields are marked *