JDBC（六）初级的JDBCTools

1. 我们已经写了通用的更新以及通用的查询

但缺少 查询多条数据、查询一行一列 的方法的封装，我直接补充完整

package com.jdbc.dason;import java.beans.Beans;import java.io.InputStream;import java.lang.reflect.Field;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;import org.apache.commons.beanutils.BeanUtils;public class JDBCTools {/** * 获取数据库资源 *  * @return * @throws Exception */public static Connection getConnection() throws Exception {String driverClass = null;String jdbcUrl = null;String user = null;String password = null;// 读取类路径下的 jdbc.properties 文件InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");Properties properties = new Properties();properties.load(in);driverClass = properties.getProperty("driverClass");jdbcUrl = properties.getProperty("jdbcUrl");user = properties.getProperty("user");password = properties.getProperty("password");// 2. 加载数据库驱动程序(对应的 Driver 实现类中有注册驱动的静态代码块.)Class.forName(driverClass);// 3. 通过 DriverManager 的 getConnection() 方法获取数据库连接.Connection connection = DriverManager.getConnection(jdbcUrl, user,password);return connection;}/** * 释放数据库资源的方法 *  * @param resultSet * @param statement * @param connection */public static void releaseDB(ResultSet resultSet, Statement statement,Connection connection) {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}/** * 通用的更新方法,包括 insert,update,detele 语句.  * ① PreparedStatement 是通过 ?占位符来填充条件的,那我们也得将填充占位符的参数也传过来 * ② 解决这一问题可以选择 可变参数数组 * @param sql * @param args : 可变参数. * @throws Exception */public static void update(String sql, Object... args) throws Exception {Connection conn = null;PreparedStatement ps = null;try {conn = getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}ps.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {releaseDB(null, ps, conn);}}/** * 通用的查询方法 返回一条数据, 只有select 语句. 返回一个泛型类型的对象 *  * @param clazz * @param sql * @param args * @return */public static <T> T serach(Class<T> clazz, String sql, Object... args) {T entity = null;Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();/** * 如何将结果集中的值怎么赋值给对象  * ① ResultSetMetaData.getColumnCount():ResultSet的列的个数  * ② ResultSetMetaData.getColumnLabel(int):ResultSet的列的别名，参数从1开始  * ③ ResultSet.getObject(int): 列的值  * ④ 利用别名是我们定义的，我们就可以将别名与类属性名联系起来,就可以利用反射将值赋值给对象 */ResultSetMetaData rsmd = rs.getMetaData();// 将别名,列值存入mapMap<String, Object> map = new HashMap<String, Object>();if (rs.next()) {for (int i = 0; i < rsmd.getColumnCount(); i++) {map.put(rsmd.getColumnLabel(i + 1), rs.getObject(i + 1));}}// 结果集不能为空if (map.size() > 0) {// 创建对象entity = clazz.newInstance();// 遍历map, entry 的key 是属性名, value 是属性值for (Map.Entry<String, Object> entry : map.entrySet()) {// 获取对象指定的属性Field field = clazz.getDeclaredField(entry.getKey());// 让属性变为可访问（更改）field.setAccessible(true);// 将 entry.getValue() 赋值给 entity 的field 属性field.set(entity, entry.getValue());}}} catch (Exception e) {e.printStackTrace();} finally {releaseDB(rs, ps, conn);}return entity;}/** * 查询 返回多条数据. *  * @param clazz * @param sql * @param args * @return List<T>: ben 的list  */public static <T> List<T> serachAll(Class<T> clazz, String sql,Object... args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;T bean = null;List<T> list = null;try {//1.得到结果集conn = getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();//2.处理结果集 ,创建一个List<Map<String, Object>>,其中一个map 存放一条记录ResultSetMetaData rsmd = rs.getMetaData();List<Map<String, Object>> listOfMap = new ArrayList<Map<String,Object>>();Map<String, Object> map = null;while(rs.next()){map = new HashMap<String, Object>();for(int i = 0; i < rsmd.getColumnCount(); i++){map.put(rsmd.getColumnLabel(i + 1), rs.getObject(i+1));}listOfMap.add(map);}//3.将一个个map 转为 bean，存入list中if(listOfMap.size() > 0 ){list = new ArrayList<T>();for(Map<String, Object> m : listOfMap){bean = clazz.newInstance();for(Map.Entry<String,Object> entry : m.entrySet()){//Field field = clazz.getDeclaredField(entry.getKey());//field.setAccessible(true);//field.set(bean, entry.getValue());//BeanUtils :Apache 封装的处理bean 的类//需导入: ommons-beanutils-1.8.0.jar、commons-logging-1.1.1.jarBeanUtils.setProperty(bean, entry.getKey(), entry.getValue());}list.add(bean);}}} catch (SQLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();} finally{releaseDB(rs, ps, conn);}return list;}/** * 查询 返回某条记录的第一列的值  *  * @param sql * @param args * @return */public static <E> E serachForValue(String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();if (rs.next()) {return (E) rs.getObject(1);}} catch (SQLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();} finally {releaseDB(rs, ps, conn);}return null;}}

2. 发现很多代码都有雷同的地方，可以进一步优化：

package com.jdbc.dason;import java.io.InputStream;import java.lang.reflect.InvocationTargetException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;import org.apache.commons.beanutils.BeanUtils;public class JDBCTools {/** * 获取数据库资源 *  * @return * @throws Exception */public static Connection getConnection() throws Exception {String driverClass = null;String jdbcUrl = null;String user = null;String password = null;// 读取类路径下的 jdbc.properties 文件InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");Properties properties = new Properties();properties.load(in);driverClass = properties.getProperty("driverClass");jdbcUrl = properties.getProperty("jdbcUrl");user = properties.getProperty("user");password = properties.getProperty("password");// 2. 加载数据库驱动程序(对应的 Driver 实现类中有注册驱动的静态代码块.)Class.forName(driverClass);// 3. 通过 DriverManager 的 getConnection() 方法获取数据库连接.Connection connection = DriverManager.getConnection(jdbcUrl, user,password);return connection;}/** * 释放数据库资源的方法 *  * @param resultSet * @param statement * @param connection */public static void releaseDB(ResultSet resultSet, Statement statement,Connection connection) {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}/** * 通用的更新方法,包括 insert,update,detele 语句.  * ① PreparedStatement 是通过 ?占位符来填充条件的,那我们也得将填充占位符的参数也传过来 * ② 解决这一问题可以选择 可变参数数组 * @param sql * @param args : 可变参数. * @throws Exception */public static void update(String sql, Object... args) throws Exception {Connection conn = null;PreparedStatement ps = null;try {conn = getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}ps.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {releaseDB(null, ps, conn);}}/** * 通用的查询方法 返回一条数据, 只有select 语句. 返回一个泛型类型的对象 *  * @param clazz * @param sql * @param args * @return */public static <T> T serach(Class<T> clazz, String sql, Object... args) {//直接调用查询多条 serachAll() 方法,返回的 list<T> 只有一个clazz 对象 List<T> list = serachAll(clazz, sql, args);if(list.size() > 0){return list.get(0);}return null;}/** * 查询 返回多条数据. *  * @param clazz * @param sql * @param args * @return */public static <T> List<T> serachAll(Class<T> clazz, String sql,Object... args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;List<T> list = null;try {//1.得到结果集conn = getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();//2.处理结果集 ,创建一个List<Map<String, Object>>,其中一个map 对象存放一条记录List<Map<String, Object>> listOfMap = resultSetToList(rs);//3.将一个个map 转为 bean,存入list中list = listMapToListBean(clazz, listOfMap);} catch (SQLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();} finally{releaseDB(rs, ps, conn);}return list;}/** * 查询 返回某条记录的第一列的值  *  * @param sql * @param args * @return */public static <E> E serachForValue(String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = getConnection();ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();if (rs.next()) {return (E) rs.getObject(1);}} catch (SQLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();} finally {releaseDB(rs, ps, conn);}return null;}/*------------------以下方法都是优化抽取出来的方法--------------------*//** * 处理结果集 ,将resultSet 转为 List<Map<String, Object>> * 其中一个map 存放一条记录  *  * @param rs * @return * @throws SQLException */private static List<Map<String, Object>> resultSetToList(ResultSet rs)throws SQLException {List<Map<String, Object>> listOfMap = new ArrayList<Map<String,Object>>();Map<String, Object> map = null;List<String> labels = getColumnLabels(rs);while(rs.next()){map = new HashMap<String, Object>();for(String label : labels){map.put(label, rs.getObject(label));}listOfMap.add(map);}return listOfMap;}/**将  List<Map<String, Object>> 转为 List<T> * 其中List<Map<String, Object>> 中的每一个map对应 一条记录,key:列的别名,value:列的值 * List<T>: bean 的list 集合 *  * @param clazz * @param listOfMap * @return * @throws InstantiationException * @throws IllegalAccessException * @throws InvocationTargetException */private static <T> List<T> listMapToListBean(Class<T> clazz,List<Map<String, Object>> listOfMap) throws InstantiationException,IllegalAccessException, InvocationTargetException {T bean = null;List<T> list =null;if(listOfMap.size() > 0 ){list = new ArrayList<T>();//遍历 List<Map<String, Object>>for(Map<String, Object> m : listOfMap){bean = clazz.newInstance();//遍历 Map<String, Object>,将for(Map.Entry<String,Object> entry : m.entrySet()){BeanUtils.setProperty(bean, entry.getKey(), entry.getValue());}list.add(bean);}}return list;}/**  * 获取ResultSet 的columnLabels 对应的list *  * @param rs * @return * @throws SQLException */private static List<String> getColumnLabels(ResultSet rs) throws SQLException{List<String> labels = new ArrayList<String>();ResultSetMetaData rsmd = rs.getMetaData();for(int i = 0; i < rsmd.getColumnCount(); i++){labels.add(rsmd.getColumnLabel(i + 1));}return labels;}}