V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
irisdev
V2EX  ›  Java

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

  •  
  •   irisdev · 16 小时 44 分钟前 · 1977 次点击

    做管理系统,我们现在开发的大多数表单都是“与”的关系,比如前端有四个查询条件 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 去掉,还是说这个工作前端做比较好

    49 条回复    2024-10-31 17:36:35 +08:00
    irisdev
        1
    irisdev  
    OP
       16 小时 41 分钟前
    不小心点了发送了,有回复的先感谢一下,总感觉这个项目的这种写法太草台班子了,但是又没见过好的,希望开开眼
    carrotliang
        2
    carrotliang  
       16 小时 34 分钟前
    可以参考 mybatis 的解决方案
    BugCry
        3
    BugCry  
       16 小时 30 分钟前 via Android
    已经等不及要注入了!
    chendy
        4
    chendy  
       16 小时 26 分钟前
    mybatis 的硬拼方案
    jpa 的 criteria 模式
    手拼 sql 的除非场景简单单一否则就等着被灌满不是被注入吧
    irisdev
        5
    irisdev  
    OP
       16 小时 2 分钟前
    @carrotliang
    @chendy 谢谢两位,我了解一下 mybatis 和 jpa
    irisdev
        6
    irisdev  
    OP
       16 小时 1 分钟前
    @BugCry 不会被注入的,内网环境,写代码时会加 Parameter 的,写 demo 时懒得加了
    XuHuan1025
        7
    XuHuan1025  
       15 小时 59 分钟前
    不是有那啥 graphsql 吗
    irisdev
        8
    irisdev  
    OP
       15 小时 53 分钟前
    @XuHuan1025 都是用的 post+json ,切换不现实了,而且 graphql 好像解决不了这个问题吧
    sagaxu
        9
    sagaxu  
       15 小时 52 分钟前
    这是典型的递归应用场景,把多个条件放入 list ,再用" or " / " and " 做分隔符拼接起来,每一层都用()包起来
    Bingchunmoli
        10
    Bingchunmoli  
       15 小时 39 分钟前 via Android
    mybatis
    miracleyao
        11
    miracleyao  
       15 小时 38 分钟前
    mybatis-plus lambda
    EastLord
        12
    EastLord  
       15 小时 25 分钟前 via iPhone
    mybatis 动态 sql
    JPA 也行
    90d0n
        13
    90d0n  
       15 小时 21 分钟前   ❤️ 1
    巧了, 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
        14
    csys  
       15 小时 17 分钟前   ❤️ 1
    构造表达式语法树,然后根据表达式生成 sql
    拼接表达式要比拼接 sql 容易的多
    市面上应该有类似的库吧
    xuanbg
        15
    xuanbg  
       15 小时 16 分钟前
    别想太多,条件基本都是固定的。就算变,也不会频繁变。而且有上限,总不能用不存在的字段做条件吧?就算是,也是无效的啊。

    所以硬拼 sql 没毛病。
    wolfie
        16
    wolfie  
       15 小时 14 分钟前   ❤️ 1
    用 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
        17
    sparklee  
       15 小时 9 分钟前   ❤️ 1
    内网环境直接由前端传 SQL 得了
    gbw1992
        18
    gbw1992  
       15 小时 8 分钟前
    c# 的做法就是实体类特性声明+表达式树,ORM 库都是这样实现的
    其中的一个用法你可以参考 https://www.cnblogs.com/FreeSql/p/16485310.html
    Java 实现的话应该类似
    Noicdi
        19
    Noicdi  
       15 小时 3 分钟前
    SELECT *
    FROM table t
    WHERE (:a IS NULL OR t.a = :a)
    AND (:b IS NULL OR t.b = :b)
    hukei
        21
    hukei  
       14 小时 34 分钟前
    额 没有 ORM 吗 交给框架就好
    cheng6563
        22
    cheng6563  
       14 小时 28 分钟前
    让前端传 SQL ,后台查的时候设置连接 readonly 。
    注入随他注吧,别把表删了就行。
    siweipancc
        23
    siweipancc  
       14 小时 28 分钟前 via iPhone
    兄弟你好奔放啊,没被攻击过?
    sparklee
        24
    sparklee  
       14 小时 22 分钟前
    前端传 sql 语句中的 where 部分, 做好验证, 内网基本没啥大问题吧
    nice2cu
        25
    nice2cu  
       13 小时 41 分钟前
    <if test="param.xx!=null">
    AND xxx = #{param.xxx}
    </if>
    mybatis
    adoal
        26
    adoal  
       13 小时 32 分钟前
    大概率是业务需求没梳理清楚……
    ccsert
        27
    ccsert  
       13 小时 17 分钟前
    可以看看 bean-searcher 这个框架,专门做复杂查询的 https://gitee.com/troyzhxu/bean-searcher
    NoKey
        28
    NoKey  
       13 小时 6 分钟前
    常规业务吧,产品经理可以定一下,哪些字段可以查询,可以限制一下,别太多,后端就好处理了
    vcbal
        29
    vcbal  
       13 小时 2 分钟前
    标准 XY 问题
    fzdfengzi
        30
    fzdfengzi  
       13 小时 0 分钟前
    用表达式目录树动态拼 sql
    kingcanfish
        31
    kingcanfish  
       12 小时 59 分钟前
    如果是各种条件乱七八糟的复杂查询 是不是应该用 cel https://cel.dev/?hl=zh-cn
    815979670
        32
    815979670  
       12 小时 51 分钟前
    插个楼 问一下 为什么后台还需要考虑注入的问题?@siweipancc @cheng6563 @BugCry

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

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

    当然 可能有我没想到的原因,所以想提出来交流一下。
    codehz
        33
    codehz  
       12 小时 34 分钟前
    @815979670 就算假设业务人员不会有恶意的情况,也可能意外复制粘贴了有问题的文本到查询框里
    siweipancc
        34
    siweipancc  
       12 小时 28 分钟前 via iPhone
    @815979670 搜索用户名我传两”_ ”进去 ,上家就是这么烂的
    skallz
        35
    skallz  
       12 小时 24 分钟前
    @siweipancc 说的对,就算内网用户没有任何恶意行为,也会因为预想之外的内容导致出现问题
    treblex
        36
    treblex  
       11 小时 59 分钟前
    https://github.com/LazyFury/SpringLearn/blob/master/kt/src/main/java/io/lazyfury/lucky_cat/common/helper/SearchHelper.kt

    试试这个,url 格式类似 django ,用 jpa Specification 实现的,
    我在 java 和 php 和 go 都尝试了类似的实现,最后选的 go ,所以这个代码不太严谨,但是能跑😂
    815979670
        37
    815979670  
       11 小时 8 分钟前
    @codehz @siweipancc @skallz
    有道理 我忽略了非主观故意的场景,所以最好的方式还是走 SQL 预处理,通过占位符,使数据与 SQL 分离,这样即使非主观故意 也不会对业务产生影响
    wogogoing
        38
    wogogoing  
       10 小时 53 分钟前
    @BugCry 哈哈 果然有这样的回复。
    Sayuri
        39
    Sayuri  
       10 小时 47 分钟前
    https://www.npmjs.com/package/nicot

    安利一下自己写的 npm 包,可以解决这个问题。
    diagnostics
        40
    diagnostics  
       10 小时 46 分钟前
    @irisdev #8 graphql 可以解决
    importmeta
        41
    importmeta  
       10 小时 29 分钟前
    说个之前碰见的某国企的做法, 有个传公式, 另一个字段传数据.
    BigEarMosquito
        42
    BigEarMosquito  
       10 小时 20 分钟前
    lucasdev
        43
    lucasdev  
       9 小时 23 分钟前   ❤️ 1
    可以看看 React Query Builder ( https://react-querybuilder.js.org/) 是怎么做的


    Convert to 那里可以
    - 选 SQL (parameterized) / SQL (named parameters),输出防注入的 SQL
    - 选 JSON (和你给的那个 JSON 格式差不多),但这种需要你自己构建语法树,再转成 SQL (也可以找找现成的三方库)
    Anakin078
        44
    Anakin078  
       9 小时 8 分钟前
    @815979670 #32 并不能确保后台的认证、鉴权不会出问题。在有 SQL 注入和没有 SQL 注入的场景下,绕过认证鉴权访问后台的攻击影响是不一样的,在有 SQL 注入的场景下会危害到整库整表,甚至达到服务器的 RCE 。层层防护才能有效增加攻击成本,最大化的保障数据安全。
    815979670
        45
    815979670  
       8 小时 50 分钟前
    @Anakin078 是的 不能因为假设的前提 就完全信任客户端体积的数据
    Anakin078
        46
    Anakin078  
       8 小时 48 分钟前
    @815979670 #45 赞!有这种认识很好,开发的代码安全性会高很多的。
    ninjamk200
        47
    ninjamk200  
       7 小时 16 分钟前
    这个 react 库不错
    JYii
        48
    JYii  
       7 小时 13 分钟前
    这么拼起来数据量上来了,sql 优化怎么办(小表当没看见
    irisdev
        49
    irisdev  
    OP
       6 小时 45 分钟前
    @JYii 都不用数据量大起来,一般都是多表 join 拼的,不过用 orm 更难优化吧
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1970 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 16:22 · PVG 00:22 · LAX 09:22 · JFK 12:22
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.