package util;

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.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

public class Jdbc {

    private static ResourceBundle res = ResourceBundle.getBundle("jdbc");
    private static String url = res.getString("db.url");
    private static String user = res.getString("db.user");
    private static String psw = res.getString("db.password");
    private static String driverClass = res.getString("driverClass");
    private static Integer poolSize = Integer.parseInt(res.getString("poolSize"));
    private static LinkedList<Connection> connections = new LinkedList<Connection>();  
    
    private Connection conn;
    private ResultSet rs;
    private ResultSetMetaData md;
    private PreparedStatement ps;
    private List<Map<String,Object>> list;
    private Map<String,Object> map;
    
    static{
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        initConnPool();
    }
    //初始化连接池
    private static void initConnPool() {
        for (int i = 0; i < poolSize ; i++) {
            try {
                Connection conn = DriverManager.getConnection(url, user, psw);
                connections.add(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }    
    }
    
    //拿出一个连接
    private synchronized void getConnection() {
        conn = connections.removeFirst();
    }
    
    //放回一个连接
    private synchronized void backConnection(Connection connection,PreparedStatement ps,ResultSet rs) {
        try {
            if(ps!=null) ps.close();
            if(rs!=null) rs.close();
            connections.addLast(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }

    //执行数据查询语句
    public List<Map<String,Object>> dql(String sql,Object...objs){
        getConnection();
        list = new ArrayList<Map<String,Object>>();
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= objs.length; i++) {
                ps.setObject(i, objs[i]);
            }
            rs = ps.executeQuery();
            md = rs.getMetaData();
            while (rs.next()) {
                map = new HashMap<String,Object>();
                for (int i = 1; i <= md.getColumnCount(); i++) {
                    String key = md.getColumnName(i);
                    Object value = rs.getObject(i);
                    map.put(key, value);
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            backConnection(conn,ps,rs);
        }
        return list;
    }
    
    //执行数据管理语句
    public boolean dml(String sql,Object...objs){
        getConnection();
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= objs.length; i++) {
                ps.setObject(i, objs[i]);
            }
            return ps.executeUpdate()==1?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            backConnection(conn,ps,rs);
        }
        return false;
    }
    
}