package com.foc.util;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.dbutils.QueryRunner;

import com.foc.conf.GConstants;
/**
 * 数据处理者
 * @author panjianlin
 *
 */
public class DBHandler {    

    public int[] batchInsert(String tableName, List<Map<String, Object>> columnMapList){
        
        InsertBean insertBean = new InsertBean();
        insertBean.setTableName(tableName);
        QueryRunner run = null;
        int[] inserts = null;
        try {
            run = new QueryRunner(DBHelper.getDataSource());
            insertBean.params = new Object[columnMapList.size()][];     
            
            boolean isSqlAppend = true;
            
            for(Map<String,Object> columnMap : columnMapList){
                
                if(isSqlAppend){
                    this.appendColumnSql(insertBean,columnMap);    
                    isSqlAppend = false;
                }
                
                this.setColumnValues(insertBean,columnMap);
                                                
            }
            
            String sql = this.appendInsertSql(insertBean);
            
            inserts = run.batch(sql,insertBean.params);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return inserts;
    }
    
    public int update(String tableName, Map<String, Object> columnMap, Map<String, Object> whereMap){
        
        UpdateBean updateBean = new UpdateBean();
        updateBean.setTableName(tableName);
        QueryRunner run = null;
        int update = 0;
        try {
            run = new QueryRunner(DBHelper.getDataSource());
            this.appendColumnSql(updateBean,columnMap);                    
//            this.setColumnValues(updateBean,columnMap);
            this.appendWhereSql(updateBean, whereMap);            
            String sql = this.appendUpdateSql(updateBean);
            this.setParams(updateBean);
            if(GConstants.DEBUG){
                
                System.out.println("[UPDATE][SQL]"+sql+" ");
            }
//            for (Object o:updateBean.params) {
//                
//                System.out.println(o);
//            }
//            System.exit(0);
            update = run.update(sql, updateBean.params.toArray());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return update;
    }
    
//       int updates = run.update("UPDATE Person SET height=? WHERE name=?",
//                2.05, "John Doe" );
//        
//        int inserts = run.update("INSERT INTO Person (name,height) VALUES (?,?)",
//                "John Doe", 1.82 );
    
    private void setParams(UpdateBean updateBean) {
        
        updateBean.params.addAll(updateBean.columnValues);
        updateBean.params.addAll(updateBean.whereValues);
    }

    private String appendUpdateSql(UpdateBean updateBean) {
        if(updateBean.whereAppend.length()>0){
            
            updateBean.updateSql += UpdateBean.UPDATEHEAD + updateBean.tableName 
                    + UpdateBean.SET + updateBean.columnAppend.toString()
                    + UpdateBean.WHERE + updateBean.whereAppend.toString();
        } else{
            
            updateBean.updateSql += UpdateBean.UPDATEHEAD + updateBean.tableName 
                    + UpdateBean.SET + updateBean.columnAppend.toString();
        }
        
//        System.out.println(updateBean.updateSql);
        return updateBean.updateSql;
    }
    

//    private void setColumnValues(UpdateBean updateBean, Map<String, Object> columnMap) {
//        for(Entry<String, Object> columnEntry : columnMap.entrySet()){
//            updateBean.columnValues.add(columnEntry.getValue());
//        }  
//    }

 
    
    private void appendColumnSql(UpdateBean updateBean, Map<String, Object> columnMap) {
        int index = 0;
        for(Entry<String, Object> columnEntry : columnMap.entrySet()){

            if((index + 1) != columnMap.size()){
                
                updateBean.columnAppend.append(columnEntry.getKey()).append("=?, ");
            }else{
                
                updateBean.columnAppend.append(columnEntry.getKey()).append("=?");
            }
            updateBean.columnValues.add(columnEntry.getValue());
            index++;
        }
        
    }
    
    private void appendWhereSql(UpdateBean updateBean, Map<String, Object> whereMap) {
        int index = 0;
        for(Entry<String, Object> whereEntry : whereMap.entrySet()){
            
//            if(index == 0){
//                updateBean.columnAppend.append("(");
//                updateBean.valueAppend.append("(");
//            }                
                        
            if(index == 0){
//                updateBean.valueAppend.append("?, ");    
                updateBean.whereAppend.append(whereEntry.getKey()).append("=?");
            }else{
//                updateBean.valueAppend.append("?");
                updateBean.whereAppend.append(" and ").append(whereEntry.getKey()).append("=?");
                
            }    
            updateBean.whereValues.add(whereEntry.getValue());
            index++;
        }
        
    }

    private String appendInsertSql(InsertBean insertBean) {
        insertBean.insertSql += insertBean.insertHead + insertBean.tableName + " " + insertBean.columnAppend.toString() + InsertBean.VALUES + insertBean.valueAppend.toString();
//        System.out.println(insertBean.insertSql);
        return insertBean.insertSql;
    }



    private void setColumnValues(InsertBean insertBean, Map<String, Object> columnMap) {        
        List<Object> columnValues = new ArrayList<Object>();
        for(Entry<String, Object> columnEntry : columnMap.entrySet()){
            columnValues.add(columnEntry.getValue());
        }    
        insertBean.params[insertBean.insertIndex++] = columnValues.toArray();
        
    }

    private void appendColumnSql(InsertBean insertBean, Map<String, Object> columnMap) {
        int index = 0;
        for(Entry<String, Object> columnEntry : columnMap.entrySet()){
            
            if(index == 0){
                insertBean.columnAppend.append("(");
                insertBean.valueAppend.append("(");
            }                
            
            
            if((index + 1) != columnMap.size()){
                insertBean.valueAppend.append("?, ");    
                insertBean.columnAppend.append(columnEntry.getKey()).append(", ");
            }else{
                insertBean.valueAppend.append("?");
                insertBean.columnAppend.append(columnEntry.getKey());
            }
                    
            
            index++;
        }
        insertBean.columnAppend.append(")");    
        insertBean.valueAppend.append(")");
    }
    
    class InsertBean{
        private String insertSql = "";
        private String insertHead = "INSERT INTO ";
        private String tableName;
        private static final String VALUES = " VALUES ";
        private StringBuffer columnAppend = new StringBuffer();
        private StringBuffer valueAppend = new StringBuffer();
        private Object[][] params = null;
        private int insertIndex = 0;
        
        public String getTableName() {
            return tableName;
        }
        public void setTableName(String tableName) {
            this.tableName = tableName;
        }                
    }
    
    class UpdateBean{
        private String updateSql = "";
        private static final String UPDATEHEAD = "UPDATE ";
        private String tableName;
        private static final String WHERE = " WHERE ";
        private StringBuffer columnAppend = new StringBuffer();
        private StringBuffer whereAppend = new StringBuffer();
        private List<Object> params = new ArrayList<Object>();
        private List<Object> columnValues = new ArrayList<Object>();
        private List<Object> whereValues = new ArrayList<Object>();
        private static final String SET = " SET ";
        
        public String getTableName() {
            return tableName;
        }
        public void setTableName(String tableName) {
            this.tableName = tableName;
        }                
    }
}


