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