Working with Stored Procedures with JDBC

How To Deal with Stored Procedures with JDBC 

To Deal with Stored Procedures with JDBC we need to create the connection and callable statement in the init() of the servlet.Here is an example on how to execute a stored procedure with JDBC ....



package app;
import java.sql.*;
public class Dips1

{

private String url = "jdbc:oracle:thin:@localhost:1521:XE";
private String driverClass = "oracle.jdbc.driver.OdbcDriver";
private Connection con;
private CallableStatement cs;

public Dips1()

{
try {
Class.forName(driverClass);
con= DriverManager.getConnection(url, "scott", "tiger" );
cs= con.prepareCall(
"{? = call sp_sav_Bom_Header( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }"
);
cs.registerOutParameter( 1, java.sql.Types.VARCHAR );
cs.setInt( 2, -2 );
cs.setInt( 3, 39 );
cs.setString( 4, "format" );
long ltTest = new java.util.Date().getTime();
System.out.println( "Today: " + ltTest );
cs.setTimestamp( 5, new Timestamp( ltTest ) );
cs.setString( 6, "type" );
cs.setString( 7, "submitter" );
cs.setString( 8, "email" );
cs.setString( 9, "phone" );
cs.setString( 10, "comments" );
cs.setString( 11, "label" );
cs.setInt( 12, 52 );
cs.setBoolean( 13, true );
cs.setBoolean( 14, false );
cs.setInt( 15, 53 );
cs.setString( 16, "runtime" );
cs.setString( 17, "configuration" );
cs.setBoolean( 18, true );
cs.setBoolean( 19, false );
cs.setString( 20, "special instructions" );
cs.setInt( 21, 54 );

ResultSet rs = null;
System.out.println( "Execute: " + (rs = cs.executeQuery() ) );
while( rs.next() ) {
System.out.println( "Got from result set: " + rs.getInt( 1 ) );
}
System.out.println( "Got from stored procedure: " + cs.getString( 1 ) );
} catch( Throwable e ) {
e.printStackTrace();
}
}

public static void main(String[] args)
    {
        new Dips1();
    }
    }

I also tried it by using a native JDBC driver (i-net) and it also works fine. The only problem we encounter with JDBC-ODBC bridge is that a stored procedure pads spaces to the full length of a VARCHAR but the native JDBC behaves right. Therefore It is suggested to use JDBC native drivers.
The above example uses the MS SQL Server.