packagecom.zdlt.auth.api.common.druid;importlombok.extern.slf4j.Slf4j;importjavax.sql.DataSource;import java.sql.*;importjava.util.ArrayList;importjava.util.LinkedHashMap;importjava.util.List;importjava.util.Map;/*** 数据库操作工具类
*
*@authorshiyanjun
*@since2019-12-15*/@Slf4jpublic classDbUtil {/*** 获取数据库连接
*
*@paramdataSource 数据源
*@return连接
*@throwsSQLException*/
public static Connection getConnection(DataSource dataSource) throwsSQLException {returndataSource.getConnection();
}/*** 获取模型项实例数据源
*
*@return数据源
*@throwsException*/
public staticDataSource getDMInstanceDataSource() {returnDMInstanceDb.INSTANCE.getDataSource();
}/*** 执行查询SQL语句
*
*@paramdataSource 数据源
*@paramquerySql 查询SQL语句
*@return查询结果*/
public static List>executeQuery(DataSource dataSource, String querySql) {if (dataSource == null) {throw new RuntimeException("DataSource not be null!");
}if (querySql == null || querySql.trim().length() == 0) {throw new RuntimeException("Query SQL not be empty!");
}
List> resultList = new ArrayList<>();
Connection connection= null;
Statement statement= null;
ResultSet resultSet= null;try{
connection=DbUtil.getConnection(dataSource);
statement=connection.createStatement();
resultSet=statement.executeQuery(querySql);
ResultSetMetaData metaData=resultSet.getMetaData();
resultList=getResultMap(resultSet, metaData);
}catch(SQLException e) {
e.printStackTrace();
}finally{
DbUtil.closeResource(resultSet, statement, connection);
}returnresultList;
}/*** 解析结果集
*
*@paramrs 结果集
*@parammd 结果集元数据
*@return表数据
*@throwsSQLException*/
private static List> getResultMap(ResultSet rs, ResultSetMetaData md) throwsSQLException {
List> resultList = new ArrayList<>();int columnCount =md.getColumnCount();while(rs.next()) {
Map resultMap = new LinkedHashMap<>();for (int i = 1; i <= columnCount; i++) {
String columnName=md.getColumnLabel(i);
Object columnVal=getType(rs, md, columnName, i);
resultMap.put(columnName, columnVal);
}
resultList.add(resultMap);
}returnresultList;
}/*** 根据字段名称和字段类型获取字段的值
*
*@paramrs 结果集
*@parammd 结果集元数据
*@paramcolumnName 字段名称
*@paramindex 字段序号
*@return字段的值
*@throwsSQLException*/
private staticObject getType(ResultSet rs, ResultSetMetaData md,
String columnName,int index) throwsSQLException {int columnType =md.getColumnType(index);switch(columnType) {caseTypes.ARRAY:returnrs.getArray(columnName);caseTypes.BIGINT:returnrs.getInt(columnName);caseTypes.BOOLEAN:returnrs.getBoolean(columnName);caseTypes.BLOB:returnrs.getBlob(columnName);caseTypes.DOUBLE:returnrs.getDouble(columnName);caseTypes.FLOAT:returnrs.getFloat(columnName);caseTypes.INTEGER:returnrs.getInt(columnName);caseTypes.NVARCHAR:returnrs.getNString(columnName);caseTypes.VARCHAR:returnrs.getString(columnName);caseTypes.TINYINT:returnrs.getInt(columnName);caseTypes.SMALLINT:returnrs.getInt(columnName);caseTypes.DATE:returnrs.getDate(columnName);caseTypes.TIMESTAMP:returnrs.getTimestamp(columnName);default:returnrs.getObject(columnName);
}
}/*** 关闭资源
*
*@paramstatement 语句执行器
*@paramconnection 连接*/
public static voidcloseResource(Statement statement, Connection connection) {
closeStatement(statement);
closeConnection(connection);
}/*** 关闭资源
*
*@paramresultSet 结果集
*@paramstatement 语句执行器
*@paramconnection 连接*/
public static voidcloseResource(ResultSet resultSet, Statement statement, Connection connection) {
closeResultSet(resultSet);
closeStatement(statement);
closeConnection(connection);
}/*** 关闭结果集
*
*@paramresultSet 结果集*/
public static voidcloseResultSet(ResultSet resultSet) {if (resultSet != null) {try{
resultSet.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}/*** 关闭语句执行器
*
*@paramstatement 语句执行器*/
public static voidcloseStatement(Statement statement) {if (statement != null) {try{
statement.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}/*** 关闭连接
*
*@paramconnection 连接*/
public static voidcloseConnection(Connection connection) {if (connection != null) {try{
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}