package com.zhel.jdbc1.jdbcUtil;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;


public class JdbcUtilsDao {
    private static String USERNAME = "";
    private static String PASSWORD = "";
    private static String DRIVER = "";
    private static String URL = "";
    private Connection connection;
    private PreparedStatement pstmt;
    private ResultSet resultSet;

    /**
     * 构造方法，读取配置文件，并加载驱动
     */
    public JdbcUtilsDao() throws ClassNotFoundException, IOException {
        // 读取db.properties文件中的数据库连接信息
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("db.properties");
        Properties prop = new Properties();
        prop.load(in);

        USERNAME = prop.getProperty("jdbc.username");
        PASSWORD = prop.getProperty("jdbc.password");
        DRIVER = prop.getProperty("jdbc.driver");
        URL = prop.getProperty("jdbc.url");
        
        Class.forName(DRIVER);
    }

    /**
     * 获得数据库的连接
     */
    public Connection getConnection() throws SQLException {
        this.connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        return connection;
    }

    /**
     * 释放数据库
     */
    private void release() throws SQLException {
        if (resultSet != null) {
            resultSet.close();
        }
        if (pstmt != null) {
            pstmt.close();
        }
    }
    
    
    /**
     * 关闭数据库
     */
    public void closeConn() throws SQLException {
        this.connection.close();
    }

    /**
     * 增加、删除、改 操作<br/>
     * 
     * @param sql 使用占位符的 sql 语句
     * @param params
     */
    public int update(String sql, List<Object> params) throws SQLException {
        int result = -1;
        pstmt = connection.prepareStatement(sql);
        int index = 1;
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        result = pstmt.executeUpdate();
        this.release();
        return result;
    }

    /**
     * 查询单条或单列记录
     * 
     * @param sql 使用占位符的 sql 语句
     * @param params
     */
    public Map<String, Object> querySimple(String sql, List<Object> params) throws SQLException {
        Map<String, Object> map = new HashMap<String, Object>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        
        ResultSetMetaData metaData = resultSet.getMetaData(); // 查询结果的系统信息类
        int col_len = metaData.getColumnCount();
        while (resultSet.next()) {
            for (int i = 0; i < col_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
        }
        this.release();
        return map;
    }

    /**
     * 查询多条记录
     * 
     * @param sql 使用占位符的 sql 语句
     * @param params
     */
    public List<Map<String, Object>> queryComplex(String sql, List<Object> params) throws SQLException {
        List<Map<String, Object>> list = new LinkedList<Map<String, Object>>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        
        ResultSetMetaData metaData = resultSet.getMetaData(); // 查询结果的系统信息类
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
            list.add(map);
        }
        this.release();
        return list;
    }

    /**
     * 通过反射机制查询单条或单列记录
     * 
     * @param sql 使用占位符的 sql 语句
     * @param params
     * @param cls
     */
    public <T> T querySimpleRef(String sql, List<Object> params, Class<T> cls) throws SQLException,
            InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
        T resultObject = null;
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        
        ResultSetMetaData metaData = resultSet.getMetaData(); // 查询结果的系统信息类
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            resultObject = cls.newInstance(); // 通过反射机制创建一个实例
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name.toLowerCase());
                if (cols_value == null) {
                    cols_value = "";
                }
                Field field = cls.getDeclaredField(cols_name);
                field.setAccessible(true); // 打开javabean的访问权限
                field.set(resultObject, cols_value);
            }
        }
        this.release();
        return resultObject;
    }

    /**
     * 通过反射机制查询多条记录
     * 
     * @param sql 使用占位符的 sql 语句
     * @param params
     * @param cls
     */
    public <T> List<T> queryComplexRef(String sql, List<Object> params, Class<T> cls) throws SQLException,
            InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
        List<T> list = new LinkedList<T>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        
        ResultSetMetaData metaData = resultSet.getMetaData();  // 查询结果的系统信息类
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            T resultObject = cls.newInstance(); // 通过反射机制创建一个实例
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name.toLowerCase());
                if (cols_value == null) {
                    cols_value = "";
                }
                Field field = cls.getDeclaredField(cols_name);
                field.setAccessible(true); // 打开javabean的访问权限
                field.set(resultObject, cols_value);
            }
            list.add(resultObject);
        }
        this.release();
        return list;
    }

}