001package io.ebean;
002
003/**
004 * A SqlUpdate for executing insert update or delete statements.
005 * <p>
006 * Provides a simple way to execute raw SQL insert update or delete statements
007 * without having to resort to JDBC.
008 * <p>
009 * Supports the use of positioned or named parameters and can automatically
010 * notify Ebean of the table modified so that Ebean can maintain its cache.
011 * <p>
012 * Note that {@link #setAutoTableMod(boolean)} and
013 * Ebean#externalModification(String, boolean, boolean, boolean)} can be to
014 * notify Ebean of external changes and enable Ebean to maintain it's "L2"
015 * server cache.
016 *
017 * <h2>Positioned parameter example</h2>
018 * <pre>{@code
019 *
020 *   // example using 'positioned' parameters
021 *
022 *   String sql = "insert into audit_log (group, title, description) values (?, ?, ?);
023 *
024 *   int rows =
025 *     DB.sqlUpdate(sql)
026 *       .setParams("login", "new user", "user rob was created")
027 *       .executeNow();
028 *
029 * }</pre>
030 *
031 * <h2>Named parameter example</h2>
032 * <pre>{@code
033 *
034 *   // example using 'named' parameters
035 *
036 *   String sql = "update topic set post_count = :count where id = :id";
037 *
038 *   int rows =
039 *     DB.sqlUpdate(sql)
040 *       .setParameter("id", 1)
041 *       .setParameter("count", 50)
042 *       .execute();
043 *
044 *   String msg = "There were " + rows + " rows updated";
045 *
046 * }</pre>
047 *
048 * <h2>Index parameter examples (e.g. ?1, ?2, ?3 ...)</h2>
049 * <p>
050 * We can use index parameters like ?1, ?2, ?3 etc when binding arrays/collections
051 * of values into an IN expression.
052 * </p>
053 * <pre>{@code
054 *
055 *   // Binding a list of 3 values (9991, 9992, 9993) into an IN expression
056 *
057 *   DB.sqlUpdate("delete from o_customer where name = ? and id in (?2)")
058 *     .setParameter(1, "Foo")
059 *     .setParameter(2, asList(9991, 9992, 9993))
060 *     .execute();
061 *
062 *   // note this effectively is the same as
063 *
064 *   DB.sqlUpdate("delete from o_customer where name = ? and id in (?2)")
065 *     .setParameter("Foo")
066 *     .setParameter(asList(9991, 9992, 9993))
067 *     .execute();
068 *
069 * }</pre>
070 *
071 * <h3>Example: Using setParameter()</h3>
072 * <pre>{@code
073 *
074 *  String sql = "insert into audit_log (id, description, modified_description) values (?,?,?)";
075 *
076 *  SqlUpdate insert = DB.sqlUpdate(sql);
077 *
078 *  try (Transaction txn = DB.beginTransaction()) {
079 *    txn.setBatchMode(true);
080 *
081 *    insert.setParameter(10000);
082 *    insert.setParameter("hello");
083 *    insert.setParameter("rob");
084 *    insert.execute();
085 *
086 *    insert.setParameter(10001);
087 *    insert.setParameter("goodbye");
088 *    insert.setParameter("rob");
089 *    insert.execute();
090 *
091 *    insert.setParameter(10002);
092 *    insert.setParameter("chow");
093 *    insert.setParameter("bob");
094 *    insert.execute();
095 *
096 *    txn.commit();
097 *  }
098 * }</pre>
099 * <p>
100 * An alternative to the batch mode on the transaction is to use addBatch() and executeBatch() like:
101 * </p>
102 * <pre>{@code
103 *
104 *   try (Transaction txn = DB.beginTransaction()) {
105 *
106 *     insert.setParameter(10000);
107 *     insert.setParameter("hello");
108 *     insert.setParameter("rob");
109 *     insert.addBatch();
110 *
111 *     insert.setParameter(10001);
112 *     insert.setParameter("goodbye");
113 *     insert.setParameter("rob");
114 *     insert.addBatch();
115 *
116 *     insert.setParameter(10002);
117 *     insert.setParameter("chow");
118 *     insert.setParameter("bob");
119 *     insert.addBatch();
120 *
121 *     int[] rows = insert.executeBatch();
122 *
123 *     txn.commit();
124 *   }
125 *
126 * }</pre>
127 *
128 * @see Update
129 * @see SqlQuery
130 * @see CallableSql
131 */
132public interface SqlUpdate {
133
134  /**
135   * Execute the update returning the number of rows modified.
136   * <p>
137   * Note that if the transaction has batch mode on then this update will use JDBC batch and may not execute until
138   * later - at commit time or a transaction flush. In this case this method returns -1 indicating that the
139   * update has been batched for later execution.
140   * </p>
141   * <p>
142   * After you have executed the SqlUpdate you can bind new variables using
143   * {@link #setParameter(String, Object)} etc and then execute the SqlUpdate
144   * again.
145   * </p>
146   * <p>
147   * For JDBC batch processing refer to
148   * {@link Transaction#setBatchMode(boolean)} and
149   * {@link Transaction#setBatchSize(int)}.
150   * </p>
151   */
152  int execute();
153
154  /**
155   * Execute the statement now regardless of the JDBC batch mode of the transaction.
156   */
157  int executeNow();
158
159  /**
160   * Execute when addBatch() has been used to batch multiple bind executions.
161   *
162   * @return The row counts for each of the batched statements.
163   */
164  int[] executeBatch();
165
166  /**
167   * Add the statement to batch processing to then later execute via executeBatch().
168   */
169  void addBatch();
170
171  /**
172   * Return the generated key value.
173   */
174  Object getGeneratedKey();
175
176  /**
177   * Execute and return the generated key. This is effectively a short cut for:
178   * <p>
179   * <pre>{@code
180   *
181   *   sqlUpdate.execute();
182   *   Object key = sqlUpdate.getGeneratedKey();
183   *
184   * }</pre>
185   *
186   * @return The generated key value
187   */
188  Object executeGetKey();
189
190  /**
191   * Return true if eBean should automatically deduce the table modification
192   * information and process it.
193   * <p>
194   * If this is true then cache invalidation and text index management are aware
195   * of the modification.
196   * </p>
197   */
198  boolean isAutoTableMod();
199
200  /**
201   * Set this to false if you don't want eBean to automatically deduce the table
202   * modification information and process it.
203   * <p>
204   * Set this to false if you don't want any cache invalidation or text index
205   * management to occur. You may do this when say you update only one column
206   * and you know that it is not important for cached objects or text indexes.
207   * </p>
208   */
209  SqlUpdate setAutoTableMod(boolean isAutoTableMod);
210
211  /**
212   * Return the label that can be seen in the transaction logs.
213   */
214  String getLabel();
215
216  /**
217   * Set a descriptive text that can be put into the transaction log.
218   * <p>
219   * Useful when identifying the statement in the transaction log.
220   * </p>
221   */
222  SqlUpdate setLabel(String label);
223
224  /**
225   * Set to true when we want to use getGeneratedKeys with this statement.
226   */
227  SqlUpdate setGetGeneratedKeys(boolean getGeneratedKeys);
228
229  /**
230   * Return the sql statement.
231   */
232  String getSql();
233
234  /**
235   * Return the generated sql that has named parameters converted to positioned parameters.
236   */
237  String getGeneratedSql();
238
239  /**
240   * Return the timeout used to execute this statement.
241   */
242  int getTimeout();
243
244  /**
245   * Set the timeout in seconds. Zero implies no limit.
246   * <p>
247   * This will set the query timeout on the underlying PreparedStatement. If the
248   * timeout expires a SQLException will be throw and wrapped in a
249   * PersistenceException.
250   * </p>
251   */
252  SqlUpdate setTimeout(int secs);
253
254  /**
255   * Set one of more positioned parameters.
256   * <p>
257   * This is a convenient alternative to multiple setParameter() calls.
258   *
259   * <pre>{@code
260   *
261   *   String sql = "insert into audit_log (id, name, version) values (?,?,?)";
262   *
263   *   DB.sqlUpdate(sql)
264   *       .setParameters(UUID.randomUUID(), "Hello", 1)
265   *       .executeNow();
266   *
267   *
268   *   // is the same as ...
269   *
270   *   DB.sqlUpdate(sql)
271   *       .setParameter(UUID.randomUUID())
272   *       .setParameter("Hello")
273   *       .setParameter(1)
274   *       .executeNow();
275   *
276   *   // which is the same as ...
277   *
278   *   DB.sqlUpdate(sql)
279   *       .setParameter(1, UUID.randomUUID())
280   *       .setParameter(2, "Hello")
281   *       .setParameter(3, 1)
282   *       .executeNow();
283   *
284   * }</pre>
285   */
286  SqlUpdate setParameters(Object... values);
287
288  /**
289   * Deprecated migrate to setParameters(Object... values).
290   */
291  @Deprecated
292  SqlUpdate setParams(Object... values);
293
294  /**
295   * Set the next bind parameter by position.
296   *
297   * @param value The value to bind
298   */
299  SqlUpdate setParameter(Object value);
300
301  /**
302   * Deprecated migrate to setParameter(value).
303   */
304  @Deprecated
305  SqlUpdate setNextParameter(Object value);
306
307  /**
308   * Set a parameter via its index position.
309   */
310  SqlUpdate setParameter(int position, Object value);
311
312  /**
313   * Set a null parameter via its index position.
314   */
315  SqlUpdate setNull(int position, int jdbcType);
316
317  /**
318   * Set a null valued parameter using its index position.
319   */
320  SqlUpdate setNullParameter(int position, int jdbcType);
321
322  /**
323   * Set a named parameter value.
324   */
325  SqlUpdate setParameter(String name, Object param);
326
327  /**
328   * Set a named parameter that has a null value. Exactly the same as
329   * {@link #setNullParameter(String, int)}.
330   */
331  SqlUpdate setNull(String name, int jdbcType);
332
333  /**
334   * Set a named parameter that has a null value.
335   */
336  SqlUpdate setNullParameter(String name, int jdbcType);
337
338}