329 lines
7.5 KiB
Markdown
329 lines
7.5 KiB
Markdown
# 数据接口基础规则
|
||
|
||
## 核心机制
|
||
|
||
### 接口类型 (type)
|
||
|
||
| type | 名称 | 说明 | 示例 |
|
||
|------|------|------|------|
|
||
| 1 | SQL操作 | 执行SQL语句 | SELECT/INSERT/UPDATE/DELETE |
|
||
| 2 | 静态数据 | 返回固定JSON | 图表配置、选项列表 |
|
||
| 3 | API操作 | 调用HTTP接口 | 第三方API、内部接口 |
|
||
|
||
### 动作 (action)
|
||
|
||
| action | 名称 | 说明 |
|
||
|--------|------|------|
|
||
| 1 | 增加 | INSERT |
|
||
| 2 | 修改 | UPDATE |
|
||
| 3 | 查询 | SELECT |
|
||
| 4 | 删除 | DELETE |
|
||
|
||
> **注意**: action仅对SQL操作(type=1)有意义。静态数据action=0,API操作action由接口决定。
|
||
|
||
### 分页 (hasPage)
|
||
|
||
| 值 | 说明 |
|
||
|----|------|
|
||
| 0 | 禁用分页,返回全部数据 |
|
||
| 1 | 启用分页,需配置分页SQL |
|
||
|
||
### 鉴权 (isPostPosition)
|
||
|
||
| 值 | 说明 |
|
||
|----|------|
|
||
| 0 | 普通接口 |
|
||
| 1 | 鉴权接口(后置),作为权限验证 |
|
||
|
||
> **重要**: isPostPosition必须为0,否则Preview时后端会NPE
|
||
|
||
---
|
||
|
||
## 数据配置结构 (dataConfigJson)
|
||
|
||
### SQL操作配置
|
||
|
||
```json
|
||
{
|
||
"staticData": "",
|
||
"sqlData": {
|
||
"dbLinkId": "0",
|
||
"sql": "SELECT * FROM base_user WHERE f_real_name = {name}"
|
||
},
|
||
"apiData": {
|
||
"method": 1,
|
||
"url": "",
|
||
"header": [],
|
||
"query": [],
|
||
"body": "",
|
||
"bodyType": 0
|
||
}
|
||
}
|
||
```
|
||
|
||
### 静态数据配置
|
||
|
||
```json
|
||
{
|
||
"staticData": "[{\"id\":\"1\",\"name\":\"选项1\"},{\"id\":\"2\",\"name\":\"选项2\"}]",
|
||
"sqlData": {"dbLinkId": "", "sql": ""},
|
||
"apiData": {"method": 1, "url": "", "header": [], "query": [], "body": "", "bodyType": 0}
|
||
}
|
||
```
|
||
|
||
### API操作配置
|
||
|
||
```json
|
||
{
|
||
"staticData": "",
|
||
"sqlData": {"dbLinkId": "", "sql": ""},
|
||
"apiData": {
|
||
"method": 1,
|
||
"url": "/api/extend/CrmOrder/Goods",
|
||
"header": [{"field": "Authorization", "defaultValue": "token", "source": "1"}],
|
||
"query": [{"field": "keyword", "defaultValue": "", "source": "1"}],
|
||
"body": "",
|
||
"bodyType": 0
|
||
}
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 参数配置 (parameterJson)
|
||
|
||
### 参数结构
|
||
|
||
```json
|
||
[
|
||
{
|
||
"field": "参数名",
|
||
"fieldName": "中文名",
|
||
"dataType": "varchar|int|datetime|decimal",
|
||
"required": 0,
|
||
"sourceType": 1,
|
||
"defaultValue": "默认值"
|
||
}
|
||
]
|
||
```
|
||
|
||
### sourceType 类型
|
||
|
||
| 值 | 说明 | 行为 |
|
||
|----|------|------|
|
||
| 1 | 字段 | 使用defaultValue作为参数值 |
|
||
| 2 | 自定义 | 使用relationField作为参数值 |
|
||
| 3 | 空值 | **推荐使用**:参数值直接替换,最可靠 |
|
||
| 4 | 系统参数 | 使用系统变量值 |
|
||
|
||
> **重要区别**:
|
||
> - `sourceType=1`:受 DI 中配置的 `defaultValue` 影响
|
||
> - `sourceType=3`:**直接使用传入的参数值**,忽略 `defaultValue`
|
||
> - AIBridge `execute()` 内部使用 `sourceType=3`
|
||
|
||
---
|
||
|
||
## SQL占位符规则
|
||
|
||
### 自定义参数占位符
|
||
|
||
```sql
|
||
SELECT * FROM table WHERE field = {参数名}
|
||
```
|
||
- `{参数名}` → 替换为 `?` 占位符
|
||
- 通过PreparedStatement安全赋值
|
||
|
||
### 系统变量占位符
|
||
|
||
```sql
|
||
SELECT * FROM base_user WHERE f_user_id = {@userId}
|
||
```
|
||
|
||
| 系统变量 | 说明 |
|
||
|----------|------|
|
||
| `{@userId}` | 当前用户ID |
|
||
| `{@organizeId}` | 当前组织ID |
|
||
| `{@positionId}` | 当前岗位ID |
|
||
| `{@snowFlakeID}` | 雪花ID(新增时) |
|
||
| `{@lotSnowID}` | 批量雪花ID |
|
||
| `{@keyword}` | 关键词搜索 |
|
||
| `{@pageSize}` | 每页条数 |
|
||
| `{@offsetSize}` | 开始条数 |
|
||
| `{@currentPage}` | 当前页码 |
|
||
|
||
### 禁止事项
|
||
|
||
⚠️ **SQL中不能有`{}`空字面量**,会被当作参数占位符解析
|
||
|
||
```sql
|
||
-- 错误示例
|
||
SELECT * FROM table WHERE json = '{}'
|
||
|
||
-- 正确示例
|
||
SELECT * FROM table WHERE json = '''{}'''
|
||
```
|
||
|
||
---
|
||
|
||
## 数据库连接 (dbLinkId)
|
||
|
||
| 值 | 说明 |
|
||
|----|------|
|
||
| "0" | 主数据库 |
|
||
| 其他ID | 业务数据库连接 |
|
||
|
||
> **注意**: 多租户环境下,dbLinkId=0连接主库,部分业务表仅在租户库中
|
||
>
|
||
> ⚠️ **dbLinkId 位置**:不是 `dataConfigJson.dbLinkId`,而是 `dataConfigJson.sqlData.dbLinkId`
|
||
|
||
```json
|
||
{
|
||
"dataConfigJson": {
|
||
"sqlData": {
|
||
"dbLinkId": "0", // ← 实际位置在 sqlData 子对象中
|
||
"sql": "SELECT ..."
|
||
}
|
||
}
|
||
}
|
||
```
|
||
|
||
### 业务表字段命名规律
|
||
|
||
| 表类型 | 字段命名 | 示例 |
|
||
|--------|----------|------|
|
||
| JNPF系统表 | snake_case | f_id, f_full_name |
|
||
| 业务表(VisualDev) | PascalCase | F_Id, F_Company_Name |
|
||
|
||
> ⚠️ **重要**: 业务表(如 opportunity_mgmt, mirror_demand 等 VisualDev 创建的表)字段名是大写的 PascalCase,如 `F_Company_Name`,而非 `f_company_name`
|
||
|
||
可通过 `SELECT * FROM table LIMIT 1` 查看实际字段名。
|
||
|
||
---
|
||
|
||
## 数据处理 (dataJsJson)
|
||
|
||
```javascript
|
||
(data) => {
|
||
// 处理数据逻辑
|
||
// 返回所需的数据
|
||
return data
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 异常验证 (dataExceptionJson)
|
||
|
||
```javascript
|
||
(data) => {
|
||
// 返回true表示接口验证成功
|
||
// 返回false表示接口验证失败
|
||
return true
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 执行流程
|
||
|
||
```
|
||
1. 参数处理
|
||
├── replaceDefaultVale: 填充默认值(sourceType=1 时)
|
||
├── checkRequestParams: 验证必填和类型
|
||
└── paramSourceTypeReplaceValue: 替换参数值(sourceType=3 时直接用传入值)
|
||
|
||
2. 数据获取
|
||
├── type=1: executeSql → JdbcUtil
|
||
├── type=2: callStaticData → JSON.parse
|
||
└── type=3: callHTTP → HttpUtil
|
||
|
||
3. 数据处理
|
||
├── handlePostVariate: 后置变量处理
|
||
└── JScriptUtil.callJs: 执行JS处理脚本
|
||
|
||
4. 返回结果
|
||
└── ActionResult.success(data)
|
||
```
|
||
|
||
## execute() vs preview()
|
||
|
||
| 特性 | execute() | preview() |
|
||
|------|-----------|-----------|
|
||
| 参数格式 | `{"key": "value"}` | `{"paramList": [...]}` |
|
||
| sourceType | 固定为 3 | 可手动指定 |
|
||
| defaultValue | 被忽略 | sourceType=1 时生效 |
|
||
| 适用场景 | 需要覆盖默认行为 | 需要使用 DI 默认值 |
|
||
|
||
---
|
||
|
||
## AIBridge build_payload
|
||
|
||
```python
|
||
DataInterfaceAPI.build_payload(
|
||
full_name="接口名称",
|
||
en_code="接口编码",
|
||
sql="SELECT * FROM table WHERE field = {param}",
|
||
parameters=[{"field": "param", "fieldName": "参数", "dataType": "varchar", "required": 1, "sourceType": 1}],
|
||
category="分类ID",
|
||
action=3,
|
||
db_link_id="0"
|
||
)
|
||
```
|
||
|
||
> **关键**: isPostPosition强制设为0,避免后端NPE
|
||
|
||
## AIBridge 扩展的增删改方法
|
||
|
||
AIBridge 提供了简化创建增删改 DI 的方法:
|
||
|
||
```python
|
||
di = DataInterfaceAPI(client)
|
||
|
||
# INSERT
|
||
insert_payload = di.build_insert_payload(
|
||
full_name="新增接口",
|
||
en_code="insert_demo",
|
||
table_name="base_user",
|
||
fields=["f_real_name", "f_account"],
|
||
parameters=[
|
||
{"field": "real_name", "fieldName": "姓名", "dataType": "varchar", "required": 1, "sourceType": 1},
|
||
{"field": "account", "fieldName": "账号", "dataType": "varchar", "required": 1, "sourceType": 1},
|
||
],
|
||
category="分类ID"
|
||
)
|
||
# SQL: INSERT INTO base_user (f_real_name, f_account) VALUES ({real_name}, {account})
|
||
|
||
# UPDATE
|
||
update_payload = di.build_update_payload(
|
||
full_name="修改接口",
|
||
en_code="update_demo",
|
||
table_name="base_user",
|
||
set_fields=["f_real_name"],
|
||
where_field="f_id",
|
||
parameters=[...],
|
||
category="分类ID"
|
||
)
|
||
# SQL: UPDATE base_user SET f_real_name = {real_name} WHERE f_id = {id}
|
||
|
||
# DELETE
|
||
delete_payload = di.build_delete_payload(
|
||
full_name="删除接口",
|
||
en_code="delete_demo",
|
||
table_name="base_user",
|
||
where_field="f_id",
|
||
parameters=[...],
|
||
category="分类ID"
|
||
)
|
||
# SQL: DELETE FROM base_user WHERE f_id = {id}
|
||
|
||
# SELECT (支持分页)
|
||
select_payload = di.build_select_payload(
|
||
full_name="查询接口",
|
||
en_code="select_demo",
|
||
sql="SELECT * FROM base_user LIMIT {offset}, {size}",
|
||
parameters=[...],
|
||
category="分类ID",
|
||
has_page=1
|
||
)
|
||
```
|