通用SQL查询设计-MySql

最新推荐文章于 2024-07-22 16:16:39 发布

金元520

最新推荐文章于 2024-07-22 16:16:39 发布

阅读量1.1k

点赞数 3

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/lijy520/article/details/88804708

一般前后台分步开发，前端任何一个数据的需求，都要求后台提供对应接口去支持。但往往在实际开发中，可能仅是一个很小的查询需求，如字典类等，也必须后台对应一个接口，虽然可能会有大神讲那也可以封装一个特定查询通用接口，但要是非字典类呢，比如前端的一个报表，或者简单的一个单据查询的打印。这怎么办，再封装？

鉴于此，个人实际经验记录，特设计此通用查询。以备于自己后期项目备查等。

接口功能:提供其它端调用执行SQL方法。

如：C->调用接口入参：{sql_id:xxxx ,params:{k1:v1,k2:v2},page_num:1,page_size:500}

sql_id:需要执行（查询）的sqlid，params:传入的参数.

接口出参:{code:0,message:"成功",results:[{object},{object},{object}],page_count:15,record_count:7423}

code:执行的结果，message:描述 ,results:结果列表,page_count:总共多少页,record_count:总记录条数.

可设计一个通用SQL配置表，各端需要时可自行配置SQL，然后调用此通用接口查询即可。、

配置表结构:

主要包含: sql_id:配置的sql编号唯一 ex:sql-0001

sql_name:sql标识名称（或功能说明，便于维护） ex:查询门店字典

sql_content:sql实际内容 ex:select sno,sname from merchant where [sno like ?]

sql实际内容配置说明:?号为替换项（即入参）如果此?对应前后第一对 [] 表示此段参数内容为非必传 param_content:入参配置每一个参数对应sql_content里的一个?号

入参配置说明:主要为json列表形式 ex: [{"NAME": "sno", "TYPE": "STRING", "query_like": "around", "MUSTHAVE": "false"}] 内部包含每条记录为一个参数的说明

内部key说明:

必有 name:对应入参名多条可用同一name表示多个这样的参数

必有 type:入参类型用于sql拼接替换时是否加前后的 ' 配置为:string number

非必有 query_like:参数为like条件配置为 left around right

此时对应需要sql_content对应应该为 key like ? 后台拼接逻辑为:left key like '%x', right:'x%', around:'%x%'

非必有 query_in:参数为in条件配置为in "query_in":"in"

此时对应需要 sql_content对应应该为 key in ? 后台拼接逻辑为:key in (x,xx,xxx)

jdbc_name:jdbc连接名字连接的库名

db_type:数据库类型影响拼接sql写法配置为 MySQL Oracle Postgre...

sub_sql_id:子查询的sql_id主要用于跨库查询

其实现方式为: 主查询查出的所有字段作为入参传入子查询，每一个主查询的结果仅能对应一个子查询结果，

子查询出的结果，跟主查询列拼成记录返回。

配置表结构:

CREATE TABLE `common_query` (
`series` bigint(20) NOT NULL COMMENT '行号',
`sql_name` varchar(1000) NOT NULL COMMENT 'sql执行描述',
`sql_id` varchar(200) NOT NULL COMMENT 'sql编号',
`sql_content` text NOT NULL COMMENT 'sql内容',
`param_content` text NOT NULL COMMENT 'sql参数',
`jdbc_name` varchar(100) NOT NULL COMMENT 'jdbc连接名字',
`create_dtme` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`last_updtme` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间',
`create_user_id` bigint(20) DEFAULT '0' COMMENT '用户',
`last_update_user_id` bigint(20) DEFAULT '0' COMMENT '更新用户',
`cancel_sign` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0为不可使用,1为可使用',
`db_type` varchar(100) DEFAULT 'MySQL' COMMENT '数据库类型 MySQL Oracle Postgre...',
`annotate_prefix` varchar(50) DEFAULT NULL,
`sub_sql_id` varchar(256) DEFAULT '' COMMENT '子查询SQLID',
`no_data_exception` tinyint(4) DEFAULT '0' COMMENT '无数据是否报错，0不报错,1是报错',
`cache_sign` int(3) DEFAULT '0' COMMENT '缓存使用标识0:不使用1：直接缓存，2缓存服务',
`method_name` varchar(100) DEFAULT '' COMMENT '方法名称',
`cache_live_time` int(11) DEFAULT '0' COMMENT '缓存存活时间',
`return_handle_content` text COMMENT '返回参数处理',
`_dble_op_time` bigint(20) DEFAULT NULL COMMENT 'field for checking consistency',
PRIMARY KEY (`series`),
UNIQUE KEY `idx_common_query_01` (`sql_id`,`data_sign`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;