package com.hwtd.mes.collect.service.impl;
|
|
import com.hwtd.mes.collect.dto.DatabaseDTO;
|
import com.hwtd.mes.collect.service.DataCollectionService;
|
import com.zaxxer.hikari.HikariConfig;
|
import com.zaxxer.hikari.HikariDataSource;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.stereotype.Service;
|
|
import java.sql.*;
|
import java.util.*;
|
|
@Service
|
@Slf4j
|
public class DataCollectionServiceImpl implements DataCollectionService {
|
|
/**
|
* 处理mdb数据库排除字段类型
|
*/
|
private final static List<String> MDB_EXCLUDE_TYPES = Arrays.asList("java.sql.Blob");
|
|
@Override
|
public List<Map<String, Object>> getAccessData(DatabaseDTO databaseDTO) {
|
List<Map<String,Object>> list = new ArrayList<>();
|
try{
|
Properties prop = new Properties();
|
//设置编码
|
prop.put("charSet", "UTF-8");
|
prop.put("user", StringUtils.isNotBlank(databaseDTO.getUserName())?databaseDTO.getUserName():"");
|
prop.put("password", StringUtils.isNotBlank(databaseDTO.getPassword())?databaseDTO.getPassword():"");
|
//数据地址
|
String dbUrl = "jdbc:ucanaccess://" + databaseDTO.getFilePath();
|
//引入驱动
|
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver").newInstance();
|
Connection conn = null;
|
PreparedStatement preparedStatement = null;
|
ResultSet rs = null;
|
//连接数据库资源
|
conn = DriverManager.getConnection(dbUrl, prop);
|
try {
|
//遍历获取多张表数据
|
String s = "SELECT "+databaseDTO.getPointColumns()+" FROM " + databaseDTO.getTableName() + " WHERE 1=1";
|
if(StringUtils.isNotBlank(databaseDTO.getMainColumn()) && StringUtils.isNotBlank(databaseDTO.getBatchCode())){
|
s+=" and " + databaseDTO.getMainColumn() + " = '" + databaseDTO.getBatchCode()+ "'";
|
}
|
if(StringUtils.isNotBlank(databaseDTO.getOrderColumn())){
|
String orderRule = StringUtils.isNotBlank(databaseDTO.getOrderRule())?databaseDTO.getOrderRule():"ASC";
|
s+=" ORDER BY " + databaseDTO.getOrderColumn() + " " + orderRule;
|
}
|
preparedStatement = conn.prepareStatement(s);
|
rs = preparedStatement.executeQuery();
|
ResultSetMetaData data = rs.getMetaData();
|
while (rs.next()) {
|
Map<String, Object> map = new HashMap<>();
|
for (int i = 1; i <= data.getColumnCount(); i++) {
|
//列名
|
String columnName = data.getColumnName(i);
|
//列字段类型
|
String columnClassName = data.getColumnClassName(i);
|
Object columnValue = null;
|
if(!MDB_EXCLUDE_TYPES.contains(columnClassName)){
|
columnValue = rs.getObject(i);
|
}
|
map.put(columnName, columnValue);
|
}
|
list.add(map);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
closeA1l(conn, preparedStatement, rs);
|
}
|
return list;
|
}catch (Exception e){
|
throw new RuntimeException("Access数据库采集异常："+e.getMessage());
|
}
|
}
|
|
@Override
|
public List<Map<String, Object>> getPostgreSqlData(DatabaseDTO databaseDTO) {
|
List<Map<String, Object>> dataList = new ArrayList<>();
|
try{
|
String dbName = databaseDTO.getDatabaseName();
|
String user = databaseDTO.getUserName();
|
String password = databaseDTO.getPassword();
|
// 从 GetFileDto 获取数据表名，对应【数据库表名】字段
|
String table = databaseDTO.getTableName();
|
// 检查数据库名和表名是否为空
|
if (dbName == null || dbName.isEmpty() || table == null || table.isEmpty()) {
|
throw new RuntimeException("数据库名或表名不能为空");
|
}
|
// 数据库连接信息
|
String url = String.format("jdbc:postgresql://%s:%s/%s",databaseDTO.getIpAddress(),databaseDTO.getServerPort(),dbName);
|
Connection connection = null;
|
PreparedStatement preparedStatement = null;
|
ResultSet resultSet = null;
|
|
HikariConfig config = new HikariConfig();
|
config.setJdbcUrl(url);
|
config.setUsername(user);
|
config.setPassword(password);
|
config.setMaximumPoolSize(10);
|
config.setConnectionTimeout(30000);
|
|
HikariDataSource ds = new HikariDataSource(config);
|
try {
|
// 建立连接
|
connection = ds.getConnection();
|
// 构建基础 SQL
|
String sql = "SELECT "+databaseDTO.getPointColumns()+" FROM "+table+" WHERE 1=1";
|
if(StringUtils.isNotBlank(databaseDTO.getMainColumn()) && StringUtils.isNotBlank(databaseDTO.getBatchCode())){
|
sql+=" AND (" + databaseDTO.getMainColumn() + " = TRIM('" + databaseDTO.getBatchCode()+ "')";
|
}
|
if(StringUtils.isNotBlank(databaseDTO.getOrderColumn())){
|
String orderRule = StringUtils.isNotBlank(databaseDTO.getOrderRule())?databaseDTO.getOrderRule():"ASC";
|
sql+=" ORDER BY " + databaseDTO.getOrderColumn() + " " + orderRule;
|
}
|
// 创建 PreparedStatement 对象执行 SQL
|
preparedStatement = connection.prepareStatement(sql);
|
resultSet = preparedStatement.executeQuery();
|
ResultSetMetaData metaData = resultSet.getMetaData();
|
int columnCount = metaData.getColumnCount();
|
// 遍历结果集获取数据
|
while (resultSet.next()) {
|
Map<String, Object> rowData = new HashMap<>();
|
for (int i = 1; i <= columnCount; i++) {
|
String columnName = metaData.getColumnName(i);
|
rowData.put(columnName, resultSet.getObject(i));
|
}
|
dataList.add(rowData);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
closeA1l(connection, preparedStatement, resultSet);
|
}
|
return dataList;
|
}catch (Exception e){
|
throw new RuntimeException("PostgreSql数据库采集异常："+e.getMessage());
|
}
|
}
|
|
private static void closeA1l(Connection conn, PreparedStatement preparedStatement, ResultSet rs) {
|
try {
|
if (null != rs) {
|
rs.close();
|
}
|
if (null != preparedStatement) {
|
preparedStatement.close();
|
}
|
if (null != conn) {
|
conn.close();
|
}
|
} catch (Exception ignore) {
|
}
|
}
|
|
}
|
