Wednesday, June 25, 2008

How to Call Stored procedure in JAVA

For making Connection load sqljdbc.jar from Microsoft website.
Connection Provider Class.
package database;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MSSQLDBConnection {
    private MSSQLDBConnection(){
    }
    static Connection conn=null;

    static{
       try {
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver") ;
      } catch (ClassNotFoundException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
    }

    public static Connection getConnection() throws SQLException, IOException {
      if(conn==null){
         conn = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=TestDB","sa","sql");
      }
         return conn;
    }
}

Java Class for Calling STored procedure in MSSQL Database
package database.storedprocedure;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import database.MSSQLDBConnection;

public class CallStoredProcedure {

   /**
   * @param args
   */
   public static void main(String[] args) {
       Connection conn=null;
       try {
           conn=MSSQLDBConnection.getConnection();
       } catch (SQLException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       } catch (IOException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }

      CallableStatement callableStatement;
      try {
           callableStatement = conn.prepareCall("{ call spInsertEventContact(?,?,?,?,?,?) }");
           callableStatement.setInt(1, 12);
           callableStatement.setInt(2, 13);
           callableStatement.setString(3, "ASKER");
           callableStatement.setString(4, "ALI");
           callableStatement.setInt(5, 1);
           callableStatement.registerOutParameter(6, java.sql.Types.INTEGER);
           callableStatement.executeUpdate();
      } catch (SQLException e) {
           e.printStackTrace();
      }finally{
           try{
               conn.close();
           }catch (Exception ignore){}
      }

      System.out.println("Connection created");
   }
}
For database Connections please refer: IBM Universal database

1 comment:

Asker Ali M said...

To get the Stored procedure returned value use the following code.
CallableStatement cstmt = con.prepareCall(
"{call getTestData(?, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);
ResultSet rs = cstmt.executeQuery();
// . . . retrieve result set values with rs.getXXX methods
byte x = cstmt.getByte(1);
java.math.BigDecimal n = cstmt.getBigDecimal(2);