JDBC的封装

在学习hibernate的时候对hibernate的对象化操作很感兴趣，看了一下JDBC的API，自己也写了几个查询的对象化，分享出来。

如果大家看过SpringJDBC就会发现Spring的JDBC查询这一块做了更加抽象的封装。下面的几个方法其实就是通过ResultSetMetaData这个类来取到数据。这个类使用了我们常说的metadata模式。

public static Map<String, Object> queryForMap(String sql, Object[] parameters) {Map<String, Object> map = new HashMap<String, Object>();ResultSet rs = null; try {rs = DataAccess.select(sql, parameters);//ResultSetMetaData rsmd = rs.getMetaData();int cols = rsmd.getColumnCount();// 获取每一个字段名List<String> colNames = new ArrayList<String>();// 保存所有的字段for (int i = 0; i < cols; i++) {String colName = rsmd.getColumnName(i + 1);colNames.add(colName);}// 获取数据while (rs.next()) {for (String nm : colNames) {// 遍历一行中的所列Object val = rs.getObject(nm);map.put(nm, val);}}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.closeResultSet(rs);}return map;}

/*** 将结果集自动包装为一个List<T>* @param <T>* @param sql* @param parameters* @param clazz* @return*/@SuppressWarnings("unchecked")public static <T> List<T> selectForList(String sql, Object[] parameters,Class<T> clazz) {ResultSet rs = null;try {rs = DataAccess.select(sql, parameters);return JdbcUtils.createListBean(rs, clazz);} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.closeResultSet(rs);}return null;}

/** *将查询结果自动包装为一个对象 * @param <T> * @param sql * @param parameters * @param clazz * @return */@SuppressWarnings("unchecked")public static <T> T selectForObject(String sql, Object[] parameters,Class<T> clazz) throws NoSuchMethodException {ResultSet rs = null;try {rs = DataAccess.select(sql, parameters);return JdbcUtils.createBean(rs, clazz);} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.closeResultSet(rs);}return null;}/** * 将查询的数据放到List Map中 * 每一行是一个Map，然后放入List中 * @param sql * @param parameters * @return */public static List<Map<String, Object>> selectForListMaps(String sql,Object[] parameters) {// 封装数据用List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();// 声明返回的对象ResultSet rs = null;try {rs = DataAccess.select(sql, parameters);ResultSetMetaData rsmd = rs.getMetaData();int cols = rsmd.getColumnCount();// 获取每一个字段名List<String> colNames = new ArrayList<String>();// 保存所有的字段for (int i = 0; i < cols; i++) {String colName = rsmd.getColumnName(i + 1);colNames.add(colName);}// 获取数据while (rs.next()) {// 一行数据Map<String, Object> mm = new LinkedHashMap<String, Object>();for (String nm : colNames) {// 遍历一行中的所列Object val = rs.getObject(nm);mm.put(nm, val);}// 将这个map放到listlist.add(mm);}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.closeResultSet(rs);}return list;}

import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.math.BigDecimal;import java.sql.Blob;import java.sql.Clob;import java.sql.Connection;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.Collections;import java.util.Date;import java.util.List;import org.apache.commons.beanutils.BeanUtils;public abstract class JdbcUtils {public static final int TYPE_UNKNOWN = Integer.MIN_VALUE;public static void closeConnection(Connection con) {if (con != null) {try {con.close();} catch (SQLException ex) {ex.printStackTrace();} catch (Throwable ex) {}}}public static void closeStatement(Statement stmt) {if (stmt != null) {try {stmt.close();} catch (SQLException ex) {ex.printStackTrace();} catch (Throwable ex) {}}}public static void closeResultSet(ResultSet rs) {if (rs != null) {try {rs.close();} catch (SQLException ex) {ex.printStackTrace();} catch (Throwable ex) {}}}public static Object getResultSetValue(ResultSet rs, String fieldName, Class requiredType) throws SQLException {Object value = null;if (String.class.equals(requiredType)) {value = rs.getString(fieldName);} else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) {value = rs.getBoolean(fieldName);} else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) {value = rs.getByte(fieldName);} else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) {value = rs.getShort(fieldName);} else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) {value = rs.getInt(fieldName);} else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) {value = rs.getLong(fieldName);} else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) {value = rs.getFloat(fieldName);} else if (double.class.equals(requiredType) || Double.class.equals(requiredType)|| Number.class.equals(requiredType)) {value = rs.getDouble(fieldName);} else if (byte[].class.equals(requiredType)) {value = rs.getBytes(fieldName);} else if (java.sql.Date.class.equals(requiredType) || java.util.Date.class.equals(requiredType)) {value = new Date(rs.getDate(fieldName).getTime());} else if (java.sql.Time.class.equals(requiredType)) {value = rs.getTime(fieldName);} else if (java.sql.Timestamp.class.equals(requiredType)) {value = rs.getTimestamp(fieldName);} else if (BigDecimal.class.equals(requiredType)) {value = rs.getBigDecimal(fieldName);} else if (Blob.class.equals(requiredType)) {value = rs.getBlob(fieldName);} else if (Clob.class.equals(requiredType)) {value = rs.getClob(fieldName);} else {}return value;}public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException {String name = resultSetMetaData.getColumnLabel(columnIndex);if (name == null || name.length() < 1) {name = resultSetMetaData.getColumnName(columnIndex);}return name;}public static <T> T createBean(ResultSet rs, Class<T> clazz) {T t = null;Field[] fields = clazz.getDeclaredFields();try {// 获取数据if (rs.next()) {t = (T) clazz.newInstance();for (Field field : fields) {String fieldName = field.getName();Class<? extends Object> fieldType = field.getType();Object value = JdbcUtils.getResultSetValue(rs, fieldName, fieldType);if (null != value) {BeanUtils.setProperty(t, fieldName, value);}}}if (rs.next()) {throw new RuntimeException("查询结果集不止一条数据，请确认SQL语句是否正确");}return t;} catch (SQLException e) {e.printStackTrace();} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}return null;}public static <T> List<T> createListBean(ResultSet rs, Class<T> clazz) {T t = null;Field[] fields = clazz.getDeclaredFields();List<T> tList = new ArrayList<T>();try {// 获取数据while (rs.next()) {t = (T) clazz.newInstance();for (Field field : fields) {String fieldName = field.getName();Class<?> fieldType = field.getType();Object value = JdbcUtils.getResultSetValue(rs, fieldName, fieldType);if (null != value) {BeanUtils.setProperty(t, fieldName, value);}}tList.add(t);}return tList;} catch (SQLException e) {e.printStackTrace();} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}return Collections.EMPTY_LIST;}}