Spring boot 搭建一个简易的动态数据源的编写sql平台

本文链接：https://blog.csdn.net/axxaaxxaaxxa/article/details/133862393

1、简介

1.1 功能介绍

公司希望开发一个平台，希望平台能支持如下一些功能：

1、在线编写Sql(支持多层级的增删改查操作，如查询返回格式如：{a : [{ b: null}], c: {} })
2、将Sql运行的结果通过Http方式传递到异构平台的接口/甲方提供的接口上
3、暴露给异构平台/甲方一个接口，根据传递过来的参数执行相应的Sql

1.2 重要功能点

围绕上述平台的功能，梳理出如下几个功能：
1、数据库管理：数据库配置的管理，及试一试（测试数据库配置是否正确）
2、Sql逻辑管理：实现多层级的增删改查，及试一试（测试sql是否可以正常执行）
3、接口管理：暴露一簇的接口(Get/Post)，用于提供给外部调用使用(调用关系：外部系统 --> 平台)
4、凭据用户、凭据角色管理。维护凭据用户可以调用哪几个接口，数据关系为(凭据用户–1:N–凭据角色–1:N–接口管理接口)
5、调用管理：维护平台的接口/甲方的接口及Sql逻辑的管理，也包括调用接口的凭据管理(调用关系：平台 --> 外部系统)
6、调度管理：用于定时触发调用管理的接口，如定时发送每日生产进度到客户系统
7、日志管理：包括调用管理和接口管理的每次调用及被调用日志

本文主要围绕两个重要的方面进行说明

1、如何实现不同数据库的切换
2、如何动态语句查询

2、实现不同数据库的切换

不同数据库的切换基于Spring jdbc的AbstractRoutingDataSource类实现

2.1、基于AbstractRoutingDataSource进行动态切换数据库，并实现自定义的一个接口实现动态添加及移除

2.1.1、DataSourceRegister自定义注册的接口功能如下：

/**
 * 数据源注册/取消注册/替换
 */
public interface DataSourceRegister {

    /**
     * 数据源替换
     * @param name 数据源名称
     * @param dataSource 数据库
     * @return
     */
    boolean replace(String name, DataSource dataSource);

    /**
     * 注册新的数据源
     * @param name 数据源名称
     * @param dataSource 数据库
     * @return
     */
    boolean registry(String name, DataSource dataSource);

    /**
     * 注销数据源
     * @param name 数据源名称
     * @return
     */
    boolean unRegistry(String name);

    /**
     * 包含数据库名
     * @param name
     * @return
     */
    boolean contains(String name);

}

2.2.2动态数据源的编码如下

public class DynamicMultipleDatasource extends AbstractRoutingDataSource implements InitializingBean, DataSourceRegister {
    /**
     * ThreadLocal,叫线程本地变量或线程本地存储。
     * ThreadLocal为变量在每个线程中都创建了一个副本，那么每个线程可以访问自己内部的副本变量。
     * 这里使用它的子类InheritableThreadLocal用来保证父子线程都能拿到值。
     */
    private static final ThreadLocal<String> DATA_SOURCE_THREAD = new InheritableThreadLocal<>();

    private  static  final Map<Object, Object> dataSourceMap = new ConcurrentHashMap<>();

    private static final String DEFAULT_DB_NAME = "_db_default_s";

    private DataSource dataSource;

    public DynamicMultipleDatasource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DATA_SOURCE_THREAD.get() == null ? DEFAULT_DB_NAME : DATA_SOURCE_THREAD.get();
    }

    public DataSource getUsedDataSources() {
        return (DataSource) dataSourceMap.get(determineCurrentLookupKey());
    }

    public static void setCurrentDataSource(String dataSource) {
        DATA_SOURCE_THREAD.set(dataSource);
    }

    public static void clear() {
        DATA_SOURCE_THREAD.remove();
    }

    public static String getCurrentDataSource() {
        return DATA_SOURCE_THREAD.get();
    }

    @Override
    public synchronized boolean replace(String name, DataSource dataSource) {
        unRegistry(name);
        return registry(name, dataSource);
    }


    @Override
    /**
     * 注册数据源
     *
     * @param name
     * @param dataSource
     * @return
     */
    public synchronized boolean registry(String name, DataSource dataSource) {
        boolean res = dataSourceMap.putIfAbsent(name, dataSource) == null;
        // 添加后需要刷新到动态数据源的对象中，需要调用 afterPropertiesSet;
        super.afterPropertiesSet();
        return res;
    }


    @Override
    /**
     * 注销数据源
     *
     * @param name
     * @return
     */
    public synchronized boolean unRegistry(String name) {
        Object remove = dataSourceMap.remove(name);
        if (remove != null) {
            if (remove instanceof DruidDataSource) {
                // 销毁数据源需要调用close方法
                ((DruidDataSource) remove).close();
            } else if (remove instanceof HikariDataSource) {
                // 销毁数据源需要调用close方法
                ((HikariDataSource) remove).close();
            }
        }
        // 然后刷新下afterPropertiesSet
        super.afterPropertiesSet();
        return remove != null;
    }


    @Override
    public boolean contains(String name) {
        return dataSourceMap.keySet().contains(name);
    }

    /**
     * 启动后的操作
     */
    @Override
    public void afterPropertiesSet() {
        // 把默认的数据源加到dataSourceMap中
        if (dataSource != null) {
            dataSourceMap.putIfAbsent(DEFAULT_DB_NAME, dataSource);
        }
        // 调用super.afterPropertiesSet();
        super.setTargetDataSources(dataSourceMap);
        super.afterPropertiesSet();
    }
}

2.2.3 配置动态数据源

配置使用DynamicMultipleDatasource替换原来的Datasource

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return DruidDataSourceBuilder.create().build();
    }
    @Bean
    @Primary
    @ConditionalOnBean(DataSource.class)
    public DynamicMultipleDatasource dynamicMultipleDatasource(DataSource dataSource) {
        return new DynamicMultipleDatasource(dataSource);
    }

}

3、如何动态语句查询

基于mybatis-plus执行动态语句查询

3.1、Mapper的实现如下：


import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Param;

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

/**
 * 动态数据源仓库
 * @author hf
 */
public interface DynamicSourceOperationRepository {

    /** 查询接口
     * @return
     */
    Map<String, Object> selectObject(@Param("sql") String sql);

    /** 查询接口
     * @return
     */
    Map<String, Object> selectObjectParams(@Param("sql") String sql, @Param("params") Map<String, Object> params);

    /** 查询接口
     * @return
     */
    List<Map<String, Object>> selectObjectParamsWithList(@Param("sql") String sql, @Param("params") List<Map<String, Object>> params);

    /** 查询接口
     * @return
     */
    List<Map<String, Object>> selectList(@Param("sql") String sql);

    /** 查询接口
     * @return
     */
    List<Map<String, Object>> selectListParams(@Param("sql") String sql, @Param("params") Map<String, Object> params);

    /** 查询接口
     * @return
     */
    List<List<Map<String, Object>>> selectMultipleListParams(@Param("sql") String sql, @Param("params") Map<String, Object> params);
    /**
     * 插入
     * @param sql
     * @return
     */
    int insert(@Param("sql")String sql, @Param("params") Map<String, Object> params);

    /**
     * 修改
     * @param sql
     * @return
     */
    int update(@Param("sql")String sql, @Param("params")Object obj);

    /**
     * 聚合查询结果
     * @param sql
     * @param params
     * @return
     */
    Long aggSearch(@Param("sql") String sql, @Param("params") Map<String, Object> params);

    /**
     * 分页查询
     * @param sql
     * @param params
     * @param page
     * @return
     */
    IPage pageSearch(@Param("sql") String sql, @Param("params") Map<String, Object> params, IPage page);
}

3.2、对应的xml设计如下：

<?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.hf.core.mapper.DynamicSourceOperationRepository">

    <resultMap id="ADomain" type="java.util.Map"/>
    <resultMap id="BDomain" type="java.util.Map"/>
    <resultMap id="CDomain" type="java.util.Map"/>
    <resultMap id="DDomain" type="java.util.Map"/>
    <resultMap id="EDomain" type="java.util.Map"/>
    <resultMap id="FDomain" type="java.util.Map"/>

    <insert id="insert">
        ${sql}
    </insert>

    <update id="update">
        ${sql}
    </update>
    <select id="selectObject" resultType="java.util.Map">
         ${sql}
    </select>


    <select id="selectObjectParams" resultType="java.util.Map">
         ${sql}
    </select>
    <select id="selectList" resultType="java.util.Map">
         ${sql}
    </select>


    <select id="selectListParams" resultType="java.util.Map">
         ${sql}
    </select>

    <select id="selectMultipleListParams" statementType="CALLABLE"
            resultType="java.util.List" resultMap="ADomain,BDomain,CDomain,DDomain,EDomain,FDomain">
        ${sql}
    </select>

    <select id="aggSearch" resultType="java.lang.Long">
        ${sql}
    </select>

    <select id="pageSearch" resultType="java.util.Map">
        ${sql}
    </select>

</mapper>

3.3、编写sql范例如下

select * from demo_table where status = #{params.status}

3.4 调用DynamicSourceOperationRepository如下：
以查询方法为例，可以实现查询返回单个对象/集合对象，其中:


import com.hf.core.process.ExecuteSqlInfo;
import com.hf.core.util.DynamicSqlUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 查询语句执行逻辑
 * @author hf
 */
@Component
public class QuerySqlExecutorImpl extends AbstractSqlExecutor {

    @Autowired
    private DynamicSourceOperationRepository dynamicSourceOperationRepository;

    @Override
    public Object execute(List<? extends ExecuteSqlInfo> executeSqlInfos, Map<String, Object> params) {
        Map<String, Object> result = new HashMap<>();
        executeSqlInfos.forEach(t -> {
            result.put(t.paramName(), execute(t, params));
        });
        return result;
    }

    @Override
    public <T extends ExecuteSqlInfo> Object execute(T executeSqlInfo, Map<String, Object> params, List<String> path) {
        // 这边判断查询返回的是单个还是集合形式，返回形式不同取决了是使用List<Map>还是Map方式接收返回
        if (executeSqlInfo.singleResult()) {
            return singleResultExecute(executeSqlInfo, params, path);
        } else {
            return listResultExecute(executeSqlInfo, params, path);
        }
    }

    @Override
    public Object execute(List<? extends ExecuteSqlInfo> executeSqlInfos, List<Map<String, Object>> params) {
        Map<String, Object> result = new HashMap<>();
        executeSqlInfos.forEach(t -> {
            result.put(t.paramName(), execute(t, params));
        });
        return result;
    }

    @Override
    public <T extends ExecuteSqlInfo> Object execute(T executeSqlInfo, List<Map<String, Object>> params, List<String> path) {
        List<Map<String, Object>> mapList =
                dynamicSourceOperationRepository.selectObjectParamsWithList(executeSqlInfo.getUguid(), executeSqlInfo.sql(), params);
        Map<String, Object> hashMap = new HashMap<>();
        return mapList.stream().map(map -> {
            // 返回参数转驼峰
            keyToCamel(map);
            // 执行子语句
            return iteratorExecute(executeSqlInfo, hashMap, map, path);
        }).collect(Collectors.toList());
    }


    /**
     * 单个结果集
     * @param executeSqlInfo
     * @param params
     * @return
     */
    private Object singleResultExecute(ExecuteSqlInfo executeSqlInfo, Map<String, Object> params, List<String> path) {
        Map<String, Object> result;
        if (DynamicSqlUtils.isDynamicSql(executeSqlInfo.sql())) {
            // 如果有mybatis的<if></if>/<foreach></foreach>的等标签语句时候, 优先解析后再执行
            DynamicSqlUtils.DynamicContext dynamicSql = DynamicSqlUtils.getDynamicSql(executeSqlInfo, params);
            result = dynamicSourceOperationRepository.selectObjectParams(executeSqlInfo.getUguid(), dynamicSql.getSql(), dynamicSql.getParams());
        } else {
            result = dynamicSourceOperationRepository.selectObjectParams(executeSqlInfo.getUguid(), executeSqlInfo.sql(), params);
        }
        // 执行查询
        //判断结果集是否为空
        if (result == null) {
            return null;
        }
        keyToCamel(result);
        return iteratorExecute(executeSqlInfo, params, result, path);

    }

    /**
     * 列表结果集
     * @param executeSqlInfo
     * @param params
     * @return
     */
    private Object listResultExecute(ExecuteSqlInfo executeSqlInfo, Map<String, Object> params, List<String> path) {
        List<Map<String, Object>> maps;
        if (DynamicSqlUtils.isDynamicSql(executeSqlInfo.sql())) {
            // 如果有mybatis的<if></if>/<foreach></foreach>的等标签语句时候, 优先解析后再执行
            DynamicSqlUtils.DynamicContext dynamicSql = DynamicSqlUtils.getDynamicSql(executeSqlInfo, params);
            maps = dynamicSourceOperationRepository.selectListParams(executeSqlInfo.getUguid(), dynamicSql.getSql(), dynamicSql.getParams());
        } else {
            maps = dynamicSourceOperationRepository.selectListParams(executeSqlInfo.getUguid(), executeSqlInfo.sql(), params);
        }
        if (CollectionUtils.isEmpty(maps)) {
            return null;
        }
        List<Object> collect = maps.stream().map(map -> {
            // 返回参数转驼峰
            keyToCamel(map);
            // 执行子语句
            return iteratorExecute(executeSqlInfo, params, map, path);
        }).collect(Collectors.toList());
        return collect;
    }

    @Override
    public SqlType getExecuteSqlType() {
        return SqlType.SELECT;
    }
}

ExecuteSqlInfo 主要是存储sql配置相关信息

import com.hf.core.process.impl.SqlType;

import java.util.Iterator;

/**
 * sql信息
 * @author hf
 */
public interface ExecuteSqlInfo {

    /**
     * sql主键
     * @return
     */
    default String getUguid() {
        return "";
    }
    /**
     * 查询语句类型枚举：
     PAGE_QUERY, 分页查询
     INSERT,  insert插入语句
     UPDATE,  update修改语句
     SELECT,  查询语句
     CALL   存储过程调用
     * @return
     */
    SqlType sqlType();
    /**
     * 是否是单个结果集
     * @return
     */
    boolean singleResult();

    /**
     * 结果集参数名称
     * @return
     */
    String paramName();

    /**
     * 参数的语句
     * @return
     */
    String sql();

    /**
     * 是否有子查询/子插入/子删除/子修改
     * @return
     */
    boolean hasChildSql();

    /**
     * 子语句查询
     * @return
     */
    Iterator<ExecuteSqlInfo> child();
}

4、开发过程中遇到的相关问题

4、1 分页查询的时候不同数据库的分页语句不一样，如何兼容？

自定义实现mybatis-plus的PaginationInnerInterceptor, 根据驱动的类型判断是那种数据库进行自动切换，代码如下：


import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectFactory;
import com.baomidou.mybatisplus.extension.plugins.pagination.dialects.IDialect;

import javax.sql.DataSource;

@Component
public class MyPaginationInnerInterceptor extends PaginationInnerInterceptor {

    @Autowired
    private DynamicMultipleDatasource dynamicMultipleDatasource;

    @Override
    protected IDialect findIDialect(Executor executor) {
        DataSource usedDataSources = dynamicMultipleDatasource.getUsedDataSources();
        if(usedDataSources instanceof DruidDataSource) {
            DruidDataSource druidDataSource = (DruidDataSource) usedDataSources;
            String dbType = druidDataSource.getDbType();
            DbType dtype = DbType.MYSQL;
            switch (com.alibaba.druid.DbType.valueOf(dbType)) {
                case oracle:
                    dtype = DbType.ORACLE;
                    break;
                case sqlserver:
                    dtype = DbType.SQL_SERVER;
                    break;
                default:
                    break;

            }
            return DialectFactory.getDialect(dtype);
        }
        return super.findIDialect(executor);
    }
}

4.2 关于切换数据源的时机

常规的service写法如下, 这个在service里去执行数据库切换会导致事务不生效，因为spring会为@Transactional的接口代理，里面再去切换无效


import org.springframework.transaction.annotation.Transactional;

public class XxServiceImpl implements XxService {

    @Transactional(rollbackFor = Throwable.class)
    public Result insert() {
        //
        String currentDataSource = DynamicMultipleDatasource.getCurrentDataSource();
        // 切换数据库
        DynamicMultipleDatasource.setCurrentDataSource("目标数据库");
        try {
            // 执行业务
        } finally {
            // 切换到原数据库
            DynamicMultipleDatasource.setCurrentDataSource(currentDataSource);
        }

    }

}

推荐在事务外层编写切换数据库，写法如下：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class XxController {

    @Autowired
    private XxService xxService;

    public Result execute() {
        // 手动切换数据库，或者写个拦截器，根据请求参数等等方式，动态切换数据库
        DynamicMultipleDatasource.setCurrentDataSource("目标数据库");
        try {
            return xxService.insert();
            // 执行业务
        } finally {
            DynamicMultipleDatasource.clear();
        }
    }

}