# 数据地图项目

# 简介

一个通用的查询统计表的，可动态配置修改统计语句，灵活的参数查询，结合 starrocks、flink 可以做到一个大数据实时统计表查询中

# 框架

框架	说明	版本
springboot	应用开发框架	2.3.12.RELEASE
spring-boot-starter-data-jpa	操作数据的框架
spring-boot-starter-web	MVC 框架
spring-boot-starter-security	Spring 安全框架
spring-security-cas	cas 验证框架
knife4j-spring-boot-starter	api 文档框架
mybatis	数据库框架

# 系统功能

# 动态处理查询参数，实现通用统计表查询

# 功能点：

配置查询统计语句，条件和相关统计指标由前端传递，因为用的是 mybatis plus 的自定义 Wrapper 条件构造器，是预编译的，不会出现 sql 注入的问题

统一接口，不同类型的查询类型和条件动态传递
灵活只有配置统计表
如果有数据中台，那么就可以多平台使用通一个查询接口

# 思路：

核心点就是怎么实现构造通用的查询条件的参数，可以在数据库中的 sql 语句外层在套一层查询，这样就可以在最外层拼接条件。
select * from (数据库中配置的查询统计表语句) as data ${ew.customSqlSegment} 这里的$ {ew.customSqlSegment} 就是 Wrapper 构造的条件语句
对条件的处理，也就是 eq、like、in、or 这些参数的处理

# 具体实现步骤：

application.yml

	server:
	port: 8085

	spring:

	devtools:
	restart:
	enabled: true #设置开启热部署
	additional-paths: src/main/java #重启目录
	exclude: WEB-INF/**
	freemarker:
	cache: false #页面不加载缓存，修改即时生效
	thymeleaf:
	cache: false

	datasource:
	driver-class-name: com.mysql.jdbc.Driver
	# 填写你数据库的 url、登录名、密码和数据库名
	url: jdbc:mysql://127.0.0.1:3306/map_data?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true
	username: root
	password: 123456


	swagger:
	enable: false


	pagehelper:
	helperDialect: mysql
	reasonable: true
	supportMethodsArguments: true
	params: count=countSql

	logging:
	level:
	com.bright.map.mapper: debug

配置表：

	CREATE TABLE `sys_config` (
	`id` bigint(20) NOT NULL COMMENT "主键",
	`base_data_type` varchar(65533) NULL COMMENT "统计数据类型",
	`base_data_name` varchar(65533) NULL COMMENT "统计数据名称",
	`base_data_text` varchar(65533) NULL COMMENT "统计数据说明",
	`base_data_sql` varchar(65533) NULL COMMENT "统计数据查询sql",
	`visible` boolean NULL COMMENT "是否启用",
	`revision` int(11) NULL COMMENT "",
	`created_by` varchar(65533) NULL COMMENT "",
	`created_time` date NULL COMMENT "",
	`updated_by` varchar(65533) NULL COMMENT "",
	`updated_time` date NULL COMMENT ""
	)

对应实体类：

	import com.baomidou.mybatisplus.annotation.IdType;
	import com.baomidou.mybatisplus.annotation.TableField;
	import com.baomidou.mybatisplus.annotation.TableId;
	import com.baomidou.mybatisplus.annotation.TableName;
	import io.swagger.annotations.ApiModel;
	import io.swagger.annotations.ApiModelProperty;
	import lombok.AllArgsConstructor;
	import lombok.Data;
	import lombok.NoArgsConstructor;

	import java.io.Serializable;
	import java.util.Date;

	/**
	* @author Tz
	* @date 2022/2/25 10:49
	* @Description
	*/
	/**
	* undefined
	*/
	@ApiModel(value="com-bright-map-domain-SysConfig")
	@Data
	@AllArgsConstructor
	@NoArgsConstructor
	@TableName(value = "sys_config")
	public class SysConfig implements Serializable {
	/**
	* ID
	*/
	@TableId(value = "id", type = IdType.AUTO)
	@ApiModelProperty(value="ID")
	private Long id;

	/**
	* 统计数据类型
	*/
	@TableField(value = "base_data_type")
	@ApiModelProperty(value="统计数据类型")
	private String baseDataType;

	/**
	* 统计数据名称
	*/
	@TableField(value = "base_data_name")
	@ApiModelProperty(value="统计数据名称")
	private String baseDataName;

	/**
	* 统计数据说明
	*/
	@TableField(value = "base_data_text")
	@ApiModelProperty(value="统计数据说明")
	private String baseDataText;

	/**
	* 数据表 sql
	*/
	@TableField(value = "base_data_sql")
	@ApiModelProperty(value="统计数据查询sql")
	private String baseDataSql;

	/**
	* 是否显示
	*/
	@TableField(value = "visible")
	@ApiModelProperty(value="是否显示")
	private Boolean visible;

	/**
	* 乐观锁
	*/
	@TableField(value = "revision")
	@ApiModelProperty(value="乐观锁")
	private Integer revision;

	/**
	* 创建人
	*/
	@TableField(value = "created_by")
	@ApiModelProperty(value="创建人")
	private String createdBy;

	/**
	* 创建时间
	*/
	@TableField(value = "created_time")
	@ApiModelProperty(value="创建时间")
	private Date createdTime;

	/**
	* 更新人
	*/
	@TableField(value = "updated_by")
	@ApiModelProperty(value="更新人")
	private String updatedBy;

	/**
	* 更新时间
	*/
	@TableField(value = "updated_time")
	@ApiModelProperty(value="更新时间")
	private Date updatedTime;

	private static final long serialVersionUID = 1L;

	public static final String COL_ID = "id";

	public static final String COL_BASE_DATA_TYPE = "base_data_type";

	public static final String COL_BASE_DATA_NAME = "base_data_name";

	public static final String COL_BASE_DATA_TEXT = "base_data_text";

	public static final String COL_BASE_DATA_SQL = "base_data_sql";

	public static final String COL_VISIBLE = "visible";

	public static final String COL_REVISION = "revision";

	public static final String COL_CREATED_BY = "created_by";

	public static final String COL_CREATED_TIME = "created_time";

	public static final String COL_UPDATED_BY = "updated_by";

	public static final String COL_UPDATED_TIME = "updated_time";
	}

定义通用的 mapper 查询方法，这里使用自定义的类来提供动态 sql：

	import com.baomidou.mybatisplus.core.conditions.Wrapper;
	import com.baomidou.mybatisplus.core.toolkit.Constants;
	import org.apache.ibatis.annotations.Param;
	import org.apache.ibatis.annotations.SelectProvider;

	import java.util.List;
	import java.util.Map;

	/**
	* @author Tz
	* @date 2022/2/22 14:36
	* @Description
	*/
	public interface CurrencyMapper {

	/**
	* 通过数据库表配置 sql 查询数据
	* @param sysConfig
	* @param wrapper
	* @return
	*/
	@SelectProvider(type = SqlProvider.class, method = "currencySql")
	public List<Map<String, Object>> select(SysConfig sysConfig, @Param(Constants.WRAPPER) Wrapper wrapper);
	}

提供动态 sql 的 SqlProvider

	/**
	* @author Tz
	* @date 2022/2/22 14:33
	* @Description
	*/
	public class SqlProvider {

	public String currencySql(SysConfig sysConfig){
	String sql = "select * from (" + sysConfig.getBaseDataSql() + ") as data ${ew.customSqlSegment}";
	return sql;
	}
	}

基础的查询 mapper 已经可以了，现在新建查询接口，接口需要接收前端传递的条件参数，首先定好接收参数的实体

	import io.swagger.annotations.ApiModel;
	import io.swagger.annotations.ApiModelProperty;
	import lombok.AllArgsConstructor;
	import lombok.Data;
	import lombok.NoArgsConstructor;

	import java.io.Serializable;

	/**
	* @author Tz
	* @date 2022/2/22 15:20
	* @Description
	*/
	@ApiModel(value="通用的Vo")
	@Data
	@AllArgsConstructor
	@NoArgsConstructor
	public class CurrencyVo implements Serializable {

	private static final long serialVersionUID = -2150837333679328148L;

	/**
	* 统计数据名称
	*/
	@ApiModelProperty(value="统计数据名称")
	private String baseDataName;

	/**
	* 是否分页
	*/
	@ApiModelProperty(value="是否分页")
	private Boolean isPagination;

	/**
	* 当前页码
	*/
	@ApiModelProperty(value="当前页码")
	private Integer pageNo;

	/**
	* 显示多少记录数
	*/
	@ApiModelProperty(value="显示多少记录数")
	private Integer pageSize;

	/**
	* 筛选条件字符串
	* [{\"tableColumn\":\"distno\",\"connectType\":\"like\",\"fieldType\":\"String\",\"value\":\"0111\"}]
	* tableColumn: 查询字段名称
	* connectType: 查询条件
	* fieldType: 查询字段类型
	* value: 查询的值
	*/
	@ApiModelProperty(value="筛选条件字符串")
	private String searchJson;

	}

对应 searchJson 特殊处理，写一个工具类专门处理

	import cn.hutool.core.collection.CollUtil;
	import cn.hutool.core.util.StrUtil;
	import cn.hutool.json.JSONUtil;
	import com.alibaba.fastjson.JSON;
	import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

	import java.util.List;

	/**
	* @author Tz
	* @date 2022/1/27 14:59
	* @Description
	*/
	public class SearchUtil {
	public static QueryWrapper parseWhereSql(String searchJson) {
	QueryWrapper queryWrapper = new QueryWrapper();

	if (StrUtil.isNotEmpty(searchJson)) {
	List<ScreenVo> screenList = JSON.parseArray(searchJson, ScreenVo.class);

	if (CollUtil.isNotEmpty(screenList)) {
	for (ScreenVo screenVo : screenList) {

	switch (screenVo.getConnectType()) {
	case "eq":
	queryWrapper.eq(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "ne":
	queryWrapper.ne(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "like":
	queryWrapper.like(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "likeLeft":
	queryWrapper.likeLeft(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "likeRight":
	queryWrapper.likeRight(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "notLike":
	queryWrapper.notLike(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "gt":
	queryWrapper.gt(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "lt":
	queryWrapper.lt(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "ge":
	queryWrapper.ge(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "le":
	queryWrapper.le(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	case "in":
	queryWrapper.in(screenVo.getTableColumn(), JSONUtil.parseArray(screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getFieldType() == "Array" ? JSONUtil.parseArray(screenVo.getValue()) : screenVo.getValue().toString()));
	break;
	default:
	queryWrapper.eq(screenVo.getTableColumn(), screenVo.getFieldType() == "Integer" ? Integer.valueOf(screenVo.getValue().toString()) : screenVo.getFieldType() == "String" ? screenVo.getValue().toString() : screenVo.getValue().toString());
	break;
	}
	}
	}

	}
	return queryWrapper;
	}

	}

创建 service

	/**
	* @author Tz
	* @date 2022/2/22 14:56
	* @Description
	*/
	public interface CurrencyService {

	/**
	* 查询数据
	* @param currencyVo
	* @return
	*/
	Result<?> getData(CurrencyVo currencyVo);

	}

service 具体实现

	package com.bright.map.service.impl;

	import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
	import com.bright.map.domain.SysConfig;
	import com.bright.map.mapper.CurrencyMapper;
	import com.bright.map.mapper.SysConfigMapper;
	import com.bright.map.service.CurrencyService;
	import com.bright.map.utils.Result;
	import com.bright.map.utils.SearchUtil;
	import com.bright.map.vo.CurrencyVo;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.stereotype.Service;

	import java.util.List;
	import java.util.Map;

	/**
	* @author Tz
	* @date 2022/2/22 14:56
	* @Description
	*/
	@Service
	public class CurrencyServiceImpl implements CurrencyService {

	@Autowired
	private CurrencyMapper currencyMapper;

	@Autowired
	private SysConfigMapper sysConfigMapper;

	@Override
	public Result<?> getData(CurrencyVo currencyVo) {
	QueryWrapper<SysConfig> sysConfigQueryWrapper = new QueryWrapper<>();
	sysConfigQueryWrapper.eq(SysConfig.COL_BASE_DATA_NAME, currencyVo.getBaseDataName());
	SysConfig sysConfig = sysConfigMapper.selectOne(sysConfigQueryWrapper);

	QueryWrapper queryWrapper = SearchUtil.parseWhereSql(currencyVo.getSearchJson());
	List<Map<String, Object>> maps = currencyMapper.select(sysConfig, queryWrapper);
	return new Result<>(maps.size(), maps);

	}

	}

添加查询的 controller

	import io.swagger.annotations.Api;
	import io.swagger.annotations.ApiImplicitParam;
	import io.swagger.annotations.ApiOperation;
	import org.springframework.web.bind.annotation.PostMapping;
	import org.springframework.web.bind.annotation.RequestBody;
	import org.springframework.web.bind.annotation.RequestMapping;
	import org.springframework.web.bind.annotation.RestController;

	import javax.annotation.Resource;

	/**
	* @author Tz
	* @date 2022/2/22 15:14
	* @Description
	*/
	@RestController
	@RequestMapping
	@Api(tags = "基础数据接口")
	public class CurrencyController {

	@Resource
	private CurrencyService currencyService;


	@PostMapping("/getData")
	@ApiOperation(value = "获取基础数据")
	@ApiImplicitParam(name="currencyVo", value = "currencyVo", required = false, dataType = "CurrencyVo")
	public Result<?> getData(@RequestBody CurrencyVo currencyVo){
	Result<?> data = currencyService.getData(currencyVo);
	return data;
	}

	}

# 使用：

直接注入使用

	// 请求参数
	{"baseDataName":"jgyt_htqdnxgc","pageNo":1,"pageSize":1000,"searchJson":"[{\"tableColumn\":\"distno\",\"connectType\":\"like\",\"fieldType\":\"String\",\"value\":\"0111\"}]"}

个人项目