Java获取数据库各种查询结果

在查询时候有时候要一条数据，有时候要的是一个结果集，然而有时候返回就是一个统计值，通过对ResultSet和ResultSetMetaData的变换得到各类所需的查询结果，因为没有利用连接池数据链接管理比较麻烦，所以谢了一个工具类，

package com.sky.connect;import java.lang.reflect.InvocationTargetException;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.beanutils.BeanUtils;import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;import com.mysql.jdbc.ResultSetMetaData;/** * DAO设计模式 *  * @author 潘琢文 *  */public class DAO {/** * 更新数据库操作 *  * @param sql * @param args */public void update(String sql, Object... args) {Connection connection = null;PreparedStatement preparedStatement = null;try {connection = JDBCTools.getConnection();preparedStatement = (PreparedStatement) connection.prepareStatement(sql);for (int i = 0; i < args.length; i++) {preparedStatement.setObject(i + 1, args[i]);}preparedStatement.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {JDBCTools.release(preparedStatement, connection);}}/** * 通用查询方法，返回一条记录 *  * @param clazz * @param sql * @param args * @return */public <T> T get(Class<T> clazz, String sql, Object... args) {T entity = null;Connection connection = null;PreparedStatement preparedStatement = null;ResultSet result = null;try {connection = JDBCTools.getConnection();preparedStatement = (PreparedStatement) connection.prepareStatement(sql);for (int i = 0; i < args.length; i++) {preparedStatement.setObject(i + 1, args[i]);}result = preparedStatement.executeQuery();Map<String, Object> map = new HashMap<String, Object>();ResultSetMetaData rsmd = (ResultSetMetaData) result.getMetaData();if (result.next()) {for (int i = 0; i < rsmd.getColumnCount(); i++) {String columnLabel = rsmd.getColumnLabel(i + 1);Object value = result.getObject(i + 1);map.put(columnLabel, value);}}if (map.size() > 0) {entity = clazz.newInstance();for (Map.Entry<String, Object> entry : map.entrySet()) {String filedName = entry.getKey();Object filedObject = entry.getValue();BeanUtils.setProperty(entity, filedName, filedObject);}}} catch (Exception e) {e.printStackTrace();} finally {JDBCTools.release(result, preparedStatement, connection);}return entity;}/** * 通用查询方法，返回一个结果集 *  * @param clazz * @param sql * @param args * @return */public <T> List<T> getForList(Class<T> clazz, String sql, Object... args) {List<T> list = new ArrayList<T>();Connection connection = null;PreparedStatement preparedStatement = null;ResultSet result = null;try {connection = JDBCTools.getConnection();preparedStatement = (PreparedStatement) connection.prepareStatement(sql);for (int i = 0; i < args.length; i++) {preparedStatement.setObject(i + 1, args[i]);}result = preparedStatement.executeQuery();List<Map<String, Object>> values = handleResultSetToMapList(result);list = transfterMapListToBeanList(clazz, values);} catch (Exception e) {e.printStackTrace();} finally {JDBCTools.release(result, preparedStatement, connection);}return list;}/** *  * @param clazz * @param values * @return * @throws InstantiationException * @throws IllegalAccessException * @throws InvocationTargetException */public <T> List<T> transfterMapListToBeanList(Class<T> clazz,List<Map<String, Object>> values) throws InstantiationException,IllegalAccessException, InvocationTargetException {List<T> result = new ArrayList<T>();T bean = null;if (values.size() > 0) {for (Map<String, Object> m : values) {bean = clazz.newInstance();for (Map.Entry<String, Object> entry : m.entrySet()) {String propertyName = entry.getKey();Object value = entry.getValue();BeanUtils.setProperty(bean, propertyName, value);}// 13. 把 Object 对象放入到 list 中.result.add(bean);}}return result;}/** *  * @param resultSet * @return * @throws SQLException */public List<Map<String, Object>> handleResultSetToMapList(ResultSet resultSet) throws SQLException {List<Map<String, Object>> values = new ArrayList<Map<String, Object>>();List<String> columnLabels = getColumnLabels(resultSet);Map<String, Object> map = null;while (resultSet.next()) {map = new HashMap<String, Object>();for (String columnLabel : columnLabels) {Object value = resultSet.getObject(columnLabel);map.put(columnLabel, value);}values.add(map);}return values;}/** *  * @param resultSet * @return * @throws SQLException */private List<String> getColumnLabels(ResultSet resultSet)throws SQLException {List<String> labels = new ArrayList<String>();ResultSetMetaData rsmd = (ResultSetMetaData) resultSet.getMetaData();for (int i = 0; i < rsmd.getColumnCount(); i++) {labels.add(rsmd.getColumnLabel(i + 1));}return labels;}/** * 通用查询方法，返回一个值（可能是统计值） *  * @param sql * @param args * @return */@SuppressWarnings("unchecked")public <E> E getForValue(String sql, Object... args) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {connection = JDBCTools.getConnection();preparedStatement = (PreparedStatement) connection.prepareStatement(sql);for (int i = 0; i < args.length; i++) {preparedStatement.setObject(i + 1, args[i]);}resultSet = preparedStatement.executeQuery();if (resultSet.next()) {return (E) resultSet.getObject(1);}} catch (Exception e) {e.printStackTrace();} finally {JDBCTools.release(resultSet, preparedStatement, connection);}return null;}}

package com.sky.connect;import java.io.IOException;import java.io.InputStream;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;import com.mysql.jdbc.Connection;import com.mysql.jdbc.Driver;import com.mysql.jdbc.PreparedStatement;import com.mysql.jdbc.Statement;/** * JDBC操作的工具类 版本 1.0 *  * @author 潘琢文 *  */public class JDBCTools {/** * 使用preparedStatement进行数据更新 *  * @param sql * @param args */public static void update(String sql, Object ... args) {Connection connection = null;PreparedStatement preparedStatement = null;try {connection = JDBCTools.getConnection();preparedStatement = (PreparedStatement) connection.prepareStatement(sql);for (int i = 0; i < args.length; i++) {preparedStatement.setObject(i + 1, args[i]);}preparedStatement.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {JDBCTools.release(preparedStatement, connection);}}/** * 结果查询关闭 *  * @param rs * @param statement * @param conn */public static void release(ResultSet rs, Statement statement,Connection conn) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (Exception e2) {e2.printStackTrace();}}if (conn != null) {try {conn.close();} catch (Exception e2) {e2.printStackTrace();}}}/** * 数据库更新方法 *  * @param sql */public void uodate(String sql) {Connection connection = null;Statement statement = null;try {connection = JDBCTools.getConnection();statement = (Statement) connection.createStatement();statement.executeUpdate(sql);} catch (Exception e) {e.printStackTrace();} finally {JDBCTools.release(statement, connection);}}/** * 关闭数据库连接的方法 *  * @param statement * @param conn */public static void release(Statement statement, Connection conn) {if (statement != null) {try {statement.close();} catch (Exception e2) {e2.printStackTrace();}}if (conn != null) {try {conn.close();} catch (Exception e2) {e2.printStackTrace();}}}/** * 编写通用方法获取任意数据库链接，不用修改源程序 *  * @return * @throws ClassNotFoundException * @throws IllegalAccessException * @throws InstantiationException * @throws SQLException * @throws IOException */public static Connection getConnection() throws InstantiationException,IllegalAccessException, ClassNotFoundException, SQLException,IOException {String driverClass = null;String jdbcUrl = null;String user = null;String password = null;// 读取properties文件InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");Properties properties = new Properties();properties.load(in);driverClass = properties.getProperty("driver");jdbcUrl = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");Class.forName(driverClass);Connection connection = (Connection) DriverManager.getConnection(jdbcUrl, user, password);return connection;}}