SqlUpdate

SqlUpdate 允许我们执行 sql insert、sql update 或 sql delete 语句。

示例：简单更新

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

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

示例：使用 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();

绑定集合

要将值集合/数组绑定到 IN 子句中，我们需要使用索引位置参数（如 ?1, ?2, ?3 等）或命名参数（如 :ids, :names 等）。

// 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 (?,?,?)

示例：绑定多个列表

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 - 公共表表达式

当我们想要使用 公共表表达式 执行批量更新时，最简单的方法是使用 SqlUpdate。

CTE 示例

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 和 ExecuteBatch

我们使用 addBatch() 和 executeBatch() 显式使用 JDBC 批处理。

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

我们可以执行通常特定于数据库的 upsert sql。

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 = "[email protected]";

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

Upsert - MySql

String email = "[email protected]";

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 缓存

如果正在使用 L2 缓存，Ebean 将默认自动尝试确定执行了哪些表修改，并使用此信息使 L2 缓存的适当部分失效。

使用 setAutoTableMod(false) 关闭此功能。