请教后台接口如何根据前台的筛选条件动态构造查询 sql

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

做管理系统，我们现在开发的大多数表单都是“与”的关系，比如前端有四个查询条件 a,b,c,d ，前端把这四个查询条件都传给我，我在查询接口里面会这么写:

if(a != null)
{
  sql += " and table.a = a";
}

if(b != null)
{
  sql += " and table.b = b";
}

这样写是没有问题的，假如现在有这样一种场景，前端四个查询条件中 a 和 b 是或的关系，比如 a 指的是待筛选内容的创建者，b 指的是待筛选内容的处理者，那么根据创建者或处理者来查询是合理的需求，同时 a 、b 与 c 、d 又是与的关系，比如 c 指截止时间,d 指处理状态，那么这时按照上面的思路，拼 sql 就得这么拼

if(a != null)
{
  if(b != null)
  {
    sql += " and (table.a = a or table.b = b)";
  }
  else {
    sql += " and table.a = a";
  }
}

if(b != null)
{
  if(a != null)
  {
    sql += " and (table.a = a or table.b = b)";
  }
  else {
    sql += " and table.b = b";
}

现在这样算是蒙混过关了（我现在就是这么处理的），但是我又想到一个问题，假如用户或的查询条件是不固定的，且前端只传给我一个对象，那这个 sql 拼接就是指数倍增加了。再假设，如果前端写了一个支持根据后台传过去的字段，由用户递归构造且/或查询条件的“且或组件”，这个组件支持用户自定义不确定数量的且或关系，支持自定义操作符，例如，传过来，不传 searchParam 对象，传过来是一个 Json 对象，类似以下:

{
    "prop": "",
    "relation": "and",
    "condition": [
        {
            "relation": "or",
            "condition": [
                {
                    "prop": "Name",
                    "operation": ">",
                    "value": "李华"
                },
                {
                    "prop": "Age",
                    "operation": "=".
                    "value": 24
                }
            ]
        },
        {
            "prop": "School",
            "operation": "=",
            "value": "大学"
        }
    ]
}

这时候的 sql 该咋拼呢，感觉像是一道算法题？是不是直接后序遍历这个 json 结构，把对应拼接的 sql 直接放代码就可以了？算法比较弱，还没写，主要不知道括号往哪放，还是没遍历一层直接嵌套括号就可以了？

最后又回到开始的问题，如果一开始前端就传给我一个{}，让我处理，有些字段是或，跟其他字段是且，这样的话我是不是就得写一大串 sql 了，有没有好点的办法，还是我在后台反射下，把不为 null 的给过滤出来，这样就不用判断一大坨为 null 的逻辑了，因为是维护老项目不好在序列化时把 null 去掉，还是说这个工作前端做比较好

31 条回复 • 2024-10-31 11:22:47 +08:00

irisdev

3 小时 44 分钟前

不小心点了发送了，有回复的先感谢一下，总感觉这个项目的这种写法太草台班子了，但是又没见过好的，希望开开眼

carrotliang

3 小时 37 分钟前

可以参考 mybatis 的解决方案

BugCry

3 小时 33 分钟前 via Android

已经等不及要注入了！

chendy

3 小时 29 分钟前

mybatis 的硬拼方案
jpa 的 criteria 模式
手拼 sql 的除非场景简单单一否则就等着被灌满不是被注入吧

irisdev

3 小时 5 分钟前

@carrotliang
@chendy 谢谢两位，我了解一下 mybatis 和 jpa

irisdev

3 小时 4 分钟前

@BugCry 不会被注入的，内网环境，写代码时会加 Parameter 的，写 demo 时懒得加了

XuHuan1025

3 小时 2 分钟前

不是有那啥 graphsql 吗

irisdev

2 小时 56 分钟前

@XuHuan1025 都是用的 post+json ，切换不现实了，而且 graphql 好像解决不了这个问题吧

sagaxu

2 小时 55 分钟前

这是典型的递归应用场景，把多个条件放入 list ，再用" or " / " and " 做分隔符拼接起来，每一层都用()包起来

Bingchunmoli

2 小时 42 分钟前 via Android

mybatis

miracleyao

2 小时 40 分钟前

mybatis-plus lambda

EastLord

2 小时 28 分钟前 via iPhone

mybatis 动态 sql
JPA 也行

90d0n

2 小时 24 分钟前

巧了, lz 你说的这个东西我实现过.
我理解你的问题关键点不在于 sql 拼接, 而是如何将动态的 json 转换为查询逻辑, 因为 sql 拼接有很多方式, mybatis 也好 jpa 也好都是在转换为查询逻辑之后的具体实现.

所以我最终的实现方式中使用了 visitor 模式去分片组织查询逻辑.

```json
{
"paginate": true,
"page": 1,
"size": 20,
"sort": [
{
"field": "createdDate",
"direction": "DESC"
}
],
"filter": {
"match": "ALL",
"rules": [
{
"field": "name",
"operator": "LK",
"value": "111"
},
{
"match": "ANY",
"rules": [
{
"field": "title",
"operator": "EQ",
"value": "111"
},
{
"field": "isbn",
"operator": "EQ",
"value": "1"
}
]
}
]
}
}
```

```sql
SELECT
*
FROM
book
WHERE
"name" LIKE ? ESCAPE''
AND ( title =? OR isbn =? )
ORDER BY
created_date DESC OFFSET ? ROWS FETCH FIRST ? ROWS ONLY
```

csys

2 小时 20 分钟前

构造表达式语法树，然后根据表达式生成 sql
拼接表达式要比拼接 sql 容易的多
市面上应该有类似的库吧

xuanbg

2 小时 19 分钟前

别想太多，条件基本都是固定的。就算变，也不会频繁变。而且有上限，总不能用不存在的字段做条件吧？就算是，也是无效的啊。

所以硬拼 sql 没毛病。

wolfie

2 小时 17 分钟前

用 mybatis plus

定义一个关联表基础 SQL ，外部拼接 `QueryWrapper`
根据入参数据结构，动态拼接 SQL ，多层级 json 可以搭配递归拼接 QueryWrapper

``` java
@Mapper
public interface YourMapper {
@Select("SELECT a.*, b.* FROM table_1 a LEFT JOIN table_2 b ON a.id = b.a_id ${ew.customSqlSegment}")
List<Entity> selectWithJoin(@Param("ew") QueryWrapper<?> wrapper);
}

QueryWrapper<?> wrapper = new QueryWrapper<>();
wrapper
.and(and -> {
and.eq("table_1.field_1", "1").eq("table_2.field_2", "2");
})
.and(and -> {
and.gt("table_1.field_2", 123).or().lt("table_1.field_2", 234);
});
```