SqlUpdate.execute() for RETURNING clause

[PromanSEW]

Please add SqlUpdate.execute() with ability to return rows with data specified in RETURNING clause, not only count of changed rows.
Example as in PostgreSQL docs: https://www.postgresql.org/docs/current/dml-returning.html

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

[rbygrave]

Well we can already do this but note that this returns tuples (and does not return the count of changed rows but you can derive that yourself as the total number of tuples being returned.

But yes this already works, so lets look into the details.

Our SQL used here

update e_person_online 
  set email = concat('x',email) 
  where email like ? 
  returning id, email, online_status

JDBC

So firstly lets drop down to JDBC (which we can do with ebean via getting the java.sql.Connection from a transaction) and understand that for Postgres this is executed as statement.executeQuery() so:

Setup create some rows in a e_person_online table

    Database db = DB.getDefault();
    db.truncate(EPersonOnline.class);

    var bean1 = newBean("a1@bee.com");
    var bean2 = newBean("a2@cee.com");
    var bean3 = newBean("a3@bee.com");
    db.saveAll(bean1, bean2, bean3);

Execute an UPDATE ... RETURNING ... via statement.executeQuery()

    String sql = "update e_person_online set email = concat('x',email) where email like ? returning id, email, online_status";

    try (Transaction txn = db.createTransaction()) {
      Connection connection = txn.connection();
      try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
        pstmt.setString(1, "%bee.com");
        try (ResultSet resultSet = pstmt.executeQuery()) {
          while (resultSet.next()) {
            long id = resultSet.getLong(1);
            String email = resultSet.getString(2);
            boolean status = resultSet.getBoolean(3);
            // do something with the id, email and status
            assertThat(id).isGreaterThan(0);
            assertThat(email).startsWith("x");
            assertThat(status).isTrue();
          }
        }
      }
      txn.commit();
    }

And with that we can see that although this is a DML statement, for Postgres we execute this using executeQuery().

Using SqlQuery

    List<SqlRow> list = DB.sqlQuery(sql)
      .setParameter("%bee.com")
      .findList();

    assertThat(list).hasSize(2);
    assertThat(list.get(0).getString("email")).startsWith("xx");

Using DtoQuery

    List<ReturnDto> list1 = db.findDto(ReturnDto.class, sql)
      .setParameter("%bee.com")
      .findList();

    assertThat(list1).hasSize(2);
    assertThat(list1.get(0).email).startsWith("xxx");

Using a dto bean like:

public record ReturnDto(long id, String email, boolean onlineStatus) {}

Or more like the below (or use setters rather than the constructor)

  public static class ReturnDto {

    private final long id;
    private final String email;
    private final boolean onlineStatus;

      public ReturnDto(long id, String email, boolean onlineStatus) {
          this.id = id;
          this.email = email;
          this.onlineStatus = onlineStatus;
      }
  }

[rbygrave]

Ah, but this isn't currently ideal for a few reasons:

• Implicit transactions created for SqlQuery and DtoQuery are expected to be read only (have some read only optimisation)
• The UpdateSql handling include cache invalidation (based on detecting if its an insert or update or delete etc)

So using the SqlQuery and DtoQuery like above is not ideal for those reasons and so ideally would be used with explicit transactions and also explicit transaction.addModification(String tableName, boolean inserts, boolean updates, boolean deletes)

So the examples above really should be:

    try (Transaction txn = db.beginTransaction()) {
      List<SqlRow> sqlRowList = DB.sqlQuery(sql)
        .setParameter("%bee.com")
        .findList();

      assertThat(sqlRowList).hasSize(2);
      assertThat(sqlRowList.get(0).getString("email")).startsWith("xx");

      // indicate bulk updates have occurred on e_person_online for L2 cache notification etc
      txn.addModification("e_person_online", false, true, false);
      txn.commit();
    }

    try (Transaction txn = db.beginTransaction()) {
      List<ReturnDto> dtoList = db.findDto(ReturnDto.class, sql)
        .setParameter("%bee.com")
        .findList();

      assertThat(dtoList).hasSize(2);
      assertThat(dtoList.get(0).email).startsWith("xxx");

      // indicate bulk updates have occurred on e_person_online for L2 cache notification etc
      txn.addModification("e_person_online", false, true, false);
      txn.commit();
    }

[rbygrave]

We can close this right?

[PromanSEW]

If I can use DB.sqlQuery for UPDATE with RETURNING, so OK, it can be closed

[derekm]

It would be nice if the ORM supported UPDATE ... RETURNING ... queries here: https://ebean.io/docs/query/update such that update() returned the list of entities updated having their columns refreshed with any DB-generated values. Cf., https://persistence.blazebit.com/documentation/1.6/core/manual/en_US/#returning-from-update-statement