Create JDBC CallableStatement (CS).

How to create a CallableStatement?

• The java.sql.Connection interface has several methods that can be used to create a java.sql.CallableStatement object that we can use to call database stored procedures.
  Some methods from java.sql.Connection interface to use:

  Methods in Connection interface	Description
  CallableStatement prepareCall(String sql)	Creates a CallableStatement object for calling database-stored procedures. If the return is a DB-table for a ResultSet, then the ResultSet will be non-updateable and non-scrollable.
  The next two methods are intended to be used when you expect an updatable ResultSet in return from the database-stored procedure.
  CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency)	When you use this method, the returned CallableStatement object can be used to query for a ResultSet (This require that the database-stored procedure must return a DB table) that can be updateable and can be scrollable.
  CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)	This will create a CallableStatement object as the previous one. Use this method when you also want to decide whether you want the resultset to stay open (resultSetHoldability) after a transaction is committed. (Only available in JDBC 3.0)

  
  The resultSetHoldability constant will indicates that a open ResultSet objects shall remain open or closed when the current transaction is committed.
• The CallableStatement interface extends the PreparedStatement interface, which means that the CallableStatement interface can use parametric placeholders for values in the SQL statement, which is given as first parameter to the prepareCall() method.
• Steps to create and execute a SQL statement using prepareCall() method are:
  1. When you have got a database Connection, create a CallableStatement with one of the prepareCall() methods above where the SQL statement contains a call to a known database-stored procedure.
  2. If any parametric placeholder exists in your SQL statement which can result in an output from the database-stored procedure you will call, then you must register those placeholders with a type that you will found in the java.sql.Types class (look in the API for Types). This registering is done by using one of the registerOutPutParameter() method, which you can find in the RegisterOutPutParameter methods table below.
  3. If the SQL statement, that contains the call to a database-stored procedure, has any input type placeholders, then you must set those values with the right type setXXX() method, which you can find in the table below.
  4. Now you can execute one of the Execute methods found in the table below. Which method you should choose depend on what you expect in return from the calling database-stored procedure.
  5. The CallableStatement object will now contain requested database data, which can be retrieved with the getXXX() methods (look in the table below), or if you used the executeQuery() method you can retrieve data using any of the getXXX() methods in the ResultSet.
  Example code:

  // The query should be on the form { call procedurename }
  String SQL = "{ ? = call getTotalcost(?,?) }"
  // Placeholders (?) index: 1 2 3 
  CallableStatement cs = conn.prepareCall(sql);
  // Placeholder that result in an output from the 
  // procedure should be registered with a one of the 
  // types found in the java.sql.Types class 
  cs.registerOutParameter(1, java.sql.Types.DOUBLE);
  cs.registerOutParameter(2, java.sql.Types.STRING);
  // set some values for the procedure
  cs.setString(3, 60);
  cs.execute();

Summary of execute methods in CallableStatement interface.

• When you have got a java.sql.CallableStatement object, several methods in that interface can be used to execute a parameterized SQL statement against the database.
  Execute methods in java.sql.CallableStatement interface to use:

  Methods in CallableStatement interface	Description
  int executeUpdate()	Executes the SQL procedure call statement in this CallableStatement object. The database procedure must do an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
  ResultSet executeQuery()	Executes the SQL procedure call statement in this CallableStatement object and returns the ResultSet object returned by the query contained in the database procedure.
  boolean execute()	Executes the SQL procedure call statement in this CallableStatement object. The database procedure may be contain any kind of SQL statement. This method is in most cases the preferred.

Summary of RegisterOutPutParameter methods in CallableStatement interface.

• When you have got a java.sql.CallableStatement object several methods on that object can be used to set placeholders as an output from what the SQL procedure call returns.
  RegisterOutPutParameter methods in java.sql.CallableStatement interface to use:

  Methods in CallableStatement interface	Description
  void registerOutParameter(int parameterIndex, int sqlType)	Registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType.
  void registerOutParameter(int parameterIndex, int sqlType, int scale)	Registers the parameter in ordinal position parameterIndex to be of JDBC type sqlType.
  void registerOutParameter(int parameterIndex, int sqlType, String typeName)	Registers the designated output parameter.
  void registerOutParameter(String parameterName, int sqlType)	Registers the OUT parameter named parameterName to the JDBC type sqlType.
  void registerOutParameter(String parameterName, int sqlType, int scale)	Registers the parameter named parameterName to be of JDBC type sqlType.
  void registerOutParameter(String parameterName, int sqlType, String typeName)	Registers the designated output parameter.

Summary of Set methods in CallableStatement interface.

• When you have got a java.sql.CallableStatement object several methods on that object can be used to set the value for placeholder in the SQL statement.
  Set methods in java.sql.CallableStatement interface to use:

  Methods in CallableStatement interface	Description
  void setAsciiStream(String parameterName, InputStream x)	Sets the designated parameter to the given input stream.
  void setAsciiStream(String parameterName, InputStream x, int length)	Sets the designated parameter to the given input stream, which will have the specified number of bytes.
  void setAsciiStream(String parameterName, InputStream x, long length)	Sets the designated parameter to the given input stream, which will have the specified number of bytes.
  void setBigDecimal(String parameterName, BigDecimal x)	Sets the designated parameter to the given java.math.BigDecimal value.
  void setBinaryStream(String parameterName, InputStream x)	Sets the designated parameter to the given input stream.
  void setBinaryStream(String parameterName, InputStream x, int length)	Sets the designated parameter to the given input stream, which will have the specified number of bytes.
  void setBinaryStream(String parameterName, InputStream x, long length)	Sets the designated parameter to the given input stream, which will have the specified number of bytes.
  void setBlob(String parameterName, Blob x)	Sets the designated parameter to the given java.sql.Blob object.
  void setBlob(String parameterName, InputStream inputStream)	Sets the designated parameter to a InputStream object.
  void setBlob(String parameterName, InputStream inputStream, long length)	Sets the designated parameter to a InputStream object.
  void setBoolean(String parameterName, boolean x)	Sets the designated parameter to the given Java boolean value.
  void setByte(String parameterName, byte x)	Sets the designated parameter to the given Java byte value.
  void setBytes(String parameterName, byte[] x)	Sets the designated parameter to the given Java array of bytes.
  void setCharacterStream(String parameterName, Reader reader)	Sets the designated parameter to the given Reader object.
  void setCharacterStream(String parameterName, Reader reader, int length)	Sets the designated parameter to the given Reader object, which is the given number of characters long.
  void setCharacterStream(String parameterName, Reader reader, long length)	Sets the designated parameter to the given Reader object, which is the given number of characters long.
  void setClob(String parameterName, Clob x)	Sets the designated parameter to the given java.sql.Clob object.
  void setClob(String parameterName, Reader reader)	Sets the designated parameter to a Reader object.
  void setClob(String parameterName, Reader reader, long length)	Sets the designated parameter to a Reader object.
  void setDate(String parameterName, Date x)	Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.
  void setDate(String parameterName, Date x, Calendar cal)	Sets the designated parameter to the given java.sql.Date value, using the given Calendar object.
  void setDouble(String parameterName, double x)	Sets the designated parameter to the given Java double value.
  void setFloat(String parameterName, float x)	Sets the designated parameter to the given Java float value.
  void setInt(String parameterName, int x)	Sets the designated parameter to the given Java int value.
  void setLong(String parameterName, long x)	Sets the designated parameter to the given Java long value.
  void setNCharacterStream(String parameterName, Reader value)	Sets the designated parameter to a Reader object.
  void setNCharacterStream(String parameterName, Reader value, long length)	Sets the designated parameter to a Reader object.
  void setNClob(String parameterName, NClob value)	Sets the designated parameter to a java.sql.NClob object.
  void setNClob(String parameterName, Reader reader)	Sets the designated parameter to a Reader object.
  void setNClob(String parameterName, Reader reader, long length)	Sets the designated parameter to a Reader object.
  void setNString(String parameterName, String value)	Sets the designated parameter to the given String object.
  void setNull(String parameterName, int sqlType)	Sets the designated parameter to SQL NULL.
  void setNull(String parameterName, int sqlType, String typeName)	Sets the designated parameter to SQL NULL.
  void setObject(String parameterName, Object x)	Sets the value of the designated parameter with the given object.
  void setObject(String parameterName, Object x, int targetSqlType)	Sets the value of the designated parameter with the given object.
  void setObject(String parameterName, Object x, int targetSqlType, int scale)	Sets the value of the designated parameter with the given object.
  void setRowId(String parameterName, RowId x)	Sets the designated parameter to the given java.sql.RowId object.
  void setShort(String parameterName, short x)	Sets the designated parameter to the given Java short value.
  void setSQLXML(String parameterName, SQLXML xmlObject)	Sets the designated parameter to the given java.sql.SQLXML object.
  void setString(String parameterName, String x)	Sets the designated parameter to the given Java String value.
  void setTime(String parameterName, Time x)	Sets the designated parameter to the given java.sql.Time value.
  void setTime(String parameterName, Time x, Calendar cal)	Sets the designated parameter to the given java.sql.Time value, using the given Calendar object.
  void setTimestamp(String parameterName, Timestamp x)	Sets the designated parameter to the given java.sql.Timestamp value.
  void setTimestamp(String parameterName, Timestamp x, Calendar cal)	Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object.
  void setURL(String parameterName, URL val)	Sets the designated parameter to the given java.net.URL object.
  boolean wasNull()	Retrieves whether the last OUT parameter read had the value of SQL NULL.

Summary of Get methods in CallableStatement interface.

• After you have execute the CallableStatement several methods on that interface can be used to get the value for placeholder in SQL statements.
  Get methods in java.sql.CallableStatement interface to use:

  Methods in CallableStatement interface	Description
  Array getArray(int parameterIndex)	Retrieves the value of the designated JDBC ARRAY parameter as an Array object in the Java programming language.
  Array getArray(String parameterName)	Retrieves the value of a JDBC ARRAY parameter as an Array object in the Java programming language.
  BigDecimal getBigDecimal(int parameterIndex)	Retrieves the value of the designated JDBC NUMERIC parameter as a java.math.BigDecimal object with as many digits to the right of the decimal point as the value contains.
  BigDecimal getBigDecimal(String parameterName)	Retrieves the value of a JDBC NUMERIC parameter as a java.math.BigDecimal object with as many digits to the right of the decimal point as the value contains.
  Blob getBlob(int parameterIndex)	Retrieves the value of the designated JDBC BLOB parameter as a Blob object in the Java programming language.
  Blob getBlob(String parameterName)	Retrieves the value of a JDBC BLOB parameter as a Blob object in the Java programming language.
  boolean getBoolean(int parameterIndex)	Retrieves the value of the designated JDBC BIT or BOOLEAN parameter as a boolean in the Java programming language.
  boolean getBoolean(String parameterName)	Retrieves the value of a JDBC BIT or BOOLEAN parameter as a boolean in the Java programming language.
  byte getByte(int parameterIndex)	Retrieves the value of the designated JDBC TINYINT parameter as a byte in the Java programming language.
  byte getByte(String parameterName)	Retrieves the value of a JDBC TINYINT parameter as a byte in the Java programming language.
  byte[] getBytes(int parameterIndex)	Retrieves the value of the designated JDBC BINARY or VARBINARY parameter as an array of byte values in the Java programming language.
  byte[] getBytes(String parameterName)	Retrieves the value of a JDBC BINARY or VARBINARY parameter as an array of byte values in the Java programming language.
  Reader getCharacterStream(int parameterIndex)	Retrieves the value of the designated parameter as a java.io.Reader object in the Java programming language.
  Reader getCharacterStream(String parameterName)	Retrieves the value of the designated parameter as a java.io.Reader object in the Java programming language.
  Clob getClob(int parameterIndex)	Retrieves the value of the designated JDBC CLOB parameter as a java.sql.Clob object in the Java programming language.
  Clob getClob(String parameterName)	Retrieves the value of a JDBC CLOB parameter as a java.sql.Clob object in the Java programming language.
  Date getDate(int parameterIndex)	Retrieves the value of the designated JDBC DATE parameter as a java.sql.Date object.
  Date getDate(int parameterIndex, Calendar cal)	Retrieves the value of the designated JDBC DATE parameter as a java.sql.Date object, using the given Calendar object to construct the date.
  Date getDate(String parameterName)	Retrieves the value of a JDBC DATE parameter as a java.sql.Date object.
  Date getDate(String parameterName, Calendar cal)	Retrieves the value of a JDBC DATE parameter as a java.sql.Date object, using the given Calendar object to construct the date.
  double getDouble(int parameterIndex)	Retrieves the value of the designated JDBC DOUBLE parameter as a double in the Java programming language.
  double getDouble(String parameterName)	Retrieves the value of a JDBC DOUBLE parameter as a double in the Java programming language.
  float getFloat(int parameterIndex)	Retrieves the value of the designated JDBC FLOAT parameter as a float in the Java programming language.
  float getFloat(String parameterName)	Retrieves the value of a JDBC FLOAT parameter as a float in the Java programming language.
  int getInt(int parameterIndex)	Retrieves the value of the designated JDBC INTEGER parameter as an int in the Java programming language.
  int getInt(String parameterName)	Retrieves the value of a JDBC INTEGER parameter as an int in the Java programming language.
  long getLong(int parameterIndex)	Retrieves the value of the designated JDBC BIGINT parameter as a long in the Java programming language.
  long getLong(String parameterName)	Retrieves the value of a JDBC BIGINT parameter as a long in the Java programming language.
  Reader getNCharacterStream(int parameterIndex)	Retrieves the value of the designated parameter as a java.io.Reader object in the Java programming language.
  Reader getNCharacterStream(String parameterName)	Retrieves the value of the designated parameter as a java.io.Reader object in the Java programming language.
  NClob getNClob(int parameterIndex)	Retrieves the value of the designated JDBC NCLOB parameter as a java.sql.NClob object in the Java programming language.
  NClob getNClob(String parameterName)	Retrieves the value of a JDBC NCLOB parameter as a java.sql.NClob object in the Java programming language.
  String getNString(int parameterIndex)	Retrieves the value of the designated NCHAR, NVARCHAR or LONGNVARCHAR parameter as a String in the Java programming language.
  String getNString(String parameterName)	Retrieves the value of the designated NCHAR, NVARCHAR or LONGNVARCHAR parameter as a String in the Java programming language.
  Object getObject(int parameterIndex)	Retrieves the value of the designated parameter as an Object in the Java programming language.
  Object getObject(int parameterIndex, Map<String,Class<?>> map)	Returns an object representing the value of OUT parameter parameterIndex and uses map for the custom mapping of the parameter value.
  Object getObject(String parameterName)	Retrieves the value of a parameter as an Object in the Java programming language.
  Object getObject(String parameterName, Map<String,Class<?>> map)	Returns an object representing the value of OUT parameter parameterName and uses map for the custom mapping of the parameter value.
  Ref getRef(int parameterIndex)	Retrieves the value of the designated JDBC REF(<structured-type>) parameter as a Ref object in the Java programming language.
  Ref getRef(String parameterName)	Retrieves the value of a JDBC REF(<structured-type>) parameter as a Ref object in the Java programming language.
  RowId getRowId(int parameterIndex)	Retrieves the value of the designated JDBC ROWID parameter as a java.sql.RowId object.
  RowId getRowId(String parameterName)	Retrieves the value of the designated JDBC ROWID parameter as a java.sql.RowId object.
  short getShort(int parameterIndex)	Retrieves the value of the designated JDBC SMALLINT parameter as a short in the Java programming language.
  short getShort(String parameterName)	Retrieves the value of a JDBC SMALLINT parameter as a short in the Java programming language.
  SQLXML getSQLXML(int parameterIndex)	Retrieves the value of the designated SQL XML parameter as a java.sql.SQLXML object in the Java programming language.
  SQLXML getSQLXML(String parameterName)	Retrieves the value of the designated SQL XML parameter as a java.sql.SQLXML object in the Java programming language.
  String getString(int parameterIndex)	Retrieves the value of the designated JDBC CHAR, VARCHAR, or LONGVARCHAR parameter as a String in the Java programming language.
  String getString(String parameterName)	Retrieves the value of a JDBC CHAR, VARCHAR, or LONGVARCHAR parameter as a String in the Java programming language.
  Time getTime(int parameterIndex)	Retrieves the value of the designated JDBC TIME parameter as a java.sql.Time object.
  Time getTime(int parameterIndex, Calendar cal)	Retrieves the value of the designated JDBC TIME parameter as a java.sql.Time object, using the given Calendar object to construct the time.
  Time getTime(String parameterName)	Retrieves the value of a JDBC TIME parameter as a java.sql.Time object.
  Time getTime(String parameterName, Calendar cal)	Retrieves the value of a JDBC TIME parameter as a java.sql.Time object, using the given Calendar object to construct the time.
  Timestamp getTimestamp(int parameterIndex)	Retrieves the value of the designated JDBC TIMESTAMP parameter as a java.sql.Timestamp object.
  Timestamp getTimestamp(int parameterIndex, Calendar cal)	Retrieves the value of the designated JDBC TIMESTAMP parameter as a java.sql.Timestamp object, using the given Calendar object to construct the Timestamp object.
  Timestamp getTimestamp(String parameterName)	Retrieves the value of a JDBC TIMESTAMP parameter as a java.sql.Timestamp object.
  Timestamp getTimestamp(String parameterName, Calendar cal)	Retrieves the value of a JDBC TIMESTAMP parameter as a java.sql.Timestamp object, using the given Calendar object to construct the Timestamp object.
  URL getURL(int parameterIndex)	Retrieves the value of the designated JDBC DATALINK parameter as a java.net.URL object.
  URL getURL(String parameterName)	Retrieves the value of a JDBC DATALINK parameter as a java.net.URL object.
  boolean wasNull()	Retrieves whether the last OUT parameter read had the value of SQL NULL.