package com.wenqier.dao;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ParameterMetaData;
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.List;

import com.wenqier.dbcon.DBConnection;

/**
 * 数据库操作Dao层
 * 
 * @author wenqier
 * 
 */
public abstract class Dao {

	protected Connection conn = null;
	protected Statement stmt = null;
	protected PreparedStatement pstmt = null;
	protected ResultSet rs = null;

	public abstract Class getObjectClass();

	/**
	 * 据sql获取数据
	 * 
	 * @param sql
	 * @return data查询结果集
	 */
	public List queryInfoBySql(String sql) {
		// 用于接收返回
		List data = new ArrayList();
		Class cls = this.getObjectClass();

		conn = DBConnection.getConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			// rs存储结果集类
			ResultSetMetaData rsmd = rs.getMetaData();
			Class rscls = rs.getClass();
			int cols = rsmd.getColumnCount();// rs中数据列数
			while (rs.next()) {
				Object obj = cls.newInstance();
				for (int i = 1; i <= cols; i++) {
					// 据列名拼属性名
					String fieldName = this.underlineToUpper(rsmd
							.getColumnName(i).toLowerCase());
					// 据属性名获得属性对象
					Field field = cls.getDeclaredField(fieldName);
					// 据属性名拼该属性的set方法名
					String setStr = "set"
							+ Character.toUpperCase(fieldName.charAt(0))
							+ fieldName.substring(1);
					// 据set方法名获取set方法对象
					Method setMethod = cls.getDeclaredMethod(setStr,
							field.getType());
					// 据属性类型拼rs的get方法名
					String rsget = "get"
							+ Character.toUpperCase(field.getType()
									.getSimpleName().charAt(0))
							+ field.getType().getSimpleName().substring(1);
					// 据rs的get方法名获取get方法
					Method rsgetMethod = rscls.getDeclaredMethod(rsget,
							int.class);

					setMethod.invoke(obj, rsgetMethod.invoke(rs, i));
				}
				data.add((Object) obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConnStmtRs(conn, pstmt, rs);
		}
		return data;
	}
	
	/**
	 * 向数据库添加数据
	 * 
	 * @param pojo
	 *            传入要添加的对象
	 */
	public void saveInfo(Object obj) {
		Class cls = this.getObjectClass();
		conn = DBConnection.getConnection();
		try {
			pstmt = conn.prepareStatement(this.getSaveSql(obj));
			// 获取pstmt的类对象
			Class pstmtCls = pstmt.getClass();
			// 获取实体类的属性
			Field[] field = this.getObjectClass().getDeclaredFields();
			for (int i = 0; i < field.length; i++) {
				// 获取属性名
				String fieldName = field[i].getName();
				// 获取属性类型名
				String fieldTypeName = field[i].getType().getSimpleName();
				// 拼接get和set方法
				String fieldGetName = "get"
						+ Character.toUpperCase(fieldName.charAt(0))
						+ fieldName.substring(1);
				String pstmtSetName = "set"
						+ Character.toUpperCase(fieldTypeName.charAt(0))
						+ fieldTypeName.substring(1);
				// 获取方法对象
				Method fieldGetMethod = cls.getDeclaredMethod(fieldGetName,
						null);
				Method pstmtSetMethod = pstmtCls.getDeclaredMethod(
						pstmtSetName, int.class, field[i].getType());
				// 调用方法
				pstmtSetMethod.invoke(pstmt, i + 1,
						fieldGetMethod.invoke(obj, null));
			}
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConnPstmt(conn, pstmt);
		}
	}

	/**
	 * 保存多组信息 批处理
	 * 
	 * @param list
	 */
	public void saveListInfo(List list) {
		Class cls = this.getObjectClass();
		conn = DBConnection.getConnection();
		// 获取类中个属性
		Field[] field = cls.getDeclaredFields();
		try {
			pstmt = conn.prepareStatement(this.getSaveSql(cls.newInstance()));
			Class pstmtCls = pstmt.getClass();
			for (int i = 0; i < list.size(); i++) {
				for (int j = 0; j < field.length; j++) {
					// 获取属性名字和类型名
					String fieldName = field[j].getName();
					String fieldTypeName = field[j].getType().getSimpleName();
					// 拼接get和set方法名以便获取相应的方法对象
					String fieldGetName = "get"
							+ Character.toUpperCase(fieldName.charAt(0))
							+ fieldName.substring(1);
					String pstmtSetName = "set"
							+ Character.toUpperCase(fieldTypeName.charAt(0))
							+ fieldTypeName.substring(1);
					// 据get和set的名称获取相应的方法对象
					Method fieldGetMethod = cls.getDeclaredMethod(fieldGetName,
							null);
					Method pstmtSetMethod = pstmtCls.getDeclaredMethod(
							pstmtSetName, int.class, field[j].getType());
					// 调用方法
					pstmtSetMethod.invoke(pstmt, j + 1,
							fieldGetMethod.invoke(list.get(i), null));
				}
				// 加入缓冲池
				pstmt.addBatch();
			}
			// 缓冲池提交
			pstmt.executeBatch();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConnPstmt(conn, pstmt);
		}
	}

	/**
	 * 删除数据库数据
	 * 
	 * @param id
	 */
	public void deleteInfo(String sql) {
		this.daoBySql(sql);
	}

	/**
	 * 修改数据库信息
	 * 
	 * @param obj
	 *            传入要修改的对象
	 * @param no
	 */
	public void updateInfo(String sql) {
		this.daoBySql(sql);
	}

	/**
	 * dao层由sql语句访问数据库，注意只可以增删改，返回值为void
	 * 
	 * @param sql
	 */
	public void daoBySql(String sql) {
		conn = DBConnection.getConnection();
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConnStmt(conn, stmt);
		}
	}

	/**
	 * 把第二个单词首字母大写字符串转换为以下划线分隔各单词字符串
	 * 
	 * @param 第二个单词首字母大写的字符串upper
	 * @return 下划线字符串
	 */
	private String upperToUnderline(String upper) {
		StringBuffer buff = new StringBuffer();
		buff.append(Character.toLowerCase(upper.charAt(0)));
		for (int i = 1; i < upper.length(); i++) {
			char c = upper.charAt(i);
			// 判断是否为大写，true需要buff加“_”后，字母小写加在buff后，false直接加在buff后
			if (Character.isUpperCase(c)) {
				buff.append("_" + Character.toLowerCase(c));
			} else {
				buff.append(c);
			}
		}
		return buff.toString();
	}

	/**
	 * 把下滑线的字符串转换为第二单词首字母大写的字符串
	 * 
	 * @param underline
	 * @return 第二单词大写的字符串
	 */
	private String underlineToUpper(String underline) {
		// 据”_“拆分列名
		String[] st = underline.split("_");
		StringBuffer sb = new StringBuffer();
		sb.append(st[0]);
		for (int i = 1; i < st.length; i++) {
			sb.append(Character.toUpperCase(st[i].charAt(0))).append(
					st[i].substring(1));
		}
		return sb.toString();
	}

	/**
	 * 获取保存数据的sql语句
	 * 
	 * @param obj
	 *            保存的对象
	 * @return 数据库保存数据的sql
	 */
	protected String getSaveSql(Object obj) {
		Class cls = this.getObjectClass();
		Field[] fields = cls.getDeclaredFields();
		StringBuffer sb = new StringBuffer();
		sb.append("insert into " + this.upperToUnderline(cls.getSimpleName())
				+ " values(");
		for (int i = 0; i < fields.length; i++) {
			sb.append("?,");
		}
		sb.delete(sb.length() - 1, sb.length());
		sb.append(")");
		return sb.toString();
	}

}