001package io.ebean;
002
003import java.sql.CallableStatement;
004import java.sql.SQLException;
005
006/**
007 * For making calls to stored procedures. Refer to the Ebean execute() method.
008 * <p>
009 * Note that UpdateSql is designed for general DML sql and CallableSql is
010 * designed for use with stored procedures. Also note that when using this in
011 * batch mode the out parameters are not read.
012 * </p>
013 * <p>
014 * Example 1:
015 * </p>
016 * <pre>{@code
017 *
018 * String sql = "{call sp_order_mod(?,?)}";
019 *
020 * CallableSql cs = DB.createCallableSql(sql);
021 * cs.setParameter(1, "turbo");
022 * cs.registerOut(2, Types.INTEGER);
023 *
024 * DB.execute(cs);
025 *
026 * // read the out parameter
027 * Integer returnValue = (Integer) cs.getObject(2);
028 *
029 * }</pre>
030 * <p>
031 * Example 2:<br>
032 * Includes batch mode, table modification information and label. Note that the
033 * label is really only to help people reading the transaction logs to identify
034 * the procedure called etc.
035 * </p>
036 *
037 * <pre>{@code
038 *
039 * String sql = "{call sp_insert_order(?,?)}";
040 *
041 * CallableSql cs = DB.createCallableSql(sql);
042 *
043 * // Inform Ebean this stored procedure inserts into the
044 * // oe_order table and inserts + updates the oe_order_detail table.
045 * // this is used to invalidate objects in the cache
046 * cs.addModification("oe_order", true, false, false);
047 * cs.addModification("oe_order_detail", true, true, false);
048 *
049 *
050 * try (Transaction t = DB.beginTransaction()) {
051 *
052 *   // execute using JDBC batching 10 statements at a time
053 *   t.setBatchMode(true);
054 *   t.setBatchSize(10);
055 *
056 *   cs.setParameter(1, "Was");
057 *   cs.setParameter(2, "Banana");
058 *   DB.execute(cs);
059 *
060 *   cs.setParameter(1, "Here");
061 *   cs.setParameter(2, "Kumera");
062 *   DB.execute(cs);
063 *
064 *   cs.setParameter(1, "More");
065 *   cs.setParameter(2, "Apple");
066 *   DB.execute(cs);
067 *
068 *   // DB.externalModification("oe_order",true,false,false);
069 *   // DB.externalModification("oe_order_detail",true,true,false);
070 *   t.commit();
071 *
072 * }
073 * }</pre>
074 *
075 * @see SqlUpdate
076 */
077public interface CallableSql {
078
079  /**
080   * Return the label that is put into the transaction log.
081   */
082  String getLabel();
083
084  /**
085   * Set the label that is put in the transaction log.
086   */
087  CallableSql setLabel(String label);
088
089  /**
090   * Return the statement execution timeout.
091   */
092  int getTimeout();
093
094  /**
095   * Return the callable sql.
096   */
097  String getSql();
098
099  /**
100   * Set the statement execution timeout. Zero implies unlimited time.
101   * <p>
102   * This is set to the underlying CallableStatement.
103   * </p>
104   */
105  CallableSql setTimeout(int secs);
106
107  /**
108   * Set the callable sql.
109   */
110  CallableSql setSql(String sql);
111
112  /**
113   * Bind a parameter that is bound as a IN parameter.
114   * <p>
115   * position starts at value 1 (not 0) to be consistent with CallableStatement.
116   * </p>
117   * <p>
118   * This is designed so that you do not need to set params in index order. You
119   * can set/register param 2 before param 1 etc.
120   * </p>
121   *
122   * @param position the index position of the parameter.
123   * @param value    the value of the parameter.
124   */
125  CallableSql bind(int position, Object value);
126
127  /**
128   * Bind a positioned parameter (same as bind method).
129   *
130   * @param position the index position of the parameter.
131   * @param value    the value of the parameter.
132   */
133  CallableSql setParameter(int position, Object value);
134
135  /**
136   * Register an OUT parameter.
137   * <p>
138   * Note that position starts at value 1 (not 0) to be consistent with
139   * CallableStatement.
140   * </p>
141   * <p>
142   * This is designed so that you do not need to register params in index order.
143   * You can set/register param 2 before param 1 etc.
144   * </p>
145   *
146   * @param position the index position of the parameter (starts with 1).
147   * @param type     the jdbc type of the OUT parameter that will be read.
148   */
149  CallableSql registerOut(int position, int type);
150
151  /**
152   * Return an OUT parameter value.
153   * <p>
154   * position starts at value 1 (not 0) to be consistent with CallableStatement.
155   * </p>
156   * <p>
157   * This can only be called after the CallableSql has been executed. When run
158   * in batch mode you effectively can't use this method.
159   * </p>
160   */
161  Object getObject(int position);
162
163  /**
164   * You can extend this object and override this method for more advanced
165   * stored procedure calls. This would be the case when ResultSets are returned
166   * etc.
167   */
168  boolean executeOverride(CallableStatement cstmt) throws SQLException;
169
170  /**
171   * Add table modification information to the TransactionEvent.
172   * <p>
173   * This would be similar to using the
174   * <code>DB.externalModification()</code> method. It may be easier and make
175   * more sense to set it here with the CallableSql.
176   * </p>
177   * <p>
178   * For UpdateSql the table modification information is derived by parsing the
179   * sql to determine the table name and whether it was an insert, update or
180   * delete.
181   * </p>
182   */
183  CallableSql addModification(String tableName, boolean inserts, boolean updates, boolean deletes);
184
185}