通过velocity自定义模板字符串实现可配置的外部调用查询接口

最新推荐文章于 2024-07-10 09:40:20 发布

howroad

最新推荐文章于 2024-07-10 09:40:20 发布

阅读量631

点赞数

分类专栏： Java学习笔记文章标签： java 程序设计

本文链接：https://blog.csdn.net/howroad/article/details/119924960

版权

Java学习笔记专栏收录该内容

63 篇文章 0 订阅

订阅专栏

背景

公司的系统被其他很多行内系统调用，查询某一个实体信息但是由于安全管控，每个系统调用我们系统获得的字段是不一样的。目前有两种方案可以实施：
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 ; -- 查询调用日志