在查询时候有时候要一条数据，有时候要的是一个结果集，然而有时候返回就是一个统计值，通过对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 get(Class 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 map = new HashMap(); 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 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 List getForList(Class clazz, String sql, Object... args) { List list = new ArrayList(); 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> 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 List transfterMapListToBeanList(Class clazz, List> values) throws InstantiationException, IllegalAccessException, InvocationTargetException { List result = new ArrayList(); T bean = null; if (values.size() > 0) { for (Map m : values) { bean = clazz.newInstance(); for (Map.Entry 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> handleResultSetToMapList( ResultSet resultSet) throws SQLException { List> values = new ArrayList>(); List columnLabels = getColumnLabels(resultSet); Map map = null; while (resultSet.next()) { map = new HashMap(); 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 getColumnLabels(ResultSet resultSet) throws SQLException { List labels = new ArrayList(); 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 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; }}
