package persistence;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * Created by IntelliJ IDEA.
 * User: Administrator
 * Date: 2006-2-5
 * Time: 13:17:47
 * To change this template use File | Settings | File Templates.
 */

public class DataBaseConnection {
	public static String dbDriver ="com.microsoft.jdbc.sqlserver.SQLServerDriver";
    //public static String dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
     public static String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=zhym";
   // public static String url = "jdbc:odbc:leechdom";
    public static String userName = "sa";
    public static String password = "";
    public Connection con = null;
    public Statement stmt = null;
    public ResultSet rs = null;

    //加載驅動程序
    public DataBaseConnection() {
        try {
            Class.forName(dbDriver).newInstance();
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("classForNameError!");
        }
    }

    //創建數據庫連接
    public boolean createConnection() {
        try {
            con = DriverManager.getConnection(url, userName, password);
            con.setAutoCommit(false);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("createConnectionError!");
        }
        return true;
    }

    //進行數據庫增，刪除，更新操作
    public boolean executeUpdate(String sql) {
        if (con == null) createConnection();
        try {
            stmt = con.createStatement();
            int iCount = stmt.executeUpdate(sql);
            System.out.println("操作成功，所影響的記錄數為" + String.valueOf(iCount));
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("executeUpdateError!");
        }
        return true;
    }

    public boolean executeUpdateBatch(List list) {
        if (con == null) createConnection();
        try {
        stmt = con.createStatement();
        for (int i = 0; i < list.size(); ++i) {
            stmt.addBatch((String) list.get(i));
        }
        int i[] = stmt.executeBatch();
        System.out.println("操作成功，影響的記錄數為：" + i.length);
        }catch(SQLException e){
            System.out.println(e.getMessage());
            System.out.println("executeBatchError");
        }
        return true;
    }

    //進行數據庫查詢操作
    public ResultSet executeQuery(String sql) {
        try {
            if (con == null) createConnection();
            stmt = con.createStatement();
            try {
                rs = stmt.executeQuery(sql);
            } catch (SQLException e) {
                System.out.println(e.getMessage());
                return null;
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("executeQueryError!");
            return null;
        }
        return rs;
    }

    public List executeQueryForList(String s) throws SQLException {

        ResultSet rs=this.executeQuery(s);
        List list = new ArrayList();
        HashMap hash = new HashMap();
        String tmp = null;
        int cols = rs.getMetaData().getColumnCount();

        while (rs.next()) {
            hash = new HashMap();
            for (int i = 1; i <= cols; i++) {
                tmp = rs.getString(i);
                if (tmp == null)
                    tmp = "";
            hash.put(rs.getMetaData().getColumnName(i).toLowerCase(),tmp);
            }
            list.add(hash);
            hash = null;
        }
        return list;
    }

    public HashMap executeQueryForMap(String s) throws SQLException {

        ResultSet rs=this.executeQuery(s);
        HashMap hash = new HashMap();
        String tmp = null;
        int cols = rs.getMetaData().getColumnCount();
        while (rs.next()) {
            hash = new HashMap();
            for (int i = 1; i <= cols; i++) {
                tmp = rs.getString(i);
                if (tmp == null)
                    tmp = "";
            hash.put(rs.getMetaData().getColumnName(i).toLowerCase(),tmp);
            }
        }
        return hash;
    }

    public ResultSet executeQueryForPage(String sql) {
        try {
            if (con == null) createConnection();
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            try {
                rs = stmt.executeQuery(sql);
            } catch (SQLException e) {
                System.out.println(e.getMessage());
                return null;
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("executeQueryError!");
            return null;
        }
        return rs;
    }

    //對數據庫操作進行提交
    public boolean commit() {
        try {
            con.commit();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("commintError!");
        }
        return true;
    }

    //關閉數據庫連接
    public void closeDBConnection() {
        if (con != null) {
            try {
                stmt.close();
                con.close();
            } catch (SQLException e) {
                System.out.println("Failed to close connection!");
                e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.

            } finally {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
                con = null;
            }
        }
    }
}