这段时间给公司搞了动态数据源连接,和idea连接数据库的方式差别不大,目前支持mysql和hive的连接方式。
实现动态数据源时,需要先思考,是否要做成单接口多实现的方式,如果你只支持一个数据库,其实很无所谓,如果是支持大量的数据库,就要考虑这种方法,我的代码只支持中级以上的java开发,如果有些地方不懂的,可以直接给我留言。
hive:
maven jar包:
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.6.5</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.2.1</version>
</dependency>
代码:
/**
* @author: lichengying
* @date 2020-02-10.
*/
@Slf4j
@Service
public class DBHelperFactory {
/**
* DBHelper 容器
*/
private final Map<Integer,DBHelperService> dbHelperServiceContainer;
public DBHelperFactory(List<DBHelperService> dbHelperServiceList){
dbHelperServiceContainer = Maps.newHashMapWithExpectedSize(dbHelperServiceList.size());
dbHelperServiceList.forEach(item->{
Integer datasourceType = item.supportDatasourceType();
dbHelperServiceContainer.put(datasourceType,item);
log.debug("Add DBHelper :{}", datasourceType);
});
}
/**
* 获取指定类型的DBHelper
* @param datasourceType
* @return
*/
public DBHelperService getDbhelperService(Integer datasourceType){
return dbHelperServiceContainer.get(datasourceType);
}
}
/**
* @author lichengying
*/
public interface DBHelperService {
/**
* helper支持的数据源类型
* @return
*/
Integer supportDatasourceType();
/**
* 查询某个库下的所有表
* @param sourceVO
* @return
*/
List<String> queryTableName(DynamicDataSourceVO sourceVO ) throws SQLException;
/**
* 查询表字段名
* @param sourceVO
* @return
*/
List<DynamicDataSourceVO.ColumnList> queryTableColumn(DynamicDataSourceVO sourceVO) throws SQLException;
/**
* 查询表数据前200条
* @param sourceVO
* @return
* @throws SQLException
*/
List<Object> queryTableValue(DynamicDataSourceVO sourceVO) throws SQLException;
/**
* 查询表中数据的数量
* @param sourceVO
* @return
* @throws SQLException
*/
Integer queryTableValueCount(DynamicDataSourceVO sourceVO) throws SQLException;
}
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.*;
import static org.springframework.util.StringUtils.hasText;
/**
* hive数据库连接
* @author lichengying
*/
@Service("hiveHelperService")
@Slf4j
public class HiveDBHelperServiceImpl implements DBHelperService{
//数据源
private final String name = "org.apache.hive.jdbc.HiveDriver";
@Override
public Integer supportDatasourceType() {
return DatasourceType.HIVE.intValue();
}
//查询数据库所有表
@Override
public List<String> queryTableName(DynamicDataSourceVO sourceVO) throws SQLException {
Connection conn = null;
List<String> result = new ArrayList<>();
try {
String url = "jdbc:hive2://" + sourceVO.getHostId() + ":" + sourceVO.getPort() + "/default";
Class.forName(name);
String userName = sourceVO.getUserName();
String password = sourceVO.getPassword();
if (!hasText(userName)) {
userName = "";
}
if (!hasText(password)) {
password = "";
}
conn = DriverManager.getConnection(url, userName, password);//
String[] types = {"TABLE"};
DatabaseMetaData dbMetaData = conn.getMetaData();
ResultSet tabs = dbMetaData.getTables(null,null,null,types);
while(tabs.next()){
//只要表名这一列
String table_name = tabs.getObject("TABLE_NAME").toString();
result.add(table_name);
}
return result;
} catch (Exception e) {
log.error("数据库连接异常,主机名:{},端口:{},数据库类型:{},数据库名称:{},用户名:{},密码:{},error:{}",
sourceVO.getHostId(),sourceVO.getPort(),sourceVO.getDatasourceType(),sourceVO.getDataName(),sourceVO.getUserName(),sourceVO.getPassword(),e.getMessage());
throw new SQLException(CommonConstant.DATASOURCE_CONNECTION_ERROR);
}finally {
if (null != conn) {
conn.close();
}
}
}
//查询表中所有id
@Override
public List<DynamicDataSourceVO.ColumnList> queryTableColumn(DynamicDataSourceVO sourceVO) throws SQLException {
Connection conn = null;
PreparedStatement pst = null;
List<DynamicDataSourceVO.ColumnList> result = new ArrayList<>();
try {
String sql = "desc " + sourceVO.getDataName() + "." + sourceVO.getTableName() ;
String url = "jdbc:hive2://" + sourceVO.getHostId() + ":" + sourceVO.getPort() + "/default";
Class.forName(name);
String userName = sourceVO.getUserName();
String password = sourceVO.getPassword();
if (!hasText(userName)) {
userName = "";
}
if (!hasText(password)) {
password = "";
}
conn = DriverManager.getConnection(url, userName, password);//
pst = conn.prepareStatement(sql);//
ResultSet resultSet = pst.executeQuery();
while (resultSet.next()) {
DynamicDataSourceVO.ColumnList columnList = new DynamicDataSourceVO.ColumnList();
String columnComment = resultSet.getString(1);
String columnName = resultSet.getString(2);
String dateType = resultSet.getString(3);
if (!hasText(dateType)) {
//throw new SQLException(CommonConstant.FIELD_ERROR_NOT_NULL_ENGLISH_NAME);
dateType = resultSet.getString(2);
}
columnList.setColumnComment(columnComment);
columnList.setColumnName(columnName);
columnList.setDateType(dateType);
result.add(columnList);
}
return result;
} catch (Exception e) {
log.error("数据库连接异常,主机名:{},端口:{},数据库类型:{},数据库名称:{},用户名:{},密码:{},error:{}",
sourceVO.getHostId(),sourceVO.getPort(),sourceVO.getDatasourceType(),sourceVO.getDataName(),sourceVO.getUserName(),sourceVO.getPassword(),e.getMessage());
throw new SQLException(CommonConstant.DATASOURCE_CONNECTION_ERROR);
}finally {
if (null != pst) {
pst.close();
}
if (null != conn) {
conn.close();
}
}
}
//查询表的数据,最多两百条
@Override
public List<Object> queryTableValue(DynamicDataSourceVO sourceVO) throws SQLException {
Connection conn = null;
PreparedStatement pst = null;
List<Object> result = new ArrayList<>();
try {
String sql = "select * from " + sourceVO.getTableName() + " limit 200";
String url = "jdbc:hive2://" + sourceVO.getHostId() + ":" + sourceVO.getPort() + "/default";
Class.forName(name);
String userName = sourceVO.getUserName();
String password = sourceVO.getPassword();
if (!hasText(userName)) {
userName = "";
}
if (!hasText(password)) {
password = "";
}
conn = DriverManager.getConnection(url, userName, password);//
String querySql = sourceVO.getQuerySql();
if (hasText(querySql)) {
sql = querySql + " limit 200";
}
pst = conn.prepareStatement(sql);//
ResultSet resultSet = pst.executeQuery();
ResultSetMetaData md = resultSet.getMetaData();
int columnCount = md.getColumnCount();
while (resultSet.next()) {
Map rowData = new HashMap();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), resultSet.getObject(i));
}
result.add(rowData);
}
return result;
} catch (Exception e) {
log.error("数据库连接异常,主机名:{},端口:{},数据库类型:{},数据库名称:{},用户名:{},密码:{},error:{}",
sourceVO.getHostId(),sourceVO.getPort(),sourceVO.getDatasourceType(),sourceVO.getDataName(),sourceVO.getUserName(),sourceVO.getPassword(),e.getMessage());
throw new SQLException(CommonConstant.DATASOURCE_CONNECTION_ERROR);
}finally {
if (null != pst) {
pst.close();
}
if (null != conn) {
conn.close();
}
}
}
//查询数据库表中的条数
@Override
public Integer queryTableValueCount(DynamicDataSourceVO sourceVO) throws SQLException {
Connection conn = null;
PreparedStatement pst = null;
try {
String sql = "select * from " + sourceVO.getTableName() ;
String url = "jdbc:hive2://" + sourceVO.getHostId() + ":" + sourceVO.getPort() + "/default";
Class.forName(name);
String userName = sourceVO.getUserName();
String password = sourceVO.getPassword();
if (!hasText(userName)) {
userName = "";
}
if (!hasText(password)) {
password = "";
}
conn = DriverManager.getConnection(url, userName, password);//
String querySql = sourceVO.getQuerySql();
if (hasText(querySql)) {
sql = querySql ;
}
pst = conn.prepareStatement(sql);//
ResultSet resultSet = pst.executeQuery();
ResultSetMetaData md = resultSet.getMetaData();
Integer count = 0;
while (resultSet.next()) {
count++;
}
return count;
} catch (Exception e) {
log.error("数据库连接异常,主机名:{},端口:{},数据库类型:{},数据库名称:{},用户名:{},密码:{},error:{}",
sourceVO.getHostId(),sourceVO.getPort(),sourceVO.getDatasourceType(),sourceVO.getDataName(),sourceVO.getUserName(),sourceVO.getPassword(),e.getMessage());
throw new SQLException(CommonConstant.DATASOURCE_CONNECTION_ERROR);
}finally {
if (null != pst) {
pst.close();
}
if (null != conn) {
conn.close();
}
}
}
}
mysql:
代码:
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.*;
import static org.springframework.util.StringUtils.hasText;
/**
* 动态连接mysql数据库
* @author lichengying
*/
@Slf4j
@Primary //默认的实现类
@Service("mysqlHelperService")
public class MysqlDBHelperServiceImpl implements DBHelperService {
public static final String name = "com.mysql.cj.jdbc.Driver";
@Override
public Integer supportDatasourceType() {
return DatasourceType.MYSQL.intValue();
}
@Override
public List<String> queryTableName(DynamicDataSourceVO sourceVO ) throws SQLException {
//数据库名称
String sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = " + "'" + sourceVO.getDataName() + "'";
List<String> result = new ArrayList<>();
Connection conn = null ;
PreparedStatement pst = null;
try {
String url = "jdbc:mysql://" + sourceVO.getHostId() + ":" + sourceVO.getPort() + "/" + sourceVO.getDataName() + "?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true";
Class.forName(name);
conn = DriverManager.getConnection(url, sourceVO.getUserName(), sourceVO.getPassword());//
pst = conn.prepareStatement(sql);//
ResultSet resultSet = pst.executeQuery();
while (resultSet.next()) {
result.add(resultSet.getString(GregorianCalendar.APRIL));
}
} catch (Exception e) {
log.error("数据库连接异常,主机名:{},端口:{},数据库类型:{},数据库名称:{},用户名:{},密码:{},error:{}",
sourceVO.getHostId(),sourceVO.getPort(),sourceVO.getDatasourceType(),sourceVO.getDataName(),sourceVO.getUserName(),sourceVO.getPassword(),e.getMessage());
throw new SQLException(CommonConstant.DATASOURCE_CONNECTION_ERROR);
}finally {
if (null != pst) {
pst.close();
}
if (null != conn) {
conn.close();
}
}
return result;
}
@Override
public List<DynamicDataSourceVO.ColumnList> queryTableColumn(DynamicDataSourceVO sourceVO) throws SQLException {
String dataName = sourceVO.getDataName();
//数据库名称
String sql = "SELECT COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + sourceVO.getTableName() + "' AND TABLE_SCHEMA = '" + dataName + "' " ;
List<DynamicDataSourceVO.ColumnList> result = new ArrayList<>();
Connection conn = null ;
PreparedStatement pst = null;
try {
String url = "jdbc:mysql://" + sourceVO.getHostId() + ":" + sourceVO.getPort() + "/" + sourceVO.getDataName() + "?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true";
Class.forName(name);
conn = DriverManager.getConnection(url, sourceVO.getUserName(), sourceVO.getPassword());//
pst = conn.prepareStatement(sql);//
ResultSet resultSet = pst.executeQuery();
while (resultSet.next()) {
DynamicDataSourceVO.ColumnList columnList = new DynamicDataSourceVO.ColumnList();
columnList.setColumnComment(resultSet.getString(1));
columnList.setColumnName(resultSet.getString(2));
columnList.setDateType(resultSet.getString(3));
result.add(columnList);
}
return result;
} catch (Exception e) {
log.error("数据库连接异常,主机名:{},端口:{},数据库类型:{},数据库名称:{},用户名:{},密码:{},error:{}",
sourceVO.getHostId(),sourceVO.getPort(),sourceVO.getDatasourceType(),sourceVO.getDataName(),sourceVO.getUserName(),sourceVO.getPassword(),e.getMessage());
throw new SQLException(CommonConstant.DATASOURCE_CONNECTION_ERROR);
}finally {
if (null != pst) {
pst.close();
}
if (null != conn) {
conn.close();
}
}
}
/**
* 查询表前200条的数据
* @param sourceVO
* @return
* @throws SQLException
*/
@Override
public List<Object> queryTableValue(DynamicDataSourceVO sourceVO) throws SQLException {
String sql = "SELECT * FROM " + sourceVO.getTableName() + " limit 200";
sourceVO.setQuerySql(sql);
if (hasText(sourceVO.getSqlInfo())) {
sourceVO.setQuerySql(sourceVO.getSqlInfo() + " limit 200");
}
List<Object> result = new ArrayList<>();
Connection conn = null ;
PreparedStatement pst = null;
try {
String url = "jdbc:mysql://" + sourceVO.getHostId() + ":" + sourceVO.getPort() + "/" + sourceVO.getDataName() + "?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true";
Class.forName(name);
conn = DriverManager.getConnection(url, sourceVO.getUserName(), sourceVO.getPassword());//
pst = conn.prepareStatement(sql);//
ResultSet resultSet = pst.executeQuery();
ResultSetMetaData md = resultSet.getMetaData();
int columnCount = md.getColumnCount();
while (resultSet.next()) {
Map rowData = new HashMap();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), resultSet.getObject(i));
}
result.add(rowData);
}
return result;
} catch (Exception e) {
log.error("数据库连接异常,主机名:{},端口:{},数据库类型:{},数据库名称:{},用户名:{},密码:{},error:{}",
sourceVO.getHostId(),sourceVO.getPort(),sourceVO.getDatasourceType(),sourceVO.getDataName(),sourceVO.getUserName(),sourceVO.getPassword(),e.getMessage());
throw new SQLException(CommonConstant.DATASOURCE_CONNECTION_ERROR);
}finally {
if (null != pst) {
pst.close();
}
if (null != conn) {
conn.close();
}
}
}
@Override
public Integer queryTableValueCount(DynamicDataSourceVO sourceVO) throws SQLException {
String sql = "SELECT * FROM " + sourceVO.getTableName() ;
sourceVO.setQuerySql(sql);
if (hasText(sourceVO.getSqlInfo())) {
sourceVO.setQuerySql(sourceVO.getSqlInfo());
}
Connection conn = null ;
PreparedStatement pst = null;
try {
String url = "jdbc:mysql://" + sourceVO.getHostId() + ":" + sourceVO.getPort() + "/" + sourceVO.getDataName() + "?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true";
Class.forName(name);
conn = DriverManager.getConnection(url, sourceVO.getUserName(), sourceVO.getPassword());//
pst = conn.prepareStatement(sql);//
ResultSet resultSet = pst.executeQuery();
ResultSetMetaData md = resultSet.getMetaData();
Integer count = 0;
while (resultSet.next()) {
count++;
}
return count;
} catch (Exception e) {
log.error("数据库连接异常,主机名:{},端口:{},数据库类型:{},数据库名称:{},用户名:{},密码:{},error:{}",
sourceVO.getHostId(),sourceVO.getPort(),sourceVO.getDatasourceType(),sourceVO.getDataName(),sourceVO.getUserName(),sourceVO.getPassword(),e.getMessage());
throw new SQLException(CommonConstant.DATASOURCE_CONNECTION_ERROR);
}finally {
if (null != pst) {
pst.close();
}
if (null != conn) {
conn.close();
}
}
}
}