PostgreSQL 数组类型

基础数据类型数组

在数据模型设计中，经常会遇到需要存储数组字段的需求，尤其是在 PostgreSQL 数据库中。本文将以 java-db 框架为例，详细介绍如何支持对 PostgreSQL bigint[]、varchar[] 和 int[] 等数组字段的读写操作。示例将基于 java-db 的自动生成的实体类，具体实现代码会展示如何操作这些字段。

1. 数据库表结构设计

首先，我们在 PostgreSQL 中创建一张示例表 max_kb_application_access_token，其中包含了 bigint[]、varchar[] 和 int[] 类型的数组字段：

DROP TABLE IF EXISTS max_kb_application_access_token;

CREATE TABLE "public"."max_kb_application_access_token" (
  "application_id" BIGINT PRIMARY KEY,
  "access_token" VARCHAR NOT NULL,
  "is_active" BOOL NOT NULL,
  "access_num" INT NOT NULL,
  "white_active" BOOL NOT NULL,
  "white_list" VARCHAR[] NOT NULL,
  "long_list" BIGINT[] NOT NULL,
  "int_list" INT[] NOT NULL,
  "show_source" BOOL NOT NULL,
  "remark" VARCHAR(256),
  "creator" VARCHAR(64) DEFAULT '',
  "create_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updater" VARCHAR(64) DEFAULT '',
  "update_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted" SMALLINT DEFAULT 0,
  "tenant_id" BIGINT NOT NULL DEFAULT 0
);

2. 使用 java-db 自动生成实体类

接下来，使用 java-db 的 nexus.io.db.activerecord.generator.Generator 工具生成实体类代码。生成的实体类包括基础类 BaseMaxKbApplicationAccessToken 和主实体类 MaxKbApplicationAccessToken，分别负责基础的数据库字段映射和主键、表名的定义。以下是生成的实体类代码片段：

MaxKbApplicationAccessToken.java

package nexus.io.maxkb.model;

import nexus.io.maxkb.model.base.BaseMaxKbApplicationAccessToken;

public class MaxKbApplicationAccessToken extends BaseMaxKbApplicationAccessToken<MaxKbApplicationAccessToken> {
  private static final long serialVersionUID = 1L;
  public static final MaxKbApplicationAccessToken dao = new MaxKbApplicationAccessToken().dao();
  public static final String tableName = "max_kb_application_access_token";
  public static final String primaryKey = "application_id";

  @Override
  protected String _getPrimaryKey() {
    return primaryKey;
  }

  @Override
  protected String _getTableName() {
    return tableName;
  }
}

BaseMaxKbApplicationAccessToken.java

该类定义了对数据库字段的映射方法，其中对 bigint[]、varchar[] 和 int[] 等数组字段的访问方法如下：

public M setWhiteList(java.lang.String[] whiteList) {
    set("white_list", whiteList);
    return (M)this;
}

public java.lang.String[] getWhiteList() {
    return get("white_list");
}

public M setLongList(java.lang.Long[] longList) {
    set("long_list", longList);
    return (M)this;
}

public java.lang.Long[] getLongList() {
    return get("long_list");
}

public M setIntList(java.lang.Integer[] intList) {
    set("int_list", intList);
    return (M)this;
}

public java.lang.Integer[] getIntList() {
    return get("int_list");
}

3. 服务层实现：读写数组字段

为了方便演示，我们在服务类 MaxKbApplicationAccessTokenService 中实现了一个创建和查询方法。创建方法生成一个新 MaxKbApplicationAccessToken 实例并设置相应的字段（包括数组字段），然后将记录保存到数据库中。查询方法读取所有记录并打印 JSON 格式输出。

服务类实现

package nexus.io.maxkb.service;

import java.util.List;
import nexus.io.maxkb.constant.AppConstant;
import nexus.io.maxkb.model.MaxKbApplicationAccessToken;
import nexus.io.tio.utils.json.JsonUtils;
import nexus.io.tio.utils.jwt.JwtUtils;
import nexus.io.tio.utils.snowflake.SnowflakeIdUtils;

public class MaxKbApplicationAccessTokenService {

  public void create() {
    long applicationId = SnowflakeIdUtils.id();
    String accessToken = JwtUtils.createTokenByUserId(AppConstant.SECRET_KEY, applicationId);
    MaxKbApplicationAccessToken token = new MaxKbApplicationAccessToken()
        .setApplicationId(applicationId)
        .setAccessToken(accessToken)
        .setIsActive(true)
        .setAccessNum(100)
        .setWhiteActive(false)
        .setWhiteList(new String[] { "1" })
        .setIntList(new Integer[] { 1 })
        .setLongList(new Long[] { 1L })
        .setShowSource(false);
    token.save();
  }

  public void list() {
    List<MaxKbApplicationAccessToken> find = new MaxKbApplicationAccessToken().find();
    System.out.println(JsonUtils.toJson(find));
  }
}

4. 单元测试

为了确保功能的正确性，我们编写了一个简单的 JUnit 测试类，测试数据创建和查询功能：

package nexus.io.maxkb.service;

import org.junit.Test;
import nexus.io.jfinal.aop.Aop;
import nexus.io.maxkb.config.DbConfig;
import nexus.io.tio.boot.tesing.TioBootTest;

public class MaxKbApplicationAccessTokenServiceTest {

  @Test
  public void test() {
    TioBootTest.runWith(DbConfig.class);
    MaxKbApplicationAccessTokenService service = Aop.get(MaxKbApplicationAccessTokenService.class);
    service.create();
    service.list();
  }
}

测试运行后的输出将显示数据库中插入的记录，包括数组字段内容。

5. 运行结果

示例中的 SQL 语句和查询结果如下所示：

Sql: insert into "max_kb_application_access_token"("application_id", "access_token", "is_active", "access_num", "white_active", "white_list", "int_list", "long_list", "show_source") values(?, ?, ?, ?, ?, ?, ?, ?, ?)
Sql: select * from "max_kb_application_access_token"

查询结果示例：

[
  {
    "accessNum": 100,
    "applicationId": "445857044544405504",
    "accessToken": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
    "whiteList": ["1"],
    "intList": [1],
    "showSource": false,
    "whiteActive": false,
    "isActive": true,
    "remark": null,
    "longList": ["1"],
    "tenantId": "0",
    "updater": "",
    "updateTime": 1731376611252,
    "deleted": 0,
    "createTime": 1731376611252,
    "creator": ""
  }
]

6. 总结

本文介绍了如何使用 java-db 框架支持 PostgreSQL 数组字段，并通过自动生成的实体类展示了对 bigint[]、varchar[] 和 int[] 类型的字段读写操作。

COALESCE

1、PostgreSQL 的 COALESCE 函数

  1. 作用COALESCE(arg1, arg2, …) 会从左到右依次判断每个参数，返回第一个 非 NULL 的值；如果所有参数都是 NULL，就返回 NULL。

  2. 常见用法

    • 把可能为 NULL 的列替换成默认值：

      SELECT COALESCE(middle_name, '') AS middle_name FROM users;

    • 处理聚合函数（如 ARRAY_AGG、SUM 等）在没有记录时返回 NULL 的情况：

      -- 当没有符合条件的行时，SUM(price) 会返回 NULL
SELECT COALESCE(SUM(price), 0) FROM orders WHERE user_id = 123;

  3. 与数组类型结合 在的例子里：

    COALESCE(
  (SELECT ARRAY_AGG(f.flag) FROM mw_mail_flag f WHERE f.mail_id = m.id),
  '{}'
) AS flags
    • (SELECT ARRAY_AGG(f.flag)…)：如果该邮件没有任何 flag，聚合函数返回 NULL。
    • COALESCE(..., '{}')：将 NULL 转成一个空的 PostgreSQL 数组文字常量 '{ }'（等价于 Java 端的 new String[0]）。
    • 结果：flags 列在所有情况下都是一个合法的数组；若无标志，得到空数组而不是 NULL。

2、JFinal ActiveRecord 的 Db.find 与 Row 类

当在 Java 端执行：

List<Row> mailRows = Db.find(finalSql);

每一行查询结果都会被封装进一个 Row 对象里，底层是个 Map<String, Object>。

1). row.get("flags")

  • 签名：<T> T get(String columnName)

  • 返回：JDBC 驱动从 ResultSet.getObject("flags") 拿到的原始类型。对于 PostgreSQL 数组列，通常是 java.sql.Array，而 Array#getArray() 会返回一个 Object[]，可强转为 String[]：

    java.sql.Array array = row.get("flags");
String[] flags = array == null
    ? new String[0]
    : (String[]) array.getArray();

2). row.getStringArray("flags")

  • 签名：String[] getStringArray(String columnName)

  • 实现思路：

    • 内部先 get(columnName) 拿到 java.sql.Array，
    • 然后直接调用 ((java.sql.Array)obj).getArray() 并强转成 String[]，
    • 如果是 null，则返回 null 或空数组（依版本而定）。

  • 优点：一行代码搞定，也更语义化。

3). 推荐用法

因为我们在 SQL 用了 COALESCE(...,'{}') 保证了列永不为 NULL，所以在 Java 里可以放心地：

String[] flags = row.getStringArray("flags");
// 或者
String[] flags = (String[]) row.get("flags");

两者都会拿到一个 长度 ≥ 0 的数组，不用额外判空。

3、代码示例

List<Row> mailRows = Db.find(finalSql);

for (Row row : mailRows) {
    long id            = row.getLong("id");
    long uid           = row.getLong("uid");
    Timestamp internal = row.get("internal_date");
    String rawContent  = row.get("raw_content");
    int sizeInBytes    = row.getInt("size_in_bytes");

    // 方式一：更语义化
    String[] flags = row.getStringArray("flags");

    // 方式二：手动处理
    // java.sql.Array sqlArray = row.get("flags");
    // String[] flags = sqlArray == null
    //     ? new String[0]
    //     : (String[]) sqlArray.getArray();

    System.out.printf("mail %d with flags: %s%n", uid, Arrays.toString(flags));
}

4、小结

  • SQL 层面：COALESCE(...,'{}') 保证 flags 列始终是个数组，避免 NULL 带来的空指针或分支判断。
  • Java 层面：利用 JFinal 的 Row.getStringArray("flags")（或强转 row.get("flags")）即可直接拿到 String[]，代码简洁、健壮。