JSQL (JSON to SQL)前端输入规范

JSQL(JSON To table SQL):是一种以 JSON 为载体的结构化查询语言,用于操作数据表

  • 解析前端输入的 JSON 参数
  • 转为底层数据库可以执行的 SQL 语句
  • 执行 sql.
  • 返回执行结果到前端
  • 支持对表的增删改查

基本概念

data table(数据表)

  • http://localhost:10051/table/system_users/get?username=admin 这这个请求示例中 system_users 被称为 data table name 数据表名称

数据对象

数据表实例化后的对象被称为数据对象

数据字段

数据对象的键称为数据字段,下面的数据对象有两个数据字段,分别是 creator and sex

{
  "creator": "admin",
  "sex": 1
}

操作指令

JSQL 支持的多种操作指令.操作指令支持驼峰命名规则和下划线命名规则,例如 nameOp 或者 name_op

操作指令

查询操作符

查询操作符用于对数据字段进行操作,执行的查询指令如下.具体是的实现可以参考源码 com.litongjava.data.services.OperatorService

操作符名称请求示例SQL 示例
eq等于field: "value", fieldOp: eqfield = 'value'
ne不等于field: "value", fieldOp: nefield != 'value'
gt大于field: "value", fieldOp: gtfield > 'value'
ge大于等于field: "value", fieldOp: gefield >= 'value'
lt小于field: "value", fieldOp: ltfield < 'value'
le小于等于field: "value", fieldOp: lefield <= 'value'
bt之间field: ["value1", "value2"], fieldOp: btfield between 'value1' and 'value2'
nb不在之间field: ["value1", "value2"], fieldOp: nbfield not between 'value1' and 'value2'
ct包含field: "value", fieldOp: ctfield like '%value%'
sw开始于field: "value", fieldOp: swfield like 'value%'
ew结束于field: "value", fieldOp: ewfield like '%value'
ol或类似于field: ["value1", "value2"], fieldOp: ol(field like 'value1' or field like 'value2')
nk不类似于field: "value", fieldOp: nkfield not like 'value'
il在列表中field: ["value1", "value2"], fieldOp: ilfield in ('value1', 'value2')
ni不在列表中field: ["value1", "value2"], fieldOp: nifield not in ('value1', 'value2')
nl为空fieldOp: nlfield is null
nn不为空fieldOp: nnfield is not null
ey空值fieldOp: ey(field is null or field = '')
ny非空值fieldOp: ny(field is not null and field != '')
nt非真值fieldOp: nt(field is not true)
nf非假值fieldOp: nf(field is not false)

Each row in this table describes:

  • 操作符 (Operator): The code used to identify the operation.
  • 名称 (Name): A description of what the operator does.
  • 请求示例 (Request Example): How to use the operator in a query.
  • SQL 示例 (SQL Example): The SQL equivalent of the operation, illustrating how the query translates into an SQL statement.

查找指令示例

POST /table/rumi_sjsu_professors/page
{
  "name": "Tong",
  "nameOp": "ct",
}

查找指令示例解析

POST /table/student/page
{
  "name": "Tong",
  "nameOp": "ct",
}

解析

  • 数据表名称
    • student
  • 数据字段 (Data Fields):
    • "name": 这是一个数据字段，其值为 "Tong"。
  • 操作指令:
    • "nameOp": 这个操作指令中,操作的数据字段是 name ，操作符的值为 "ct"，表示包含（contain）。

SQL 转换

上述 JSON 请求将被 JSQL 解析并转换为 SQL 查询语句。根据操作符表格，ct 表示包含操作，对应的 SQL 语句为 field like '%value%'。因此，"name": "Tong", "nameOp": "ct" 会被转换为如下 SQL 语句的一部分：

SELECT * FROM student WHERE name LIKE '%Tong%';

详细步骤

  1. 解析 JSON 输入: 服务器接收到 JSON 格式的查询请求。
  2. 识别数据表,数据字段和操作指令: 解析 JSON 对象中的数据字段 ("name") 和对应的操作指令 ("nameOp": "ct"）。
  3. 生成 SQL 语句: 根据操作符表格，将 "nameOp": "ct" 转换为 SQL 的 LIKE '%value%' 语法，生成 name LIKE '%Tong%'。
  4. 执行查询: 最终的 SQL 查询将被执行，并返回符合条件的 rumi_sjsu_professors 表中的记录。

通过上述步骤，系统可以将前端输入的结构化 JSON 查询转换为底层数据库可以执行的 SQL 语句，实现对数据表的查询操作。

数据字段指令

数据字段指令columns 用于指定查询结果中要返回的数据字段。默认情况下，如果不指定 columns，查询会返回数据表包含的所有数据字段。通过使用 columns 指令，可以明确指出需要的特定字段，从而优化查询结果和性能。

指令说明

名称描述示例
columns执行需要的数据字段，默认返回数据表包含的所有数据字段"columns": "name,id"
  • columns:
    • 描述: 用于指定查询结果中要返回的数据字段。默认情况下，查询会返回数据表包含的所有字段。如果使用 columns 指定了特定字段，查询将只返回这些指定字段。
    • 示例: "columns": "name,id"
    • 说明: 查询结果中将只包含 name 和 id 字段。

示例解析

假设你有一个查询请求，并希望只返回 name 和 id 字段：

{
  "columns": "name,id"
}

生成的 SQL 语句应为：

SELECT name, id FROM table_name;

分页指令

分页指令用于控制查询结果的分页显示，指定要返回的页码和每页的记录数。以下是分页指令的描述：

名称描述示例
pageNo要返回的页码，从 1 开始计数"pageNo": 1
current当前页码，与 pageNo 含义相同"current": 1
pageSize每页的记录数，指定返回的最大记录数"pageSize": 20

示例解析

  • pageNo:

    • 描述: 指定要返回的页码，页码从 1 开始计数。
    • 示例: "pageNo": 1
    • 说明: 请求第 1 页的记录。

  • current:

    • 描述: 当前页码，与 pageNo 含义相同，指定要返回的页码。
    • 示例: "current": 1
    • 说明: 请求第 1 页的记录。

  • pageSize:

    • 描述: 每页的记录数，指定每页返回的最大记录数。
    • 示例: "pageSize": 20
    • 说明: 每页返回 20 条记录。

综合示例

假设你有一个查询请求，并希望分页显示，每页 20 条记录，返回第 1 页的数据：

{
  "pageNo": 1,
  "pageSize": 20,
  "columns": "name,id",
  "name": "Tong",
  "nameOp": "ct"
}

生成的 SQL 语句应为：

SELECT name, id FROM users WHERE name LIKE '%Tong%' LIMIT 20 OFFSET 0;

这里 LIMIT 20 指定每页 20 条记录，OFFSET 0 指定从第 0 条记录开始（即第一页）。如果请求第 2 页的数据，pageNo 改为 2，则 OFFSET 变为 20：

{
  "pageNo": 2,
  "pageSize": 20,
  "columns": "name,id",
  "name": "Tong",
  "nameOp": "ct"
}

生成的 SQL 语句为：

SELECT name, id FROM users WHERE name LIKE '%Tong%' LIMIT 20 OFFSET 20;

排序指令

排序指令用于指定查询结果的排序方式。包括要排序的列和排序的顺序（升序或降序）。

指令说明

名称描述示例
orderBy排序方式"orderBy": "update_time"
isAsc是否升序排序"isAsc": "false"

  • orderBy:

    • 描述: 指定用来排序的列名。
    • 示例: "orderBy": "update_time"
    • 说明: 以 update_time 列进行排序。

  • isAsc:

    • 描述: 指定排序顺序。true 表示升序 (ASC)，false 表示降序 (DESC)。
    • 示例: "isAsc": "false"
    • 说明: 降序排列。

综合示例

假设你有一个查询请求，并希望根据 update_time 列进行降序排序：

{
  "columns": "name,id",
  "name": "Tong",
  "nameOp": "ct",
  "orderBy": "update_time",
  "isAsc": "false"
}

生成的 SQL 语句应为：

SELECT name, id FROM users WHERE name LIKE '%Tong%' ORDER BY update_time DESC;

如果希望升序排列，则将 isAsc 设置为 true：

{
  "columns": "name,id",
  "name": "Tong",
  "nameOp": "ct",
  "orderBy": "update_time",
  "isAsc": "true"
}

生成的 SQL 语句为：

SELECT name, id FROM users WHERE name LIKE '%Tong%' ORDER BY update_time ASC;

类型指令

类型指令用于解析数据字段的值。例如，如果数据库的主键类型是雪花 ID（Snowflake ID），前端通常会以字符串（string）类型传递该值，后端在获取值后将其转换为长整型（long）类型。类型指令通过指定字段类型，确保前端和后端和数据库的数据字段类型一致。

指令说明

名称描述示例
type指定字段类型{"id": "395190726650089472", "idType": "long"}

示例解析

type:

  • 描述: 指定字段的类型，确保数据在前端和后端之间正确传递和转换。
  • 示例:
{
  "id": "395190726650089472",
  "idType": "long"
}
  • 说明: id 字段在前端以字符串形式传递，后端接收后将其转换为长整型（long）类型。

支持的数据表

名称描述示例
int整数类型"ageType": "int"
long长整型"idType": "long"
int[]整数数组"idsType": "int[]"
long[]长整型数组"idsType": "long[]"
string[]字符串数组"namesType": "string[]"
ISO8601日期时间类型（ISO 8601 格式）{"dateType": "ISO8601","date": "2024-04-14T03:23:37.899Z"}

类型指令示例

假设你有一个查询请求，并希望使用 id 字段，指定其类型为长整型：

{
  "id": "395190726650089472",
  "idType": "long"
}

在后端，id 字段将被解析为长整型（long），确保与数据库中的数据表一致。

综合示例

假设你有一个复杂的查询请求，包含分页、排序和类型指令：

{
  "pageNo": 1,
  "pageSize": 20,
  "columns": "name,id",
  "name": "Tong",
  "nameOp": "ct",
  "orderBy": "update_time",
  "isAsc": "false",
  "id": "395190726650089472",
  "idType": "long"
}

生成的 SQL 语句应为：

SELECT name, id FROM table_name WHERE id=395190726650089472 and name LIKE '%Tong%' ORDER BY update_time DESC LIMIT 20 OFFSET 0;

并且在后端，id 字段会被正确解析为长整型（long）。

逻辑指令

逻辑指令用于在指定的字段前添加逻辑运算符（如 OR 和 AND），以构建更复杂的查询条件。通过逻辑指令，可以灵活地组合多个查询条件，满足不同的查询需求。

指令说明

名称描述示例其他
logic在指定的字段前添加逻辑运算符（OR, AND）"deleted": 0, "deletedLogic": "or", "deletedOp": "nl"logic 可选 or 或 and

示例解析

  • logic:
  • 描述: 指定逻辑运算符，用于连接不同的查询条件。可选值为 or 或 and。
  • 示例:
{
  "deleted": 0,
  "deletedLogic": "or",
  "deletedOp": "nl"
}
  • 说明: deleted 字段的查询条件使用 or 逻辑运算符，并且操作符为 nl（为空）。

示例解释

假设你有一个查询请求，要求 deleted 字段为空（is null）或等于某个值：

{
  "deleted": 0,
  "deletedLogic": "or",
  "deletedOp": "nl"
}

生成的 SQL 语句为：

SELECT * FROM posts WHERE deleted IS NULL OR deleted = 0

详细步骤

  1. 解析 JSON 输入: 服务器接收到 JSON 格式的查询请求。
  2. 识别逻辑运算符和操作符: 解析 JSON 对象中的逻辑运算符 ("deletedLogic": "or") 和操作符 ("deletedOp": "nl"）。
  3. 生成 SQL 条件:
    • deletedLogic 为 or，表示条件之间使用 OR 运算符。
    • deletedOp 为 nl，表示 deleted 字段为空（IS NULL）。
    • 因此，生成的 SQL 条件为 deleted IS NULL OR deleted = 0。
  4. 生成完整 SQL 语句: 将生成的条件添加到 SQL 语句中，并根据其他指令（如排序、分页等）生成完整的 SQL 语句。

综合示例

假设你有一个复杂的查询请求，包含分页、排序、字段选择和逻辑指令：

{
  "pageNo": 1,
  "pageSize": 10,
  "columns": "name,id",
  "name": "Tong",
  "nameOp": "ct",
  "orderBy": "created_at",
  "isAsc": "false",
  "deleted": 0,
  "deletedLogic": "or",
  "deletedOp": "nl"
}

生成的 SQL 语句应为：

SELECT name, id
FROM posts
WHERE name LIKE '%Tong%' AND (deleted IS NULL OR deleted = 0)
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

输入转换指令

输入转换指令 用于将数据字段的值转换为其他类型或者格式，以确保数据在处理过程中符合预期的类型和格式要求。

指令说明

名称描述
to_type用于将数据字段的值转为其他类型或者格式

支持的数据表

名称格式示例
ISO8601将字符串转为 YYYY-MM-DDTHH:mm:ss.sssZ 格式的日期{ "start_time": "2024-06-24 09:00:00", "end_time": "2024-06-24 10:30:00", "start_time_to_type": "ISO8601", "end_time_to_type": "ISO8601" }

示例解析

  1. 单个字段转换: 假设你有一个请求，其中 start_time 和 end_time 字段需要转换为 ISO8601 格式的日期：
{
  "start_time": "2024-06-24 09:00:00",
  "end_time": "2024-06-24 10:30:00",
  "start_time_to_type": "ISO8601",
  "end_time_to_type": "ISO8601"
}

后端在接收到数据后会将start_time 和 end_time 字段会被转换为 ISO8601 格式：

{
  "start_time": "2024-06-24T09:00:00.000Z",
  "end_time": "2024-06-24T10:30:00.000Z"
}
  1. 数组字段转换: 假设你有一个查询请求，其中 update_time 字段为数组，并需要转换为 ISO8601 格式：
{
  "update_time_op": "bt",
  "update_time": ["2024-06-25 00:00:00", "2024-06-25 00:00:07"],
  "update_time_type": "string[]",
  "update_time_to_type": "ISO8601"
}

后端在接收到数据后会将update_time 数组中的每个字符串会被转换为 ISO8601 格式：

{
  "update_time": ["2024-06-25T00:00:00.000Z", "2024-06-25T00:00:07.000Z"]
}

综合示例

假设你有一个复杂的查询请求，包含分页、排序、字段选择和输入转换指令：

{
  "pageNo": 1,
  "pageSize": 10,
  "columns": "name,id,update_time",
  "name": "Tong",
  "nameOp": "ct",
  "orderBy": "created_at",
  "isAsc": "false",
  "update_time_op": "bt",
  "update_time": ["2024-06-25 00:00:00", "2024-06-25 00:00:07"],
  "update_time_type": "string[]",
  "update_time_to_type": "ISO8601"
}

生成的 SQL 语句可能为：

SELECT name, id, update_time
FROM posts
WHERE name LIKE '%Tong%'
  AND update_time BETWEEN '2024-06-25T00:00:00.000Z' AND '2024-06-25T00:00:07.000Z'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

执行步骤

  1. 解析 JSON 输入: 服务器接收到 JSON 格式的查询请求。
  2. 识别并处理转换指令: 解析 JSON 对象中的 to_type 指令，将指定字段的值转换为目标类型或格式。
  3. 生成 SQL 条件: 使用转换后的字段值生成 SQL 条件。
  4. 生成完整 SQL 语句: 将生成的条件添加到 SQL 语句中，并根据其他指令（如分页、排序等）生成完整的 SQL 语句。

输出转换指令

输出指令用于指定数据字段值的输出格式

指令说明

名称描述示例
json_fields用于指定查询结果中需要以 JSON 对象形式返回的字段,如果不指定默认返回的是字符串，而不是 JSON 对象"json_fields":["files"]
  • json_fields:
  • 描述: json_fields 指令用于指定查询结果中需要以 JSON 对象形式返回的字段。默认情况下，数据库中的 JSON 数据表字段在查询结果中会以字符串形式返回。通过使用 json_fields 指令，可以明确指出哪些字段需要以 JSON 对象的形式返回，从而方便前端处理这些字段。
  • 示例:
{
  "json_fields": ["files"]
}
  • 说明: files 字段在查询结果中将以 JSON 对象的形式返回，而不是字符串。

示例解析

假设你有一个查询请求，并希望 files 字段以 JSON 对象形式返回：

{
  "json_fields": ["files"]
}

综合案例

分页查询

input

{
  "current": 1,
  "pageSize": 20,
  "name": "map",
  "update_time": ["2024-06-24 00:00:00", "2024-06-26 00:00:00"],
  "idType": "long",
  "remarkOp": "ct",
  "orderBy": "update_time",
  "update_time_type": "string[]",
  "update_time_op": "bt",
  "update_time_to_type": "ISO8601",
  "isAsc": "false",
  "deleted": 0,
  "json_fields": ["files"],
  "json_fields_type": "string[]",
  "nameOp": "ct",
  "departmentOp": "ct"
}

output

{
  "code": 1,
  "data":
  {
    "list":
    [
      {
        "tenant_id": "0",
        "creator": "",
        "create_time": "2024-06-25 06:26:51.525374",
        "description": "printstatioin map form",
        "url": "https://www.sjsu.edu/it/docs/print/wepaPrintStationMap.pdf",
        "updater": "",
        "update_time": "2024-06-25 06:26:51.525374",
        "deleted": 0,
        "name": "printstatioin map form",
        "files":
        [
          {
            "uid": "rc-upload-1719296816565-2",
            "size": 469981,
            "name": "1719296814911.png",
            "id": "395190747793575936",
            "type": "image/png",
            "url": "https://rumiapp.s3.us-west-1.amazonaws.com/sjsu/documents/395190740868780032.png",
            "status": "done"
          }
        ],
        "id": "395190726650089472"
      }
    ],
    "total": 1
  },
  "ok": true
}

输入解释

这个输入 JSON 请求定义了一次复杂的查询操作，包含分页、排序、字段选择、逻辑运算和类型转换指令。以下是对每个字段的详细解释：

  • current: 当前页码，表示要查询的页数。
    "current": 1
  • pageSize: 每页返回的数据条数。
    "pageSize": 20
  • name: 需要查询的名字字段。
    "name": "map"
  • update_time: 时间范围，用于查询在指定时间范围内的数据。
    "update_time": ["2024-06-24 00:00:00", "2024-06-26 00:00:00"]
  • idType: 指定 id 字段的数据表为 long。
    "idType": "long"
  • remarkOp: 对 remark 字段使用 ct（contains，包含）操作符。
    "remarkOp": "ct"
  • orderBy: 排序字段，这里指定为 update_time。
    "orderBy": "update_time"
  • update_time_type: 指定 update_time 字段的类型为 string[]。
    "update_time_type": "string[]"
  • update_time_op: 对 update_time 字段使用 bt（between，之间）操作符。
    "update_time_op": "bt"
  • update_time_to_type: 将 update_time 字段转换为 ISO8601 格式。
    "update_time_to_type": "ISO8601"
  • isAsc: 指定排序为降序。
    "isAsc": "false"
  • deleted: 查询未删除的数据。
    "deleted": 0
  • json_fields: 指定需要以 JSON 对象形式返回的字段，这里是 files 字段。
    "json_fields": ["files"]
  • json_fields_type: 指定 json_fields 的数据表为 string[]。
    "json_fields_type": "string[]"
  • nameOp: 对 name 字段使用 ct（contains，包含）操作符。
    "nameOp": "ct"
  • departmentOp: 对 department 字段使用 ct（contains，包含）操作符。
    "departmentOp": "ct"

输入解释总结

该输入定义了一次查询操作：

  • 分页参数 current 和 pageSize 用于分页。
  • name 字段的查询条件为包含 "map"。
  • update_time 字段的查询条件为介于 2024-06-24 和 2024-06-26 之间，并转换为 ISO8601 格式。
  • remark、name 和 department 字段的查询操作符为 ct（包含）。
  • 排序字段为 update_time，排序方式为降序。
  • 过滤掉 deleted 为 0 的数据。
  • 指定 files 字段以 JSON 对象形式返回。

输出解释

该输出 JSON 表示查询的结果，包含查询数据的列表和元数据。以下是对每个字段的详细解释：

  • code: 状态码，表示查询结果的状态。1 表示成功。

    "code": 1

  • data: 包含查询结果的主要数据对象。

    • list: 查询结果的列表。
      "list": [
  {
    "id": "395190726650089472"
    ...
  }
]
      • id: 数据的唯一标识符。
      • ...省略其他数据
    • total: 满足查询条件的总数据条数。
      "total": 1

  • ok: 操作是否成功，true 表示成功。

    "ok": true

输出解释总结

该输出表示：

  • 查询成功（code 为 1）。
  • 返回的数据列表中有一条数据。
  • 数据包含 tenant_id、creator、create_time、description、url、updater、update_time、deleted、name 和 files 字段。
  • files 字段以 JSON 对象形式返回，包含文件的详细信息。
  • total 表示满足查询条件的总记录数为 1。
  • 查询操作成功（ok 为 true）。