背景 公司的系统被其他很多行内系统调用,查询某一个实体信息但是由于安全管控,每个系统调用我们系统获得的字段是不一样的。目前有两种方案可以实施: 1.利用行内的数仓系统,将表备份,然后通过数据交换平台sftp作业的方式,由我方通过自动任务上传,第三方拉取。 这样对接系统调用链变长,数据是T+1到达没有时效性,每对接一个系统就要写一个针对该系统所需字段的逻辑,不易扩展。 好处是直接用文件传输,行内数仓和调度系统实现,数据也有备份,不用扯皮,自动任务定时上传,读取流量压力在数仓和sftp平台。 实际这种方法可以绕过数仓,只进行sftp的上传和通信。 2.每对接一个系统就开发一个接口。 好处是方便调试管理,运营成本低。 缺点是开发量大,易出错,需要大量的联调。 我的实现方案,因为使用的接口都是查询接口,可以利用低代码的思路,直接配置sql,到数据库。(不能配到页面,因为有敏感单词易被注入,安全规则不允许),鉴权使用行内的某系统(滤镜),通过velocity自定义模板字符串实现可配置的外部调用查询接口,就是使用下面这种模板字符串,模拟ibatis,实现简单的查询
select
project_id projectId,
project_name projectName
from fin_project_info where 1=1
#if(${projectName})
and project_name like '%${projectName}%'
#end
pom.xml
<dependency>
<artifactId>velocity</artifactId>
<groupId>org.apache.velocity</groupId>
<version>1.7</version>
</dependency>
test.http
###
POST {{interaction}}/common-authorization/general/query
Content-Type: application/json
Authorization: AC:ucm-c4hTdL0CgjRvF...
{
"systemId": "LZ44.70",
"businessType": "test",
"pageIndex": 1,
"pageSize": 200,
"param": {
"projectId": "xtqaaU1b"
}
}
{
"status": 200,
"message": "success",
"srvTime": 1631179524565,
"data": [
{
"costs": [
{
"fees": [
{
"name": "差旅费用",
"costId": "85VnONQC",
"id": "BfOGGvmh"
},
{
"name": "办公费用",
"costId": "85VnONQC",
"id": "eruVreP3"
},
{
"name": "人工费用",
"costId": "85VnONQC",
"id": "UaTQ0BYI"
}
],
"costId": "85VnONQC",
"cost_name": "GJX动支0220-11",
"projectId": "xtqaaU1b",
"cost_no": "DZ587"
},
{
"fees": [
{
"name": "差旅费用",
"costId": "yQ4qqNUi",
"id": "072kLcRC"
},
{
"name": "办公费用",
"costId": "yQ4qqNUi",
"id": "8jWqcs7a"
},
{
"name": "人工费用",
"costId": "yQ4qqNUi",
"id": "t8bGpN7Y"
},
{
"name": "咨询费用",
"costId": "yQ4qqNUi",
"id": "UW6hz8yG"
},
{
"name": "营销费用",
"costId": "yQ4qqNUi",
"id": "zmvh2PfD"
}
],
"costId": "yQ4qqNUi",
"cost_name": "GJX测试动支0220-222",
"projectId": "xtqaaU1b",
"cost_no": "DZ588"
}
],
"project_no": "FTRT02201111",
"project_name": "GJX全流程测试0220-11",
"projectId": "xtqaaU1b"
}
],
"pageIndex": 1,
"pageSize": 200,
"total": 1,
"count": 1
}
db.sql
CREATE TABLE `fin_general_query` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`system_id` varchar(64) NOT NULL COMMENT '系统编号',
`business_type` varchar(64) NOT NULL COMMENT '业务类型',
`business_name` varchar(128) NOT NULL COMMENT '接口名字',
`business_sql` text NOT NULL COMMENT '模板SQL',
`is_use_page` smallint(1) NOT NULL DEFAULT '1' COMMENT '是否使用分页',
`business_desc` varchar(128) DEFAULT NULL COMMENT '描述',
`crt_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`parent_id` int(11) DEFAULT NULL COMMENT '父ID',
`relation_key` varchar(128) DEFAULT NULL COMMENT '关联Key',
`result_key` varchar(64) DEFAULT NULL COMMENT '返回值Key',
PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='自定义查询配置'
;
CREATE TABLE `fin_customer_query_log` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`system_id` varchar(32) DEFAULT NULL COMMENT '系统编号',
`customer_no` varchar(64) NOT NULL COMMENT '接口编号',
`customer_sql` text NOT NULL COMMENT '执行SQL',
`data_total` int(11) DEFAULT NULL COMMENT '查询总数',
`customer_param` text COMMENT '查询参数',
`start_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
`is_success` smallint(1) DEFAULT NULL COMMENT '是否成功',
`message` varchar(1024) DEFAULT NULL COMMENT '错误信息',
`cost_second` int(12) DEFAULT NULL COMMENT '花费时间',
PRIMARY KEY (`id`)
) COMMENT='自定义查询日志';
INSERT INTO ft_fintech.fin_general_query (system_id,business_type,business_name,business_sql,is_use_page,business_desc,crt_time,parent_id,relation_key,result_key) VALUES
('DEFAULT','test','test','select
fpi.project_id projectId,
fpi.project_no,
fpi.project_name
from fin_project_info fpi
where 1 = 1
#if(${projectId})and fpi.project_id = ''${projectId}'' #end',1,'test','2021-09-03 16:49:25',NULL,NULL,NULL),
('DAFAULT','test','test','select
fpect.id costId,
fpect.cost_no ,
fpect.cost_name,
fpect.project_id projectId
from fin_project_expected_cost_total fpect
where 1=1
and fpect.project_id in (${keys})',0,'test','2021-09-03 16:56:49',4,'projectId','costs'),
('DEFAULT','test','test','select fpec.id,fpec.name,fpec.cost_total_id costId from ft_fintech.fin_project_expected_cost fpec
where fpec.cost_total_id in (${keys})',0,'test','2021-09-03 17:55:29',5,'costId','fees')
;
controller.java
package com.cmb.fintech.query.controller;
import com.cmb.fintech.projectInfo.msg.BaseResponse;
import com.cmb.fintech.query.service.FinGeneralQueryService;
import com.cmb.fintech.query.vo.FinGeneralQueryScope;
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 路昊/IT006728
* @since 2021-08-24 15:10:40
*/
@RestController
@RequestMapping("/common-authorization/general")
public class FinGeneralQueryController {
@Resource
private FinGeneralQueryService finGeneralQueryService;
@PostMapping("/query")
public BaseResponse query(@RequestBody FinGeneralQueryScope finGeneralQueryScope) {
return finGeneralQueryService.query(finGeneralQueryScope);
}
}
service.java
package com.cmb.fintech.query.service;
import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.fastjson.JSON;
import com.cmb.fintech.projectInfo.msg.BaseResponse;
import com.cmb.fintech.projectInfo.msg.ObjectRestResponse;
import com.cmb.fintech.projectInfo.msg.PageResponse;
import com.cmb.fintech.projectInfo.msg.RestCodeConstants;
import com.cmb.fintech.query.mapper.FinGeneralQueryMapper;
import com.cmb.fintech.query.vo.FinGeneralQuery;
import com.cmb.fintech.query.vo.FinGeneralQueryLog;
import com.cmb.fintech.query.vo.FinGeneralQueryScope;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.apache.commons.lang.BooleanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.io.StringWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import static cn.hutool.core.util.StrUtil.COMMA;
/**
* @author 路昊/IT006728
* @since 2021-08-24 15:10:39
*/
@Service
public class FinGeneralQueryService {
@Resource
private FinGeneralQueryLogService finGeneralQueryLogService;
@Resource
private FinGeneralQueryMapper finGeneralQueryMapper;
private static final String LOG_TAG = "GENERAL_QUERY";
private static final String DEFAULT = "DEFAULT";
public BaseResponse query(FinGeneralQueryScope finGeneralQueryScope) {
Integer logId = startLog(finGeneralQueryScope.getSystemId(), finGeneralQueryScope.getBusinessType(), finGeneralQueryScope.getParam());
String sql = null;
try {
FinGeneralQuery finGeneralQuery = getFinGeneralQueryByBusinessTypeAndSystemId(finGeneralQueryScope.getBusinessType(), finGeneralQueryScope.getSystemId());
sql = evaluate(finGeneralQuery.getBusinessSql(), finGeneralQueryScope.getParam());
Page<?> page = NumberUtils.INTEGER_ONE.equals(finGeneralQuery.getIsUsePage()) ? PageHelper.startPage(finGeneralQueryScope.getPageIndex(), finGeneralQueryScope.getPageSize()) : null;
List<Map<String, Object>> list = finGeneralQueryMapper.query(sql);
buildSubList(finGeneralQuery, list);
endLog(logId, true, list.size(), "成功", sql);
return new PageResponse<Map<String, Object>>(page).data(list).count((long) list.size()).status(RestCodeConstants.SUCCESS).message("success");
} catch (Exception e) {
endLog(logId, false, 0, e.getMessage(), sql);
e.printStackTrace();
return new ObjectRestResponse<>().status(RestCodeConstants.SERVER_BUSY).message("接口异常,请联系金融科技开发团队");
}
}
/**
* 组装子集合
*
* @param finGeneralQuery 通用查询配置
* @param list 需要组装的list
*/
private void buildSubList(FinGeneralQuery finGeneralQuery, List<Map<String, Object>> list) {
if (CollectionUtil.isNotEmpty(list)) {
List<FinGeneralQuery> subFinGeneralQueryList = finGeneralQueryMapper.selectSubFinGeneralQueryByParentId(finGeneralQuery.getId());
for (FinGeneralQuery subFinGeneralQuery : subFinGeneralQueryList) {
String relationKey = subFinGeneralQuery.getRelationKey();
String resultKey = subFinGeneralQuery.getResultKey();
// 子查询主键集合
List<Object> keyList = list.stream().map(m -> m.get(relationKey)).distinct().collect(Collectors.toList());
List<Map<String, Object>> subListByKeyList = selectListByKeyList(subFinGeneralQuery, keyList);
buildSubList(subFinGeneralQuery, subListByKeyList);
Map<Object, List<Map<String, Object>>> subListMap = subListByKeyList.stream().collect(Collectors.groupingBy(map -> map.get(relationKey)));
// 组装进list
for (Map<String, Object> map : list) {
map.put(resultKey, subListMap.get(map.get(relationKey)));
}
}
}
}
/**
* 查询子集合
*
* @param finGeneralQuery 通用查询配置
* @param keyList 子集合对应的Key
* @return 满足条件的在list中的子集合
*/
private List<Map<String, Object>> selectListByKeyList(FinGeneralQuery finGeneralQuery, List<Object> keyList) {
String template = finGeneralQuery.getBusinessSql();
String keys = keyList.stream().map(obj -> String.format("'%s'", obj)).collect(Collectors.joining(COMMA));
Map<String, Object> param = new HashMap<>(2);
param.put("keys", keys);
return finGeneralQueryMapper.query(evaluate(template, param));
}
private FinGeneralQuery getFinGeneralQueryByBusinessTypeAndSystemId(String businessType, String systemId) {
List<FinGeneralQuery> finGeneralQueries = finGeneralQueryMapper.selectFinGeneralQueryByBusinessType(businessType);
// 首先查找配置表businessType和systemId同时匹配的项目
// 其次查找配置表businessType匹配且systemId为默认值的数据
// 如果都没找到说明无此配置
return finGeneralQueries.stream()
.filter(f -> StringUtils.equals(systemId, f.getSystemId()))
.findAny()
.orElseGet(() -> finGeneralQueries.stream()
.filter(f -> StringUtils.equals(DEFAULT, f.getSystemId()))
.findAny()
.orElseThrow(() -> new RuntimeException(String.format("未找到相关配置,businessType:%s,systemId:%s", businessType, systemId))));
}
private String evaluate(String template, Map<String, Object> param) {
StringWriter stringWriter = new StringWriter();
Velocity.evaluate(new VelocityContext(param), stringWriter, LOG_TAG, template);
return stringWriter.toString();
}
private Integer startLog(String systemId, String businessType, Map<String, Object> param) {
FinGeneralQueryLog finGeneralQueryLog = new FinGeneralQueryLog();
finGeneralQueryLog.setSystemId(systemId);
finGeneralQueryLog.setBusinessType(businessType);
finGeneralQueryLog.setBusinessParam(JSON.toJSONString(param));
finGeneralQueryLogService.insertFinGeneralQueryLog(finGeneralQueryLog);
return finGeneralQueryLog.getId();
}
private void endLog(Integer id, boolean isSuccess, int total, String message, String businessSql) {
FinGeneralQueryLog finGeneralQueryLog = new FinGeneralQueryLog();
finGeneralQueryLog.setId(id);
finGeneralQueryLog.setBusinessSql(businessSql);
finGeneralQueryLog.setIsSuccess(BooleanUtils.toInteger(isSuccess));
finGeneralQueryLog.setMessage(StringUtils.left(message, 800));
finGeneralQueryLog.setDataTotal(total);
finGeneralQueryLogService.updateFinGeneralQueryLog(finGeneralQueryLog);
}
}
package com.cmb.fintech.customer.service;
import com.cmb.fintech.customer.mapper.FinCustomerQueryLogMapper;
import com.cmb.fintech.customer.vo.FinCustomerQueryLog;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
/**
* 自定义查询日志(FinCustomerQueryLog)表服务实现类
*
* @author 路昊/IT006728
* @since 2021-08-25 11:14:46
*/
@Service
public class FinCustomerQueryLogService {
@Resource
private FinCustomerQueryLogMapper finCustomerQueryLogMapper;
public void insertFinCustomerQueryLog(FinCustomerQueryLog finCustomerQueryLog) {
finCustomerQueryLogMapper.insertFinCustomerQueryLog(finCustomerQueryLog);
}
public void updateFinCustomerQueryLog(FinCustomerQueryLog finCustomerQueryLog) {
finCustomerQueryLogMapper.updateFinCustomerQueryLog(finCustomerQueryLog);
}
}
mapper.java
package com.cmb.fintech.query.mapper;
import com.cmb.fintech.query.vo.FinGeneralQuery;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* @author 路昊/IT006728
* @since 2021-08-24 15:10:40
*/
@Repository
public interface FinGeneralQueryMapper {
@Select("${sql}")
List<Map<String, Object>> query(@Param("sql") String sql);
List<FinGeneralQuery> selectFinGeneralQueryByBusinessType(String businessType);
List<FinGeneralQuery> selectSubFinGeneralQueryByParentId(Integer parentId);
}
package com.cmb.fintech.customer.mapper;
import com.cmb.fintech.customer.vo.FinCustomerQueryLog;
import org.springframework.stereotype.Repository;
/**
* @author 路昊/IT006728
* @since 2021-08-24 15:10:40
*/
@Repository
public interface FinCustomerQueryLogMapper {
void insertFinCustomerQueryLog(FinCustomerQueryLog finCustomerQueryLog);
void updateFinCustomerQueryLog(FinCustomerQueryLog finCustomerQueryLog);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cmb.fintech.customer.mapper.FinCustomerQueryLogMapper">
<!--新增所有列-->
<insert id="insertFinCustomerQueryLog" keyProperty="id" useGeneratedKeys="true">
insert into ft_fintech.fin_customer_query_log(system_id, customer_no, customer_sql, customer_param, start_time)
values (#{systemId}, #{customerNo}, #{customerSql}, #{customerParam}, now())
</insert>
<!--通过主键修改数据-->
<update id="updateFinCustomerQueryLog">
update ft_fintech.fin_customer_query_log
set data_total = #{dataTotal},
end_time = now(),
message = #{message},
is_success = #{isSuccess},
cost_second = timestampdiff(second, start_time, now())
where id = #{id}
</update>
</mapper>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cmb.fintech.query.mapper.FinGeneralQueryMapper">
<sql id="base_columns">
id,
business_name businessName,
business_type businessType,
business_sql businessSql,
is_use_page isUsePage,
system_id systemId,
parent_id parentId,
relation_key relationKey,
result_key resultKey
</sql>
<select id="selectFinGeneralQueryByBusinessType" resultType="com.cmb.fintech.query.vo.FinGeneralQuery">
select
<include refid="base_columns"/>
from ft_fintech.fin_general_query
where business_type = #{businessType}
</select>
<select id="selectSubFinGeneralQueryByParentId" resultType="com.cmb.fintech.query.vo.FinGeneralQuery">
select
<include refid="base_columns"/>
from ft_fintech.fin_general_query
where parent_id = #{parentId}
</select>
</mapper>
vo.java
package com.cmb.fintech.query.vo;
import lombok.Data;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;
/**
* 自定义查询配置(FinGeneralQuery)实体类
*
* @author 路昊/IT006728
* @since 2021-08-25 17:52:43
*/
@Data
@Table(name = "fin_general_query")
public class FinGeneralQuery {
/**
* ID
*/
@Id
private Integer id;
/**
* 系统编号
*/
@Column(name = "system_id")
private String systemId;
/**
* 接口编号
*/
@Column(name = "business_type")
private String businessType;
/**
* 接口名字
*/
@Column(name = "business_name")
private String businessName;
/**
* 模板SQL
*/
@Column(name = "business_sql")
private String businessSql;
/**
* 是否使用分页
*/
@Column(name = "is_use_page")
private Integer isUsePage;
/**
* 描述
*/
@Column(name = "business_desc")
private String businessDesc;
/**
* 创建时间
*/
@Column(name = "crt_time")
private Date crtTime;
/**
* 父ID
*/
@Column(name = "parent_id")
private Integer parentId;
/**
* 关联Key
*/
@Column(name = "relation_key")
private String relationKey;
/**
* 返回值Key
*/
@Column(name = "result_key")
private String resultKey;
}
package com.cmb.fintech.customer.vo;
import lombok.Data;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;
/**
* 自定义查询日志(FinCustomerQueryLog)实体类
*
* @author 路昊/IT006728
* @since 2021-08-25 17:52:58
*/
@Data
@Table(name = "fin_customer_query_log")
public class FinCustomerQueryLog {
/**
* ID
*/
@Id
private Integer id;
/**
* 系统编号
*/
@Column(name = "system_id")
private String systemId;
/**
* 接口编号
*/
@Column(name = "customer_no")
private String customerNo;
/**
* 执行SQL
*/
@Column(name = "customer_sql")
private String customerSql;
/**
* 查询总数
*/
@Column(name = "data_total")
private Integer dataTotal;
/**
* 查询参数
*/
@Column(name = "customer_param")
private String customerParam;
/**
* 开始时间
*/
@Column(name = "start_time")
private Date startTime;
/**
* 结束时间
*/
@Column(name = "end_time")
private Date endTime;
/**
* 是否成功
*/
@Column(name = "is_success")
private Integer isSuccess;
/**
* 错误信息
*/
@Column(name = "message")
private String message;
/**
* 花费时间
*/
@Column(name = "cost_second")
private Integer costSecond;
}
package com.cmb.fintech.customer.vo;
import lombok.Data;
import javax.validation.constraints.NotNull;
import java.util.Map;
/**
* 自定义查询条件
*
* @author 路昊/IT006728
* @date 2021/8/24 15:33
*/
@Data
public class FinCustomerQueryScope {
@NotNull(message = "系统编号不能为空")
private String systemId;
@NotNull(message = "查询编号不能为空")
private String customerNo;
private Integer pageIndex = 1;
private Integer pageSize = 10;
private Map<String, Object> param;
}
select * from fin_customer_query_log fcql ; -- 查询调用日志
后面增加接口只需要向fin_customer_query表添加数据即可
欢迎讨论 作者howroad wx:howroad