JDBC Dao层

//Geeksun 2018.07.31
package com.geeksun.one;

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;

public class Dao
{
	//Object ... objects 可变长参数
	public void upDate(String sql,Object ... objects)
	{
		Connection connection = null;
		PreparedStatement prepareStatement = null;
		try{
			connection = JDBCTools.getConnection();
			prepareStatement = connection.prepareStatement(sql);
			///填充占位符
			for(int i = 0;i < objects.length;i++)
			{
				prepareStatement.setObject(i + 1,objects[i]);
			}
			prepareStatement.excuteUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			//此处调用了另一篇博客中的JDBCTools工具类
			//https://blog.csdn.net/geek_sun/article/details/81286303
			JDBCTools.release(connection,prepareStatement,null);
		}
	}
	//得到数据库中某条数据的实例化后的对象
	public <T> T get(Class<T> clazz,String sql,Object ... objects)
	{
		T entity = null;
		Connection conn = null;
		PrepareStatement prepareStatement = null;
		ResultSet resultSet = null;
		ResultSetMetaData rsmd = null;
		try{
			conn = JDBCTools.getConnection();
			prepareStatement = conn.prepareStatement(sql);
			for(int i = 0;i < objects.length;i++)
			{
				prepareStatement.setObject(i + 1,objects[i]);
			}
			resultSet = prepareStatement.excuteQuery();
			//若ResultSet中有记录，准备一个Map<String,Object>键：存放列的别名 值：存放列的值
			if(resultSet.next())
			{
				Map<String,Object> values = new HashMap<String,Object>();
				//由ResultSetMetaData得到每一列的别名，由ResultSet得到每一列的值
				rsmd = result.getMetaData();
				for(int i = 1;i <= rsmd.getColumnCount();i++)
				{
					//用getColumnName查出的是表中的字段名，用getColumnLabel查出的是在sql语句后面重新定义的字段名
					String columnLabel = rsmd.getColumnLabel(i);
					Object object = resultSet.getObject(columnLabel);
					//填充Map对象
					values.put(columnlabel,object);
				}
				//用反射创建Class对应的对象
				entity = clazz.newInstance();
				//遍历Map对象，用反射填充对象的属性值：属性名为Map中的Key，属性值为Map中的value
				//values.entrySet()返回Map.Entry实例化后的对象集
				for(Map.Entry<String,Object> entry:values.entrySet())
				{
					String propertyName = entry.getKey();
					Object value = entry.getValue();
					//ReflectionUtils.setFieldValue(entity, propertyName, value);
					BeanUtils.setProperty(entity, propertyName, value);
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCTools.release(conn, prepareStatement, resultSet);
		}
		return entity;
	}
	//得到数据库中某些数据的实例化后的对象列表
	public <T> List<T> getForList(Class<T> clazz,String sql,Object ... objects)
	{
		List<T> list = new ArrayList<T>();
		T entity = null;
		Connection conn = null;
		PrepareStatement prepareStatement = null;
		ResultSet resultSet = null;
		ResultSetMetaData rsmd = null;
		try{
			conn = JDBCTools.getConnection();
			prepareStatement = conn.prepareStatement(sql);
			for(int i = 0;i < objects.length;i++)
			{
				prepareStatement.setObject(i + 1,objects[i]);
			}
			resultSet = prepareStatement.excuteQuery();
			List<Map<String,Object>> values = handleResultSetToMapList(resultSet);
			list = transferMapListToBeanList(clazz,values);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCTools.release(conn,prepareStatement,resultSet);
		}
		return list;
	}
	//获取某一特定值
	public <E> E gerForValue(String sql,Object ...objects)
	{
		Connection conn = null;
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		ResultSetMetaData rsmd = null; 
		try
		{
			conn = JDBCTools.getConnection();
			prepareStatement = conn.prepareStatement(sql);
			//填充占位符
			for(int i = 0;i < objects.length;i++)
			{
				prepareStatement.setObject(i + 1, objects[i]);
			}
			resultSet = prepareStatement.executeQuery();
			if(resultSet.next())
			{
				//获取某一列的值
				return (E)resultSet.getObject(1);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCTools.release(conn, prepareStatement, resultSet);
		}
		return null;
	}
	private List<Map<String, Object>> handleResultSetToMapList(ResultSet resultSet) throws SQLException
	{
		Map<String,Object> map = null;
		ResultSetMetaData rsmd = resultSet.getMetaData();
		List <Map<String,Object>> values = new ArrayList<>();
		while(resultSet.next())
		{
			map = new HashMap<String,Object>();
			List<String> columnLabels = this.getColumnLabels(resultSet);
			for(String columnLabel: columnLabels)
			{
				Object value = resultSet.getObject(columnLabel);
				map.put(columnLabel, value);
			}
			values.add(map);
		}
		return values;
	}
	private <T> List<T> transferMapListToBeanList(Class<T> clazz, List<Map<String, Object>> values)
			throws InstantiationException, IllegalAccessException, InvocationTargetException
	{
		List<T> result = new ArrayList<>();
		//JavaBean 是一种JAVA语言写成的可重用组件。为写成JavaBean，类必须是具体的和公共的，并且具有无参数的构造器
		T bean = null;
		if(values.size() > 0)
		{
			for(Map<String>)
		}
	}
	private List<String> getColumnLabels(ResultSet rs) throws SQLException
	{
		List<String> list = new ArrayList<>();
		ResultSetMetaData rsmd = rs.getMetaData();
		for(int i = 0;i < rsmd.getColumnCount();i++)
		{
			list.add(rsmd.getColumnLabel(i + 1));
		}
		return list;
	}
	
}