package com.foc.util;

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
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.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.xml.sax.EntityResolver;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

import com.alibaba.druid.pool.DruidDataSource;

public class DBHelper {

    private final static Map<String, DruidDataSource> POOL = new HashMap<String, DruidDataSource>(6);;
    private static String defaultName = null;
    private final static Object object = new Object();
    
    private final static DBHelper factory = new DBHelper();
    
    public DBHelper(){
        init();
    }
    
    public static DBHelper getInstance() {
        
        return factory;
    }
    /**
     * 初始化数据库
     * @throws FileNotFoundException
     * @throws DocumentException
     */
    @SuppressWarnings("unchecked")
    private static void init(){
        
        InputStream in = null;
        SAXReader reader = null;
        Document doc = null;
        Element root = null;
        List<Element> items = null;
        List<Element> entris = null;
        try {
            
            reader = new SAXReader();
            File config = new File("jdbc.xml");
            
            if (config.exists() && config.canRead()) {
                in = new FileInputStream(config);
                in = new BufferedInputStream(in);
            } else {
                in = Thread.currentThread().getContextClassLoader()
                        .getResourceAsStream("jdbc.xml");
                in = new BufferedInputStream(in);
            }
            
            reader.setValidation(false);
            reader.setEntityResolver(new EntityResolver() {
                public InputSource resolveEntity(String publicId, String systemId)
                        throws SAXException, IOException {
                    File dtd = new File("properties.dtd");
                    InputSource source = null;
                    if (dtd.exists() && dtd.canRead()) {
                        InputStream ins = new FileInputStream(dtd);
                        ins = new BufferedInputStream(ins);
                        source = new InputSource(ins);
                        source.setPublicId(publicId);
                        source.setSystemId(systemId);
                    } else {
                        source = new InputSource(new ByteArrayInputStream(""
                                .getBytes("UTF-8")));
                    }
                    return source;
                }
            });
            doc = reader.read(in);
            root = doc.getRootElement();
            items = root.elements("properties");
            
            for (Element item : items) {
                
                String key = null;
                String name = null;
                String url = null;
                DruidDataSource dataSource = null;
                
                entris = item.elements("entry");
                dataSource = new DruidDataSource();
                dataSource.setMaxActive(20);
                dataSource.setMaxWait(6000L);
                dataSource.setLogAbandoned(true);
                dataSource.setRemoveAbandoned(true);
                dataSource.setPoolPreparedStatements(true);
                dataSource.setMaxOpenPreparedStatements(100);
                dataSource.setTestWhileIdle(false);
                
                for (Element entry : entris) {
                    key = entry.attributeValue("key");
                    if (key.equals("driverClassName")) {
                        dataSource.setDriverClassName(entry.getTextTrim());
                    } else if (key.equals("url")) {
                        url = entry.getTextTrim();
                        dataSource.setUrl(entry.getTextTrim());
                    } else if (key.equals("username")) {
                        dataSource.setUsername(entry.getTextTrim());
                    } else if (key.equals("password")) {
                        dataSource.setPassword(entry.getTextTrim());
                    } else if (key.equals("initialSize")) {
                        dataSource.setInitialSize(Integer.parseInt(entry
                                .getTextTrim()));
                    } else if (key.equals("maxActive")) {
                        dataSource.setMaxActive(Integer.parseInt(entry
                                .getTextTrim()));
                    } else if (key.equals("minIdle")) {
                        dataSource.setMinIdle(Integer.parseInt(entry
                                .getTextTrim()));
                    } else if (key.equals("defaultAutoCommit")) {
                        dataSource.setDefaultAutoCommit(Boolean.valueOf(
                                entry.getTextTrim()).booleanValue());
                    } else if (key.equals("defaultReadOnly")) {
                        dataSource.setDefaultReadOnly(Boolean.valueOf(
                                entry.getTextTrim()).booleanValue());
                    } else if (key.equals("defaultTransactionIsolation")) {
                        dataSource.setDefaultTransactionIsolation(Integer
                                .parseInt(entry.getTextTrim()));
                    } else if (key.equals("defaultCatalog")) {
                        dataSource.setDefaultCatalog(entry.getTextTrim());
                    } else if (key.equals("maxWait")) {
                        dataSource.setMaxWait(Long.parseLong(entry
                                .getTextTrim()));
                    } else if (key.equals("testOnBorrow")) {
                        dataSource.setTestOnBorrow(Boolean.valueOf(
                                entry.getTextTrim()).booleanValue());
                    } else if (key.equals("testOnReturn")) {
                        dataSource.setTestOnReturn(Boolean.valueOf(
                                entry.getTextTrim()).booleanValue());
                    } else if (key.equals("timeBetweenEvictionRunsMillis")) {
                        dataSource.setTimeBetweenEvictionRunsMillis(Long
                                .parseLong(entry.getTextTrim()));
                    } else if (key.equals("minEvictableIdleTimeMillis")) {
                        dataSource.setMinEvictableIdleTimeMillis(Long
                                .parseLong(entry.getTextTrim()));
                    } else if (key.equals("testWhileIdle")) {
                        dataSource.setTestWhileIdle(Boolean.valueOf(
                                entry.getTextTrim()).booleanValue());
                    } else if (key.equals("validationQuery")) {
                        dataSource.setValidationQuery(entry.getTextTrim());
                    } else if (key.equals("validationQueryTimeout")) {
                        dataSource.setValidationQueryTimeout(Integer
                                .parseInt(entry.getTextTrim()));
                    } else if (key
                            .equals("accessToUnderlyingConnectionAllowed")) {
                        dataSource
                                .setAccessToUnderlyingConnectionAllowed(Boolean
                                        .valueOf(entry.getTextTrim())
                                        .booleanValue());
                    } else if (key.equals("removeAbandoned")) {
                        dataSource.setRemoveAbandoned(Boolean.valueOf(
                                entry.getTextTrim()).booleanValue());
                    } else if (key.equals("removeAbandonedTimeout")) {
                        dataSource.setRemoveAbandonedTimeout(Integer
                                .parseInt(entry.getTextTrim()));
                    } else if (key.equals("logAbandoned")) {
                        dataSource.setLogAbandoned(Boolean.valueOf(
                                entry.getTextTrim()).booleanValue());
                    } else if (key.equals("poolPreparedStatements")) {
                        dataSource.setPoolPreparedStatements(Boolean.valueOf(
                                entry.getTextTrim()).booleanValue());
                    } else if (key.equals("maxOpenPreparedStatements")) {
                        dataSource.setMaxOpenPreparedStatements(Integer
                                .parseInt(entry.getTextTrim()));
                    } else if (key.equals("connectionProperties")) {
                        dataSource.setConnectionProperties(entry.getTextTrim());
                    } else if ((key.equals("name")) || (key.equals("group"))) {
                        name = entry.getTextTrim();
                        if(defaultName == null){
                            defaultName = name;
                        }
                    }
                }
                System.out.println(name+" => " + url);
                POOL.put(name, dataSource);
            }
        } catch (DocumentException e) {
            // TODO: handle exception
        } catch (FileNotFoundException e) {
            // TODO: handle exception
        } catch (Exception e) {
            // TODO: handle exception
        } finally{
            if(in != null){
                try {
                    in.close();
                } catch (IOException e) {}
            }
        }
    }
    public static DataSource getDataSource() throws SQLException {
        return getDataSource(defaultName);
    }
    public static DataSource getDataSource(String name) throws SQLException {
        synchronized(object){
            return POOL.get(name);
        }
    }
    /**
     * 获取连接
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @return
     * @throws SQLException
     */
    public static Connection getConnection(String name) throws SQLException {
        synchronized(object){
            DataSource dataSource = POOL.get(name);
            Connection connection = dataSource.getConnection();
            if(connection == null){
                throw new SQLException("Unexpected: cannot get connection from pool");
            }
            return connection;
        }
    }
    public static Connection getConnection() throws SQLException {
        
        return getConnection(defaultName);
    }

    /**
     * 关闭链接
     * @time 2015-07-01
     * @author renmb
     * @param conn
     */
    public static void close(Connection conn){
        if(conn == null)
            return ;
        try {
            conn.close();
        } catch (Exception e) {}
    }
    /**
     * 关闭Statement
     * @time 2015-07-01
     * @author renmb
     * @param stmt
     */
    public static void close(Statement stmt){
        if(stmt == null)
            return ;
        try {
            stmt.close();
        } catch (Exception e) {}
    }
    /**
     * 关闭ResultSet
     * @time 2015-07-01
     * @author renmb
     * @param rs
     */
    public static void close(ResultSet rs){
        if(rs == null)
            return ;
        try {
            rs.close();
        } catch (Exception e) {}
    }
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        close(rs);
        close(stmt);
        close(conn);
    }
    
    public static String getString(Object data, String want) {
        if(data == null)
            return want;
        try {
            String val = String.valueOf(data);
            if(val == null || val.trim().isEmpty())
                return want;
            return val.trim();
        } catch (Exception e) {}
        return want;
    }
    /**
     * 预编译SQL
     * @time 2015-07-01
     * @author renmb
     * @param table
     * @param colums
     * @return
     * @throws SQLException
     */
    public static String buildInsertPreparedSQL(String table, String... colums)
            throws SQLException {
        StringBuffer fields = new StringBuffer();
        StringBuffer marks = new StringBuffer();
        fields.append("INSERT INTO `" + table.trim() + "`(");
        marks.append(" VALUES( ");
        if (colums == null || colums.length == 0) {
            throw new SQLException("列不能为空");
        }
        int length = colums.length - 1;
        for (int index = 0; index < length; index++) {
            fields.append("`" + colums[index] + "`, ");
            marks.append("?, ");
        }
        fields.append("`" + colums[length] + "`)");
        marks.append("? )");

        fields.append(marks.toString());
        marks.delete(0, marks.length());
        return fields.toString();
    }
    /**
     * 插入一行数据
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param table
     * @param row
     * @param isGeneratedKey
     * @return
     * @throws SQLException
     */
    public static int insert(String name, String table, Map<String, Object> row, boolean isGeneratedKey){
        if(name == null || table == null || row == null)
            return -1;
        synchronized(object){
            String query = null;
            PreparedStatement ps = null;
            Connection conn = null;
            ResultSet rs = null;
            try {
                conn = getConnection(name);
                query = buildInsertPreparedSQL(table, (String[]) row.keySet()
                        .toArray(new String[row.keySet().size()]));
                if(isGeneratedKey){
                    ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
                } else{
                    ps = conn.prepareStatement(query);
                }
                int index = 1;
                for (String key : row.keySet()) {
                    ps.setObject(index++, row.get(key));
                }
                int count = ps.executeUpdate();
                if(isGeneratedKey){
                    close(conn, ps, rs);
                    return count;
                }
                rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    close(conn, ps, rs);
                    return rs.getInt(1);
                }
            } catch (SQLException e) {
                // TODO: handle exception
            } catch (Exception e) {
                // TODO: handle exception
            } finally{
                
                close(conn, ps, rs);
            }
            return -1;
        }
    }
    /**
     * 插入一行记录
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param table
     * @param data
     * @return
     */
    public static int insert(String name, String table,
            List<Map<String, Object>> data){
        if(name == null || table == null || data == null || data.size() == 0)
            return -1;
        synchronized(object){
            String query = null;
            PreparedStatement ps = null;
            Connection conn = null;
            ResultSet rs = null;
            
            try {
                Set<String> colums = (data.get(0)).keySet();
                
                query = buildInsertPreparedSQL(
                        table, colums.toArray( new String[colums.size()]));
                
                conn = getConnection(name);
                conn.setAutoCommit(false);
                ps = conn.prepareStatement(query);
                int count = 0;
                for (Map<String, Object> record : data) {
                    try {
                        int index = 1;
                        for (String key : colums) {
                            ps.setObject(index++, record.get(key));
                        }
                        count += ps.executeUpdate();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                conn.commit();
                return count;
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                close(conn, ps, rs);
            }
            return -1;
        }
    }
    /**
     * 更新SQL
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param query
     * @return
     */
    public static int update(String name, String query){
        synchronized(object){
            PreparedStatement ps = null;
            Connection conn = null;
            try {
                conn = getConnection(name);
                ps = conn.prepareStatement(query);
                return ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                close(conn, ps, null);
            }
            return -1;
        }
    }
    /**
     * 无返回值的SQL
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param query
     */
    public static boolean execute(String name, String query){
        synchronized(object){
            PreparedStatement ps = null;
            Connection conn = null;
            try {
                conn = getConnection(name);
                ps = conn.prepareStatement(query);
                ps.execute();
                return true;
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                close(conn, ps, null);
            }
            return false;
        }
    }
    /**
     * 获取所有结果
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param query
     * @return
     */
    public static List<Map<String, Object>> fetch(String name, String query){
        
        return fetch(name, query, true);
    }
    /**
     * 获取所有结果
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param query 有返回值的SQL
     * @param byLabel
     * @return
     */
    public static List<Map<String, Object>> fetch(String name, String query, boolean byLabel){
        synchronized(object){
            PreparedStatement ps = null;
            Connection conn = null;
            ResultSet rs = null;
            List<Map<String, Object>> rows = null;
            
            try {
                conn = getConnection(name);
                ps = conn.prepareStatement(query);
                rs = ps.executeQuery();
                rows = new ArrayList<Map<String, Object>>();
                String coluName = null;
                while (rs.next()) {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int cc = rsmd.getColumnCount();
                    Map<String, Object> row = new HashMap<String, Object>();
                    for (int i = 1; i <= cc; i++) {
                        if (byLabel) {
                            coluName = rsmd.getColumnLabel(i);
                        } else {
                            coluName = rsmd.getColumnName(i);
                        }
                        Object value = rs.getObject(coluName);
                        if (value == null) {
                            value = "";
                        }
                        row.put(coluName, value);
                    }
                    rows.add(row);
                }
                return rows;
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                close(conn, ps, rs);
            }
            return null;
        }
    }
    /**
     * 获取首行记录
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param query
     * @return
     */
//    public static Map<String, Object> first(String name, String query){
//        return first(name, query, true);
//    }
    /**
     * 获取首行记录
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param query
     * @param byLabel
     * @return
     */
    public static Map<String, Object> first(String name, String query, boolean byLabel){
        synchronized(object){
            PreparedStatement ps = null;
            Connection conn = null;
            ResultSet rs = null;
            Map<String, Object> row = null;
            try {
                conn = getConnection(name);
                ps = conn.prepareStatement(query);
                rs = ps.executeQuery();
                String coluName = null;
                if (rs.next()) {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int cc = rsmd.getColumnCount();
                    row = new HashMap<String, Object>();
                    for (int i = 1; i <= cc; i++) {
                        if (byLabel) {
                            coluName = rsmd.getColumnLabel(i);
                        } else {
                            coluName = rsmd.getColumnName(i);
                        }
                        Object value = rs.getObject(coluName);
                        if (value == null) {
                            value = "";
                        }
                        row.put(coluName, value);
                    }
                }
                return row;
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                close(conn, ps, rs);
            }
            return null;
        }
    }
    /**
     * 某列的最大值
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param table
     * @param column
     * @return
     */
    public static long max(String name, String table, String column){
        String query = "SELECT MAX(`"+column+"`) AS _maxid FROM "+table;
        
        Map<String, Object> row = first(name, query, true);
        if(row == null)
            return -1L;
        try {
            return (Long) row.get("_maxid");
        } catch (Exception e) {
            // TODO: handle exception
        }
        return -1L;
    }
    /**
     * 某列的最小值
     * @time 2015-07-01
     * @author renmb
     * @param name
     * @param table
     * @param column
     * @return
     */
    public static long min(String name, String table, String column){
        String query = "SELECT MIN(`"+column+"`) AS _minid FROM "+table;
        
        Map<String, Object> row = first(name, query, true);
        if(row == null)
            return -1L;
        try {
            return (Long) row.get("_minid");
        } catch (Exception e) {
            // TODO: handle exception
        }
        return -1L;
    }
    /**
     * 关闭所有连接池数据
     */
    public static synchronized void destroy() {
        synchronized(object){
            DruidDataSource ds = null;
            Set<String> names = POOL.keySet();
            for (String name:names) {
                try {
                    ds = POOL.get(name);
                    ds.close();
                } catch (Exception e) {}
                ds = null;
                POOL.put(name, null);
            }
        }
    }

    public static synchronized void destroy(String name) {
        synchronized(object){
            DruidDataSource ds = null;
            try {
                ds = POOL.get(name);
                ds.close();
            } catch (Exception e) {}
            ds = null;
            POOL.put(name, null);
        }
    }

    public static String getDefaultName() {
        return defaultName;
    }
    
    public static Set<String> getDBList() {
        return POOL.keySet();
    }
}
