请教后台接口如何根据前台的筛选条件动态构造查询 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 去掉，还是说这个工作前端做比较好

51 条回复 • 2024-11-19 19:14:15 +08:00

irisdev

21 天前

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

carrotliang

21 天前

可以参考 mybatis 的解决方案

BugCry

21 天前 via Android

已经等不及要注入了！

chendy

21 天前

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

irisdev

21 天前

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

irisdev

21 天前

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

XuHuan1025

21 天前

不是有那啥 graphsql 吗

irisdev

21 天前

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

sagaxu

21 天前

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

Bingchunmoli

21 天前 via Android

mybatis

miracleyao

21 天前

mybatis-plus lambda

EastLord

21 天前 via iPhone

mybatis 动态 sql
JPA 也行

90d0n

21 天前

巧了, 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

21 天前

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

xuanbg

21 天前

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

所以硬拼 sql 没毛病。

wolfie

21 天前

用 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);
});
```

sparklee

21 天前

内网环境直接由前端传 SQL 得了

gbw1992

21 天前

c# 的做法就是实体类特性声明+表达式树，ORM 库都是这样实现的
其中的一个用法你可以参考 https://www.cnblogs.com/FreeSql/p/16485310.html
Java 实现的话应该类似

Noicdi

21 天前

SELECT *
FROM table t
WHERE (:a IS NULL OR t.a = :a)
AND (:b IS NULL OR t.b = :b)

chobitssp

21 天前

https://demos.devexpress.com/ASPNetCore/Demo/DataGrid/Filtering

hukei

21 天前

额没有 ORM 吗交给框架就好

cheng6563

21 天前

让前端传 SQL ，后台查的时候设置连接 readonly 。
注入随他注吧，别把表删了就行。

siweipancc

21 天前 via iPhone

兄弟你好奔放啊，没被攻击过？

sparklee

21 天前

前端传 sql 语句中的 where 部分, 做好验证, 内网基本没啥大问题吧

nice2cu

21 天前

<if test="param.xx!=null">
AND xxx = #{param.xxx}
</if>
mybatis

adoal

21 天前

大概率是业务需求没梳理清楚……

ccsert

21 天前

可以看看 bean-searcher 这个框架，专门做复杂查询的 https://gitee.com/troyzhxu/bean-searcher

NoKey

21 天前

常规业务吧，产品经理可以定一下，哪些字段可以查询，可以限制一下，别太多，后端就好处理了

vcbal

21 天前

标准 XY 问题

fzdfengzi

21 天前

用表达式目录树动态拼 sql

kingcanfish

21 天前

如果是各种条件乱七八糟的复杂查询是不是应该用 cel https://cel.dev/?hl=zh-cn

815979670

21 天前

插个楼问一下为什么后台还需要考虑注入的问题？@siweipancc @cheng6563 @BugCry

我的理解是后台只要把登录体系的身份验证做好（例如强密码、定期修改密码、MFA 多因素认证、短信验证码、webAuthn 、登录网段限制等手段），保证别人不能非法登录进去这个前提下。

后端不像前端不会直面用户，为了后台开发的灵活性，我觉得做一些 SQL 拼接操作是可以接受的，只要记录好操作记录、正常使用后台的内部人员不会故意通过注入等手段破坏系统数据的吧？

当然可能有我没想到的原因，所以想提出来交流一下。