Mysql 通用任意单表查询API设计

最新推荐文章于 2025-10-17 15:53:07 发布
原创 最新推荐文章于 2025-10-17 15:53:07 发布 · 888 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

应用场景

1/数据库任意单表查询的重复劳动
2/数据库任意单表多业务需要不同字段的重复劳动

#说明

1/黑名单设计，解决敏感表和敏感字段拦截
2/防SQL注入处理，对key,value的合法性进行验证
3/对于表名暴露给前端，有两种处理方式，一种由node层来传参，一种服务器添加一个表名映射，实现逻辑表与物理表的联系
4/为了性能考虑，fields与filter为必填项
5/可扩展：加入分页查询，可以实现任意表的分页查询

使用场景：
1/根据ID查询，返回id,name字段
{
	"table":"t_base_user",
	"fields":["id","name"],
	"filter":{
		"id":"1111"
	}
}
2/in查询
{
	"table":"t_base_user",
	"fields":["id","name"],
	"filter":{
		"id":["1",2]
	}
}
3/like查询
{
	"table":"t_base_user",
	"fields":["id","name"],
	"filter":{
		"name":{"$like":"%富贵%"}
	}
}
4/范围查询 大于，小于
{
	"table":"t_base_user",
	"fields":["id","name"],
	"filter":{
		"start_time":{
			"$gte":"2020-03-13 00:00:00",
			"$lte":"2020-03-20 00:00:00"
		}
	}
}
5/反向查询,不等于
{
	"table":"t_base_user",
	"fields":["id","name"],
	"filter":{
		"type":{"$ne":1}
	}
}
6/嵌套逻辑范围查询，$and,$or
{
	"table":"t_base_user",
	"fields":["id","name"],
	"filter":{
		"group_account":"001",
		"$or":{
			"type":{"$ne":1},
			"name":{"$like":"富贵%"}
		}
	}
}
where group_account='001' and (type !=1 or name like '富贵%')

7/根据关键字模糊匹配,倒序前5条记录
{
	"table":"t_base_user",
	"fields":["id","name"],
	"filter":{
		"name":{"$like":"%宝贵%"}
	},
	"sort":{"time":"desc"},
	"limit":5
}

#实体对象

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class QueryReq implements Serializable {

    /**
     * 数据源
     */
    private String table;

    /**
     * 返回字段-as别名
     * eg:["id","head_img as headImg"]
     */
    @NotNull(message = "'fields' 不能为空")
    private List<String> fields;

    /**
     * 过滤条件
     * eg:
     * {
     * 		"org_account":"ZJXHJT",
     * 		"start_time":{
     * 			"$gte":"2020-03-13 00:00:00",
     * 		    "$gt":"2020-03-13 00:00:00",
     * 			"$lte":"2020-03-20 00:00:00",
     * 		    "$lt":"2020-03-20 00:00:00"
     *      },
     * 		"activity_type":{"$ne":2},
     * 	    "activity_name":{"$like":"%hello%"},
     * 		"$and":{
     * 			"activity_status":3,
     * 			"project_account":"001"
     *        },
     * 		"$or":{
     * 			"activity_status":3,
     * 			"project_account":"001"
     *        },
     * 		"prize_configure":["1111","0001","1001"]* 	}
     */
    @NotNull(message = "'filter' 不能为空")
    private Map<String,Object> filter;

    /**
     * 返回类型 list | obj
     */
    private String resultType = "list";

    /**
     * 返回记录数
     */
    private Integer limit;

    /**
     * 排序,eg:{"id":"desc","name":"asc"}
     */
    private LinkedHashMap<String,String> sort;

    /**
     * 是否显示SQL
     */
    private boolean showSql = false;
}

#解析器，转SQL语句

@Service
@Slf4j
public class CommonService {

    @Autowired
    private CommonMapper commonMapper;

    private final List<String> scope = Arrays.asList("$or","$and");
    private final List<String> variables = Arrays.asList("$gt","$gte","$lt","$lte","$ne","$like");
    private final List<String> sortEnum = Arrays.asList("asc","desc");
    private static final Map<String,List<String>> blackTableFieldList = new HashMap<>();


    private final Pattern keyRegexPat = Pattern.compile("[^(a-zA-Z 0-9_\\-\\$)]+");
    private final Pattern valueRegexPat = Pattern.compile("[']+");
    static
    {
        //黑名单字段描述
        //blackTableFieldList.put("t_user", Arrays.asList("*"));
        //blackTableFieldList.put("t_user", Arrays.asList("password"));
    }

    public Object query(QueryReq queryReq) {
        validateKey(queryReq.getTable());


        List<String> blankFieldList = blackTableFieldList.get(queryReq.getTable());
        if (blankFieldList !=null && blankFieldList.contains("*")){
            throw new BizException(ErrorCode.SERVER_ERROR.getCode(),"无权限访问此数据源任意字段");
        }

        queryReq.getFields().forEach(item->{
            validateKey(item);
            if (blankFieldList !=null && blankFieldList.contains(item)){
                throw new BizException(ErrorCode.SERVER_ERROR.getCode(),"无权限访问此数据源,'"+item+"'字段");
            }
        });

        if (queryReq.getTable() == null){
            throw new BizException(ErrorCode.SERVER_ERROR.getCode(),"未知数据源");
        }

        StringBuilder sql = new StringBuilder("select ");
        sql.append(String.join(",",queryReq.getFields()));
        sql.append(" from ");
        sql.append(queryReq.getTable());
        sql.append(" where ");
        sql.append(buildWhere("$and",queryReq.getFilter()));
        sql.append(buildSort(queryReq.getSort()));
        sql.append(!"list".equals(queryReq.getResultType())?" limit 1 ":queryReq.getLimit() == null?"":" limit "+queryReq.getLimit());
        log.debug("generator sql:{}",sql.toString());
        if (queryReq.isShowSql()){
            return sql.toString();
        }
        try {
            List<Object> resultList = commonMapper.query(sql.toString());
            if (!"list".equals(queryReq.getResultType())){
                return resultList.size()==0?Collections.EMPTY_MAP:resultList.get(0);
            }
            return resultList;
        }catch (Exception e){
            throw new BizException(ErrorCode.SERVER_ERROR.getCode(),e.getCause().getMessage());
        }
    }

    private void validateKey(String item) {
        Matcher matcher = keyRegexPat.matcher(item);
        if(matcher.find()){
            String subStr = scopeStr(item,matcher.start(0)-10,matcher.end(0)+10);
            throw new BizException(ErrorCode.FORMAT_ERROR.getCode(),"包含特殊符号：'"+subStr+"'");
        }
    }

    private void validateValue(String item) {
        Matcher matcher = valueRegexPat.matcher(item);
        if(matcher.find()){
            String subStr = scopeStr(item,matcher.start(0)-10,matcher.end(0)+10);
            throw new BizException(ErrorCode.FORMAT_ERROR.getCode(),"包含特殊符号：'"+subStr+"'");
        }
    }

    private String scopeStr(String item, int start, int end) {
        start = start<0?0:start;
        end = end>item.length()?item.length():end;
        return item.substring(start,end);
    }

    private String buildSort(LinkedHashMap<String,String> sort) {
        if (CollectionUtils.isEmpty(sort)){
            return "";
        }
        StringBuilder valStr = new StringBuilder(" order by ");
        boolean isFirst = true;
        for(String item : sort.keySet()){
            validateKey(item);
            if (!sortEnum.contains(sort.get(item))){
                throw new BizException(ErrorCode.FORMAT_ERROR.getCode(),"包含未知匹配符："+sort.get(item));
            }
            if (!isFirst){
                valStr.append(",");
            }
            isFirst = false;
            valStr.append(item).append(" ").append(sort.get(item)).append(" ");
        }
        return valStr.toString();
    }

    private String buildWhere(String connector,Map<String, Object> filter) {
        connector = connector.replace("$","");
        StringBuilder valStr = new StringBuilder();
        boolean isFirst = true;

        for(String key:filter.keySet()){
            validateKey(key);
            if (!isFirst){
                valStr.append(" ").append(connector).append(" ");
            }
            isFirst = false;

            if (scope.contains(key)){
                valStr.append("(").append(buildWhere(key, (Map<String, Object>) filter.get(key))).append(")");
            }else if(filter.get(key) instanceof Map){
                valStr.append(buildVariableValue(key,(Map<String, Object>) filter.get(key)));
            }else {
                valStr.append(key).append(buildValue(filter.get(key)));
            }


        }

        return valStr.toString();
    }

    private String buildVariableValue(String key, Map<String, Object> filter) {
        StringBuilder valStr = new StringBuilder();
        boolean isFirst = true;
        for(String item :filter.keySet()){
            if (!variables.contains(item)){
                throw new BizException(ErrorCode.FORMAT_ERROR.getCode(),"包含未知匹配符："+item);
            }
            String connector = "";
            switch (item){
                case "$gte":connector=" >= ";break;
                case "$gt":connector=" > ";break;
                case "$lte":connector=" <= ";break;
                case "$lt":connector=" < ";break;
                case "$ne":connector=" != ";break;
                case "$like":connector=" like ";break;
            }
            if (!isFirst){
                valStr.append(" and ");
            }
            isFirst = false;
            valStr.append(key).append(connector).append(buildStrValue(filter.get(item)));
        }
        return valStr.toString();
    }

    private String buildValue(Object val) {
        StringBuilder valStr = new StringBuilder();
        if (val instanceof Collection){
            valStr.append(((Collection)val).stream().map(item->buildStrValue(item)).collect(Collectors.joining(","," in (",")")));
        }else {
            valStr.append(" = ").append(buildStrValue(val));
        }
        return valStr.toString();
    }

    private String buildStrValue(Object val){
        if (val instanceof Number){
            return val.toString();
        }
        validateValue(val.toString());
        return "'"+val.toString()+"'";
    }
}
【0076】 mysql api编程（四) 查询
Postgres 数据库内核开发工程师
06-30 514
1. 此时我的数据库date下面有一张sub数据表；该表的详细内容如下：2. 现在通过api编程来实现在终端打印出该sub表中的详细内容；/************************************************************************* * File Name: search.c * Author: The answer * Functi
【微服务】mysql + elasticsearch数据双写设计与实现
congge
11-04 1万+
mysql + elasticsearch数据双写设计与实现
单表查询API
weixin_34268610的博客
03-28 134
Django ORM单表查询 单表查询API汇总 必会13条 <1> all(): 查询所有结果 <2> filter(**kwargs): 它包含了与所给筛选条件相匹配的对象 <3> get(**kwargs): 返回与所给筛选条件相匹配的对象，返回结果有...
tk.Mybatis通过泛型扩展通用查询单表API接口
Xiong_j_L的博客
07-29 795
需求原因： 现在有单表查询需求，但是表数量众多，查询字段不确定，还需要使用时间区间进行查询（使用的时间字段可能会更新，也可能会增加其他查询条件）。 思路： 构造一个基类保存查询参数和查询泛型。使用公共方法，查询任意字段的值和时间字段的区间，可在子类扩展任意查询方法 查询基类 QueryBase package cn.?.model; import lombok.Data; import java.util.List; /** * @author XiongJl * @da..
通用的查询方法 API
Vincents_Vincents的博客
11-22 1135
package jdbc.cn; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.HashMap; import java.util.Map;
通用数据库访问接口IDBHelper
yuluo
01-16 4795
在三层构架中，关于数据库访问层的设计一般有两种设计方法         一种是：不同数据库都对应着一个DAL层，并且这些DAL中的类实现于IDAL中的接口。并且每个DAL对应着一个DBHelper数据库操作辅助类。         第二种是：只有一个DAL层，每个数据库都对应着一个DBHelper，这些DBHelper都实现IDBHelper接口，根据数据库的不同，采用抽象工厂选择相应的DBH
java一个api实现前端传表名和列名的任意表通用查询
05-22
### Java API 实现前端传入表名和列名的通用查询 #### 1. 引言 为了实现一个支持前端动态传递表名和列名的通用查询API，可以通过反射机制或者JDBC动态SQL的方式完成。这种方式能够满足不同表结构下的查询需求，同时...
MySQL 5.7 和 8.0 基于kubernetes的yaml部署方案-单实例和高可用
最新发布
随笔记录-分享&记忆
10-17 1043
MySQL 5.7在Kubernetes上的单实例部署方案，采用StatefulSet保证数据持久性，包含生产级优化配置。通过Secret管理root密码，ConfigMap配置关键参数（字符集、日志、缓存等），Service暴露3306和9104监控端口。部署包含资源限制、亲和性设置、健康检查，以及PodDisruptionBudget确保可用性。方案还集成了Prometheus监控和每日备份CronJob（保留7天），使用SSD存储类，支持后续横向扩展为主从或Group Replication架构。
java一个api实现前端传表名和列名的任意表分页查询
05-22
为了实现一个支持前端传递表名和列名进行任意表分页查询的 Java API，可以采用动态 SQL 的方式来构建查询语句。MyBatis 是一种优秀的持久层框架，它可以通过 `@SelectProvider` 或 XML 配置文件的方式灵活地处理动态...
C#通用查询器
05-27
然后，我们可以通过通用查询器的API将这个表达式树传递进去，它会自动将其转换为如下的SQL语句： ```sql SELECT * FROM Users WHERE Age > 20 ``` 除了基本的比较操作，通用查询器还可能支持更复杂的逻辑，如逻辑...
c 操作mysql通用类_c 下的通用数据库接口
weixin_42168745的博客
02-23 156
在java/C#中都有类的反射，而C下是不存在的。java/C#中可以把表设计成类，而C下只能设计成结构体形式。在java中有hibernate来操作数据库，但是在C下面怎么设计好呢？现在，我想出了一个点子，以下使用sqlite先创建一个结构体，表示一张数据库表的结构。typedef struct User {int id;char *name;char *password;} User;创建表的...
mybatis注解开发（单表查询）
Denial_learn的博客
11-01 486
注解说明： MyBatis可以利用SQL映射文件来配置，也可以利用Annotation来设置。MyBatis提供的一些基本注解如下表所示： 注解 目标 相应的XML 描述 @CacheNamespace 类 <cache> 为给定的命名空间（比如类）配置缓存。属性：implemetation,eviction,flushInterval , size 和 readWri...
数据生命周期管理的初步设计
杨建荣的学习笔记
05-30 716
这是学习笔记的第1995篇文章 之前做了一个初版的生命周期设计，导致对于实现的难度低估，在实际设计的时候，碰到了一些意料之外的边界问题。在和前端业务部门做了几次沟...
MyBatis面向接口编程以及单表查询
qq_41934990的博客
06-24 539
mybatis实现Dao层开发 传统开发模式（以上篇博客为例） 编写UserMapper接口 public interface UserMapper { // 查询所有 public List<User> findAll(); } 编写UserMapper实现类 public class UserMapperImpl implements UserMapper { @Override public List<User> findAll() {
mysql有万能表吗_MySQL查询与数据库设计 #万能公式 #思维导图 #MySQL查询 #数据库设计规范 #第二部分...
weixin_39531604的博客
01-28 559
MySQL查询零、MySQL查询万能公式一、查询1)查询表中记录2)给表取名3)去重(distinct)二、条件查询1)比较运算符2)逻辑运算符3)模糊查询like(用通配符)rlike(用正则)4)范围查询三、排序四、聚合函数五、分组六、分页1)直接限制查询出来的个数。末尾加limit 个数2)limit 开始位置的索引，个数七、连接查询1)内连接inner join2)外连接左连接left j...
前后端分离之接口参数自定义通用查询
DDLoveWorld的博客
05-18 2729
所有业务都可通过一种查询规则进行查询。这是一款基于Mybatis-plus插件查询功能的二次开发，让你的查询变得更加简单，简洁，方便和灵活。从此告别和前端人员的频繁沟通！！
java 通用查询框架Querydsl 简介
a304096740的博客
05-04 249
Querydsl是一个通用的查询框架，专注于通过JavaAPI构建类型安全的SQL查询说说Querydsl的优势吧： 1. Querydsl支持代码自动完成，因为才纯Java API编写查询，因此主流Java IDE对起的代码自动完成功能支持几乎可以发挥到极致（因为是纯Java代码，所以支持很好） 2. Querydsl几乎可以避免所有的SQL语法错误（当然用错了...
史上最牛mysql-06 (多表连接)
小北呱的博客
12-06 4052
多表连接 个人博客 ：www.xiaobeigua.icu 多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。 1. 一个案例引发的多表连接 1.1 案例说明 从多个表中获取数据： #案例：查询员工的姓名及其部门名称 SELECT last_n
【MySQL】MySQL表设计的经验（建议收藏）
热门推荐
Reische的博客
12-27 1万+
作为后端开发，我们经常需要设计数据库表。整理了21个设计MySQL表的经验准则，分享给大家，希望大家看完会有帮助。如果没有特殊的理由， 一般都建议将字段定义为 NOT NULL。为什么呢？首先，NOT NULL可以防止出现空指针问题。其次，NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL。NULL值有可能会导致索引失效如果将字段默认设置成一个空字符串或常量值并没有什么不同，且都不会影响到应用逻辑， 那就可以将这个字段设置为NOT NULL。
评论 2
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值