1.背景
同一套微服务管理系统，业务完全一样，但不同的客户可能要求使用自己熟悉的数据库，比如，mysql，oracle，postgres，还有一些国产数据库。如果能够将数据库模块独立出来，兼容各家的数据库操作，在发布系统之前，只需要通过注解切换到定制的数据库类型，那么就会方便很多。
2.实现方案
完整项目文件结构；
新建多数据源管理模块，并在application.yml里面配置不同数据库的信息；
启动类，
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);
}
}