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