Module io.ebean.api
Package 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.

Positioned parameter example



   // 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();

Named parameter example



   // 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";

Index parameter examples (e.g. ?1, ?2, ?3 ...)

We can use index parameters like ?1, ?2, ?3 etc when binding arrays/collections of values into an IN expression. 



   // Binding a list of 3 values (9991, 9992, 9993) into an IN expression

   DB.sqlUpdate("delete from o_customer where name = ? and id in (?2)")
     .setParameter(1, "Foo")
     .setParameter(2, asList(9991, 9992, 9993))
     .execute();

   // note this effectively is the same as

   DB.sqlUpdate("delete from o_customer where name = ? and id in (?2)")
     .setParameter("Foo")
     .setParameter(asList(9991, 9992, 9993))
     .execute();

Example: Using setParameter()



  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.setParameter(10000);
    insert.setParameter("hello");
    insert.setParameter("rob");
    insert.execute();

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

    insert.setParameter(10002);
    insert.setParameter("chow");
    insert.setParameter("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.setParameter(10000);
     insert.setParameter("hello");
     insert.setParameter("rob");
     insert.addBatch();

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

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

     int[] rows = insert.executeBatch();

     txn.commit();
   }
See Also:

  • Method Details

    • 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).

    • 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.

    • setParameters

      SqlUpdate setParameters(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)
       .setParameters(UUID.randomUUID(), "Hello", 1)
       .executeNow();


   // is the same as ...

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

   // which is the same as ...

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

    • setParameter

      SqlUpdate setParameter(Object value)
      Set the next bind parameter by position.
      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.

    • setArrayParameter

      SqlUpdate setArrayParameter(String name, Collection<?> values)
      Bind the named multi-value array parameter which we would use with Postgres ANY.

      For Postgres this binds an ARRAY rather than expands into multiple bind values.

    • 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.