package com.highdatas.mdm.service.impl;
|
|
import com.baomidou.mybatisplus.mapper.EntityWrapper;
|
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
|
import com.highdatas.mdm.entity.SysAssembleDb;
|
import com.highdatas.mdm.entity.SysAssembleDbdriver;
|
import com.highdatas.mdm.mapper.SysAssembleDbMapper;
|
import com.highdatas.mdm.service.ISysAssembleDbService;
|
import com.highdatas.mdm.service.ISysAssembleDbdriverService;
|
import com.highdatas.mdm.util.Constant;
|
import org.apache.commons.dbutils.DbUtils;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import java.sql.*;
|
import java.text.MessageFormat;
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.Properties;
|
|
/**
|
* <p>
|
* 服务实现类
|
* </p>
|
*
|
* @author kimi
|
* @since 2020-02-20
|
*/
|
@Service
|
public class SysAssembleDbServiceImpl extends ServiceImpl<SysAssembleDbMapper, SysAssembleDb> implements ISysAssembleDbService {
|
|
@Autowired
|
ISysAssembleDbdriverService dbdriverService;
|
|
public Connection getConnection(String parentId) {
|
SysAssembleDb assemble = this.selectById(parentId);
|
if(assemble == null){
|
return null;
|
}
|
|
return getConnection(assemble);
|
|
}
|
|
private Connection getConnection(SysAssembleDb assemble) {
|
Connection connection = null;
|
try {
|
SysAssembleDbdriver sysAssembleDbdriver = dbdriverService.selectOne(new EntityWrapper<SysAssembleDbdriver>().eq(Constant.TYPE, assemble.getDatasourceType()));
|
String driver = "com.mysql.cj.jdbc.Driver";
|
if (sysAssembleDbdriver != null) {
|
driver = sysAssembleDbdriver.getDriver();
|
// dataSource.setDriverClassName(driver);c
|
}
|
|
// Boolean bigdata = assemble.getBigdata();
|
// if (bigdata) {
|
// //HBASE
|
// }else {
|
// dataMetaOutput = new DatabaseMeta("Output", unBigDataDataSourceInfo.getDbType(), "Native", unBigDataDataSourceInfo.getDbHostName(), unBigDataDataSourceInfo.getDbName(), unBigDataDataSourceInfo.getDbPort(),
|
// unBigDataDataSourceInfo.getUsername(), unBigDataDataSourceInfo.getPassword());
|
// }
|
DriverManager.setLoginTimeout(5);
|
String datasourcePass = assemble.getDatasourcePass();
|
try {
|
datasourcePass = com.datacvg.common.EncryptUtils.decrypt(datasourcePass);
|
}catch (Exception e){
|
e.printStackTrace();
|
}
|
|
// dataSource.setUrl(assemble.getDatasourceUrl());
|
// dataSource.setUsername(assemble.getDatasourceUser());
|
// dataSource.setPassword(datasourcePass);
|
// connection = dataSource.getConnection();
|
Properties properties = new Properties();
|
properties.setProperty("user",assemble.getDatasourceUser());
|
properties.setProperty("password",datasourcePass);
|
DbUtils.loadDriver(driver);
|
// Class.forName(driver);
|
String datasourceUrl = assemble.getDatasourceUrl();
|
if (!datasourceUrl.contains(Constant.timeZone)) {
|
datasourceUrl += "&serverTimezone=Asia/Shanghai";
|
}
|
datasourceUrl += "&nullCatalogMeansCurrent=true";
|
|
connection = DriverManager.getConnection(datasourceUrl,properties);
|
return connection;
|
} catch (Exception e) {
|
e.printStackTrace();
|
return null;
|
}
|
}
|
|
|
public boolean testDatasource(String key,String driveClass,String url,String username,String password){
|
try {
|
Class.forName(driveClass);
|
DriverManager.getConnection(url, username, password);
|
return true;
|
} catch (Exception e) {
|
return false;
|
}
|
}
|
@Override
|
public List<String> getFieldsByTableName(String parentId, String tableName) {
|
SysAssembleDb assemble = this.selectById(parentId);
|
if(assemble == null){
|
return null;
|
}
|
|
Connection connection = null;
|
try {
|
connection = getConnection(parentId);
|
SysAssembleDbdriver sysAssembleDbdriver = dbdriverService.selectOne(new EntityWrapper<SysAssembleDbdriver>().eq(Constant.TYPE, assemble.getDatasourceType()));
|
String fieldSql = "";
|
if(sysAssembleDbdriver != null) {
|
String firstTemplate = sysAssembleDbdriver.getFirstTemplate();
|
fieldSql = MessageFormat.format(firstTemplate, MessageFormat.format(Constant.selectFieldSqlMysqlTemplate, tableName));
|
}else {
|
fieldSql = MessageFormat.format(Constant.selectFieldSqlMysqlTemplate, tableName);
|
}
|
PreparedStatement preparedStatement = connection.prepareStatement(fieldSql);
|
ResultSet resultSet = preparedStatement.executeQuery();
|
ResultSetMetaData metaData = resultSet.getMetaData();
|
int columnCount = metaData.getColumnCount();
|
ArrayList<String> columList = new ArrayList<>();
|
for (int i = 1; i <= columnCount; i++) {
|
String columnLabel = metaData.getColumnLabel(i);
|
columList.add(columnLabel);
|
}
|
return columList;
|
}catch (Exception e) {
|
e.printStackTrace();
|
return null;
|
}finally {
|
if (connection != null) {
|
try {
|
connection.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
}
|
|
@Override
|
public boolean testConnection(SysAssembleDb sysAssembleDb) {
|
Connection connection = null;
|
try{
|
connection = getConnection(sysAssembleDb);
|
if (connection != null) {
|
return true;
|
}
|
return false;
|
}
|
catch (Exception e) {
|
e.printStackTrace();
|
return false;
|
}
|
finally {
|
if (connection != null) {
|
try {
|
connection.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
}
|
/**
|
*
|
* @description: 测试连接
|
* @param parentId 汇集id
|
* @return: 是否测试连接成功
|
*
|
*/
|
@Override
|
public boolean testConnection(String parentId) {
|
Connection connection = null;
|
try{
|
connection = getConnection(parentId);
|
if (connection != null) {
|
return true;
|
}
|
return false;
|
}
|
catch (Exception e) {
|
e.printStackTrace();
|
return false;
|
}
|
finally {
|
if (connection != null) {
|
try {
|
connection.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
}
|
/**
|
*
|
* @description: 测试sql是否语法正常
|
* @param sql sql
|
* @param parentId 汇集id
|
* @return: 语法是否正确
|
*
|
*/
|
@Override
|
public boolean testSql(String parentId, String sql) {
|
Connection connection = null;
|
if (StringUtils.isEmpty(sql)) {
|
return false;
|
}
|
try {
|
|
connection = getConnection(parentId);
|
if (connection == null) {
|
return false;
|
}
|
|
PreparedStatement preparedStatement = connection.prepareStatement(sql);
|
preparedStatement.executeQuery();
|
|
return true;
|
|
}catch (Exception e) {
|
e.printStackTrace();
|
return false;
|
}
|
finally {
|
if (connection != null){
|
try {
|
connection.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
return false;
|
}
|
}
|
}
|
}
|
/**
|
*
|
* @description: 通过sql 获取sql 内使用的字段
|
* @param sql sql
|
* @param parentId 汇集id
|
* @return: 字段列表
|
*
|
*/
|
@Override
|
public List<String> getFieldsBySql(String parentId, String sql) {
|
Connection connection = null;
|
if (StringUtils.isEmpty(sql)) {
|
return null;
|
}
|
try {
|
|
connection = getConnection(parentId);
|
if (connection == null) {
|
return null;
|
}
|
SysAssembleDb assemble = this.selectById(parentId);
|
SysAssembleDbdriver dbdriver = dbdriverService.selectOne(new EntityWrapper<SysAssembleDbdriver>().eq(Constant.TYPE, assemble.getDatasourceType()));
|
String firstTemplate = dbdriver.getFirstTemplate();
|
if (!StringUtils.isEmpty(firstTemplate)) {
|
//有sql模板的 可以只取第一条
|
sql = MessageFormat.format(firstTemplate, sql);
|
}
|
PreparedStatement preparedStatement = connection.prepareStatement(sql);
|
ResultSet resultSet = preparedStatement.executeQuery();
|
ResultSetMetaData metaData = resultSet.getMetaData();
|
int columnCount = metaData.getColumnCount();
|
ArrayList<String> columList = new ArrayList<>();
|
for (int i = 1; i <= columnCount; i++) {
|
String columnLabel = metaData.getColumnLabel(i);
|
columList.add(columnLabel);
|
}
|
return columList;
|
|
}catch (Exception e) {
|
e.printStackTrace();
|
return null;
|
}
|
finally {
|
if (connection != null){
|
try {
|
connection.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
|
}
|
}
|
