package com.hacker.common;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.beanutils.ConvertUtils;

public class BaseDB {
	private PreparedStatement pstmt = null;
	public void executeUpdate(String sql) throws SQLException{
		BaseDao db = new BaseDao();
		try {
			db.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new SQLException(e+"error"+"增刪改");
		}finally{
			db.close();
		}
	}
	public List executeQuery(String sql,Class cls) throws SQLException{
		List list = new ArrayList();
		BaseDao db = new BaseDao();
		ResultSet rs = null;
		Object o = null;
		try {
			rs = db.executeQuery(sql);
			int count = rs.getMetaData().getColumnCount();
			String[] fields = new String[count];
			for (int i = 0; i < fields.length; i++) {
				fields[i] = rs.getMetaData().getColumnName(i+1);
			}
			while(rs.next()){
				o = cls.newInstance();
				for (int i = 0; i < fields.length; i++) {
					Field field = cls.getDeclaredField(fields[i]);
					Method method = cls.getDeclaredMethod("set"+fields[i].substring(0,1).toUpperCase()+fields[i].substring(1),new Class[]{field.getType()});
					method.invoke(o, new Object[]{ConvertUtils.convert(rs.getString(fields[i]),
							field.getType())});
				}
				list.add(o);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new SQLException(e+"error"+"查詢");
		}finally{
			db.close();
		}
		return list;	
	}
	public Object executeSingleQuery(String sql,Class cls){
		try {
			List list = this.executeQuery(sql, cls);
			//System.out.println(list.size());
			if(list != null && list.size() > 0){
				return list.get(0);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public List executeQuery(String sql) throws SQLException{
		List list = new ArrayList();
		BaseDao db = new BaseDao();
		ResultSet rs = null;
		Object o = null;
		try {
			rs = db.executeQuery(sql);
			int count = rs.getMetaData().getColumnCount();
			String[] fields = new String[count];
			for (int i = 0; i < fields.length; i++) {
				fields[i] = rs.getMetaData().getColumnName(i+1);
			}
			while(rs.next()){
				HashMap mp = new HashMap();
				for (int i = 0; i < fields.length; i++) {
					String value = rs.getString(fields[i]);
					mp.put(fields[i], value);
				}
				list.add(mp);
			}
		}catch(SQLException e){
			throw new SQLException(e+"查詢1");
		}finally{
			db.close();
		}
		
		return list;
	}
	public List executeQueryold(String sql,Class cls)
	{
		List list = new ArrayList();
		BaseDao db = new BaseDao();
		ResultSet rs;
		try {
			rs = db.executeQuery(sql);
			
			Field[] fields = cls.getDeclaredFields();
			
			while(rs.next())
			{
				Object o = cls.newInstance();//UserinfoBean bean = new UserinfoBean();
				for (int i = 0; i < fields.length; i++) {
					String value = rs.getString(fields[i].getName());
					Method method = cls.getDeclaredMethod("set"+fields[i].getName().substring(0,1).toUpperCase()+fields[i].getName().substring(1), new Class[]{fields[i].getType()});
					method.invoke(o, new Object[]{ConvertUtils.convert(value, fields[i].getType())});
				}
				list.add(o);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			db.close();
		}
		
		return list;
	}
	public PreparedStatement getPstmt(String sql) throws SQLException{
		BaseDao db = new BaseDao();
		pstmt = db.getPstmt(sql);
		return pstmt;
	}
	public void executeUpdate() throws SQLException{
		pstmt.executeUpdate();
	}
}