Dao设计模式下的JDBC反射机制的多对象查询

简单的说就是查询一条语句返回多个对象（学生，老师…）
Dao.java

package Les_12_使用Dao模式的查询方法;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
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 Les_10_Dao设计模式.JDBC_tools;


public class Dao {
	// 查询多条记录，返回对应对象的集合
	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 resultSet = null;
		try {
			// 1.得到结果集。
			connection = JDBC_tools.getConnection();

			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				preparedStatement.setObject(i + 1, args[i]);
			}
			resultSet = preparedStatement.executeQuery();
			// 2.处理结果集，得到map对应的List
			// 其中一个map对象就是一条记录，Map的key位result 的列名，Map的values 为列的值
			List<Map<String, Object>> val = handleResulSetMapList(resultSet);
			// 3。把Map 的list的List转为clazz对应的集合。
			// 其中map对应的key就是clazz对应的propertyname
			// 而mapde values 即为clazz对应的对象的propertyname的值
			list = transfterMapListToBeanList(clazz, val);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBC_tools.close(connection, preparedStatement, resultSet);
		}
		return list;
	}

	/**
	 * 声成对象的方法
	 * @param clazz
	 * @param val
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 */
	private <T> List<T> transfterMapListToBeanList(Class<T> clazz, List<Map<String, Object>> val)
			throws InstantiationException, IllegalAccessException, InvocationTargetException {
		List<T> result = new ArrayList<T>();
		T bean = null;
		if (val.size() > 0) {
			for (Map<String, Object> m : val) {
				bean = clazz.newInstance();
				for (Map.Entry<String, Object> entry : m.entrySet()) {
					String propertyName = entry.getKey();
					Object values = entry.getValue();
					BeanUtils.setProperty(bean, propertyName, values);
				}
				result.add(bean);
			}
		}
		return result;
	}
	/**
	 * 处理结果集，得到Map的一个List，其中一个Map对象对应一条记录。
	 * @param resultSet
	 * @return
	 * @throws Exception
	 * @throws SQLException
	 */
	private List<Map<String, Object>> handleResulSetMapList(ResultSet resultSet) throws Exception, SQLException {
		// 5.准备一个List<Map<String,Object>>键:存放列的名 值:存放列的值 其中一个Map 对象对应这一条记录
		List<Map<String, Object>> val = 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 columLabel : columnLabels) {
				Object value = resultSet.getObject(columLabel);
				map.put(columLabel, value);
			}
			val.add(map);
		}
		return val;
	}

	/**
	 * 
	 * @param resultSet
	 * @return
	 * @throws Exception
	 * 获取对应的 结果集 ColumnLabel 对应的List
	 */
	private List<String> getColumnLabels(ResultSet resultSet) throws Exception {
		List<String> labels = new ArrayList<String>();
		ResultSetMetaData rsmd = resultSet.getMetaData();
		for (int i = 0; i < rsmd.getColumnCount(); i++) {
			labels.add(rsmd.getColumnLabel(i + 1));
		}
		return labels;
	}
}

Student.java

package Les_12_使用Dao模式的查询方法;

public class Student {

	private int id;
	private String name="wangzijian 111";
	private String grade;

	public Student() {
		super();
	}

	public Student(int id, String name, String grade) {
		super();
		this.id = id;
		this.name = name;
		this.grade = grade;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getGrade() {
		return grade;
	}

	public void setGrade(String grade) {
		this.grade = grade;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", grade=" + grade + "]";
	}

}

demo_test.java

package Les_12_使用Dao模式的查询方法;

import static org.junit.Assert.*;

import java.util.List;
import org.junit.Test;

public class demo_test {
	Dao dao = new Dao();
	@Test
	public void testGetForList() {
		String sql = "select  s_id id,s_grade grade,s_name name from student";
		List<Student> students = dao.getForList(Student.class, sql);
		System.out.println("学生：" + students);
	}
}

JDBC_tools.java

package Les_10_Dao设计模式;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBC_tools {

	// 获取连接
	public static Connection getConnection() {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false";
		String user = "root";
		String password = "root";
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	// 关闭连接 和PreparedStatement(任何时候都不要使用 Statement！！！)
	public static void close(Connection connection, PreparedStatement preparedStatement) {
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// 关闭连接，权限， Resulset
	public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 执行sql语句，可以执行任何添加和修改的操作
	 * 
	 * @param sql:insert
	 *            ,update ,delete 不包含select
	 * @param args:填写sql占位符的可变参数
	 */
	public static void update2(String sql, Object... args) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBC_tools.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				preparedStatement.setObject(i + 1, args[i]);
			}
			preparedStatement.executeUpdate();
			System.out.println("更新成功！！");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBC_tools.close(connection, preparedStatement, null);
		}
	}

	/**
	 * 执行sql的方法
	 * 
	 * @param sql:insert
	 *            ,update ,delete 不包含select
	 * @throws SQLException
	 */
	public static void update(String sql) throws SQLException {
		Connection connection = getConnection();
		// 2.准备插入的SQL语句
		// 3执行插入操作 （1.获取操作sql语句的Statement 对象 2.
		Statement statement = connection.createStatement();
		try {
			// 调用Statement对象的executeUpdate(sql)执行SQL语句执行插入）
			statement.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				/*
				 * 关闭的顺序是：先关闭后获取的，先关闭Statement 后关闭Connection 关闭的顺序可能是
				 * 先关闭后获取的，先关闭Statement 后关闭Connection
				 */
				if (statement != null) {
					// 4.关闭Statement对象
					statement.close();
				}
			} catch (Exception e2) {
				// 关闭connection对象
				connection.close();
			}
		}
	}
}