Java statement pro

Write a program for Callable Statement with stored procedure returns OUT parameters

package com.java2novice.jdbc;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

public class MyOutparmCallableStmt

{

public static void main(String a[])

{

        Connection con = null;

        CallableStatement callSt = null;

        try

      {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            con = DriverManager.

                    getConnection("jdbc:oracle:thin: ::" ,"user","password");

            callSt = con.prepareCall("{call myprocedure(?,?)}");

            callSt.setInt(1,200);

            callSt.registerOutParameter(2, Types.Double);

            callSt.execute();

            Double output = callSt.getDouble(2);

            System.out.println("The output returned from stored procedure: "+output);

        }

         catch (ClassNotFoundException e)

         {

            e.printStackTrace();

        }

        catch (SQLException e)

        {

                    e.printStackTrace();

        }

        finally

        {

            try

             {

                if(callSt != null) callSt.close();

                if(con != null) con.close();

            }

            catch(Exception ex){}

        }

    }

}

Write a program for Callable statement with batch execution

package com.java2novice.jdbc;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class MyBatchCallableStatement

{

public static void main(String a[])

{

        Connection con = null;

        CallableStatement callSt = null;

        try

      {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            con = DriverManager.

                getConnection("jdbc:oracle:thin: ::","user","password");

            callSt = con.prepareCall("{call myprocedure(?,?)}");

            callSt.setInt(1,200);

            callSt.setDouble(2, 3000);

            callSt.addBatch();

            callSt.setInt(1,130);

            callSt.setDouble(2, 2000);

            callSt.addBatch();

            int[] updateCounts = callSt.executeBatch();

        }

         catch (ClassNotFoundException e)

        {

            e.printStackTrace();

        }

        catch (SQLException e)

        {

             e.printStackTrace();

        }  

        finally

         {

            try

             {

                if(callSt != null) callSt.close();

                if(con != null) con.close();

            }

            catch(Exception ex){}

        }

    }

}

Write a program to execute SQL function using Callable Statement

package com.java2novice.jdbc;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

public class MyFunctionCallableStmt

{

public static void main(String a[])

{

        Connection con = null;

        CallableStatement callSt = null;

        try

       {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            con = DriverManager.

                    getConnection("jdbc:oracle:thin:::","user","password");

            callSt = con.prepareCall("{?= call myfunction(?,?)}");

            callSt.setInt(1,200);

            callSt.registerOutParameter(2, Types.Double);

            callSt.execute();

            Double output = callSt.getDouble(2);

            System.out.println("The output returned from sql function: "+output);

        }

        catch (ClassNotFoundException e)

         {

             e.printStackTrace();

        }

        catch (SQLException e)

        {

           e.printStackTrace();

        }

        finally

         {

            try

            {

                if(callSt != null) callSt.close();

                if(con != null) con.close();

            }

            catch(Exception ex){}

        }

    }

}

Write a program to execute SQL cursors using Callable Statement

package com.java2novice.jdbc;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

public class MyCursorCallableStmt

{

public static void main(String a[])

{

        Connection con = null;

        CallableStatement callSt = null;

        ResultSet rs = null;

        try

      {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            con = DriverManager.

                getConnection("jdbc:oracle:thin: ::","user","password");

            callSt = con.prepareCall("{call myCursorExmp(?,?)}");

            callSt.setInt(1,200);

            callSt.registerOutParameter(2, OracleTypes.Cursor);

            callSt.execute();

            rs = (ResultSet)callSt.getObject(2);

            while(rs.next())

            {

                System.out.println(rs.getInt(1));

            }

        }

        catch (ClassNotFoundException e)

        {

              e.printStackTrace();

        }

        catch (SQLException e)

        {

            e.printStackTrace();

        }

        finally

         {

            try

             {

                if(rs != null) rs.close();

                if(callSt != null) callSt.close();

                if(con != null) con.close();

            }

            catch(Exception ex){}

        }

    }

}