JDBC : 06 - CallableStatement Interface Example

CallableStatement Interface Example

To call the stored procedures and functions, CallcableStatement Interface used.

When we have the business logic in the database, we can use the stored procedures and functions to do the execution by using CallableStatements.

DROP PROCEDURE IF EXISTS INSERTREC;

CREATE PROCEDURE INSERTREC (

IN m_USER_ID int,

IN m_USERNAME varchar(20),

IN m_CREATED_BY varchar(20),

IN m_CREATED_DATE date

)

INSERT INTO myworkspace.DBUSER (USER_ID, USERNAME, CREATED_BY, CREATED_DATE)

VALUES (m_USER_ID, m_USERNAME, m_CREATED_BY, m_CREATED_DATE);

DROP FUNCTION IF EXISTS MULTIPLY;

CREATE FUNCTION MULTIPLY(

m_Num1 int,

m_Num2 int

)

RETURNS int

RETURN m_Num1 * m_Num2;

Example : -

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.Date;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

/**

* @author sanjeeva

* To call the stored procedures and functions, CallcableStatement Interface used.

*

* callableStatement.execute();

* Returns:

     * [true] if the first result is a ResultSet object;

     * [false] if the first result is an update count or there is no result

*/

public class CallableStatementExample {

       Connection connection = null;

       /**

       * @param args

       */

       public static void main(String[] args) {

              CallableStatementExample example = new CallableStatementExample();

              example.execute();

       }

       private void execute(){ 

              try {

                     Class.forName("com.mysql.jdbc.Driver");

                     String username = "root";

                     String password = "res13pg";

                     connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/myworkspace",username,password);

                     if(null != connection){

                            callProcedure(connection);

                            callFunction(connection);

                     }

              }catch (Exception e) {

                     e.printStackTrace();

              } finally {

                     if(null != connection){

                           try{

                                connection.close();

                           }catch (Exception e) {

                                // TODO: handle exception

                           }

                     }

              }

       }

       private void callProcedure(Connection connection) throws SQLException{

              CallableStatement callableStatement = null;

              callableStatement = connection.prepareCall("{call INSERTREC(?,?,?,?)}");

              callableStatement.setInt(1, 106);

              callableStatement.setString(2, "Chandima");

              callableStatement.setString(3, "system");

              callableStatement.setDate(4, new Date(new java.util.Date().getTime()));

              boolean execute = callableStatement.execute();

              System.out.println("Execute [callProcedure] --> " + execute);

              callableStatement.close();

       }

       private void callFunction(Connection connection) throws SQLException{

              CallableStatement callableStatement = null;

              callableStatement = connection.prepareCall("{? = call MULTIPLY(?,?)}");

              callableStatement.setInt(2, 106);

              callableStatement.setInt(3, 625);

              callableStatement.registerOutParameter(1, Types.INTEGER);

              boolean execute = callableStatement.execute();

              System.out.println("Execute [callFunction] --> " + execute);

              System.out.println("Multiply Value --> " + callableStatement.getInt(1));

              callableStatement.close();

       }

}

Output :

Execute [callProcedure] --> false

Execute [callFunction] --> false

Multiply Value --> 66250