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:
    Update, SqlQuery, CallableSql

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

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

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

      • 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

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