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
的 com.litongjava.db.activerecord.generator.Generator
工具生成实体类代码。生成的实体类包括基础类 BaseMaxKbApplicationAccessToken
和主实体类 MaxKbApplicationAccessToken
,分别负责基础的数据库字段映射和主键、表名的定义。以下是生成的实体类代码片段:
MaxKbApplicationAccessToken.java
package com.litongjava.maxkb.model;
import com.litongjava.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 com.litongjava.maxkb.service;
import java.util.List;
import com.litongjava.maxkb.constant.AppConstant;
import com.litongjava.maxkb.model.MaxKbApplicationAccessToken;
import com.litongjava.tio.utils.json.JsonUtils;
import com.litongjava.tio.utils.jwt.JwtUtils;
import com.litongjava.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 com.litongjava.maxkb.service;
import org.junit.Test;
import com.litongjava.jfinal.aop.Aop;
import com.litongjava.maxkb.config.DbConfig;
import com.litongjava.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
函数
作用
COALESCE(arg1, arg2, …)
会从左到右依次判断每个参数,返回第一个 非NULL
的值;如果所有参数都是NULL
,就返回NULL
。常见用法
把可能为
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;
与数组类型结合 在你的例子里:
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[]
,代码简洁、健壮。