@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(description = "数据库查询类")
public class DbQO {
private final String urlTemplate = "jdbc:{dbType}://{ip}:{port}/{databaseName}";
@ApiModelProperty(value = "数据库类型(dbType)")
String dbType;
@ApiModelProperty(value = "数据库IP地址(ip)")
String ip;
@ApiModelProperty(value = "数据库端口号(port)")
String port;
@ApiModelProperty(value = "数据库库名(databaseName)")
String databaseName;
@ApiModelProperty(value = "其他参数(otherParams) == 比如时区、编码等配置")
Map<String, String> otherParams;
@ApiModelProperty(value = "数据库url(url) == 可使用这个替代ip、port、databaseName")
String url;
@ApiModelProperty(value = "数据库账号(userName)")
@NotBlank(message = "数据库账号缺失")
String userName;
@ApiModelProperty(value = "数据库密码(password)")
@NotBlank(message = "数据库密码缺失")
String password;
@ApiModelProperty(value = "需匹配的表名,多个则逗号分隔(inclueTableName) = 某些接口支持正则匹配")
String inclueTableName;
@ApiModelProperty(value = "被排除的表名,多个则逗号分隔(excludeTableName) = 某些接口支持正则匹配")
String excludeTableName;
/**
* 获取数据库链接
* 如果{@link DbQO#url}有值则取这个,否则通过{@link DbQO#dbType, DbQO#ip}等进行构建
* @return
*/
public String blankToBuildUrl() {
String result = url;
if (StrUtil.isBlank(result)) {
result = StrUtil.format(urlTemplate, BeanUtil.beanToMap(this), true);
if(CollUtil.isNotEmpty(otherParams)) {
String otherParamsStr = URLUtil.buildQuery(otherParams, CharsetUtil.CHARSET_UTF_8);
result = StrUtil.format("{}?{}",result,otherParamsStr);
}
}
return result;
}
}
hutool - 仅能精确匹配找单表(找多表自行增强Hutool代码)
依赖
<!-- https://mvnrepository.com/artifact/cn.hutool/hutool-all -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.11</version>
</dependency>
核心代码
/**
* 获取数据源
* @param dbQO
* @return
*/
private DataSource getDataSource(DbQO dbQO) {
return new DriverManagerDataSource(dbQO.blankToBuildUrl(), dbQO.getUserName(), dbQO.getPassword());
}
/**
* 获取数据库的某表的元信息
* @param dbQO 数据库信息
* @return
*/
@Override
public Table getTableMeta(DbQO dbQO) {
String targetTableName = dbQO.getInclueTableName();
Assert.notBlank(targetTableName,() -> new SystemBaseCustomException(RequestEnum.ResponseCodeEnum.DB_MISSING_PARAMETER_ERROR, "表名"));
DataSource dataSource = getDataSource(dbQO);
return MetaUtil.getTableMeta(dataSource, targetTableName);
}
MyBatisPlus - 支持正则匹配模糊查询多表
依赖
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-generator -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.3.1</version>
</dependency>
核心代码
/**
* 获取数据源
* @param dbQO
* @return
*/
private DataSource getDataSource(DbQO dbQO) {
return new DriverManagerDataSource(dbQO.blankToBuildUrl(), dbQO.getUserName(), dbQO.getPassword());
}
/**
* 获取数据库的某表的元信息
* @param dbQO 数据库信息
* @return
*/
@Override
public List<TableInfo> getTablesMeta(DbQO dbQO) {
//1. 数据库连接
DataSourceConfig dataSourceConfig = new DataSourceConfig.Builder(getDataSource(dbQO))
.build();
//2. 查找策略
String inclueTableName = dbQO.getInclueTableName();
String excludeTableName = dbQO.getExcludeTableName();
StrategyConfig.Builder strategyConfigBuilder = new StrategyConfig.Builder();
if(StrUtil.isNotBlank(inclueTableName)) {
strategyConfigBuilder.addInclude(StrUtil.split(inclueTableName,StrUtil.COMMA));
}
if(StrUtil.isNotBlank(excludeTableName)) {
strategyConfigBuilder.addExclude(StrUtil.split(excludeTableName,StrUtil.COMMA));
}
ConfigBuilder configBuilder = new ConfigBuilder(null,dataSourceConfig,strategyConfigBuilder.build(),null,null,null);
DefaultQuery defaultQuery = new DefaultQuery(configBuilder);
return defaultQuery.queryTables();
}