io.ebean

Interface SqlUpdate

public interface SqlUpdate

A SqlUpdate for executing insert update or delete statements.

Provides a simple way to execute raw SQL insert update or delete statements without having to resort to JDBC.

Supports the use of positioned or named parameters and can automatically notify Ebean of the table modified so that Ebean can maintain its cache.

Note that setAutoTableMod(boolean) and Ebean#externalModification(String, boolean, boolean, boolean)} can be to notify Ebean of external changes and enable Ebean to maintain it's "L2" server cache.

  // example using 'positioned' parameters

  String sql = "insert into audit_log (group, title, description) values (?, ?, ?);

  int rows =
    DB.sqlUpdate(sql)
      .setParams("login", "new user", "user rob was created")
      .executeNow();

  // example using 'named' parameters

  String sql = "update topic set post_count = :count where id = :id";

  int rows =
    DB.sqlUpdate(sql)
      .setParameter("id", 1)
      .setParameter("count", 50)
      .execute();

  String msg = "There were " + rows + " rows updated";

Example: Using setNextParameter()

 String sql = "insert into audit_log (id, description, modified_description) values (?,?,?)";

 SqlUpdate insert = DB.sqlUpdate(sql);

 try (Transaction txn = DB.beginTransaction()) {
   txn.setBatchMode(true);

   insert.setNextParameter(10000);
   insert.setNextParameter("hello");
   insert.setNextParameter("rob");
   insert.execute();

   insert.setNextParameter(10001);
   insert.setNextParameter("goodbye");
   insert.setNextParameter("rob");
   insert.execute();

   insert.setNextParameter(10002);
   insert.setNextParameter("chow");
   insert.setNextParameter("bob");
   insert.execute();

   txn.commit();
 }

An alternative to the batch mode on the transaction is to use addBatch() and executeBatch() like:

  try (Transaction txn = DB.beginTransaction()) {

    insert.setNextParameter(10000);
    insert.setNextParameter("hello");
    insert.setNextParameter("rob");
    insert.addBatch();

    insert.setNextParameter(10001);
    insert.setNextParameter("goodbye");
    insert.setNextParameter("rob");
    insert.addBatch();

    insert.setNextParameter(10002);
    insert.setNextParameter("chow");
    insert.setNextParameter("bob");
    insert.addBatch();

    int[] rows = insert.executeBatch();

    txn.commit();
  }

See Also:

Update, SqlQuery, CallableSql

Method Summary

Modifier and Type	Method and Description
void	addBatch() Add the statement to batch processing to then later execute via executeBatch().
int	execute() Execute the update returning the number of rows modified.
int[]	executeBatch() Execute when addBatch() has been used to batch multiple bind executions.
Object	executeGetKey() Execute and return the generated key.
int	executeNow() Execute the statement now regardless of the JDBC batch mode of the transaction.
Object	getGeneratedKey() Return the generated key value.
String	getGeneratedSql() Return the generated sql that has named parameters converted to positioned parameters.
String	getLabel() Return the label that can be seen in the transaction logs.
String	getSql() Return the sql statement.
int	getTimeout() Return the timeout used to execute this statement.
boolean	isAutoTableMod() Return true if eBean should automatically deduce the table modification information and process it.
SqlUpdate	setAutoTableMod(boolean isAutoTableMod) Set this to false if you don't want eBean to automatically deduce the table modification information and process it.
SqlUpdate	setGetGeneratedKeys(boolean getGeneratedKeys) Set to true when we want to use getGeneratedKeys with this statement.
SqlUpdate	setLabel(String label) Set a descriptive text that can be put into the transaction log.
SqlUpdate	setNextParameter(Object value) Set the next positioned parameter.
SqlUpdate	setNull(int position, int jdbcType) Set a null parameter via its index position.
SqlUpdate	setNull(String name, int jdbcType) Set a named parameter that has a null value.
SqlUpdate	setNullParameter(int position, int jdbcType) Set a null valued parameter using its index position.
SqlUpdate	setNullParameter(String name, int jdbcType) Set a named parameter that has a null value.
SqlUpdate	setParameter(int position, Object value) Set a parameter via its index position.
SqlUpdate	setParameter(String name, Object param) Set a named parameter value.
SqlUpdate	setParams(Object... values) Set one of more positioned parameters.
SqlUpdate	setTimeout(int secs) Set the timeout in seconds.

Method Detail

execute

int execute()

Execute the update returning the number of rows modified.

Note that if the transaction has batch mode on then this update will use JDBC batch and may not execute until later - at commit time or a transaction flush. In this case this method returns -1 indicating that the update has been batched for later execution.

After you have executed the SqlUpdate you can bind new variables using setParameter(String, Object) etc and then execute the SqlUpdate again.

For JDBC batch processing refer to Transaction.setBatchMode(boolean) and Transaction.setBatchSize(int).

See Also:

Ebean.execute(SqlUpdate)

executeNow

int executeNow()

Execute the statement now regardless of the JDBC batch mode of the transaction.

executeBatch

int[] executeBatch()

Execute when addBatch() has been used to batch multiple bind executions.

Returns:

The row counts for each of the batched statements.

addBatch

void addBatch()

Add the statement to batch processing to then later execute via executeBatch().

getGeneratedKey

Object getGeneratedKey()

Return the generated key value.

executeGetKey

Object executeGetKey()

Execute and return the generated key. This is effectively a short cut for:

  sqlUpdate.execute();
  Object key = sqlUpdate.getGeneratedKey();

Returns:

The generated key value

isAutoTableMod

boolean isAutoTableMod()

Return true if eBean should automatically deduce the table modification information and process it.

If this is true then cache invalidation and text index management are aware of the modification.

setAutoTableMod

SqlUpdate setAutoTableMod(boolean isAutoTableMod)

Set this to false if you don't want eBean to automatically deduce the table modification information and process it.

Set this to false if you don't want any cache invalidation or text index management to occur. You may do this when say you update only one column and you know that it is not important for cached objects or text indexes.

getLabel

String getLabel()

Return the label that can be seen in the transaction logs.

setLabel

SqlUpdate setLabel(String label)

Set a descriptive text that can be put into the transaction log.

Useful when identifying the statement in the transaction log.

setGetGeneratedKeys

SqlUpdate setGetGeneratedKeys(boolean getGeneratedKeys)

Set to true when we want to use getGeneratedKeys with this statement.

getSql

String getSql()

Return the sql statement.

getGeneratedSql

String getGeneratedSql()

Return the generated sql that has named parameters converted to positioned parameters.

getTimeout

int getTimeout()

Return the timeout used to execute this statement.

setTimeout

SqlUpdate setTimeout(int secs)

Set the timeout in seconds. Zero implies no limit.

This will set the query timeout on the underlying PreparedStatement. If the timeout expires a SQLException will be throw and wrapped in a PersistenceException.

setParams

SqlUpdate setParams(Object... values)

Set one of more positioned parameters.

This is a convenient alternative to multiple setParameter() calls.

  String sql = "insert into audit_log (id, name, version) values (?,?,?)";

  DB.sqlUpdate(sql)
      .setParams(UUID.randomUUID(), "Hello", 1)
      .executeNow();


  // is the same as ...

  DB.sqlUpdate(sql)
      .setParameter(1, UUID.randomUUID())
      .setParameter(2, "Hello")
      .setParameter(3, 1)
      .executeNow();

setNextParameter

SqlUpdate setNextParameter(Object value)

Set the next positioned parameter.

Parameters:

value - The value to bind

setParameter

SqlUpdate setParameter(int position,
                       Object value)

Set a parameter via its index position.

setNull

SqlUpdate setNull(int position,
                  int jdbcType)

Set a null parameter via its index position.

setNullParameter

SqlUpdate setNullParameter(int position,
                           int jdbcType)

Set a null valued parameter using its index position.

setParameter

SqlUpdate setParameter(String name,
                       Object param)

Set a named parameter value.

setNull

SqlUpdate setNull(String name,
                  int jdbcType)

Set a named parameter that has a null value. Exactly the same as setNullParameter(String, int).

setNullParameter

SqlUpdate setNullParameter(String name,
                           int jdbcType)

Set a named parameter that has a null value.