package com.product.data.utli;
|
|
import com.alibaba.fastjson.JSON;
|
import com.alibaba.fastjson.JSONObject;
|
import com.product.core.exception.BaseException;
|
import com.product.core.spring.context.SpringMVCContextHolder;
|
import com.product.data.config.ErrorCode;
|
import com.product.data.entity.QueryResultEntity;
|
|
import java.sql.*;
|
import java.util.*;
|
|
/**
|
* 数据查询
|
*
|
* @author cheng
|
* @date 2022年2月7日17:48:37
|
*/
|
public class QueryDataService {
|
private Connection connection;
|
|
public QueryDataService(Connection connection) {
|
this.connection = connection;
|
}
|
|
public QueryResultEntity getResult(String sql) throws BaseException {
|
return getResult(sql, null, null);
|
}
|
|
public QueryResultEntity getResult(String sql, Object[] params, List<String> columnNames) throws BaseException {
|
SpringMVCContextHolder.getSystemLogger().info(sql);
|
try (PreparedStatement pst = connection.prepareStatement(sql)) {
|
if (params != null && params.length > 0) {
|
for (int i = 0; i < params.length; i++) {
|
pst.setObject(i + 1, params[i]);
|
SpringMVCContextHolder.getSystemLogger().info("参数：" + params[i]);
|
}
|
}
|
ResultSet resultSet = pst.executeQuery();
|
QueryResultEntity queryResultEntity = new QueryResultEntity(null);
|
if (columnNames == null || columnNames.size() <= 0) {
|
columnNames = new ArrayList<>(Arrays.asList(getColumnNames(resultSet)));
|
}
|
queryResultEntity.setColumnNames(columnNames);
|
if (resultSet != null) {
|
while (resultSet.next()) {
|
List<String> errorFields = null;
|
Map<String, Object> map = new HashMap<>();
|
for (String columnName : columnNames) {
|
Object object;
|
try {
|
object = resultSet.getObject(columnName);
|
} catch (Exception e) {
|
//字段获取出错捕获错误将字段置空并继续 cheng 2022年3月14日10:55:47
|
object = "";
|
if (errorFields == null) {
|
errorFields = new ArrayList<>();
|
}
|
errorFields.add(columnName);
|
}
|
if (object instanceof UUID) {
|
map.put(columnName, object.toString());
|
continue;
|
}
|
map.put(columnName, object);
|
}
|
queryResultEntity.addResult(map);
|
if (errorFields != null && errorFields.size() > 0) {
|
//字段获取出错打印错误内容 2022年3月14日10:55:19 cheng
|
SpringMVCContextHolder.getSystemLogger().error("获取字段值时出现错误，错误的字段：\n" + errorFields + "\n,已获取到的数据集：\n" + JSON.toJSONString(new JSONObject(map)));
|
}
|
}
|
resultSet.close();
|
}
|
|
return queryResultEntity;
|
} catch (Exception e) {
|
e.printStackTrace();
|
throw new BaseException(e);
|
}
|
}
|
|
public QueryResultEntity getResult(String sql, List<String> columnNames) throws BaseException {
|
if (columnNames == null || columnNames.size() <= 0) {
|
return getResult(sql);
|
}
|
return getResult(sql, null, columnNames);
|
}
|
|
/**
|
* 获取结果集中列的数量
|
*
|
* @param resultSet 结果集
|
* @return
|
* @throws BaseException
|
*/
|
public static int getColumnCount(ResultSet resultSet) throws BaseException {
|
try {
|
ResultSetMetaData metaData = resultSet.getMetaData();
|
if (metaData != null) {
|
return metaData.getColumnCount();
|
}
|
} catch (Exception e) {
|
SpringMVCContextHolder.getSystemLogger().error(e);
|
throw new BaseException(ErrorCode.GET_RESULTSET_COLUMN_COUNT_FAIL.getValue(), ErrorCode.GET_RESULTSET_COLUMN_COUNT_FAIL.getText() + (e.getMessage() != null ? "," + e.getMessage() : ""));
|
}
|
return 0;
|
}
|
|
public static String[] getColumnNames(ResultSet resultSet) throws BaseException {
|
try {
|
ResultSetMetaData metaData = resultSet.getMetaData();
|
if (metaData != null) {
|
int columnCount = getColumnCount(resultSet);
|
String[] fieldName = new String[columnCount];
|
for (int i = 1; i <= columnCount; i++) {
|
String columnName = metaData.getColumnLabel(i);
|
fieldName[i - 1] = columnName;
|
}
|
return fieldName;
|
}
|
throw new BaseException(ErrorCode.GET_RESULTSET_COLUMN_NAME_FAIL.getValue(), ErrorCode.GET_RESULTSET_COLUMN_NAME_FAIL.getText());
|
} catch (Exception e) {
|
SpringMVCContextHolder.getSystemLogger().error(e);
|
throw new BaseException(ErrorCode.GET_RESULTSET_COLUMN_NAME_FAIL.getValue(), ErrorCode.GET_RESULTSET_COLUMN_NAME_FAIL.getText() + (e.getMessage() != null ? "," + e.getMessage() : ""));
|
}
|
}
|
|
public static String[] getColumnTable(ResultSet resultSet) throws BaseException {
|
try {
|
ResultSetMetaData metaData = resultSet.getMetaData();
|
if (metaData != null) {
|
int columnCount = getColumnCount(resultSet);
|
String[] fieldName = new String[columnCount];
|
for (int i = 1; i <= columnCount; i++) {
|
String columnName = metaData.getTableName(i);
|
fieldName[i - 1] = columnName;
|
}
|
return fieldName;
|
}
|
throw new BaseException(ErrorCode.GET_RESULTSET_COLUMN_NAME_FAIL.getValue(), ErrorCode.GET_RESULTSET_COLUMN_NAME_FAIL.getText());
|
} catch (Exception e) {
|
SpringMVCContextHolder.getSystemLogger().error(e);
|
throw new BaseException(ErrorCode.GET_RESULTSET_COLUMN_NAME_FAIL.getValue(), ErrorCode.GET_RESULTSET_COLUMN_NAME_FAIL.getText() + (e.getMessage() != null ? "," + e.getMessage() : ""));
|
}
|
}
|
|
}
|
