package cn.stylefeng.roses.kernel.db.api.util;
|
|
import cn.hutool.core.io.IoUtil;
|
import cn.hutool.core.util.StrUtil;
|
import cn.stylefeng.roses.kernel.db.api.exception.DaoException;
|
import cn.stylefeng.roses.kernel.db.api.exception.enums.DatabaseExceptionEnum;
|
import cn.stylefeng.roses.kernel.db.api.pojo.db.TableFieldInfo;
|
import cn.stylefeng.roses.kernel.db.api.pojo.db.TableInfo;
|
import cn.stylefeng.roses.kernel.db.api.pojo.druid.DruidProperties;
|
import cn.stylefeng.roses.kernel.db.api.sqladapter.database.CreateDatabaseSql;
|
import cn.stylefeng.roses.kernel.db.api.sqladapter.database.GetDatabasesSql;
|
import cn.stylefeng.roses.kernel.db.api.sqladapter.table.TableFieldListSql;
|
import cn.stylefeng.roses.kernel.db.api.sqladapter.table.TableListSql;
|
import cn.stylefeng.roses.kernel.rule.constants.RuleConstants;
|
import lombok.extern.slf4j.Slf4j;
|
|
import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* 数据库操作工具类，可用来获取一些元数据
|
*
|
* @author fengshuonan
|
* @date 2021/5/19 10:35
|
*/
|
@Slf4j
|
public class DatabaseUtil {
|
|
/**
|
* 获取数据库中的所有数据库列表
|
*
|
* @author fengshuonan
|
* @date 2021/5/26 20:42
|
*/
|
public static List<String> getDatabases(DruidProperties druidProperties) {
|
Connection conn = null;
|
List<String> databasesList = new ArrayList<>();
|
try {
|
Class.forName(druidProperties.getDriverClassName());
|
conn = DriverManager.getConnection(
|
druidProperties.getUrl(), druidProperties.getUsername(), druidProperties.getPassword());
|
PreparedStatement preparedStatement = conn.prepareStatement(new GetDatabasesSql().getSql(druidProperties.getUrl()));
|
ResultSet resultSet = preparedStatement.executeQuery();
|
while (resultSet.next()) {
|
String database = resultSet.getString("database");
|
if (StrUtil.isNotBlank(database)) {
|
if (StrUtil.startWith(database, RuleConstants.TENANT_DB_PREFIX)) {
|
database = database.replaceAll(RuleConstants.TENANT_DB_PREFIX, "");
|
databasesList.add(database);
|
}
|
}
|
}
|
return databasesList;
|
} catch (Exception e) {
|
log.error("查询所有库错误！", e);
|
throw new DaoException(DatabaseExceptionEnum.DATABASE_LIST_ERROR, e.getMessage());
|
} finally {
|
IoUtil.close(conn);
|
}
|
}
|
|
/**
|
* 查询某个数据库连接的所有表
|
*
|
* @author fengshuonan
|
* @date 2021/5/19 10:35
|
*/
|
public static List<TableInfo> selectTables(DruidProperties druidProperties) {
|
List<TableInfo> tables = new ArrayList<>();
|
Connection conn = null;
|
try {
|
Class.forName(druidProperties.getDriverClassName());
|
conn = DriverManager.getConnection(
|
druidProperties.getUrl(), druidProperties.getUsername(), druidProperties.getPassword());
|
|
// 获取数据库名称
|
String dbName = getDbName(druidProperties);
|
|
// 构造查询语句
|
PreparedStatement preparedStatement = conn.prepareStatement(new TableListSql().getSql(druidProperties.getUrl()));
|
|
// 拼接设置数据库名称
|
if (!druidProperties.getUrl().contains("sqlserver") && !druidProperties.getUrl().contains("postgresql")) {
|
preparedStatement.setString(1, dbName);
|
}
|
|
ResultSet resultSet = preparedStatement.executeQuery();
|
|
while (resultSet.next()) {
|
TableInfo tableInfo = new TableInfo();
|
String tableName = resultSet.getString("tableName");
|
String tableComment = resultSet.getString("tableComment");
|
tableInfo.setTableName(tableName);
|
tableInfo.setTableComment(tableComment);
|
tables.add(tableInfo);
|
}
|
return tables;
|
} catch (Exception ex) {
|
log.error("查询所有表错误！", ex);
|
throw new DaoException(DatabaseExceptionEnum.TABLE_LIST_ERROR, ex.getMessage());
|
} finally {
|
IoUtil.close(conn);
|
}
|
}
|
|
/**
|
* 查询某个表的所有字段
|
*
|
* @author fengshuonan
|
* @date 2021/5/19 11:01
|
*/
|
public static List<TableFieldInfo> getTableFields(DruidProperties druidProperties, String tableName) {
|
ArrayList<TableFieldInfo> fieldList = new ArrayList<>();
|
Connection conn = null;
|
try {
|
Class.forName(druidProperties.getDriverClassName());
|
conn = DriverManager.getConnection(
|
druidProperties.getUrl(), druidProperties.getUsername(), druidProperties.getPassword());
|
|
PreparedStatement preparedStatement = conn.prepareStatement(new TableFieldListSql().getSql(druidProperties.getUrl()));
|
|
if (druidProperties.getUrl().contains("oracle")) {
|
preparedStatement.setString(1, tableName);
|
} else if (druidProperties.getUrl().contains("postgresql")) {
|
preparedStatement.setString(1, tableName);
|
} else if (druidProperties.getUrl().contains("sqlserver")) {
|
preparedStatement.setString(1, tableName);
|
} else {
|
String dbName = getDbName(druidProperties);
|
preparedStatement.setString(1, tableName);
|
preparedStatement.setString(2, dbName);
|
}
|
|
//执行查询
|
ResultSet resultSet = preparedStatement.executeQuery();
|
|
while (resultSet.next()) {
|
TableFieldInfo tableFieldInfo = new TableFieldInfo();
|
String columnName = resultSet.getString("columnName");
|
String columnComment = resultSet.getString("columnComment");
|
tableFieldInfo.setColumnName(columnName);
|
tableFieldInfo.setColumnComment(columnComment);
|
tableFieldInfo.setCamelFieldName(StrUtil.toCamelCase(columnName));
|
fieldList.add(tableFieldInfo);
|
}
|
return fieldList;
|
} catch (Exception ex) {
|
log.error("查询表的所有字段错误！", ex);
|
throw new DaoException(DatabaseExceptionEnum.FIELD_GET_ERROR, ex.getMessage());
|
} finally {
|
IoUtil.close(conn);
|
}
|
}
|
|
/**
|
* 创建数据库
|
*
|
* @author fengshuonan
|
* @date 2021/5/19 10:39
|
*/
|
public static void createDatabase(DruidProperties druidProperties, String databaseName) {
|
Connection conn = null;
|
try {
|
Class.forName(druidProperties.getDriverClassName());
|
conn = DriverManager.getConnection(druidProperties.getUrl(), druidProperties.getUsername(), druidProperties.getPassword());
|
|
//创建sql
|
String sql = new CreateDatabaseSql().getSql(druidProperties.getUrl());
|
sql = sql.replaceAll("\\?", databaseName);
|
|
PreparedStatement preparedStatement = conn.prepareStatement(sql);
|
|
int i = preparedStatement.executeUpdate();
|
log.info("创建数据库！数量：" + i);
|
|
} catch (Exception ex) {
|
log.error("执行sql出现问题！", ex);
|
throw new DaoException(DatabaseExceptionEnum.CREATE_DATABASE_ERROR, ex.getMessage());
|
} finally {
|
IoUtil.close(conn);
|
}
|
}
|
|
/**
|
* 获取数据库名称
|
* <p>
|
* oracle的数据库会直接返回username作为数据库名称，这里需要注意一下，如果用户名不是数据库名则返回不准确
|
*
|
* @author fengshuonan
|
* @date 2021/5/19 10:39
|
*/
|
private static String getDbName(DruidProperties druidProperties) {
|
|
if (druidProperties.getUrl().contains("oracle")) {
|
// 如果是oracle，直接返回username
|
return druidProperties.getUsername();
|
} else if (druidProperties.getUrl().contains("postgresql")) {
|
// postgresql，直接返回最后一个/后边的字符
|
int first = druidProperties.getUrl().lastIndexOf("/") + 1;
|
return druidProperties.getUrl().substring(first);
|
} else if (druidProperties.getUrl().contains("sqlserver")) {
|
// sqlserver，直接返回最后一个=后边的字符
|
int first = druidProperties.getUrl().lastIndexOf("=") + 1;
|
return druidProperties.getUrl().substring(first);
|
} else {
|
// mysql，返回/和?之间的字符
|
String jdbcUrl = druidProperties.getUrl();
|
int first = jdbcUrl.lastIndexOf("/") + 1;
|
int last = jdbcUrl.indexOf("?");
|
return jdbcUrl.substring(first, last);
|
}
|
}
|
|
}
|
