Loading
View RSS Feed

indies

The Best Way To Learn JDBC Programs Part 2

Rate this Entry
Visit First part here : The Best Way To Learn JDBC Programs Part 1
Callable statement example

Code:
public class CallableStmt
    {
     public static void main(String args[])
         {
         try
             {
             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
             Connection con = DriverManager.getConnection("jdbc:odbc:uma","kworker","kworker");
            
             //calling a stored procedure with no input/output param
             /*
             CREATE PROCEDURE HELLOWORLD
             AS
             SELECT 'HELLOWORLD' AS HELLO
             */
             CallableStatement cs1 = con.prepareCall("{call HelloWorld}");
             ResultSet rs1 = cs1.executeQuery();
             while(rs1.next())
                 {
                 String one = rs1.getString("HELLO");
                 System.out.println(one);
             }
            
            
             //Calling a stored procedure which takes in 2 parameters for addition
             /*
             --EXECUTE ADDITION 10,25,NULL
             ALTER PROCEDURE ADDITION
             @A INT
             , @B INT
             , @C INT OUT
             AS
             SELECT @C = @A + @B
             */
             CallableStatement cs2 = con.prepareCall("{call ADDITION(?,?,?)}");
             cs2.registerOutParameter(3,java.sql.Types.INTEGER);
             cs2.setInt(1,10);
             cs2.setInt(2,25);
             cs2.execute();
             int res = cs2.getInt(3);
             System.out.println(res);
            
             //Another way
             /*
             --create table test(slno int,ques varchar(100),ans text)
             --EXECUTE fetchRec 1



             create procedure fetchRec
             @A int
             as
             select * from test where slno=@A
             */
             CallableStatement cs3 = con.prepareCall("{call fetchRec(?)}");
             cs3.registerOutParameter(1,java.sql.Types.INTEGER);
             cs3.setInt(1,5);
             ResultSet rs3 = cs3.executeQuery();
             while(rs3.next())
                 {
                 String ques = rs3.getString(2);
                 String ans = rs3.getString(3);
                 System.out.println(ques);
                 System.out.println(ans);
             }
            
            
         }
         catch(Exception e)
             {
             e.printStackTrace();
         }
     }
}
Sometimes this is not possible to retrieve the whole manipulated data with a single query from database. So many queries clubbed together to form a single block. This block is known as procedures in database. So if you want to call these blocks from your java programs so you need to use a special statement i.e Callable Statement. The below example will demonstrate the parameter passing, calling mechanism and also getting the results from the Database procedures.
Suppose, there is a procedure "Test" for the adding the two numbers. Here is code for the procedure :-

Code:
  TG DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`Test` $$ 
CREATE DEFINER=`root`@`localhost` PROCEDURE `Test`(IN num1 INT, IN num2 INT ,OUT param1 INT)
BEGIN 
set param1 := num1 + num2; 
END $$ DELIMITER ;
The following code will call the procedure “Test”.


Code:
package com.visualbuilder; 
import java.sql.CallableStatement; 
import java.sql.Connection; 
import java.sql.DriverManager;
import java.sql.ResultSet; 
import java.sql.Types; 
public class CallableStatementExample { 
  public static void main(String[] args) { 
   try { 
        /** Loading the driver*/ 
        Class.forName("com.mysql.jdbc.Driver");
        /** Getting Connection*/ 
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root"); 
        /** Creating Statement*/ 
                CallableStatement call = con.prepareCall("call test.Test(?,?,?)"); 
        call.setInt(1,2); 
        call.setInt(2,2); 
        call.registerOutParameter(3,Types.INTEGER); 
        call.execute(); 
                System.out.println("The addition is "+call.getInt(3));
        con.close(); 
                } catch (Exception e) {
                         e.printStackTrace();
                } 
        }
}
Output is:-
Code:
The addition is 4
A JDBC driver is a software component enabling a Java application to interact with a database.

To connect with individual databases, JDBC (the Java Database Connectivity API) requires drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.

Type 1 Driver - JDBC-ODBC bridge

Schematic of the JDBC-ODBC bridge

The JDBC type 1 driver, also known as the JDBC-ODBC bridge, is a database driver implementation that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls. The bridge is usually used when there is no pure-Java driver available for a particular database.

The driver is platform-dependent as it makes use of ODBC which in turn depends on native libraries of the underlying operating system the JVM is running upon. Also, use of this driver leads to other installation dependencies; for example, ODBC must be installed on the computer having the driver and the database must support an ODBC driver. The use of this driver is discouraged if the alternative of a pure-Java driver is available. The other implication is that any application using a type 1 driver is non-portable given the binding between the driver and platform.

Type 1 is the simplest of all but platform specific e.g. only to Microsoft platform.


Functions
Translates query obtained by JDBC into corresponding ODBC query, which is then handled by the ODBC driver.
Sun provides a JDBC-ODBC Bridge driver. sun.jdbc.odbc.JdbcOdbcDriver. This driver is native code and not Java, and is closed source.
Client -> JDBC Driver -> ODBC Driver -> Database
There is some overhead associated with the translation work to go from JDBC to ODBC.


Advantages
Almost any database for which ODBC driver is installed, can be accessed.
A type 1 driver is easy to install.


Disadvantages
Performance overhead since the calls have to go through the JDBC overhead bridge to the ODBC driver, then to the native db connectivity interface.
The ODBC driver needs to be installed on the client machine.
Considering the client-side software needed, this might not be suitable for applets.
Will not be suitable for internet applications.

Submit "The Best Way To Learn JDBC Programs Part 2" to Digg Submit "The Best Way To Learn JDBC Programs Part 2" to del.icio.us Submit "The Best Way To Learn JDBC Programs Part 2" to StumbleUpon Submit "The Best Way To Learn JDBC Programs Part 2" to Google

Updated 11-23-2011 at 09:30 PM by angad

Categories
Java , Web Design

Comments




Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.