java jdbc原生util层_JDBC操作数据库工具类（使用阿里Druid原生API创建数据源）

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();

}

}

}

}