动态SQL查询分页改造说明

最新推荐文章于 2024-07-24 20:31:18 发布

肥鲶鱼报道

最新推荐文章于 2024-07-24 20:31:18 发布

阅读量122

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_59181205/article/details/133359166

版权

1、动态查询使用

1.1、参数传递:

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName": "CREATE_USER_NAME", // 查询列列名
            "queryItem": {
                "conjunction": "AND", // 逻辑短路：AND、OR，可不传：默认为AND
                "operator": "like_right", // 查询符号
                "value": "胡" // 查询的值
            }// 查询条件子项
        },
        {
            "columnName": "CREATE_USER_NAME",
            "queryItem": {
                "conjunction": "OR",
                "operator": "like_left",
                "value": "声康"
            }
        },
        {
            "columnName": "THINGS_CHANGE",
            "queryItem": {
                "conjunction": "AND",
                "operator": "in",
                "value": [
                    "JPMsoft_SXBG_QZ",
                    "JPMsoft_SXBG_SJ"
                ]
            }
        }
    ], // 查询条件
    "orderBys": {
        "CREATE_TIME": "DESC,",
        "UPDATE_TIME": "DESC"
    } // 排序条件
}

1.2、conditions使用说明：

1.2.1、请求方式为Post，使用json的形式传递参数给后端

1.2.2、参数说明：

pageNum:与之前用法一样
pageSize:同上

conditions:这个为自定义的查询条件，与之前的filter用法类似。conditions为数组类型，可传多个参数，也可为空。

使用示例：

例如我们想查询表中创建人为吴彦祖条件的分页数据：

之前filter字符串的用法：‘filter = create_user_name eq ‘吴彦祖’’，一般来说，不建议http请求url中出现中文。

现在的的用法：

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"create_user_name",
            "queryItem": {
                "conjunction": "AND",
                "operator":"eq", 
                "value":"吴彦祖"
            }
        }
    ]
}

operator可用参数说明：

eq:等于( == )，查询等于的情况下：比如查询金额等于xxx的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"amount",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"eq", 
                "value":xxx
            }
        }
    ]
}
                    
// 查询amount字段等于xxx的记录

ne:不等于（ != ），查询不等于的情况：比如查询金额不等于xxx的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"amount",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"ne", 
                "value":xxx
            }
        }
    ]
}
                    
// 查询amount字段不等于xxx的记录

lt:小于( < )，查询小于情况下使用：查询金额小于xxx的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"amount",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"lt", 
                "value":xxx
            }
        }
    ]
}
                    
// 查询amount字段小于xxx的记录

le:小于等于( <= )，查询小于等于情况下使用：查询金额小于等于xxx的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"amount",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"le", 
                "value":xxx
            }
        }
    ]
}
                    
// 查询amount字段小于等于xxx的记录

gt:大于( > )，查询大于情况下使用：查询金额大于xxx的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"amount",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"gt", 
                "value":xxx
            }
        }
    ]
}
                    
// 查询amount字段大于xxx的记录

ge:大于等于( >= )，查询大于等于情况下使用：查询金额大于等于xxx的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"amount",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"ge", 
                "value":xxx
            }
        }
    ]
}
                    
// 查询amount字段大于等于xxx的记录

in:小于等于( in )，查询包含的情况下使用：查询金额是1111，2222，3333的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"amount",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"in", 
                "value":[
                    1111,
                    2222,
                    3333
                ] // not_in 和 in的情况下"value"值为数组类型
            }
        }
    ]
}
                    
// 查询amount字段为：1111、2222、3333的记录

not_in:小于等于( not in)，查询不包含的情况下使用：查询金额不是1111，2222，3333的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"amount",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"not_in", 
                "value":[
                    1111,
                    2222,
                    3333
                ] // not_in 和 in的情况下"value"值为数组类型
            }
        }
    ]
}
                    
// 查询amount字段不为：1111、2222、3333的记录

like:模糊查询，情况示例：查询姓名中有‘一’的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"create_user_name",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"like", 
                "value":"一"
            }
        }
    ]
}
                    
// "一一一"✔
// "一二三"✔
// "三二一"✔
// "三一二"✔

like_right:模糊查询，情况示例：查询姓名中以‘一’开头的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"create_user_name",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"like_right", 
                "value":"一"
            }
        }
    ]
}
                        
// "一一一"✔
// "一二三"✔
// "三二一"✖

like_left:模糊查询，情况示例：查询姓名中以‘一’结尾的记录。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"create_user_name",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"like_left", 
                "value":"一"
            }
        }
    ]
}
                        
// "一一一"✔
// "一二三"✖
// "三二一"✔

conjunction可用参数说明：

AND:默认值，做AND连接，示例：查询姓名为吴彦祖并且金额大于1000的数据。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"create_user_name",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"eq", 
                "value":"吴彦祖"
            },
            "columnName":"amount",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"gt", 
                "value":1000
            }
        }
    ]
}

OR:做OR连接，示例：查询姓名为吴彦祖或者金额大于1000的金额。

{
    "pageNum": 1, // 当前页
    "pageSize": 10, // 页大小
    "conditions": [
        {
            "columnName":"create_user_name",
            "queryItem": {
                "conjunction": "AND", // AND可不传
                "operator":"eq", 
                "value":"吴彦祖"
            },
            "columnName":"amount",
            "queryItem": {
                "conjunction": "OR",
                "operator":"gt", 
                "value":1000
            }
        }
    ]
}

1.3、排序条件orderBys使用说明：

{
    "orderBys": {
        "CREATE_TIME": "DESC,",
        "UPDATE_TIME": "DESC"
    } // 排序条件()
}

1.3.1、非必传（不传时默认按照该功能表创建时间降序排序）
1.3.2、‘CREATE_TIME’列名，‘DESC’ 排序方式
1.3.3、排序方式
- 1.3.3.1 ‘ASC’ 升序排序。
```
{
    "orderBys": {
        "CREATE_TIME": "ASC,",
        "UPDATE_TIME": "DESC"
    } // 排序条件()
}
```
  按照创建时间升序排序，创建时间一致时，再按照更新时间降序排序
- 1.3.3.2 ‘DESC’ 降序排序
```
{
    "orderBys": {
        "UPDATE_TIME": "DESC"
    } // 排序条件()
}
```
  按照更新时间降序排序
- 1.3.3.3 注意事项：
  - 当出现多个字段排序时：
```
{
    "orderBys": {
        "CREATE_TIME": "ASC,",
        "UPDATE_TIME": "DESC,",
        "xxx1":"asc,",
        "xxx2":"desc"
    } // 排序条件()
}
```
    只有最后一个需要排序的字段排序方式后不要加 ’ , ’ ，其他在它前面的所有字段排序方式后都要加 ’ , '，
    
    上述例子中：xxx2字段的desc后面不用加逗号’ , ‘，在xxx2字段之前的所有要参与排序的字段，排序方式的后面都要加’ , '

1.4 不足之处及初步解决办法(可不看，目前所有字段都是mn.开头，如果后面需要查询其他的表，则该方法不满足，需要另写)

1.4.1、前端同事如果按照我上面的要求调用接口查询出现查询失败，或者接口返回500的情况是，很有可能是后端拼接的sql有问题。
- 解决办法：
  
  因为后端sql语句有很多都是做了多表查询这个操作，所以，你查询的列，可能和连接表里的列名一样，前端直接传的列字段名，没有加对应的别名就导致了数据库不知道用哪个表的这个字段进行查询。
  
  这种情况，可以试着在列字段名前加：mn.字段名，mn即这个功能的表，如果你要查询的字段，这个功能的表有这个字段（新增页面，有这个字段，或者一些公共字段（创建时间，流程状态之类的）），就可以用这种方法，给字段名前加：mn.

1.5 现已支持or写法，会根据连接词是否为or来为条件加括号。

详情见代码，目前仅支持一个or，不可连续or，中间可隔一个and后方可再or。如果有多个连续的or，建议用in或者notin。

/**
 * 动态查询条件
 *
 * @author GM
 * @date 2023/7/13 10:29
 * @description TODO
 */
@Getter
@ToString
public class DynamicQueryCondition implements Serializable {

    private static final long serialVersionUID = -614823886398323862L;

    // private Map<String, QueryItem> conditions;

    /**
     * 条件
     */
    private List<ColumnQueryItem> conditions;

    /**
     * 排序方式
     */
    private Map<String, String> orderBys;

    /**
     * 页大小 , 默认值 10
     */
    private Integer pageSize = 10;

    /**
     * 当前页 , 默认值 1
     */
    private Integer pageNum = 1;

    /**
     * 鉴权SQL
     */
    private String authSql;

    public DynamicQueryCondition() {
    }

    public DynamicQueryCondition(List<ColumnQueryItem> conditions,
                                 Map<String, String> orderBys,
                                 Integer pageSize,
                                 Integer pageNum,
                                 String authSql) {
        this.conditions = conditions;
        this.orderBys = orderBys;
        this.pageSize = pageSize;
        this.pageNum = pageNum;
        this.authSql = authSql;
    }

    public void setConditions(List<ColumnQueryItem> conditions) {
        // 设置or的连接条件时，把前面一个条件的and后面加 ‘(’ 左括号，
        // xml的sql片段中，如果条件为or，则在最后会加 ‘）’ 这样就能把or的条件用‘(条件)’括起来，让查询更准确更合理
        // 目前仅支持一个or，不可连续or，中间可隔一个and后方可再or。如果有多个连续的or，建议用in或者notin
        for (int i = 0; i < conditions.size(); i++) {
            ColumnQueryItem queryItem = conditions.get(i);
            QueryItem query = queryItem.getQueryItem();
            // 如果当前条件的连接词是 or
            if (query.getConjunction().equalsIgnoreCase("or") && i > 0) {
                // 取出上一条连接词为的and的条件，目前默认or前面一个就是and
                QueryItem query_1 = conditions.get(i - 1).getQueryItem();
                // 设置前面一个条件的连接词后加 ‘(’ 左括号
                conditions.get(i - 1).getQueryItem().setConjunction(query_1.getConjunction() + " (");
            }
        }
        this.conditions = conditions;
    }

    public void setOrderBys(Map<String, String> orderBys) {
        this.orderBys = orderBys;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public void setAuthSql(String authSql) {
        this.authSql = authSql;
    }
}

对应的后台动态xml文件，可以看到，没加什么，就是判断，如果连接词是or，则在后面多加一个右括号’)‘，这样就可以和上面代码的左括号’('相对应。

<!-- 动态拼接sql：查询片段 -->
        <if test="conditions != null and conditions.size() > 0">
            <foreach collection="conditions" item="item" index="">
                <choose>
                    <!-- 模糊 ‘%xxx%’ -->
                    <when test=" item.queryItem.operator == 'like' ">
                        <if test=" item.queryItem.conjunction == 'OR' ">
                            ${item.queryItem.conjunction} mn.${item.columnName} LIKE CONCAT('%', #{item.queryItem.value},'%'))
                        </if>
                        <if test=" item.queryItem.conjunction != 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} LIKE CONCAT('%', #{item.queryItem.value},'%')
                        </if>
                    </when>
                    <!-- 模糊（右） ‘xxx%’ -->
                    <when test=" item.queryItem.operator == 'like_right' ">
                        <if test=" item.queryItem.conjunction == 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} LIKE CONCAT(#{item.queryItem.value}, '%'))
                        </if>
                        <if test=" item.queryItem.conjunction != 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} LIKE CONCAT(#{item.queryItem.value}, '%')
                        </if>
                    </when>
                    <!-- 模糊（左） ‘%xxx’ -->
                    <when test=" item.queryItem.operator == 'like_left' ">
                        <if test=" item.queryItem.conjunction == 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} LIKE CONCAT('%', #{item.queryItem.value}))
                        </if>
                        <if test=" item.queryItem.conjunction != 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} LIKE CONCAT('%', #{item.queryItem.value})
                        </if>
                    </when>
                    <!-- 等于 -->
                    <when test=" item.queryItem.operator == 'eq' ">
                        <if test=" item.queryItem.conjunction == 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} = #{item.queryItem.value})
                        </if>
                        <if test=" item.queryItem.conjunction != 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} = #{item.queryItem.value}
                        </if>
                    </when>
                    <!-- 不等于 -->
                    <when test=" item.queryItem.operator == 'ne' ">
                        <if test=" item.queryItem.conjunction == 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} != #{item.queryItem.value})
                        </if>
                        <if test=" item.queryItem.conjunction != 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} != #{item.queryItem.value}
                        </if>
                    </when>
                    <!-- 大于 -->
                    <when test=" item.queryItem.operator == 'gt' ">
                        <if test=" item.queryItem.conjunction == 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} &gt; #{item.queryItem.value})
                        </if>
                        <if test=" item.queryItem.conjunction != 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} &gt; #{item.queryItem.value}
                        </if>
                    </when>
                    <!-- 小于 -->
                    <when test=" item.queryItem.operator == 'lt' ">
                        <if test=" item.queryItem.conjunction == 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} &lt; #{item.queryItem.value})
                        </if>
                        <if test=" item.queryItem.conjunction != 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} &lt; #{item.queryItem.value}
                        </if>
                    </when>
                    <!-- 大于等于 -->
                    <when test=" item.queryItem.operator == 'ge' ">
                        <if test=" item.queryItem.conjunction == 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} &gt;= #{item.queryItem.value})
                        </if>
                        <if test=" item.queryItem.conjunction != 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} &gt;= #{item.queryItem.value}
                        </if>
                    </when>
                    <!-- 小于等于 -->
                    <when test=" item.queryItem.operator == 'le' ">
                        <if test=" item.queryItem.conjunction == 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} &lt;= #{item.queryItem.value})
                        </if>
                        <if test=" item.queryItem.conjunction != 'OR'">
                            ${item.queryItem.conjunction} mn.${item.columnName} &lt;= #{item.queryItem.value}
                        </if>
                    </when>
                    <!-- IN -->
                    <when test=" item.queryItem.operator == 'in' ">
                        <if test=" item.queryItem.value != null and item.queryItem.value.size() > 0">
                            <if test=" item.queryItem.conjunction == 'OR'">
                                ${item.queryItem.conjunction} mn.${item.columnName} IN
                                <foreach collection="item.queryItem.value" open="(" separator="," close="))" item="inItem" index="">
                                    #{inItem}
                                </foreach>
                            </if>
                            <if test=" item.queryItem.conjunction != 'OR'">
                                ${item.queryItem.conjunction} mn.${item.columnName} IN
                                <foreach collection="item.queryItem.value" open="(" separator="," close=")" item="inItem" index="">
                                    #{inItem}
                                </foreach>
                            </if>
                        </if>
                    </when>
                    <!-- NOT IN -->
                    <when test=" item.queryItem.operator == 'not_in' ">
                        <if test=" item.queryItem.value != null and item.queryItem.value.size() > 0">
                            <if test=" item.queryItem.conjunction == 'OR'">
                                ${item.queryItem.conjunction} mn.${item.columnName} NOT IN
                                <foreach collection="item.queryItem.value" open="(" separator="," close="))" item="notInItem" index="">
                                    #{notInItem}
                                </foreach>
                            </if>
                            <if test=" item.queryItem.conjunction != 'OR'">
                                ${item.queryItem.conjunction} mn.${item.columnName} NOT IN
                                <foreach collection="item.queryItem.value" open="(" separator="," close=")" item="notInItem" index="">
                                    #{notInItem}
                                </foreach>
                            </if>
                        </if>
                    </when>
                    <!-- 添加更多操作符的处理 -->
                    <!--<otherwise>
                        1 = 1
                    </otherwise>-->
                </choose>
            </foreach>
        </if>
        <!-- 动态拼接sql：排序片段 -->
        <if test=" orderBys != null and orderBys.size() > 0">
            ORDER BY
            <foreach collection="orderBys.entrySet()" item="item" index="key">
                <if test=" item != null and item != '' ">
                    mn.${key} ${item}
                </if>
            </foreach>
        </if>
        <if test=" orderBys == null or orderBys.size() == 0">
            ORDER BY mn.CREATE_TIME DESC
        </if>

肥鲶鱼报道

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
动态SQL查询分页改造说明

1、动态查询使用1.1、参数传递:{ "pageNum": 1, // 当前页 "pageSize": 10, // 页大小 "conditions": [ { "columnName": "CREATE_USER_NAME", // 查询列列名 "queryItem": { "conjunction": "AND", // 逻辑短路：AND、OR，可不传：默认为AND
复制链接

扫一扫