SqlUpdate

SqlUpdate allows us to execute sql insert, sql update or sql delete statements.

Example: simple update

String sql = "update audit_log set description = description where id = ?";

int row = DB.sqlUpdate(sql)
  .setParameter(1, 42)
  .execute();

Example: insert with getGeneratedKeys

String sql = "insert into e_person_online (email, online_status, when_updated) " +
             "values (:email, :online, current_time)";

SqlUpdate sqlUpdate = DB.sqlUpdate(sql)
  .setGetGeneratedKeys(true)
  .setParameter("email", email)
  .setParameter("online", false);

sqlUpdate.execute();

Object key = sqlUpdate.getGeneratedKey();

Binding collections

To bind collections/arrays of values into an IN clause we need to use indexed positioned parameters like ?1, ?2, ?3 etc or named parameters like :ids, :names etc.

// using ?1 index positioned parameter

String sql = "delete from customer where ref_id in (?1)";

int rows = DB.sqlUpdate(sql)
  .setParameter(asList(9991, 9992, 9993))
  .execute();

delete from customer where ref_id in (?,?,?)

// using :ids named parameter

String sql = "delete from customer where ref_id in (:ids)";

int rows = DB.sqlUpdate(sql)
  .setParameter("ids", asList(9991, 9992, 9993))
  .execute();

delete from customer where ref_id in (?,?,?)

Example: binding multiple lists

String sql = "delete from customer where ref_id in (:ids) and name in (:names)";

int rows = DB.sqlUpdate(sql)
    .setParameter("ids", asList(9991, 9992, 9993))
    .setParameter("names", asList("rob", "jim"))
    .execute();

delete from customer where ref_id in (?,?,?) and name in (?,?)

CTE - Common table expression

When we want to perform a bulk update using a common table expression this is easiest done using SqlUpdate.

CTE Example

String sql = """
WITH t AS (
    SELECT s.id AS _id
    FROM store_price s
    JOIN promotion_vw p ...
    WHERE ...
)
UPDATE store_price
SET promotion_price = null, price = active_price, price_reason = ?,
    version = version+1, when_modified = current_timestamp
FROM t
WHERE id = t._id
"""

int rows = DB.sqlUpdate(sql)
  .setParameter(1, "Expired promotions")
  .execute();

AddBatch and ExecuteBatch

We use addBatch() and executeBatch() to explicitly use JDBC batching.

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

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

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

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

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

  int[] rows = insert.executeBatch();

  txn.commit();
}

Upsert

We can execute upsert sql which is generally database specific.

Upsert - Postgres

String sql =
  "insert into e_person_online (email, online_status, when_updated) values (?, ?, now()) " +
  "on conflict (email) do update set when_updated=now(), online_status = ?";

String email = "foo@one.com";

Object key = DB.sqlUpdate(sql)
  .setGetGeneratedKeys(true)
  .setParameter(1, email)
  .setParameter(2, true)
  .setParameter(3, true)
  .executeGetKey();

Upsert - MySql

String email = "bar@one.com";

String sql =
  "insert into e_person_online (email, online_status, when_updated) values (?, ?, current_time) " +
  "on duplicate key update when_updated=current_time, online_status = ?";

Object key = DB.sqlUpdate(sql)
  .setGetGeneratedKeys(true)
  .setParameter(1, email)
  .setParameter(2, true)
  .setParameter(3, true)
  .executeGetKey();

L2 cache

If L2 caching is being used Ebean will by default automatically try and determine what table modifications are performed and use this to invalidate appropriate parts of the L2 cache.

Use setAutoTableMod(false) to turn this off.