package com.scitel.gdnumcommon.utils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.scitel.gdnumcommon.entity.Pagination;
import com.scitel.gdnumcommon.entity.BaseVO;


public class BaseDAO {
?private static final Log log = LogFactory.getLog(BaseDAO.class);

?/**
? * 保存數據,新建和修改都用這個
? *
? * @param con
? * @param SQL
? * @param params
? * @throws Exception
? * @author
? */
?public void save(Connection con, String SQL, List params)
???throws Exception {
??PreparedStatement ps = null;
??try {
???ps = con.prepareStatement(SQL);
???if (SQL == null) {
????throw new Exception();
???}

???if (params != null && params.size() > 0) {
????int count = 0;
????for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
?????Object object = iterator.next();
?????setObjectValue(ps, count + 1, object);
????}
???}
???ps.executeUpdate();
??} catch (Exception e) {
???log.error(e);
???throw e;
??} finally {
???try{
????if(ps != null) {
?????ps.close();
????}
???}catch(Exception e){

???}
??}
?}

?/**
? * 保存數據,新建和修改都用這個,通過字段名稱匹配類型
? * @param con
? * @param SQL
? * @param voclass
? * @param paramMap
? * @throws Exception
? * @author
? */
?public void save(Connection con, String SQL, Class voclass, Map paramMap)
???throws Exception {
??PreparedStatement ps = null;
??try {
???ps = con.prepareStatement(SQL);
???if (SQL == null) {
????throw new Exception();
???}

???if (paramMap != null && paramMap.size() > 0) {
????int count = 0;
????for (Iterator iterator = paramMap.keySet().iterator(); iterator.hasNext(); count++) {

?????String key = (String)iterator.next();
?????Object object = paramMap.get(key);
?????setObjectValue(ps, voclass, count+1, key, object);
????}
???}
???ps.executeUpdate();
??} catch (Exception e) {
???log.error(e);
???throw e;
??} finally {
???try{
????if(ps != null) {
?????ps.close();
????}
???}catch(Exception e){

???}
??}
?}

?/**
? * 刪除數據
? *
? * @param con
? * @param SQL
? * @param params
? * @throws Exception
? * @author
? */
?public void remove(Connection con, String SQL, List params)
???throws Exception {
??PreparedStatement ps = null;
??try {
???ps = con.prepareStatement(SQL);
???if (SQL == null) {
????throw new Exception();
???}
???if (params != null && params.size() > 0) {
????int count = 0;
????for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
?????Object object = iterator.next();
?????setObjectValue(ps, count + 1, object);
????}
???}
???ps.executeUpdate();
??} catch (Exception e) {
???log.error(e);
???throw e;
??} finally {
???try{
????if(ps != null) {
?????ps.close();
????}
???}catch(Exception e){

???}
??}
?}

?/**
? * 根據ID選擇數據
? *
? * @param con
? * @param SQL
? * @param id
? * @param voclass
? * @return
? * @throws Exception
? * @author
? */
?public BaseVO selectById(Connection con, String SQL, String id,
???Class voclass) throws Exception {
??Object po = null; // 承載值對象
??PreparedStatement ps = null;
??ResultSet rs = null;
??ResultSetMetaData rsm = null;
??try {
???ps = con.prepareStatement(SQL);
???if (SQL == null) {
????throw new Exception();
???}

???ps.setString(1, id);

???rs = ps.executeQuery();
???rsm = rs.getMetaData();
???if (rs.next()) {
????Map entity = new HashMap();
????for (int i = 1; i <= rsm.getColumnCount(); i++) {
?????String columnName = rsm.getColumnName(i).toLowerCase();
?????Object columnValue = getObjectValue(rs, voclass, i, columnName);
?????entity.put(columnName, columnValue);
????}
????if (voclass != null) {
?????po = voclass.newInstance();
?????BeanUtils.populate(po, entity);
????}
????
???}
??} catch (Exception e) {
???log.error(e);
???throw e;
??} finally {
???try {
????if (rs != null) {
?????rs.close();
????}
???} catch (Exception e) {

???}
???try {
????if (ps != null) {
?????ps.close();
????}
???} catch (Exception e) {

???}
??}
??return (BaseVO) po;
?}

?/**
? * 選擇記錄,不分頁
? * @param con
? * @param SQL
? * @param params
? * @param voclass
? * @return
? * @throws Exception
? * @author
? */
?public List select(Connection con, String SQL, List params, Class voclass)
???throws Exception {
??Object vo = null; // 承載值對象
??PreparedStatement ps = null;
??ResultSet rs = null;
??ResultSetMetaData rsm = null;
??List relist = null;
??try {
???ps = con.prepareStatement(SQL);
???if (SQL == null) {
????throw new Exception();
???}
???if (params != null && params.size() > 0) {
????int count = 0;
????for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
?????Object object = iterator.next();
?????setObjectValue(ps, count + 1, object);
????}
???}
???rs = ps.executeQuery();
???rsm = rs.getMetaData();
???relist = new ArrayList();
???while (rs.next()) {
????Map entity = new HashMap();
????for (int i = 1; i <= rsm.getColumnCount(); i++) {
?????String columnName = rsm.getColumnName(i).toLowerCase();
?????Object columnValue = getObjectValue(rs, voclass, i, columnName);
?????entity.put(columnName, columnValue);
?????
????}
????if (voclass != null) {
?????vo = voclass.newInstance();
?????BeanUtils.populate(vo, entity);
?????relist.add(vo);
????} else {
?????relist.add(entity);
????}
???}
??} catch (Exception e) {
???log.error(e);
???throw e;
??} finally {
???try {
????if (rs != null) {
?????rs.close();
????}
???} catch (Exception e) {

???}
???try {
????if (ps != null) {
?????ps.close();
????}
???} catch (Exception e) {

???}
???
??}
??return relist;
?}

?/**
? * 分頁查詢
? *
? * @param con
? * @param SQL
? * @param params
? * @param voclass
? * @param pagination
? * @return
? * @throws Exception
? * @author
? */
?public List selectPagination(Connection con, String SQL, List params,
???Class voclass, Pagination pagination) throws Exception {
??if (SQL == null) {
???throw new NullPointerException("SQL不能為空!");
??}
??if (pagination == null) {
???throw new NullPointerException("分頁類不能為空!");
??}

??// TODO Auto-generated method stub
??Object vo = null; // 承載值對象
??PreparedStatement ps = null;
??ResultSet rs = null;
??ResultSetMetaData rsm = null;
??List relist = null;
??try {

???ps = con.prepareStatement("select count(1) as count_ from ( " + SQL + " )");

???if (params != null && params.size() > 0) {
????int count = 0;
????for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
?????Object object = iterator.next();
?????setObjectValue(ps, count + 1, object);
????}
???}

???rs = ps.executeQuery();
???if (rs.next()) {
????pagination.setTotalCount(rs.getInt(1));
????
???}
???

???if (pagination.getTotalCount() > 0) {
????/* 組成分頁內容 */
????StringBuffer pagingSelect = new StringBuffer(100);
????pagingSelect
??????.append("select * from ( select row_.*, rownum rownum_ from ( ");
????pagingSelect.append(SQL);
????pagingSelect
??????.append(" ) row_ where rownum <= ?) where rownum_ > ?");

????ps = con.prepareStatement(pagingSelect.toString());
????int count = 0;
????if (params != null && params.size() > 0) {
?????for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
??????Object object = iterator.next();
??????setObjectValue(ps, count + 1, object);
?????}
????}
????
????ps.setInt(count + 1, pagination.getPage()
??????* pagination.getCount());
????ps.setInt(count + 2, (pagination.getPage() - 1)
??????* pagination.getCount());

????log.info("pagination.getPage():" + pagination.getPage());
????log.info("pagination.getCount():" + pagination.getCount());
????rs = ps.executeQuery();
????rsm = rs.getMetaData();
????relist = new ArrayList();
????while (rs.next()) {
?????Map entity = new HashMap();
?????for (int i = 1; i <= rsm.getColumnCount(); i++) {
??????String columnName = rsm.getColumnName(i).toLowerCase();
??????Object columnValue = getObjectValue(rs, voclass, i, columnName);
??????entity.put(columnName, columnValue);
?????}
?????if (voclass != null) {
??????vo = voclass.newInstance();
??????BeanUtils.populate(vo, entity);
??????relist.add(vo);
?????} else {
??????relist.add(entity);
?????}
????}
???}
??} catch (Exception e) {
???log.error(e);
???throw e;
??} finally {
???try {
????if (rs != null) {
?????rs.close();
????}
???} catch (Exception e) {

???}
???try {
????if (ps != null) {
?????ps.close();
????}
???} catch (Exception e) {

???}

??}
??return relist;
?}

?/**
? * 獲得SequenceValue
? * @param sequenceName
? * @return
? * @throws Exception
? * @author
? */
?public Long getSequenceValue(Connection con, String sequenceName)throws Exception {
??PreparedStatement ps = null;
??ResultSet rs = null;
??Long sequenceValue = null;
??try{
???ps = con.prepareStatement("select " + sequenceName + ".nextval from dual");
???rs = ps.executeQuery();
???if(rs.next()) {
????sequenceValue = new Long(rs.getLong(1));
???}
??}catch(Exception e){
???log.error(e);
???throw e;
??}finally{
???try {
????if (rs != null) {
?????rs.close();
????}
???} catch (Exception e) {

???}
???try {
????if (ps != null) {
?????ps.close();
????}
???} catch (Exception e) {

???}
??}
??return sequenceValue;
?}
?/**
? * 把對象傳入數據庫
? * @param ps
? * @param count
? * @param object
? * @author
? */
?private final void setObjectValue(PreparedStatement ps, int count, Object object) throws Exception {
??log.debug("count is " + count + " object is " + object);
??if(object != null) {
???if(object instanceof Integer){
????ps.setInt(count, ((Integer)object).intValue());
???}else if(object instanceof Long) {
????ps.setLong(count, ((Long)object).longValue());
???}else if(object instanceof BigDecimal){
????ps.setBigDecimal(count, (BigDecimal)object);
???}else if(object instanceof String){
????ps.setString(count, (String)object);
???}else if(object instanceof java.util.Date) {
????if(object!=null){
?????long time = ((java.util.Date)object).getTime();
?????ps.setDate(count, new java.sql.Date(time));
????}else{
?????ps.setDate(count, null);
????}
???}else{
????ps.setObject(count, object);
???}
??}else{
???ps.setNull(count, Types.INTEGER);
??}
?}

?/**
? * 把對象傳入數據庫
? * @param ps
? * @param clazz
? * @param count
? * @param columnName
? * @param object
? * @throws Exception
? * @author
? */
?private final void setObjectValue(PreparedStatement ps, Class clazz, int count,
???String columnName, Object object)throws Exception {
??log.debug("count is " + count + " columnName is " + columnName + " object is " + object);
??String classType = clazz.getDeclaredField(columnName).getType().getName();
??if(classType.equals("java.lang.Integer")){
???if(object != null) {
????ps.setInt(count, ((Integer)object).intValue());
???}else{
????ps.setNull(count, Types.INTEGER);
???}
??}else if(classType.equals("java.lang.Long")) {
???if(object != null ) {
????ps.setLong(count, ((Long)object).longValue());
???}else{
????ps.setNull(count, Types.INTEGER);
???}
??}else if(classType.equals("java.math.BigDecimal")){
???if(object != null) {
????ps.setBigDecimal(count, (BigDecimal)object);
???}else{
????ps.setNull(count, Types.NUMERIC);
???}
??}else if(classType.equals("java.lang.String")){
???if(object != null) {
????ps.setString(count, (String)object);
???}else{
????ps.setString(count, null);
???}
??}else if(classType.equals("java.util.Date")) {
???if(object!=null){
????long time = ((java.util.Date)object).getTime();
????ps.setDate(count, new java.sql.Date(time));
???}else{
????ps.setDate(count, null);
???}
??}else{
???ps.setObject(count, object);
??}
?}

?/**
? * 把數據從數據取出來
? * @param rs
? * @param clazz
? * @param count
? * @param columnName
? * @return
? * @throws Exception
? * @author
? */
?private final Object getObjectValue(ResultSet rs, Class clazz, int count, String columnName) throws Exception {
??Object fieldValue = null;
??log.debug("columnName is " + columnName + " count is " + count);
??if(columnName != null) {
???if("rownum".equals(columnName)) {
????fieldValue = new Long(rs.getLong(count));
???}else if("rownum_".equals(columnName)) {
????fieldValue = new Long(rs.getLong(count));
???}else if("count_".equals(columnName)) {
????fieldValue = new Long(rs.getLong(count));
???}else{
????String classType = clazz.getDeclaredField(columnName).getType().getName();

????if(classType.equals("java.lang.Integer")){
?????fieldValue =new Integer( rs.getInt(count));
????}else if(classType.equals("java.lang.Long")) {
?????fieldValue =new Long( rs.getLong(count));
????}else if(classType.equals("java.math.BigDecimal")){
?????fieldValue = rs.getBigDecimal(count);
????}else if(classType.equals("java.lang.String")){
?????fieldValue = rs.getString(count);
????}else if(classType.equals("java.util.Date")) {
?????java.sql.Date date = rs.getDate(count);
?????if(date!= null){
??????fieldValue = new java.util.Date(date.getTime());
?????}
????}else{
?????fieldValue = rs.getString(count);
????}
???}
??}
??return fieldValue;
?}

}