springcloud微服务搭建多数据源（mysql,oracle,postgres,等等）管理模块，支持通过注解方式切换不同类型的数据库

1.背景

同一套微服务管理系统，业务完全一样，但不同的客户可能要求使用自己熟悉的数据库，比如，mysql，oracle，postgres，还有一些国产数据库。如果能够将数据库模块独立出来，兼容各家的数据库操作，在发布系统之前，只需要通过注解切换到定制的数据库类型，那么就会方便很多。

2.实现方案

完整项目文件结构；

2.1.单体项目运行，未注册到nacos

新建多数据源管理模块，并在application.yml里面配置不同数据库的信息；

# Tomcat
server:
  port: 9305

# Spring
spring:
  application:
    # 应用名称
    name: sharetek-multi-datasource
  profiles:
    # 环境配置
    active: @profiles.active@

--- # nacos 配置
spring:
  cloud:
    nacos:
      # nacos 服务地址
      server-addr: @nacos.server@
      discovery:
        # 注册组
        group: @nacos.discovery.group@
        namespace: ${spring.profiles.active}
        cluster-name: xa
      config:
        # 配置组
        group: @nacos.config.group@
        namespace: ${spring.profiles.active}
  config:
    import:
      - optional:nacos:application-common.yml
      - optional:nacos:datasource.yml
      - optional:nacos:${spring.application.name}.yml

  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    # 数据源
    mysql:
      type: ${spring.datasource.type}
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/sharetek-demodb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
      username: xxx
      password: xxx
    oracle:
      type: ${spring.datasource.type}
      driverClassName: oracle.jdbc.OracleDriver
      url: jdbc:oracle:thin:@//localhost:1521/XE
      username: xxx
      password: xxx
      hikari:
        connectionTestQuery: SELECT 1 FROM DUAL
#    postgres:
#      type: ${spring.datasource.type}
#      driverClassName: org.postgresql.Driver
#      url: ${datasource.system-postgres.url}
#      username: ${datasource.system-postgres.username}
#      password: ${datasource.system-postgres.password}

启动类，

pom.xml配置内容如下，（有些依赖是其他系统模块的，主要是加入数据库驱动等依赖）

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <groupId>org.sharetek</groupId>
        <artifactId>sharetek-visual</artifactId>
        <version>${revision}</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>sharetek-multi-datasource</artifactId>

    <description>
        sharetek-multi-datasource 多数据源管理
    </description>

    <dependencies>
        <dependency>
            <groupId>org.sharetek</groupId>
            <artifactId>sharetek-common-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>

        <!-- 数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.16</version>
        </dependency>

        <!-- sharetek Common Log -->
        <dependency>
            <groupId>org.sharetek</groupId>
            <artifactId>sharetek-common-log</artifactId>
        </dependency>

        <!-- Mysql Connector -->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
        </dependency>
        <!-- Oracle -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
        </dependency>
        <!-- PostgreSql -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
        </dependency>
        <!-- SqlServer -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>

    </dependencies>

    <build>
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring-boot.version}</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>

        </plugins>
    </build>

</project>

以mysql为例，读取mysql数据库配置信息，（dml时会用到，如果未来使用代码生成功能，用mybatisplus操作数据库，此处可以不需要，包括MysqlHelper.java等）

package org.sharetek.multids.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * @author tjm
 * @title MysqlProperties
 * @date 2024/4/26 13:27
 * @description mysql数据库配置信息
 */
@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.mysql")
public class MysqlProperties {

    private String jdbcUrl;

    private String userName;

    private String passWord;

    private String driverClassName;

}

多数据源配置类DataSourceConfig.class，用来读取application.yml里面配置的各种数据库信息，在容器中生成DataSource对象；

package org.sharetek.multids.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * @author junlintianxia
 * @title DataSourceConfig
 * @date 2024/4/26 10:59
 * @description 多数据源配置类
 */
@Configuration
public class DataSourceConfig {

    /**
     * mysql数据源
     *
     * @Param: []
     * @Return: javax.sql.DataSource
     * @Author: junlintianxia
     * @Date: 2024/4/26 11:19
     */
    @Bean
    @ConfigurationProperties("spring.datasource.mysql")
    DataSource dataSourceMysql() {
        return DataSourceBuilder.create().build();
    }

    /**
     * oracle数据源
     *
     * @Param: []
     * @Return: javax.sql.DataSource
     * @Author: junlintianxia
     * @Date: 2024/4/26 11:19
     */
    @Bean
    @ConfigurationProperties("spring.datasource.oracle")
    DataSource dataSourceOracle() {
        return DataSourceBuilder.create().build();
    }

//    /**
//     * postgresql数据源
//     *
//     * @Param: []
//     * @Return: javax.sql.DataSource
//     * @Author: junlintianxia
//     * @Date: 2024/4/26 11:19
//     */
//    @Bean
//    @ConfigurationProperties("spring.datasource.postgresql")
//    DataSource dataSourcePostgresql() {
//        return DruidDataSourceBuilder.create().build();
//    }

}

JdbcTemplate配置类JdbcTemplateConfig.class，用DataSource生成JdbcTemplate对象，用于ddl相关操作；

package org.sharetek.multids.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * @author junlintianxia
 * @title JdbcTemplateConfig
 * @date 2024/4/25 17:24
 * @description JdbcTemplate配置类
 */
@Configuration
public class JdbcTemplateConfig {

    /**
     * mysql数据源
     *
     * @Param: [dataSource]
     * @Return: org.springframework.jdbc.core.JdbcTemplate
     * @Author: junlintianxia
     * @Date: 2024/4/26 11:25
     */
    @Bean
    JdbcTemplate getJdbcTemplateMysql(@Qualifier("dataSourceMysql") DataSource dataSource)
    {
        return new JdbcTemplate(dataSource);
    }

    /**
     * oracle数据源
     *
     * @Param: [dataSource]
     * @Return: org.springframework.jdbc.core.JdbcTemplate
     * @Author: junlintianxia
     * @Date: 2024/4/26 11:25
     */
    @Bean
    JdbcTemplate getJdbcTemplateOracle(@Qualifier("dataSourceOracle") DataSource dataSource)
    {
        return new JdbcTemplate(dataSource);
    }

//    /**
//     * postgresql数据源
//     *
//     * @Param: [dataSource]
//     * @Return: org.springframework.jdbc.core.JdbcTemplate
//     * @Author: junlintianxia
//     * @Date: 2024/4/26 11:25
//     */
//    @Bean
//    JdbcTemplate getJdbcTemplatePostgresql(@Qualifier("dataSourcePostgresql") DataSource dataSource)
//    {
//        return new JdbcTemplate(dataSource);
//    }


}

MySQL的crud工具类如下，

package org.sharetek.multids.utils;

import cn.hutool.core.util.ObjectUtil;
import jakarta.annotation.PostConstruct;
import lombok.extern.slf4j.Slf4j;
//import org.sharetek.multids.config.MysqlProperties;
import org.sharetek.multids.config.MysqlProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author tjm
 * @title MysqlUtils
 * @date 2024/4/25 14:20
 * @description mysql操作类，包括增删改查
 */
@Slf4j
@Component
public class MysqlHelper {

    //定义数据库的连接
    private Connection connection;

    //定义sql语句的执行对象
    private PreparedStatement pStatement;

    //定义查询返回的结果集合
    private ResultSet resultset;

    private static final String SQL = "SELECT * FROM ";     // 数据库操作

    @Autowired
    private MysqlProperties mysqlProperties;

    @PostConstruct
    public void init() {
        try {
            Class.forName(mysqlProperties.getDriverClassName()); //注册驱动
            connection = DriverManager.getConnection(mysqlProperties.getJdbcUrl(), mysqlProperties.getUserName(), mysqlProperties.getPassWord()); //定义连接
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    /**
     * 对数据库的增/删/改
     *
     * @Param: [sql, params]
     * @Return: boolean
     * @Author: tjm
     * @Date: 2024/4/25 14:30
     */
    public boolean addDeleteModify(String sql, List<Object> params) throws SQLException {
        int result = -1;//设置为
        pStatement = connection.prepareStatement(sql);  //填充占位符
        int index = 1; //从第一个开始添加
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pStatement.setObject(index++, params.get(i));//填充占位符
            }
        }
        result = pStatement.executeUpdate();    //执行成功将返回大于0的数
        return result > 0 ? true : false;
    }

    /**
     * 查询单条记录
     *
     * @Param: [sql: sql语句, params: 占位符]
     * @Return: java.util.Map<java.lang.String, java.lang.Object>
     * @Author: tjm
     * @Date: 2024/4/25 14:32
     */
    public Map<String, Object> getSimpleResult(String sql, List<Object> params) throws SQLException {
        Map<String, Object> map = new HashMap<>();
        int index = 1;  //从1开始设置占位符
        pStatement = connection.prepareStatement(sql);
        // 判断参数是否为空
        if (params != null && !params.isEmpty()) {
            // 循环填充占位符
            for (int i = 0; i < params.size(); i++) {
                pStatement.setObject((i+1), params.get(i));
            }
        }
        resultset = pStatement.executeQuery();
        // 将查询结果封装到map集合
        ResultSetMetaData metaDate = resultset.getMetaData();   //获取resultSet列的信息
        int columnLength = metaDate.getColumnCount();   //获得列的长度
        while (resultset.next()) {
            for (int i = 0; i < columnLength; i++) {
                String metaDateKey = metaDate.getColumnName(i + 1); //获得列名
                Object resultsetValue = resultset.getObject(metaDateKey);   //通过列名获得值
                if (resultsetValue == null) {
                    resultsetValue = "";    //转成String类型
                }
                //添加到map集合（以上代码是为了将从数据库返回的值转换成map的key和value）
                map.put(metaDateKey, resultsetValue);
            }
            break;
        }
        return map;
    }

    /**
     * 查询多条记录
     *
     * @Param: [sql: sql语句, params: 占位符]
     * @Return: java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     * @Author: tjm
     * @Date: 2024/4/25 14:35
     */
    public List<Map<String, Object>> getMultipleResult(String sql, List<Object> params) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<>();
        //填充占位符
        int index = 1;
        pStatement = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pStatement.setObject(index++, params.get(i));
            }
        }
        //执行SQL语句
        resultset = pStatement.executeQuery();
        //封装resultset成map类型
        ResultSetMetaData metaDate = resultset.getMetaData();//获取列信息,交给metaDate
        int columnLength = metaDate.getColumnCount();
        while (resultset.next()) {
            Map<String, Object> map = new HashMap<>();
            for (int i = 0; i < columnLength; i++) {
                // 获取列名
                String metaDateKey = metaDate.getColumnName(i + 1);
                Object resultsetValue = resultset.getObject(metaDateKey);
                if (resultsetValue == null) {
                    resultsetValue = "";
                }
                map.put(metaDateKey, resultsetValue);
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 应用反射机制返回单条记录
     *
     * @Param: [sql: sql语句, params: 占位符, javabean,会执行javabean类里面的toString方法]
     * @Return: T 泛型
     * @Author: tjm
     * @Date: 2024/4/25 14:38
     */
    public <T> T getSimpleResult_Ref(String sql, List<Object> params, Class<T> javabean) throws Exception {
        T result = null;
        int index = 1;
        pStatement = connection.prepareStatement(sql);
        if (ObjectUtil.isNotEmpty(params)) {
            for (int i = 0; i < params.size(); i++) {
                pStatement.setObject(index++, params.get(i));
            }
        }
        resultset = pStatement.executeQuery(sql);
        //封装resultset
        ResultSetMetaData metaData = resultset.getMetaData();//获得列的信息
        int columnLength = metaData.getColumnCount();//获得列的长度
        while (resultset.next())//循环取值
        {
            result = javabean.newInstance();//通过反射机制创建一个实例
            for (int i = 0; i < columnLength; i++) {
                String metaDateKey = metaData.getColumnName(i + 1);
                Object resultsetValue = resultset.getObject(metaDateKey);
                if (resultsetValue == null) {
                    resultsetValue = "";
                }
                //获取列的属性，无论是公有。保护还是私有，都可以获取
                Field field = javabean.getDeclaredField(metaDateKey);
                field.setAccessible(true);//打开javabean的访问private权限
                field.set(result, resultsetValue);//给javabean对应的字段赋值
            }
        }
        return result;
    }

    /**
     * 通过反射机制访问数据库，并返回多条记录
     *
     * @Param: [sql: sql语句, params: 占位符,Javabean,会执行javabean类里面的toString方法]
     * @Return: java.util.List<T>
     * @Author: tjm
     * @Date: 2024/4/25 14:52
     */
    public <T> List<T> getMultipleResult_Ref(String sql, List<Object> params, Class<T> javabean) throws Exception {
        List<T> list = new ArrayList<T>();
        int index = 1;
        pStatement = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pStatement.setObject(index, params.get(i));
            }
        }
        resultset = pStatement.executeQuery(sql);
        //封装resultset
        ResultSetMetaData metaData = resultset.getMetaData();//取出列的信息
        int columnLength = metaData.getColumnCount();//获取列数
        while (resultset.next()) {
            T tResult = javabean.newInstance();//通过反射机制创建一个对象
            for (int i = 0; i < columnLength; i++) {
                String metaDataKey = metaData.getColumnName(i + 1);
                Object resultsetValue = resultset.getObject(metaDataKey);
                if (resultsetValue == null) {
                    resultsetValue = "";
                }
                Field field = javabean.getDeclaredField(metaDataKey);
                field.setAccessible(true);
                field.set(tResult, resultsetValue);
            }
            list.add(tResult);
        }
        return list;
    }

    /**
     * 获取数据库下的所有表名
     *
     * @Param: []
     * @Return: java.util.List<java.lang.String>
     * @Author: tjm
     * @Date: 2024/4/25 15:00
     */
    public List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = connection.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            log.error("getTableNames failure", e);
        } finally {
            try {
                rs.close();
                closeConnection();
            } catch (SQLException e) {
                log.error("close ResultSet failure", e);
            }
        }
        return tableNames;
    }

    /**
     * 获取表中所有字段名称
     *
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: tjm
     * @Date: 2024/4/25 15:01
     */
    public List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = connection.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            log.error("getColumnNames failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection();
                } catch (SQLException e) {
                    log.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     *
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: tjm
     * @Date: 2024/4/25 15:02
     */
    public List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = connection.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            log.error("getColumnTypes failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection();
                } catch (SQLException e) {
                    log.error("getColumnTypes close pstem and connection failure", e);
                }
            }
        }
        return columnTypes;
    }

    /**
     * 获取表中所有字段的注释
     *
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: tjm
     * @Date: 2024/4/25 15:02
     */
    public List<String> getColumnComments(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        List<String> columnComments = new ArrayList<>();//列名注释集合
        ResultSet rs = null;
        try {
            pStemt = connection.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection();
                } catch (SQLException e) {
                    log.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return columnComments;
    }

    /**
     * 获取当前数据库下的所有表名称
     *
     * @Param: []
     * @Return: java.util.List
     * @Author: tjm
     * @Date: 2024/4/25 15:04
     */
    public List getAllTableName() throws Exception {
        List tables = new ArrayList();
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW TABLES ");
        while (rs.next()) {
            String tableName = rs.getString(1);
            tables.add(tableName);
        }
        rs.close();
        stmt.close();
        closeConnection();
        return tables;
    }

    /**
     * 根据表名获得建表sql语句（批量）
     *
     * @Param: [tableNames]
     * @Return: java.util.Map
     * @Author: tjm
     * @Date: 2024/4/25 15:05
     */
    public Map<String, String> getCreateSqlByTableNameBatch(List<String> tableNames) throws Exception {
        Map<String, String> map = new HashMap<>();
        Statement stmt = connection.createStatement();
        for (int i = 0; i < tableNames.size(); i++) {
            String table = tableNames.get(i);
            ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + table);
            if (rs != null && rs.next()) {
                String createDDL = rs.getString(2);
                String comment = parse(createDDL);
                map.put(table, comment);
            }
            rs.close();
        }
        stmt.close();
        closeConnection();
        return map;
    }

    /**
     * 根据表名获得所有字段名及注释
     *
     * @Param: [tableName]
     * @Return: void
     * @Author: tjm
     * @Date: 2024/4/25 15:07
     */
    public Map<String, String> getColumnCommentByTableName(String tableName) throws Exception {
        Map<String, String> map = new HashMap();
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("show full columns from " + tableName);
        if (rs != null && rs.next()) {
            map.put(rs.getString("Field"), rs.getString("Comment"));
        }
        rs.close();
        stmt.close();
        closeConnection();
        return map;
    }

    /**
     * 返回注释信息
     *
     * @Param: [all]
     * @Return: java.lang.String
     * @Author: tjm
     * @Date: 2024/4/25 15:04
     */
    public static String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }

    /**
     * 关闭连接（注意在finally里面执行以下方法）
     *
     * @Param: []
     * @Return: void
     * @Author: tjm
     * @Date: 2024/4/25 14:54
     */
    public void closeConnection() {
        if (resultset != null) {
            try {
                resultset.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                resultset = null;
            }
        }
        if (pStatement != null) {
            try {
                pStatement.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                pStatement = null;
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                connection = null;
            }
        }
    }

}

mysql库ddl工具类MysqlUtils.class，用于对表格结构的操作；

package org.sharetek.multids.utils;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.sharetek.multids.domain.AttributeItemEntity;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import static org.sharetek.common.core.constant.MultiDbConstants.TABLE_PREFIX;

/**
 * @author junlintianxia
 * @title MysqlUtils
 * @date 2024/4/25 10:54
 * @description mysql 工具类
 */
@Slf4j
@Component
public class MysqlUtils {

    private JdbcTemplate jdbcTemplate;

    public MysqlUtils(@Qualifier("getJdbcTemplateMysql") JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 创建数据库
     *
     * @Param: [dbName]
     * @Return: void
     * @Author: junlintianxia
     * @Date: 2024/4/25 11:12
     */
    public void createDatabase(String dbName) {
        String query = "CREATE DATABASE IF NOT EXISTS " + dbName;
        jdbcTemplate.execute(query);
    }

    /**
     * 删除数据库
     *
     * @Param: [dbName]
     * @Return: void
     * @Author: junlintianxia
     * @Date: 2024/4/25 11:12
     */
    public void dropDatabase(String dbName) {
        String query = "DROP DATABASE IF EXISTS " + dbName;
        jdbcTemplate.execute(query);
    }

    /**
     * 创建表
     *
     * @Param: [id, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:21
     */
    public boolean createTable(String definedName, List<AttributeItemEntity> columns) {
        return doCreateTable(TABLE_PREFIX + definedName, columns);
    }

    /**
     * 批量删除表
     *
     * @Param: [tableNames]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:22
     */
    @Transactional(rollbackFor = Throwable.class)
    public boolean dropTables(List<String> tableNames) {
        return dropTable(tableNames);
    }

    /**
     * 添加字段
     *
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:22
     */
    public boolean addColumns(String tableName, List<AttributeItemEntity> columns) {
        return doAddColumns(tableName, columns);
    }

    /**
     * 修改字段名称
     *
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:23
     */
    public boolean updateColumns(String tableName, List<AttributeItemEntity> columns) {
        return doUpdateColumns(tableName, columns);
    }

    /**
     * 删除字段
     *
     * @Param: [id, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:24
     */
    public boolean dropColumns(Long id, List<AttributeItemEntity> columns) {
        return dropColumns(TABLE_PREFIX + id, columns);
    }

    /**
     * 获取列类型
     *
     * @Param: [type, length]
     * @Return: java.lang.String
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:39
     */
    private String columnType(String type, Integer length) {
        if ("varchar".equalsIgnoreCase(type)) {
            if (ObjectUtil.isEmpty(length) || length == 0) {
                length = 255;
            }
            return type + "(" + length + ")";
        }
        return type;
    }

    /**
     * 创建表（详细）
     *
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:26
     */
    private boolean doCreateTable(String tableName, List<AttributeItemEntity> columns) {
        StringBuffer sb = new StringBuffer();
        sb.append("CREATE TABLE IF NOT EXISTS `" + tableName + "` (\n");
        sb.append("`id` bigint NOT NULL COMMENT '主键',\n");
        columns.forEach(e -> {
            sb.append("`" + e.getName() + "` " + columnType(e.getType(), e.getLength()) + " " + isNullSql(e.getEnableNull()) + " " + isDefaultSql(e.getDefaultValue()) + " COMMENT '" + e.getComment() + "',\n");
            if (e.getIsUnique()) {
                sb.append("UNIQUE KEY " + "uniq_" + e.getName() + " (`" + e.getName() + "`),\n");
            }
        });
        sb.append("`create_time` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间', \n");
        sb.append("PRIMARY KEY (`id`) \n");
        sb.append(");\n");
        return execute(sb);
    }

    /**
     * 添加字段（详细）
     *
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:33
     */
    private boolean doAddColumns(String tableName, List<AttributeItemEntity> columns) {
        StringBuffer sb = new StringBuffer();
        columns.forEach(e -> {
            sb.append("ALTER TABLE `" + tableName + "`  ADD COLUMN " + e.getName() + " " + columnType(e.getType(), e.getLength()) + " "
                + isNullSql(e.getEnableNull()) + " " + isDefaultSql(e.getDefaultValue()) + " COMMENT '" + e.getComment() + "';\n");
        });
        return execute(sb);
    }

    /**
     * 修改字段（详细），只能改名称
     *
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:34
     */
    private boolean doUpdateColumns(String tableName, List<AttributeItemEntity> columns) {
        StringBuffer sb = new StringBuffer();
        columns.forEach(e -> {
            if (StrUtil.isNotEmpty(e.getNewName()) && !e.getName().equalsIgnoreCase(e.getNewName())) {
                sb.append("ALTER TABLE `" + tableName + "` RENAME COLUMN " + e.getName() + " TO " + e.getNewName() + ";\n");
            }
        });
        return execute(sb);
    }

    /**
     * 删除字段（详细）
     *
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:34
     */
    private boolean dropColumns(String tableName, List<AttributeItemEntity> columns) {
        StringBuffer sb = new StringBuffer();
        columns.forEach(e -> {
            sb.append("ALTER TABLE `" + tableName + "`  DROP COLUMN \"" + e.getName() + "\";\n");
        });
        return execute(sb);
    }

    /**
     * 批量删除表（详细）
     *
     * @Param: [tableNames]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:34
     */
    private boolean dropTable(List<String> tableNames) {
        StringBuffer sb = new StringBuffer();
        tableNames.forEach(tableName -> {
            sb.append("DROP TABLE IF EXISTS `" + tableName + "`;");
        });
        return execute(sb);
    }

    /**
     * 获取所有表名称
     *
     * @Param: []
     * @Return: java.util.List<java.lang.String>
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:21
     */
    public List<String> tableNames() {
        List<String> tableNames = new ArrayList<>();
        try {
            Connection getConnection = jdbcTemplate.getDataSource().getConnection();
            DatabaseMetaData metaData = getConnection.getMetaData();
            ResultSet rs = metaData.getTables(getConnection.getCatalog(), null, null, new String[]{"TABLE"});
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tableNames.add(tableName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tableNames;
    }

    /**
     * 执行sql
     *
     * @Param: [sb]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:35
     */
    private boolean execute(StringBuffer sb) {
        try {
            jdbcTemplate.execute(sb.toString());
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 是否可为空
     *
     * @Param: [boo]
     * @Return: java.lang.String
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:35
     */
    private String isNullSql(Boolean boo) {
        if (boo) {
            return "NULL";
        }
        return "NOT NULL";
    }

    /**
     * 是否有默认值
     *
     * @Param: [obj]
     * @Return: java.lang.String
     * @Author: junlintianxia
     * @Date: 2024/4/25 13:35
     */
    private String isDefaultSql(Object obj) {
        if (ObjectUtil.isNotEmpty(obj)) {
            if (obj instanceof String) {
                return "DEFAULT '" + obj + "'";
            }
            return "DEFAULT " + obj;
        }
        return "";
    }

    /**
     * 获取所有表名
     *
     * @Param: []
     * @Return: java.util.List<java.lang.String>
     * @Author: junlintianxia
     * @Date: 2024/4/25 11:11
     */
    public List<String> getAllTableNames() {
        String query = "SHOW TABLES;";
        List<String> tableNames = new ArrayList<>();
        jdbcTemplate.query(query, rs -> {
            while (rs.next()) {
                tableNames.add(rs.getString(1));
            }
        });
        return tableNames;
    }

    /**
     * 获取表数据
     *
     * @Param: [tableName]
     * @Return: java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     * @Author: junlintianxia
     * @Date: 2024/4/25 11:11
     */
    public List<Map<String, Object>> getTableData(String tableName) {
        String query = "SELECT * FROM " + tableName + ";";
        return jdbcTemplate.queryForList(query);
    }


}

数据源DML接口IDmlService.class，数据的增删改查，所有数据源通用；

package org.sharetek.multids.service;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author tjm
 * @title IDmlService
 * @date 2024/4/29 10:58
 * @description 数据源DML接口
 */
public interface IDmlService {

    /**
     * 对数据库的增/删/改
     *
     * @Param: [sql, params]
     * @Return: boolean
     * @Author: tjm
     * @Date: 2024/4/29 11:16
     */
    public boolean addDeleteModify(String sql, List<Object> params) throws SQLException;

    /**
     * 查询单条记录
     *
     * @Param: [sql, params]
     * @Return: T
     * @Author: tjm
     * @Date: 2024/4/29 11:16
     */
    Map<String, Object> getSimpleResult(String sql, List<Object> params) throws SQLException;

    /**
     * 查询多条记录
     *
     * @Param: [sql, params]
     * @Return: java.util.List<T>
     * @Author: tjm
     * @Date: 2024/4/29 11:17
     */
    public List<Map<String, Object>> getMultipleResult(String sql, List<Object> params) throws SQLException;

    /**
     * 应用反射机制返回单条记录
     *
     * @Param: [sql, params, javabean]
     * @Return: T
     * @Author: tjm
     * @Date: 2024/4/29 11:17
     */
    public <T> T getSimpleResult_Ref(String sql, List<Object> params, Class<T> javabean) throws Exception;

    /**
     * 通过反射机制访问数据库，并返回多条记录
     *
     * @Param: [sql, params, javabean]
     * @Return: java.util.List<T>
     * @Author: tjm
     * @Date: 2024/4/29 11:17
     */
    public <T> List<T> getMultipleResult_Ref(String sql, List<Object> params, Class<T> javabean) throws Exception;

    /**
     * 获取数据库下的所有表名
     *
     * @Param: []
     * @Return: java.util.List<java.lang.String>
     * @Author: tjm
     * @Date: 2024/4/29 11:18
     */
    public List<String> getTableNames();

    /**
     * 获取表下的所有列名
     *
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: tjm
     * @Date: 2024/4/29 11:18
     */
    public List<String> getColumnNames(String tableName);

    /**
     * 获取表中所有字段类型
     *
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: tjm
     * @Date: 2024/4/29 11:19
     */
    public List<String> getColumnTypes(String tableName);

    /**
     * 获取表中所有字段的注释
     *
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: tjm
     * @Date: 2024/4/29 11:19
     */
    public List<String> getColumnComments(String tableName);

    /**
     * 根据表名获得建表sql语句（批量）
     *
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: tjm
     * @Date: 2024/4/29 11:19
     */
    public Map<String, String> getCreateSqlByTableNameBatch(List<String> tableNames) throws Exception;

    /**
     * 根据表名获得所有字段名及注释
     *
     * @Param: [tableName]
     * @Return: void
     * @Author: tjm
     * @Date: 2024/4/29 11:29
     */
    public Map<String, String> getColumnCommentByTableName(String tableName) throws Exception;



}

数据源DDL接口IDdlService.class，多数据库表结构的相关操作，所有数据源通用；

package org.sharetek.multids.service;

import org.sharetek.multids.domain.AttributeItemEntity;

import java.util.List;

/**
 * @author junlintianxia
 * @title IDdlService
 * @date 2024/4/25 15:25
 * @description 数据源DDL接口
 */
public interface IDdlService {

    /**
     * 创建数据库
     *
     * @Param: [dbName]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/28 17:21
     */
    public boolean createDatabase(String dbName);

    /**
     * 删除数据库
     *
     * @Param: [dbName]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/28 17:21
     */
    public void dropDatabase(String dbName);

    /**
     * 创建表
     *
     * @Param: [definedName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/28 17:21
     */
    public boolean createTable(String definedName, List<AttributeItemEntity> columns);

    /**
     * 删除表
     *
     * @Param: [tableNames]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/28 17:22
     */
    public boolean dropTables(List<String> tableNames);

    /**
     * 添加列
     *
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/28 17:22
     */
    public boolean addColumns(String tableName, List<AttributeItemEntity> columns);

    /**
     * 修改列
     *
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/28 17:22
     */
    public boolean updateColumns(String tableName, List<AttributeItemEntity> columns);

}

mysql库ddl操作service层实现类，对表结构的处理；

package org.sharetek.multids.service.impl.mysql;

import lombok.RequiredArgsConstructor;
import org.sharetek.multids.domain.AttributeItemEntity;
import org.sharetek.multids.service.IDdlService;
import org.sharetek.multids.utils.MysqlUtils;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author junlintianxia
 * @title MysqlDdlServiceImpl
 * @date 2024/4/25 15:25
 * @description mysql数据库DDL服务实现类
 */
@Service
@RequiredArgsConstructor
public class MysqlDdlServiceImpl implements IDdlService {

    private final MysqlUtils mysqlUtils;

    @Override
    public boolean createDatabase(String dbName) {
        try {
            mysqlUtils.createDatabase(dbName);
            return true;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public void dropDatabase(String dbName) {

    }

    /**
     * 创建表
     *
     * @Param: [definedName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/29 17:45
     */
    @Override
    public boolean createTable(String definedName, List<AttributeItemEntity> columns) {
        return mysqlUtils.createTable(definedName, columns);
    }

    /**
     * 删除表
     *
     * @Param: [tableNames]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/29 17:45
     */
    @Override
    public boolean dropTables(List<String> tableNames) {
        return mysqlUtils.dropTables(tableNames);
    }

    /**
     * 添加列
     *
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/29 17:46
     */
    @Override
    public boolean addColumns(String tableName, List<AttributeItemEntity> columns) {
        return mysqlUtils.addColumns(tableName, columns);
    }

    /**
     * 修改列
     *
     * @param tableName
     * @param columns
     * @Param: [tableName, columns]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/28 17:22
     */
    @Override
    public boolean updateColumns(String tableName, List<AttributeItemEntity> columns) {
        return mysqlUtils.updateColumns(tableName, columns);
    }
}

mysql库dml操作service层实现类MysqlDmlServiceImpl.class；

package org.sharetek.multids.service.impl.mysql;

import lombok.RequiredArgsConstructor;
import org.sharetek.multids.service.IDmlService;
import org.sharetek.multids.utils.MysqlHelper;
import org.springframework.stereotype.Service;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author junlintianxia
 * @title MysqlDmlServiceImpl
 * @date 2024/4/29 11:37
 * @description mysql数据库DML服务实现类
 */
@Service
@RequiredArgsConstructor
public class MysqlDmlServiceImpl implements IDmlService {

    private final MysqlHelper mysqlHelper;

    /**
     * 对数据库的增/删/改
     *
     * @param sql
     * @param params
     * @Param: [sql, params]
     * @Return: boolean
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:16
     */
    @Override
    public boolean addDeleteModify(String sql, List<Object> params) throws SQLException {
        return mysqlHelper.addDeleteModify(sql, params);
    }

    /**
     * 查询单条记录
     *
     * @param sql
     * @param params
     * @Param: [sql, params]
     * @Return: T
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:16
     */
    @Override
    public Map<String, Object> getSimpleResult(String sql, List<Object> params) throws SQLException {
        return mysqlHelper.getSimpleResult(sql, params);
    }

    /**
     * 查询多条记录
     *
     * @param sql
     * @param params
     * @Param: [sql, params]
     * @Return: java.util.List<T>
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:17
     */
    @Override
    public List<Map<String, Object>> getMultipleResult(String sql, List<Object> params) throws SQLException {
        return mysqlHelper.getMultipleResult(sql, params);
    }

    /**
     * 应用反射机制返回单条记录
     *
     * @param sql
     * @param params
     * @param javabean
     * @Param: [sql, params, javabean]
     * @Return: T
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:17
     */
    @Override
    public <T> T getSimpleResult_Ref(String sql, List<Object> params, Class<T> javabean) throws Exception {
        return mysqlHelper.getSimpleResult_Ref(sql, params, javabean);
    }

    /**
     * 通过反射机制访问数据库，并返回多条记录
     *
     * @param sql
     * @param params
     * @param javabean
     * @Param: [sql, params, javabean]
     * @Return: java.util.List<T>
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:17
     */
    @Override
    public <T> List<T> getMultipleResult_Ref(String sql, List<Object> params, Class<T> javabean) throws Exception {
        return mysqlHelper.getMultipleResult_Ref(sql, params, javabean);
    }

    /**
     * 获取数据库下的所有表名
     *
     * @Param: []
     * @Return: java.util.List<java.lang.String>
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:18
     */
    @Override
    public List<String> getTableNames() {
        return mysqlHelper.getTableNames();
    }

    /**
     * 获取表下的所有列名
     *
     * @param tableName
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:18
     */
    @Override
    public List<String> getColumnNames(String tableName) {
        return mysqlHelper.getColumnNames(tableName);
    }

    /**
     * 获取表中所有字段类型
     *
     * @param tableName
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:19
     */
    @Override
    public List<String> getColumnTypes(String tableName) {
        return mysqlHelper.getColumnTypes(tableName);
    }

    /**
     * 获取表中所有字段的注释
     *
     * @param tableName
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:19
     */
    @Override
    public List<String> getColumnComments(String tableName) {
        return mysqlHelper.getColumnComments(tableName);
    }

    /**
     * 根据表名获得建表sql语句（批量）
     *
     * @param tableNames
     * @Param: [tableName]
     * @Return: java.util.List<java.lang.String>
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:19
     */
    @Override
    public Map<String, String> getCreateSqlByTableNameBatch(List<String> tableNames) throws Exception {
        return mysqlHelper.getCreateSqlByTableNameBatch(tableNames);
    }

    /**
     * 根据表名获得所有字段名及注释
     *
     * @param tableName
     * @Param: [tableName]
     * @Return: void
     * @Author: junlintianxia
     * @Date: 2024/4/29 11:29
     */
    @Override
    public Map<String, String> getColumnCommentByTableName(String tableName) throws Exception {
        return mysqlHelper.getColumnCommentByTableName(tableName);
    }

}

package org.sharetek.multids.domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author junlintianxia
 * @title AttributeItemEntity
 * @date 2024/4/25 11:48
 * @description 数据库表的field实体
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AttributeItemEntity {

    public AttributeItemEntity(String name, String type, String comment, Integer length, Boolean enableNull, Boolean isUnique) {
        this.name = name;
        this.type = type;
        this.comment = comment;
        this.length = length;
        this.enableNull = enableNull;
        this.isUnique = isUnique;
    }

    // 当前名称
    private String name;

    // 新名称
    private String newName;

    // 类型
    private String type;

    // 注释
    private String comment;

    // 默认值
    private String defaultValue = "";

    // 长度
    private Integer length = 32;

    // 是否允许为空
    private Boolean enableNull = true;

    // 别名
    private String alias;

    // 是否唯一
    private Boolean isUnique = false;

}

用户操作入口DDLController.class，表结构相关操作，

package org.sharetek.multids.controller;

import jakarta.annotation.Resource;
import jakarta.validation.constraints.NotEmpty;
import org.sharetek.common.log.annotation.Log;
import org.sharetek.common.log.enums.BusinessType;
import org.sharetek.multids.domain.AttributeItemEntity;
import org.sharetek.multids.service.IDdlService;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import java.util.ArrayList;
import java.util.List;


/**
 * ddl控制器
 *
 * @Param:
 * @Return:
 * @Author: junlintianxia
 * @Date: 2024/4/25 15:42
 */
@Validated
@RestController
@RequestMapping("/ddl")
public class DDLController {

    @Resource(name = "mysqlDdlServiceImpl")
    private IDdlService ddlService;

    /**
     * 新建数据库
     */
    @Log(title = "新建数据库", businessType = BusinessType.INSERT)
    @PostMapping(value = "/addDB")
    public boolean addDB(String dbName) {
        return ddlService.createDatabase(dbName);
    }

    /**
     * 新建表
     */
    @Log(title = "新建表", businessType = BusinessType.INSERT)
    @PostMapping(value = "/addTable")
//    public boolean addTable(String tableName, List<AttributeItemEntity> columns) {
    public boolean addTable() {
        String definedName = "student2";
        List<AttributeItemEntity> columns = new ArrayList<>();

        AttributeItemEntity column = new AttributeItemEntity();
        column.setName("stuNo");
        column.setType("VARCHAR");
        column.setLength(11);
        column.setDefaultValue("");
        column.setEnableNull(true);
        column.setComment("学号");
        columns.add(column);

        return ddlService.createTable(definedName, columns);
    }

    /**
     * 删除表（批量）
     */
    @Log(title = "删除表", businessType = BusinessType.DELETE)
    @DeleteMapping("/delTables/{tableNames}")
    public boolean delTables(@NotEmpty(message = "表名称不能为空")
                          @PathVariable String[] tableNames) {
        return ddlService.dropTables(List.of(tableNames));
    }

    /**
     * 添加列（批量）
     */
    @Log(title = "添加列", businessType = BusinessType.INSERT)
    @PostMapping("/addColumns/{tableName}")
//    public boolean addColumns(@PathVariable String tableName, @RequestBody List<AttributeItemEntity> columns) {
    public boolean addColumns() {
        String tableName = "table_student";
        List<AttributeItemEntity> columns = new ArrayList<>();
        columns.add(new AttributeItemEntity("stuName", "VARCHAR", "姓名", 32, true, false));

        return ddlService.addColumns(tableName, columns);
    }

    /**
     * 修改列名
     */
    @Log(title = "修改列名", businessType = BusinessType.UPDATE)
    @PutMapping("/updateColumns/{tableName}")
//    public boolean updateColumns(@PathVariable String tableName, @RequestBody List<AttributeItemEntity> columns) {
    public boolean updateColumns() {
        String tableName = "table_student";
        List<AttributeItemEntity> columns = new ArrayList<>();
        AttributeItemEntity column = new AttributeItemEntity();
        column.setName("stuName");
        column.setNewName("stuName2");
        columns.add(column);

        return ddlService.updateColumns(tableName, columns);
    }


}

用户crud操作控制器入口，DMLController.class，所有数据源通用；

package org.sharetek.multids.controller;

import cn.hutool.core.date.DateTime;
import jakarta.annotation.Resource;
import org.sharetek.common.log.annotation.Log;
import org.sharetek.common.log.enums.BusinessType;
import org.sharetek.multids.service.IDmlService;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author junlintianxia
 * @title DMLController
 * @date 2024/4/29 10:54
 * @description dml控制器
 */
@Validated
@RestController
@RequestMapping("/dml")
public class DMLController {

    @Resource(name = "mysqlDmlServiceImpl")
    private IDmlService dmlService;

    /**
     * 对数据库的增/删/改
     */
    @Log(title = "对数据库的增/删/改", businessType = BusinessType.OTHER)
    @PostMapping(value = "/addDeleteModify")
//    public boolean addDeleteModify(String sql, List<Object> params) throws SQLException {
    public boolean addDeleteModify() throws SQLException {
        // test
        String sql = "insert into table_student(id,stuNo,addr,create_time) values(?,?,?,?)";
        List<Object> params = List.of(1, "no001", "xi'an", new DateTime());
        return dmlService.addDeleteModify(sql, params);
    }

    /**
     * 查询单条记录
     */
    @Log(title = "查询单条记录", businessType = BusinessType.QUERY)
    @GetMapping(value = "/getSimpleResult")
//    public Map<String, Object> getSimpleResult(String sql, List<Object> params) throws SQLException {
    public Map<String, Object> getSimpleResult() throws SQLException {
        // test 查询
        String sql = "select * from table_student where id = ?";
        List<Object> params = List.of(1L);
        return dmlService.getSimpleResult(sql, params);
    }

    /**
     * 查询多条记录
     */
    @Log(title = "查询多条记录", businessType = BusinessType.QUERY)
    @GetMapping(value = "/getMultipleResult")
//    public List<Map<String, Object>> getMultipleResult(String sql, List<Object> params) throws SQLException {
    public List<Map<String, Object>> getMultipleResult() throws SQLException {
        // test 查询
        String sql = "select * from table_student where addr = ?";
        List<Object> params = List.of("xi'an");
        return dmlService.getMultipleResult(sql, params);
    }





}

2.2.微服务方案，数据库配置信息通过nacos读取

pom.xml补充如下，

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <groupId>org.sharetek</groupId>
        <artifactId>sharetek-visual</artifactId>
        <version>${revision}</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>sharetek-multi-datasource</artifactId>

    <description>
        sharetek-multi-datasource 多数据源管理
    </description>

    <dependencies>
        <!-- SpringCloud Alibaba Nacos -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
        </dependency>

        <!-- SpringCloud Alibaba Nacos Config -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-nacos-config</artifactId>
        </dependency>

        <dependency>
            <groupId>org.sharetek</groupId>
            <artifactId>sharetek-common-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>

        <!-- 数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.16</version>
        </dependency>

        <!-- sharetek Common Log -->
        <dependency>
            <groupId>org.sharetek</groupId>
            <artifactId>sharetek-common-log</artifactId>
        </dependency>

        <!-- Mysql Connector -->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
        </dependency>
        <!-- Oracle -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
        </dependency>
        <!-- PostgreSql -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
        </dependency>
        <!-- SqlServer -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>

    </dependencies>

    <build>
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring-boot.version}</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>

        </plugins>
    </build>

</project>

在application.yml中注释掉数据库配置信息，

# Tomcat
server:
  port: 9305

# Spring
spring:
  application:
    # 应用名称
    name: sharetek-multi-datasource
  profiles:
    # 环境配置
    active: @profiles.active@

--- # nacos 配置
spring:
  cloud:
    nacos:
      # nacos 服务地址
      server-addr: @nacos.server@
      discovery:
        # 注册组
        group: @nacos.discovery.group@
        namespace: ${spring.profiles.active}
        cluster-name: xa
      config:
        # 配置组
        group: @nacos.config.group@
        namespace: ${spring.profiles.active}
  config:
    import:
      - optional:nacos:application-common.yml
      - optional:nacos:datasource.yml
      - optional:nacos:${spring.application.name}.yml

#  datasource:
#    type: com.zaxxer.hikari.HikariDataSource
#    # 数据源
#    mysql:
#      type: ${spring.datasource.type}
#      driver-class-name: com.mysql.cj.jdbc.Driver
#      jdbc-url: jdbc:mysql://localhost:3306/sharetek-demodb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
#      username: xxx
#      password: xxx
#    oracle:
#      type: ${spring.datasource.type}
#      driverClassName: oracle.jdbc.OracleDriver
#      url: jdbc:oracle:thin:@//localhost:1521/XE
#      username: xxx
#      password: xxx
#      hikari:
#        connectionTestQuery: SELECT 1 FROM DUAL
#    postgres:
#      type: ${spring.datasource.type}
#      driverClassName: org.postgresql.Driver
#      url: ${datasource.system-postgres.url}
#      username: ${datasource.system-postgres.username}
#      password: ${datasource.system-postgres.password}