io.ebean

Interface CallableSql

public interface CallableSql

For making calls to stored procedures. Refer to the Ebean execute() method.

Note that UpdateSql is designed for general DML sql and CallableSql is designed for use with stored procedures. Also note that when using this in batch mode the out parameters are not read.

Example 1:

String sql = "{call sp_order_mod(?,?)}";

CallableSql cs = DB.createCallableSql(sql);
cs.setParameter(1, "turbo");
cs.registerOut(2, Types.INTEGER);

DB.execute(cs);

// read the out parameter
Integer returnValue = (Integer) cs.getObject(2);

Example 2:
Includes batch mode, table modification information and label. Note that the label is really only to help people reading the transaction logs to identify the procedure called etc.

String sql = "{call sp_insert_order(?,?)}";

CallableSql cs = DB.createCallableSql(sql);

// Inform Ebean this stored procedure inserts into the
// oe_order table and inserts + updates the oe_order_detail table.
// this is used to invalidate objects in the cache
cs.addModification("oe_order", true, false, false);
cs.addModification("oe_order_detail", true, true, false);


try (Transaction t = DB.beginTransaction()) {

  // execute using JDBC batching 10 statements at a time
  t.setBatchMode(true);
  t.setBatchSize(10);

  cs.setParameter(1, "Was");
  cs.setParameter(2, "Banana");
  DB.execute(cs);

  cs.setParameter(1, "Here");
  cs.setParameter(2, "Kumera");
  DB.execute(cs);

  cs.setParameter(1, "More");
  cs.setParameter(2, "Apple");
  DB.execute(cs);

  // DB.externalModification("oe_order",true,false,false);
  // DB.externalModification("oe_order_detail",true,true,false);
  t.commit();

}

See Also:

SqlUpdate

Method Summary

Modifier and Type	Method and Description
CallableSql	addModification(String tableName, boolean inserts, boolean updates, boolean deletes) Add table modification information to the TransactionEvent.
CallableSql	bind(int position, Object value) Bind a parameter that is bound as a IN parameter.
boolean	executeOverride(CallableStatement cstmt) You can extend this object and override this method for more advanced stored procedure calls.
String	getLabel() Return the label that is put into the transaction log.
Object	getObject(int position) Return an OUT parameter value.
String	getSql() Return the callable sql.
int	getTimeout() Return the statement execution timeout.
CallableSql	registerOut(int position, int type) Register an OUT parameter.
CallableSql	setLabel(String label) Set the label that is put in the transaction log.
CallableSql	setParameter(int position, Object value) Bind a positioned parameter (same as bind method).
CallableSql	setSql(String sql) Set the callable sql.
CallableSql	setTimeout(int secs) Set the statement execution timeout.

Method Detail

getLabel

String getLabel()

Return the label that is put into the transaction log.

setLabel

CallableSql setLabel(String label)

Set the label that is put in the transaction log.

getTimeout

int getTimeout()

Return the statement execution timeout.

getSql

String getSql()

Return the callable sql.

setTimeout

CallableSql setTimeout(int secs)

Set the statement execution timeout. Zero implies unlimited time.

This is set to the underlying CallableStatement.

setSql

CallableSql setSql(String sql)

Set the callable sql.

bind

CallableSql bind(int position,
                 Object value)

Bind a parameter that is bound as a IN parameter.

position starts at value 1 (not 0) to be consistent with CallableStatement.

This is designed so that you do not need to set params in index order. You can set/register param 2 before param 1 etc.

Parameters:

position - the index position of the parameter.

value - the value of the parameter.

setParameter

CallableSql setParameter(int position,
                         Object value)

Bind a positioned parameter (same as bind method).

Parameters:

position - the index position of the parameter.

value - the value of the parameter.

registerOut

CallableSql registerOut(int position,
                        int type)

Register an OUT parameter.

Note that position starts at value 1 (not 0) to be consistent with CallableStatement.

This is designed so that you do not need to register params in index order. You can set/register param 2 before param 1 etc.

Parameters:

position - the index position of the parameter (starts with 1).

type - the jdbc type of the OUT parameter that will be read.

getObject

Object getObject(int position)

Return an OUT parameter value.

position starts at value 1 (not 0) to be consistent with CallableStatement.

This can only be called after the CallableSql has been executed. When run in batch mode you effectively can't use this method.

executeOverride

boolean executeOverride(CallableStatement cstmt)
                 throws SQLException

You can extend this object and override this method for more advanced stored procedure calls. This would be the case when ResultSets are returned etc.

Throws:

SQLException

addModification

CallableSql addModification(String tableName,
                            boolean inserts,
                            boolean updates,
                            boolean deletes)

Add table modification information to the TransactionEvent.

This would be similar to using the DB.externalModification() method. It may be easier and make more sense to set it here with the CallableSql.

For UpdateSql the table modification information is derived by parsing the sql to determine the table name and whether it was an insert, update or delete.