Db 与 PostgreSQL 业务实践

import nexus.io.db.activerecord.Db;
import nexus.io.db.activerecord.Row;

Row user = Db.findFirst("select id,nickname from app_user where tenant_id=? and id=? and deleted=0", tenantId, userId);
if (user == null) {
  // 按业务契约返回未找到，不要立即 user.get(...)
}

List<Row> rows = Db.find("select id,title from app_resource where tenant_id=? and status=? order by id desc limit ?", tenantId, "published", 20);
Long total = Db.queryLong("select count(*) from app_resource where tenant_id=?", tenantId);
int changed = Db.update("update app_resource set title=? where tenant_id=? and id=?", title, tenantId, resourceId);

String[] jsonFields = { "form_data", "region_codes" };
List<Row> resources = Db.findWithJsonField(
    "select id,form_data,region_codes from app_resource where tenant_id=?", jsonFields, tenantId);
// 列名与结果集标签一致，参数仍使用占位符绑定。

org.postgresql.util.PGobject json = nexus.io.kit.PgObjectUtils.jsonb(
    com.alibaba.fastjson2.JSON.toJSONString(formData));
Db.update("update app_resource set form_data=? where tenant_id=? and id=?", json, tenantId, resourceId);

boolean committed = Db.tx(java.sql.Connection.TRANSACTION_READ_COMMITTED, () -> {
  Row account = Db.findFirst("select balance from app_wallet where tenant_id=? and user_id=? for update", tenantId, userId);
  if (account == null || account.getLong("balance") < amount) return false;
  int changed = Db.update("update app_wallet set balance=balance-? where tenant_id=? and user_id=?", amount, tenantId, userId);
  if (changed != 1) return false;
  Db.update("insert into app_ledger(id,tenant_id,user_id,amount) values (?,?,?,?)", ledgerId, tenantId, userId, -amount);
  return true;
});
if (!committed) {
  // 已回滚，返回业务失败，不要继续发放权益。
}

if (nexus.io.db.activerecord.DbKit.getConfig() != null) {
  throw new IllegalStateException("Run in an isolated test JVM with no existing Db configuration");
}
ActiveRecordPlugin plugin = new ActiveRecordPlugin(isolatedDataSource);
plugin.setDialect(new nexus.io.db.activerecord.dialect.PostgreSqlDialect());
plugin.start();
try {
  if (!testSchema.equals(Db.queryStr("select current_schema()"))) {
    throw new IllegalStateException("Unexpected database schema");
  }
  // 在测试专用 schema 验证提交、回滚、JSONB、唯一约束和并发行为。
} finally {
  plugin.stop();
}

import java.sql.Connection;
import nexus.io.db.activerecord.Db;
import nexus.io.tio.utils.snowflake.SnowflakeIdUtils;

// 以下代码位于已配置数据源的业务方法内，tenantId 由服务端认证上下文提供。
long orderId = SnowflakeIdUtils.id();
Long id = Db.txResult(Connection.TRANSACTION_READ_COMMITTED, () -> {
  Db.update("insert into app_order(id,tenant_id) values (?,?)", orderId, tenantId);
  return Db.queryLong("select id from app_order where tenant_id=? and id=?", tenantId, orderId);
});

Row resource = Db.findFirstWithJsonField(
    "select id,form_data from app_resource where tenant_id=? and id=? limit 1",
    new String[] { "form_data" }, tenantId, resourceId);

import com.jfinal.kit.Kv;

String[] jsonFields = { "payload" };
Object[] parameters = Db.toJsonbParameters(tenantId, payload);
List<Kv> rows = Db.findMaps(
    "select id,payload from app_event where tenant_id=? and payload @> ?::jsonb",
    jsonFields, parameters);

Kv fields = Kv.create();
fields.put("id", eventId);
fields.put("tenant_id", tenantId);
fields.put("payload", payload);
Kv inserted = Db.insertMapReturning("app_event", fields, jsonFields);

Kv changes = Kv.create();
changes.put("payload", newPayload);
Kv conditions = Kv.create();
conditions.put("id", eventId);
conditions.put("tenant_id", tenantId);
int updated = Db.updateMapByColumns("app_event", changes, conditions, "update_time");

Kv row = Db.findFirstMap(sql, jsonFields, parameters);
if (row != null) {
  Long id = row.getLong("id");
  Integer quantity = row.getInt("quantity");
  String name = row.getStr("name");
}
Kv page = Db.paginateMap(1, 20, countSql, findSql, jsonFields, parameters);
Long total = page.getLong("total");
List<Kv> rows = page.getAs("list");

import java.sql.Connection;
import nexus.io.db.activerecord.Db;
import nexus.io.db.activerecord.Row;
import nexus.io.tio.utils.snowflake.SnowflakeIdUtils;

public final class DraftReservation {
  public boolean reserve(long accountId, long postId) {
    return Db.tx(Connection.TRANSACTION_READ_COMMITTED, () -> {
      Row account = Db.findFirst(
          "select id from demo_account where id=? for update", accountId);
      if (account == null) {
        throw new IllegalArgumentException("账号不存在");
      }
      Row slot = Db.findFirst("""
          select n from generate_series(1,10) n
          where not exists (
            select 1 from demo_draft where account_id=?
            and slot_no=n and released_at is null
          ) order by n limit 1
          """, accountId);
      if (slot == null) {
        return false;
      }
      Db.update("""
          insert into demo_draft(id,account_id,post_id,slot_no)
          values(?,?,?,?)
          """, SnowflakeIdUtils.id(), accountId, postId, slot.getInt("n"));
      return true;
    });
  }
}

import com.jfinal.kit.Kv;
import nexus.io.kit.PgObjectUtils;

public class JsonSettingExample {
  public Setting convert(Kv record) {
    PgObjectUtils.toBean(record, "setting", Setting.class);
    return record.getAs("setting");
  }

  public static class Setting {
    private String name;

    public String getName() {
      return name;
    }

    public void setName(String name) {
      this.name = name;
    }
  }
}

import com.jfinal.kit.Kv;
import java.sql.Connection;
import java.time.Instant;
import java.util.List;
import nexus.io.db.activerecord.Db;
import nexus.io.model.exception.BusinessException;

List<Kv> rows = Db.findMaps("select id, title from task where created_at > ?", Instant.now().minusSeconds(3600));
Kv saved = Db.txResult(Connection.TRANSACTION_REPEATABLE_READ, () -> {
  Kv row = Db.findFirstMap("select id, status from task where id=? for update", 123L);
  BusinessException.require(row != null, 404, "NOT_FOUND", "Task not found");
  Db.update("update task set updated_at=? where id=?", Instant.now(), row.getLong("id"));
  return row;
});