Module io.ebean.api
Package 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:

  • Method Details

    • 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.