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放到list
				list.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;
	}

}