JDBC Statements

There are three main types of Statement objects: the base class Statement, the PreparedStatement, and the CallableStatement. These objects are instantiated from your JDBC Connection object.

Following table provides a summary of each interface's purpose to understand how do you decide which interface to use:

Interfaces	Recommended Use
Statement	Use for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters.
PreparedStatement	used to run Pre compiled sql. Use when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.
CallableStatement	used to execute the stored procedures. The CallableStatement interface can also accept runtime input parameters.

Note:For simplicity, in the following examples we will use the Locations table in hr schema that we used earlier

JDBC Statement

JDBC Statement is an interface of java.sql.*; package. It is used for execution of SQL statements. It returns a ResultSet object. This result set object contains the result of the query. Statement interface provides basic method for SELECT, INSERT, UPDATE, DELETE operations in the database. Its query is compiled every time when request is made, therefore is little slower in comparison to PreparedStatement.

It’s example has been explained already here

                                        PreparedStatement

Executes a pre-compiled SQL statement with or without IN parameters. The PreparedStatement interface extends the Statement interface which gives you added functionality with a couple of advantages over a generic Statement object.It is useful because it avoids manual conversion of Java types to SQL types. PreparedStatement objects are precompiled.    
PROGRAM:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
class JDBCPreparedStatement
{
 public static void main( String args[] )
 {
  try
  {
      // Load the database driver
 Class.forName("oracle.jdbc.driver.OracleDriver");

      // Get a connection to the database
      Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr");


      // Prepare a statement
      PreparedStatement ps = conn.prepareStatement( "SELECT * FROM Locations WHERE LOCATION_ID = ? and POSTAL_CODE=?" ) ;

      // Set the first parameter of the statement
      ps.setInt( 1, 3100);
      // Set the second parameter of the statement
      ps.setString(2, "3029SK");

      // Execute the query
      ResultSet rs = ps.executeQuery() ;

      // Loop through the result set
      while( rs.next() )
         System.out.println( rs.getString("LOCATION_ID") + " " + rs.getString("STREET_ADDRESS") ) ;

      // Close the result set, statement and the connection
      rs.close() ;
      ps.close() ;
      conn.close() ;
  }
  catch( SQLException se )
  {
      System.out.println( "SQL Exception:" ) ;

      // Loop through the SQL Exceptions
      while( se != null )
      {
          System.out.println( "State  : " + se.getSQLState()  ) ;
          System.out.println( "Message: " + se.getMessage()   ) ;
          System.out.println( "Error  : " + se.getErrorCode() ) ;

          se = se.getNextException() ;
      }
  }
  catch( Exception e )
  {
      System.out.println( e ) ;
  }
 }
}

                                                   Callable Statements

A java.sql.CallableStatement is used to call stored procedures in a database.

A stored procedure is like a function or method in a class, except it lives inside the database. Some database heavy operations may benefit performance-wise from being executed inside the same memory space as the database server, as a stored procedure.

OUT Parameters

A stored procedure may return OUT parameters. That is, values that are returned instead of, or in addition to, a ResultSet. After executing the CallableStatement you can then access these OUTparameters from the CallableStatement object. 

See a full JDBC CallableStatement example for OUT parameter.
STORED PROCEDURE:

CREATE OR REPLACE PROCEDURE getLocationById(

             p_locationId IN Locations .LOCATION_ID%TYPE,

             o_street_address OUT Locations .STREET_ADDRESS%TYPE,

             o_postal_code OUT  Locations .POSTAL_CODE%TYPE,

             o_country_id OUT Locations .COUNTRY_ID%TYPE)

IS

BEGIN

  SELECT STREET_ADDRESS, POSTAL_CODE, COUNTRY_ID

  INTO o_street_address, o_postal_code,  o_country_id

  FROM  Locations WHERE LOCATION_ID= p_locationId;

END;

/

Commit;

Program:

import java.sql.CallableStatement;

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


public class JDBCCallableStatement {

public static void main(String[] argv) {
try {
            
callOracleStoredProcOUTParameter();

} catch (SQLException e) {

System.out.println(e.getMessage());

}
}

private static void callOracleStoredProcOUTParameter() throws SQLException {

Connection dbConnection = null;
CallableStatement callableStatement = null;
try
{
// Load the database driver
Class.forName("oracle.jdbc.driver.OracleDriver");

// Get a connection to the database
dbConnection =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr");

String getDBUSERByUserIdSql = "{call getLocationById(?,?,?,?)}";

            //preparing callable statement
callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);

callableStatement.setInt(1, 3100);
//registering Out parameters
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4, java.sql.Types.VARCHAR);

// execute getDBUSERByUserId store procedure
callableStatement.executeUpdate();
                       //accessing out parameters
String streetAddress = callableStatement.getString(2);
String postalCode = callableStatement.getString(3);
String countryId = callableStatement.getString(4);

System.out.println("Street Address : " + streetAddress);
System.out.println("Postal Code : " + postalCode);
System.out.println("Country Id : " + streetAddress);



} catch( SQLException se )
{
System.out.println( "SQL Exception:" ) ;

// Loop through the SQL Exceptions
while( se != null )
{
System.out.println( "State  : " + se.getSQLState()  ) ;
System.out.println( "Message: " + se.getMessage()   ) ;
System.out.println( "Error  : " + se.getErrorCode() ) ;

se = se.getNextException() ;
}
}
catch( Exception e )
{
System.out.println( e ) ;
}
finally {

if (callableStatement != null) {
callableStatement.close();
}

if (dbConnection != null) {
dbConnection.close();
}
}
}
}